Inside the dbms_sqltune Package tasks
SQL tuning work is one of the most time consuming and challenging
tasks faced by Oracle DBAs and application developers and the
Oracle10g SQL Tuning Advisor is intended to
facilitate SQL tuning tasks and to help the DBA find the optimal SQL
execution plan. The SQL Tuning Advisor can
search the SQL cache, the AWR, or user inputs searching for
inefficient SQL statements. The SQL Tuning Advisor is available
through the OEM console, or the dbms_sqltune package can be
invoked manually. Figure 8.3 is a representation of the SQL Tuning
Advisor tool in the OEM console.
Figure 8.3:
Access SQL
Tuning Advisor in the OEM Console.
The Ion tool, which has been provided free with the purchase of
this book, also supports use of the SQL Tuning Advisor and allows the DBA to tune any SQL statement that
is stored in the AWR. Figure 8.4 is a representation of the SQL
Tuning Advisor in the Ion tool.
Figure 8.4:
Access SQL
Tuning Advisor in Ion
The dbms_sqltune package provides the
DBA with a PL/SQL API for using the SQL Tuning Advisor tool. Running the SQL Tuning Advisor using PL/SQL
API includes two steps:
§
Create the SQL tuning task.
§
Execute the SQL tuning task.
There are several options for the creation of an SQL tuning task.
For example, the following process will examine the invocation of a
single SQL statement. The
dbms_sqltune.create_task function can be used
to do the following:
DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := 'SELECT object_type, count(*) FROM ';
my_sqltext := my_sqltext || ' all_objects GROUP BY object_type';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text =>
my_sqltext,
bind_list =>
sql_binds(anydata.ConvertNumber(100)),
user_name => 'DABR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name =>
'sql_tuning_task1');
END;
/
SQL tuning tasks can be created with SQL from the cursor cache, the
AWR views, or previously collected SQL Tuning Sets. The overloaded
dbms_sqltune.create_task functions are
provided to allow the DBA to change the inputs.
After successfully creating a SQL tuning task, the DBA can launch
the SQL Tuning Optimizer to produce
tuning recommendations. Use the
dbms_sqltune.execute_tuning_task procedure to execute
the specified task:
exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task1');
Now, the DBA is ready to review recommendation details produced by
the SQL Tuning Advisor. A query like the
one below can be used to retrieve the SQL analysis results:
select dbms_sqltune.report_tuning_task(‘sql_tuning_task1’) from
dual;
The following is the resulting report.
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK1’)
--------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------
Tuning Task Name : SQL_TUNING_TASK1
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 09/07/2004 16:24:41
Completed at : 09/07/2004 16:24:41
--------------------------------------------------------------------
SQL ID : g2wr3u7s1gtf3
SQL Text: 'SELECT object_type, count(*) FROM
all_objects GROUP BY object_type'
--------------------------------------------------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."OBJ$" and its indices are
stale.
Recommendation
--------------
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS',
The recommendation report contains problem findings and the
corresponding recommendations for fixing them.
The analysis process could consume significant processing times.
Therefore, the dbms_sqltune
package provides an API to manage tuning tasks such as:
§
The interrupt_tuning_task procedure is used to stop the executing task. Any results that have
already been produced will be preserved.
§
The cancel_tuning_task procedure terminates the task that is executing without preserving
its results.
§
The reset_tuning_task procedure is used to stop the running task and reset it to the
initial state.
§
The drop_tuning_task procedure can be used to remove the task from the database.
During tuning analysis, the SQL Tuning Advisor can recommend and automatically create SQL profiles. The
SQL profile is a special object that is used by
the optimizer. The SQL Profile contains auxiliary statistics
specific to a particular SQL statement.
The SQL optimizer uses the information in the SQL profile to adjust the execution plan for the
SQL statement that has the associated SQL profile. SQL profiles are
great for SQL tuning because it is possible to tune SQL statements
without any modification of the application source code or the text
of SQL queries. The
dba_sql_profiles view shows
information about all existing SQL profiles.
The dbms_sqltune package can be used
to manage SQL profiles. The SQL Tuning Advisor can recommend the use of a specific SQL Profile. This SQL
profile can be associated with SQL statements that
are being analyzed by accepting it using
dbms_sqltune.accept_sql_profile:
DECLARE
sqlprofile VARCHAR2(30);
BEGIN
sqlprofile := dbms_sqltune.accept_sql_profile (
task_name => 'sql_tuning_task1',
name => 'sql_profile1');
END;
After the profile is defined, the DBA can alter any stored SQL
profile attributes such as
status
, name
, description
, and
category using
dbms_sqltune.alter_sql_profile. The
category attribute is
used to limit user sessions that can use the particular SQL profile.
There is an initialization parameter called
sqltune_category
, which allows the DBA
to set up the default SQL profile category for the database.
The
dbms_sqltune.drop_sql_profile procedure is used to
remove the SQL profile from the database.
The dbms_sqltune
package also provides a PL/SQL API to work with SQL Tuning Sets (STS). The STS is a database object that contains
one or more SQL statements combined with their execution statistics
and context such as particular schema, application module name, list
of bind variables, etc. The STS also includes a set of basic
execution statistics such as CPU and elapsed times, disk reads and
buffer gets, number of executions, etc.
When creating a STS, the SQL statements can be filtered by different
patterns such as application module name or execution statistics,
such as high disk reads. Once created, STS can be an input source
for the SQL Tuning Advisor.
Typically, the following steps are used to work with STS using the
dbms_sqltune API:
1.
STS is created using the dbms_sqltune.create_sqlset procedure. For example, the following script
can be used to create a STS called SQLSET1:
exec dbms_sqltune.create_sqlset ( ‘SQLSET1’);
2.
STS is loaded from such sources as the AWR, another STS, or
the cursor cache. The following sample PL/SQL block loads STS from
the current cursor cache:
DECLARE
cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN cur FOR
SELECT VALUE(p)
FROM TABLE (dbms_sqltune.select_cursor_cache) p;
dbms_sqltune.load_sqlset(
sqlset_name => 'SQLSET1',
populate_cursor => cur);
END;
/
3.
An SQL tuning task that uses STS as input can be created and
executed like this:
exec dbms_sqltune.create_tuning_task (sqlset_name => ‘SQLSET1’,
task_name => ‘TASK1’);
exec dbms_sqltune.execute_tuning_task (‘TASK1’);
4.
The following syntax can be used to drop a SQL tuning set
when finished:
exec dbms_sqltune.drop_sqlset ( ‘SQLSET1’ );
All SQL tuning sets created in the database by querying the
dba_sqlset , dba_sqlset_binds , dba_sqlset_definitions
, and dba_sqlset_statements views are reviewed. For example, the
dbms_sqltune_show_sts.sql
query below shows the particular SQL statements associated with STS:
SELECT
s.sql_text,
s.cpu_time
FROM
dba_sqlset_statements s,
dba_sqlset a
WHERE
a.name = 'SQLSET1'
AND
s.sqlset_id = a.id
AND
rownum <= 10
ORDER BY
s.cpu_time DESC
Clearly, Oracle10g has introduced a rich set of powerful tools for
the DBA to use to identify and resolve possible performance problems
and while these advisors cannot yet replicate the behavior of a
senior DBA, they promise to get more intelligent with each new
release of Oracle.
SEE CODE DEPOT FOR FULL SCRIPTS
