sql tuning advisor in 10g
Oracle 10g is the release for automation and advisors. Oracle has introduced several new advisors which will make numerous recommendations on how to streamline everything from SGA configuration to individual SQL statements. One of the key advisors in 10g is the SQL Tuning Advisor and we will take a brief look at it in this article. The recommended interface to the SQL Tuning Advisor is OEM (via DBControl or Grid Control), but I'm yet to hear of a site that allows developers to have access to it (note to Oracle: developers write and tune SQL. We would like access to the SQL tuning tools please).
The SQL Tuning Advisor is supported by a number of APIs in the new DBMS_SQLTUNE package and it is this that we shall concentrate on. We will create a poorly performing SQL statement and ask the DBMS_SQLTUNE package to make recommendations on how to improve it. Note that to use DBMS_SQLTUNE, the ADVISOR system privilege is required.
setup
We'll base the examples in this article on the following table. We'll create a table with five times the number of records in DBA_SOURCE and multiple duplicates.
SQL> CREATE TABLE t1 2 NOLOGGING 3 AS 4 SELECT * 5 FROM dba_source 6 , (SELECT * FROM dual CONNECT BY ROWNUM < 5);
Table created.
dbms_sqltune
DBMS_SQLTUNE has many APIs to assist with tuning poorly performing SQL and DML. It enables us to create tuning "tasks" (a task is a statement to be tuned), task "sets" (a group of statements to be tuned) and even enables us to accept Oracle's recommendations in the form of a SQL Profile (an execution plan to be used for future executions of a statement). Oracle will also make recommendations for ways to tune a statement without having to create a profile. The tuning tasks can be manually provided, as with this article, or fetched from either the cursor cache or Automatic Workload Repository (useful for poorly-performing statements that have already been running in our system).
As stated, we are going to ask DBMS_SQLTUNE to tune a poorly performing de-duplication SQL statement for us. We'll wrap this statement in the following procedure. This will enable us to repeatedly execute this statement with different recommendations and inputs. Included in this will be the necessary DBMS_SQLTUNE calls to create a tuning task and execute it. First we'll create the procedure and then we'll look at the various operations within it.
SQL> CREATE PROCEDURE sql_tuning_demo ( 2 hint_in IN VARCHAR2 DEFAULT NULL 3 ) AS 4 5 v_task VARCHAR2(30); 6 v_sql CLOB; 7 8 BEGIN 9 10 /* Assign our de-dupe statement... */ 11 v_sql := ' DELETE /*+ ' || hint_in || ' */ FROM t1 a 12 WHERE a.ROWID > ( SELECT min( b.ROWID ) 13 FROM t1 b 14 WHERE a.owner = b.owner 15 AND a.name = b.name 16 AND a.type = b.type 17 AND a.line = b.line )'; 18 19 /* Drop the task in case we are re-running... */ 20 BEGIN 21 DBMS_SQLTUNE.DROP_TUNING_TASK( 22 task_name => 'sql_tuning_task' 23 ); 24 EXCEPTION 25 WHEN OTHERS THEN -- ORA-13605 26 NULL; 27 END; 28 29 /* Create a SQL Tuning task for our SQL... */ 30 v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( 31 sql_text => v_sql, 32 time_limit => 1, 33 scope => 'COMPREHENSIVE', 34 task_name => 'sql_tuning_task', 35 description => 'Demo of DBMS_SQLTUNE' 36 ); 37 38 /* Execute the task... */ 39 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( 40 task_name => 'sql_tuning_task' 41 ); 42 43 /* We want to run this again... */ 44 ROLLBACK; 45 46 END sql_tuning_demo; 47 /
Procedure created.
We can now take a closer look at what this procedure will do when we execute it.
- Lines 11-17. We store the SQL statement we wish to tune in a CLOB, as this will need to be passed as a parameter to DBMS_SQLTUNE. The statement in question is a standard de-duplication DELETE but with the option to add a hint to force a slow execution (for demonstration purposes only, of course);
- Lines 20-27. Because we wish to run this procedure repeatedly, we need to remove any previous instance of the tuning task that we are about to create. Note that the WHEN OTHERS THEN NULL is a convenience for this demonstration only and should not be used in production code;
- Lines 30-36. We create the tuning task by calling the CREATE_TUNING_TASK API. We pass it the SQL statement to be tuned and a time-limit of just 1 second to execute (for longer-running tuning tasks we can also interrupt and resume the advisor via specific APIs under certain conditions). We also give the task a name for us to identify it and the scope of the task (the function returns a system-generated name if we omit our own task name). We could also pass in bind variable values if the statement used them. Note there are several overloads to this API, depending on the source of the SQL statement (as mentioned previously, this can be the cursor cache or AWR). In this example we are passing a CLOB, but it could also be a SQL_ID from the cursor cache, a begin and end snapshot from AWR or the name of a SQL set (a database object containing a group of statements and their associated workload statistics);
- Lines 39-41. We execute the tuning task, identifying it by the name we created it with; and
- Line 44. We rollback because we wish to execute the procedure more than once during this article.
tuning results
Now we can begin our tuning session and see if Oracle has any recommendations on how we can improve the statement.
SQL> exec sql_tuning_demo;
PL/SQL procedure successfully completed.
The results are available via the REPORT_TUNING_TASK function that returns a CLOB. The findings are split into numerous sections depending on which options we requested (default is ALL available). In our report, we have three sections available to us. The first is a general information section that provides an overview of the tuning task and the statement being tuned. The second is the advisory section, where Oracle offers potential tuning solutions. The third is the explain plan section where we can see the current plan for the statement. We can choose between two levels of reporting (TYPICAL and BASIC; default TYPICAL) and several of the execution plan formats provided by DBMS_XPLAN. The following report uses TYPICAL formats.
SQL> set long 80000
SQL> col recs format a90
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS recs
2 FROM dual;
RECS
------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 1
Completion Status : COMPLETED
Started at : 11/26/2004 16:40:03
Completed at : 11/26/2004 16:40:05
-------------------------------------------------------------------------------
SQL ID : fmfpfhqbrg13w
SQL Text: DELETE /*+ */ FROM t1 a
WHERE a.ROWID > ( SELECT min( b.ROWID )
FROM t1 b
WHERE a.owner = b.owner
AND a.name = b.name
AND a.type = b.type
AND a.line = b.line )
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T1" was not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO')
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3468302729
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 374K| 35M| | 14434 (2)| 00:02:54 |
| 1 | DELETE | T1 | | | | | |
| 2 | HASH JOIN | | 374K| 35M| 33M| 14434 (2)| 00:02:54 |
| 3 | VIEW | VW_SQ_1 | 468K| 27M| | 8280 (2)| 00:01:40 |
| 4 | SORT GROUP BY | | 468K| 16M| 46M| 8280 (2)| 00:01:40 |
| 5 | TABLE ACCESS FULL| T1 | 468K| 16M| | 2105 (1)| 00:00:26 |
| 6 | TABLE ACCESS FULL | T1 | 468K| 16M| | 2105 (1)| 00:00:26 |
-----------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1 row selected.
It's fairly obvious but we forgot to gather statistics on our table. So let's gather statistics using Oracle's recommended statement and execute the tuning task again.
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 ownname => USER, 4 tabname => 'T1', 5 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 6 method_opt => 'FOR ALL COLUMNS SIZE AUTO' 7 ); 8 END; 9 /
PL/SQL procedure successfully completed.
SQL> exec sql_tuning_demo;
PL/SQL procedure successfully completed.
We can now see if Oracle has any further recommendations.
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS recs
2 FROM dual;
RECS
------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 1
Completion Status : COMPLETED
Started at : 11/26/2004 16:40:05
Completed at : 11/26/2004 16:40:06
-------------------------------------------------------------------------------
SQL ID : fmfpfhqbrg13w
SQL Text: DELETE /*+ */ FROM t1 a
WHERE a.ROWID > ( SELECT min( b.ROWID )
FROM t1 b
WHERE a.owner = b.owner
AND a.name = b.name
AND a.type = b.type
AND a.line = b.line )
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
1 row selected.
Incidentally, the information from the report can be found in several new views, some of which are listed as follows (note that XXX can be either DBA or USER). Investigating these can be an exercise for the reader.
- XXX_ADVISOR_LOG;
- XXX_ADVISOR_TASKS;
- XXX_ADVISOR_FINDINGS;
- XXX_ADVISOR_RECOMMENDATIONS;
- XXX_ADVISOR_RATIONALE;
- XXX_SQLTUNE_STATISTICS (and SQLSET equivalent);
- XXX_SQLTUNE_BINDS (and SQLSET set equivalent); and
- XXX_SQLTUNE_PLANS (and SQLSET set equivalent).
sql profiles
We've seen how the DBMS_SQLTUNE package works above, albeit with a very simple example. We ran this example twice and are happy with the results. Yet we've not really tested Oracle at all. Let's create and execute a tuning task again but this time supply a hint to force an inefficient access path (remember the example procedure above included a parameter to "inject" a hint into the SQL statement).
SQL> exec sql_tuning_demo('ORDERED');
PL/SQL procedure successfully completed.
We can now run our report as follows.
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS recs
2 FROM dual;
RECS
------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 1
Completion Status : COMPLETED
Started at : 11/26/2004 16:40:07
Completed at : 11/26/2004 16:40:08
-------------------------------------------------------------------------------
SQL ID : 2jh5b9y9n4c3p
SQL Text: DELETE /*+ ORDERED */ FROM t1 a
WHERE a.ROWID > ( SELECT min( b.ROWID )
FROM t1 b
WHERE a.owner = b.owner
AND a.name = b.name
AND a.type = b.type
AND a.line = b.line )
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation
--------------
Consider accepting the recommended SQL profile.
execute :profile_name := dbms_sqltune.accept_sql_profile(task_name =>
'sql_tuning_task')
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3468302729
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 374K| 36M| | 14202 (2)| 00:02:51 |
| 1 | DELETE | T1 | | | | | |
| 2 | HASH JOIN | | 374K| 36M| 31M| 14202 (2)| 00:02:51 |
| 3 | VIEW | VW_SQ_1 | 444K| 26M| | 8192 (2)| 00:01:39 |
| 4 | SORT GROUP BY | | 444K| 16M| 47M| 8192 (2)| 00:01:39 |
| 5 | TABLE ACCESS FULL| T1 | 444K| 16M| | 2105 (1)| 00:00:26 |
| 6 | TABLE ACCESS FULL | T1 | 444K| 16M| | 2105 (1)| 00:00:26 |
-----------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 1879584134
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 374K| 36M| | 14202 (2)| 00:02:51 |
| 1 | DELETE | T1 | | | | | |
| 2 | HASH JOIN | | 374K| 36M| 21M| 14202 (2)| 00:02:51 |
| 3 | TABLE ACCESS FULL | T1 | 444K| 16M| | 2105 (1)| 00:00:26 |
| 4 | VIEW | VW_SQ_1 | 444K| 26M| | 8192 (2)| 00:01:39 |
| 5 | SORT GROUP BY | | 444K| 16M| 47M| 8192 (2)| 00:01:39 |
| 6 | TABLE ACCESS FULL| T1 | 444K| 16M| | 2105 (1)| 00:00:26 |
-----------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1 row selected.
This time, Oracle has recognised that our forced execution plan is inefficient and has generated a better alternative that we can choose to accept via a SQL Profile. A SQL Profile is essentially a stored set of hints that enable Oracle to "lock down" an execution plan to use for any subsequent matching SQL statements (SQL statements are standardised by stripping whitespace and upper-casing). In this respect SQL Profiles are similar to stored outlines (available since Oracle 8i) which are also stored set of hints. Where they differ, however, is that outlines tend to store hints for specific access paths whereas SQL Profiles store offsets and corrections to some stages of CBO arithmetic. Note, however, that the presence of a stored outline will take priority over a SQL Profile. Information on SQL Profiles can be accessed via the DBA_SQL_PROFILES view.
In our report, Oracle provided the syntax to create a SQL Profile for our example DML statement. We can now execute it. Note that this requires the CREATE ANY SQL PROFILE system privilege (there are also equivalent DROP and ALTER privileges).
SQL> VAR profile_name VARCHAR2(30); SQL> BEGIN 2 :profile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE( 3 task_name => 'sql_tuning_task' 4 ); 5 END; 6 /
PL/SQL procedure successfully completed.
Now we have created our SQL Profile, we can see if Oracle uses it.
SQL> EXPLAIN PLAN FOR 2 DELETE /*+ ORDERED */ FROM t1 a 3 WHERE a.ROWID > ( SELECT min( b.ROWID ) 4 FROM t1 b 5 WHERE a.owner = b.owner 6 AND a.name = b.name 7 AND a.type = b.type 8 AND a.line = b.line );
Explained.
SQL> SELECT plan_table_output 2 FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1879584134
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 374K| 36M| | 14202 (2)| 00:02:51 |
| 1 | DELETE | T1 | | | | | |
|* 2 | HASH JOIN | | 374K| 36M| 21M| 14202 (2)| 00:02:51 |
| 3 | TABLE ACCESS FULL | T1 | 444K| 16M| | 2105 (1)| 00:00:26 |
| 4 | VIEW | VW_SQ_1 | 444K| 26M| | 8192 (2)| 00:01:39 |
| 5 | SORT GROUP BY | | 444K| 16M| 47M| 8192 (2)| 00:01:39 |
| 6 | TABLE ACCESS FULL| T1 | 444K| 16M| | 2105 (1)| 00:00:26 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="OWNER" AND "A"."NAME"="NAME" AND "A"."TYPE"="TYPE"
AND "A"."LINE"="LINE")
filter("A".ROWID>"VW_COL_1")
Note
-----
- SQL profile "SYS_SQLPROF_041126164008223" used for this statement
24 rows selected.
DBMS_XPLAN tells us that Oracle has used the SQL Profile for our statement and we can now be sure of a better execution plan for future runs. The SQL Profile will be available for Oracle to use until it is either dropped (using the DROP_SQL_PROFILE API) or disabled (via the ALTER_SQL_PROFILE API).
cleanup
The DBMS_SQLTUNE package provides APIs to remove the objects we created as follows (we saw the DROP_TUNING_TASK API earlier in this article).
SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_041126164008223');
PL/SQL procedure successfully completed.
further reading
This article has really only scratched the surface of the SQL Tuning Advisor. There is much more to learn about this topic, whether using the DBControl/Grid Control GUIs or the DBMS_SQLTUNE APIs directly. For example, we have not covered SQL Sets in this article at all, nor have we looked at loading in SQL statements via the cursor cache, Automatic Workload Repository or SQL Sets. The Performance Tuning Guide is a good starting place for further information, as is the DBMS_SQLTUNE package reference. The DBMS_SQLTUNE package specification itself (browsed in your favourite IDE or even a sqlplus query against DBA_SOURCE) provides good examples of how to use the SQL Set APIs.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, November 2004
Back to Top