Mit dem Oracle Tuning Advisor bietet Oracle eine kostenpflichtige Zusatzoption, die optimierte Ausführungspläne finden kann und diesen dann für ein bestimmtes Statement festschreiben kann.
Der Tuning Advisor ist ein Teil des Oracle Tuning Packs und somit kostenpflichtig. Wenn man nur Ausführungspläne festschreiben will bietet sich mit Oracle SQL Plan Management eine kostenfreie Alternative. Dafür muss man aber die Statements selber tunen. Im folgenden soll die Arbeit das Tuning-Advisors an einem Beispiel erläutert werden.
Im ersten Schritt wird eine Tuningtask definiert. Hier können folgende Möglichkeiten genutzt werden:
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; /
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; /
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; /
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; /
Nach der Definition der Tuning Tasks müssen diese gestartet werden. Tuning Tasks können sehr lange laufen, daher bietet das Package Funktionen zum Stoppen, Löschen und Fortsetzen von Tuningtasks an.
Starten einer Tuning Task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_sample_tuning_task');-- Interrupt and resume
Unterprechen und neu starten einer 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');
Abbruch einer Tuning Task
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'my_sample_tuning_task');
Reset einer Tuning Task
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'my_sample_tuning_task');
Prüfen des Status einer Tuning Task
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT'; TASK_NAME STATUS ------------------------------ ----------- my_sample_tuning_task COMPLETED 1 row selected.
Die Ausgabe der Ergbnisse erfolg über ein einfaches 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