Oracle SQL TUNING ADVISOR
Oracle allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further.
In tuning mode the optimizer performs the following analysis:
1) Statistics Analysis - The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
2) SQL Profiling - The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as the OPTIMIZER_MODE.
If such improvements are possible the information is stored in an SQL profile.
If accepted this information can then used by the optimizer when running in normal mode.
Unlike a stored outline which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically.
Even so, it's sensible to update profiles periodically. The SQL profiling is not performed when the tuining optimizer is run in limited mode.
3) Access Path Analysis - The optimizer investigates the effect of new or modified indexes on the access path.
It's index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
SQL Structure Analysis - The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance.
The implementation of these suggestions requires human intervention to check their validity.
SQL Tuning Advisor
In order to access the SQL tuning advisor API a user must be granted the ADVISOR privilege:
CONN sys/password AS SYSDBA
GRANT ADVISOR TO scott;
CONN scott/tiger
The first step when using the SQL tuning advisor is to create a new tuning task using the CREATE_TUNING_TASK function.
The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR), the cursor cache, an SQL tuning set or specified manually:
SET SERVEROUTPUT ON
-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 764,
end_snap => 938,
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_AWR_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
-- Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
-- Tuning task created from an SQL tuning set.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'test_sql_tuning_set',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'sqlset_tuning_task',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
-- Tuning task created for a manually specified statement.
DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_task',
description => 'Tuning task for an EMP to DEPT join query.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
If the TASK_NAME parameter is specified it's value is returned as the SQL tune task identifier.
If ommitted a system generated name like "TASK_1478" is returned.
If the SCOPE parameter is set to scope_limited the SQL profiling analysis is omitted.
The TIME_LIMIT parameter simply restricts the time the optimizer can spend compiling the recommendations.
The following examples will reference the last tuning set as it has no external dependancies other than the SCOTT schema.
The NVL in the SQL statement was put in to provoke a reaction from the optimizer.
In addition we can delete the statistics from one of the tables to provoke it even more:
EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP');
With the tuning task defined the next step is to execute it using the EXECUTE_TUNING_TASK procedure:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');
During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:
-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');
-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');
-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');
The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';
TASK_NAME STATUS
------------------------------ -----------
emp_dept_tuning_task COMPLETED
1 row selected.
Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('SYS_AUTO_SPCADV_19292320092012') AS recommendations FROM dual;
SET PAGESIZE 24In this case the output looks like this:
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : emp_dept_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/06/2004 09:29:13
Completed at : 05/06/2004 09:29:15
-------------------------------------------------------------------------------
SQL ID : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE NVL(empno, '0') = :empno
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."EMP" and its indices were not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan.
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan
contains an expression on indexed column "EMPNO". This expression prevents
the optimizer from selecting indices on table "SCOTT"."EMP".
Recommendation
--------------
Rewrite the predicate into an equivalent form to take advantage of indices. Alternatively, create a function-based index on the expression.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality condition or if there is an expression or an implicit data type conversion on the indexed column.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1863486531
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 107 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
-------------------------------------------------------------------------------
1 row selected.
Once the tuning session is over the tuning task can be dropped using the DROP_TUNING_TASK procedure:
exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task');
Managing SQL Profiles
To manage SQL profiles a user needs the following privileges:
CONN sys/password AS SYSDBA
GRANT CREATE ANY SQL PROFILE TO scott;
GRANT DROP ANY SQL PROFILE TO scott;
GRANT ALTER ANY SQL PROFILE TO scott;
CONN scott/tiger
If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the ACCEPT_SQL_PROFILE procedure:
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(20);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => 'emp_dept_tuning_task',
name => 'emp_dept_profile');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
The NAME parameter is used to specify a name for the profile. If it is not specified a system generated name will be used.
The STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an SQL profile can be altered using the ALTER_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.alter_sql_profile (
name => 'emp_dept_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
Existing SQL profiles can be dropped using the DROP_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.drop_sql_profile (
name => 'emp_dept_profile',
ignore => TRUE);
END;
/
The IGNORE parameter prevents errors being reported if the specified profile does not exist.
SQL Tuning Sets
An SQL tuning set is a group of statements along with their execution context. These can be created automatically via Enterprise Manager or manually provided you have the necessary privileges:
CONN sys/password AS SYSDBA
GRANT ADMINISTER ANY SQL TUNING SET TO scott;
CONN scott/tiger
An SQL tuning set is created using the CREATE_SQLSET procedure:
BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'test_sql_tuning_set',
description => 'A test SQL tuning set.');
END;
/
Statements are added to the set using the LOAD_SQLSET procedure which accepts a REF CURSOR of statements retrieved using one of the following pipelined functions:
SELECT_WORKLOAD_REPOSITORY - Retrieves statements from the Automatic Workload Repository (AWR).
SELECT_CURSOR_CACHE - Retrieves statements from the cursor cache.
SELECT_SQLSET - Retrieves statements from another SQL tuning set.
The following are examples of their usage:
-- Load the SQL set from the Automatic Workload Repository (AWR).
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
765, -- begin_snap
766, -- end_snap
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
10) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'test_sql_tuning_set',
populate_cursor => l_cursor);
END;
/
-- Load the SQL set from the cursor cache.
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_cursor_cache (
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
1) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'test_sql_tuning_set',
populate_cursor => l_cursor);
END;
/
-- Create a new set and load it from the existing one.
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
DBMS_SQLTUNE.create_sqlset(
sqlset_name => 'test_sql_tuning_set_2',
description => 'Another test SQL tuning set.');
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_sqlset (
'test_sql_tuning_set', -- sqlset_name
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
NULL) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'test_sql_tuning_set_2',
populate_cursor => l_cursor);
END;
/
The contents of an SQL tuning set can be displayed using the SELECT_SQLSET function:
SELECT *
FROM TABLE(DBMS_SQLTUNE.select_sqlset ('test_sql_tuning_set'));References can be added to a set to indicate its usage by a client using the ADD_SQLSET_REFERENCE function. The resulting reference ID can be used to remove it using the REMOVE_SQLSET_REFERENCE procedure:
DECLARE
l_ref_id NUMBER;
BEGIN
-- Add a reference to a set.
l_ref_id := DBMS_SQLTUNE.add_sqlset_reference (
sqlset_name => 'test_sql_tuning_set',
reference => 'Used for manual tuning by SQL*Plus.');
-- Delete the reference.
DBMS_SQLTUNE.remove_sqlset_reference (
sqlset_name => 'test_sql_tuning_set',
reference_id => l_ref_id);
END;
/
The UPDATE_SQLSET procedure is used to update specific string (MODULE and ACTION) and number (PRIORITY and PARSING_SCHEMA_ID) attributes of specific statements within a set:
BEGIN
DBMS_SQLTUNE.update_sqlset (
sqlset_name => 'test_sql_tuning_set',
sql_id => '19v5guvsgcd1v',
attribute_name => 'ACTION',
attribute_value => 'INSERT');
END;
/
The contents of a set can be trimmed down or deleted completely using the DELETE_SQLSET procedure:
BEGIN
-- Delete statements with less than 50 executions.
DBMS_SQLTUNE.delete_sqlset (
sqlset_name => 'test_sql_tuning_set',
basic_filter => 'executions < 50');
-- Delete all statements.
DBMS_SQLTUNE.delete_sqlset (
sqlset_name => 'test_sql_tuning_set');
END;
/
Tuning sets can be dropped using the DROP_SQLSET procedure:
BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set');
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set_2');
END;
/
Oracle 11g:
-----------
Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week.
The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.
The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects.
The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).
The ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks.
-- Enable
BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
-- Disable
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
It is also indirectly disabled by setting the STATISTICS_LEVEL parameter to BASIC, as this stops automatic statistics gathering by the AWR.
The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package controls the behavior of the SQL tuning advisor. The parameters specifically for the automatic runs include:
ACCEPT_SQL_PROFILES - Automatically accept SQL profiles (default FALSE).
MAX_SQL_PROFILES_PER_EXEC - The maximum number of SQL profiles automatically implemented per run (default 20).
MAX_AUTO_SQL_PROFILES - The maximum number of automatic SQL profiles allowed on the system (default 10000).
The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
ACCEPT_SQL_PROFILES FALSE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES 10000
3 rows selected.
SQL>
The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
END;
/
The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, -- 'TEXT'
level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
section => DBMS_SQLTUNE.section_all, -- 'ALL'
object_id => NULL,
result_limit => NULL);
END;
/
SET LONG 1000000
PRINT :l_report
L_REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner : SYS
Workload Type : Automatic High-Load SQL Workload
Execution Count : 31
Current Execution : EXEC_1_25
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 01/16/2008 22:00:06
Completed at : 01/16/2008 22:00:46
Number of Candidate SQLs : 0
Cumulative Elapsed Time of SQL (s) : 0
-------------------------------------------------------------------------------
SQL>
As you can see from the above report, this was run against a very quiet system, so there were no candidate SQL statements to process. If this were run against a more active system, you might expect the report to contain the following sections:
General information - High-level information about the SQL tuning task.
Summary - A summary of the SQL statements tuned during the task, including the estimated benefit associated with the tuning operation.
Tuning finding - Information about findings, acceptance of the profile, implementation of the profile, and detailed execution statistics for each analyzed statement.
Explain plans - The old and new execution plans for each analyzed statement.
Errors - Any errors encountered during the task.
col STATUS_MESSAGE for a30
col ERROR_MESSAGE for a40
select owner,TASK_NAME,EXECUTION_START,EXECUTION_END,STATUS,STATUS_MESSAGE,ERROR_MESSAGE from dba_advisor_log order by EXECUTION_START;
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('SYS_AUTO_SPCADV_59032226092012') AS recommendations FROM dual;
set serveroutput on
DECLARE
retVal CLOB;
BEGIN
retVal := dbms_auto_sqltune.report_auto_tuning_task(type=>'HTML', result_limit=>20);
dbms_output.put_line(retVal);
END;
/
