Oracle Advisors — tuning made easy
The Oracle Tuning Advisor is a licensed add-on that can find better execution plans and then pin them for a given statement.
Overview
The Tuning Advisor is part of the Oracle Tuning Pack and therefore requires a licence. If all you want is to pin execution plans, Oracle SQL Plan Management is a free alternative — but then you have to tune the statements yourself. The following example walks through how the Tuning Advisor works.
Example
The first step is to define a tuning task. The following options are available:
- a tuning task for a specific statement from the AWR report
- a tuning task for a specific statement from the cursor cache
- a tuning task for a SQL tuning set
- a tuning task for a manually defined SQL statement
Tuning task for a specific statement from the AWR report
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 => 'die_sql_id',
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 for a specific 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 => 'die_sql_id',
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 for a 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 for a manually defined SQL 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)),
--bind_list => null,
user_name => 'scott',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'my_sample_tuning_task',
description => 'my_sample_tuning_task');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/Running the tuning task
Once defined, tuning tasks have to be started. They can run for a long time, so the package offers functions to stop, delete and resume them.
Starting a tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_sample_tuning_task');Interrupting and restarting a tuning task:
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'my_sample_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'my_sample_tuning_task');Cancelling a tuning task:
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'my_sample_tuning_task');Resetting a tuning task:
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'my_sample_tuning_task');Checking the status of a tuning task:
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';
TASK_NAME STATUS
------------------------------ -----------
my_sample_tuning_task COMPLETED
1 row selected.The results are retrieved with a simple SQL statement.
-- Ausgabe der Ergebnisse
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('my_sample_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sample_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/06/2009 12:29:15
Completed at : 05/06/2009 12:29:19
-------------------------------------------------------------------------------
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