|
In der Version 10g sind eine Reihe von Features entstanden, die ein automatisches Tunen von SQL-Statements zulassen.
Im Normalfall muss der kostenbasierte Optimierer möglichst schnell den günstigsten Ausführungsplan bestimmen. Dabei geht er allerdings nur bis zu einer bestimmten Rechentiefe vor, findet also aus allen möglichen Ausführungsplänen unter Umständen nur einen suboptimalen Weg.
ORACLE 10g erlaubt es, den Optimizer im TUNING MODE laufen zu lassen, wobei zusätzliche statistische Werte ermittelt werden können und Vorschläge für eine weitere Verbesserung des SQL Befehls generiert werden können. Dieser Prozess kann allerdings durchaus ein paar Minuten dauern, so dass ein Einsatz des automatischen Tunings für ressourcenintensive Befehle gedacht ist.
Das automatische SQL-TUNING wird durch das Package DBMS_SQLTUNE ermöglicht.
Um das Package zu nutzen, braucht man das Recht ADVISOR. grant advisor to scott;
Der erste Schritt ist nun das Erzeugen eines TUNING_TASK. Die Kommandos, die in diesem TUNING_TASK analysiert werden sollen, können manuell hinzugefügt werden, aber auch z.B. aus dem Cursor Cache ermittelt werden.
Um das ganze zu testen, wollen wir ein spezifisches Statement tunen. Wir wollen uns Empfehlungen für die Ausführung eines Joins über EMP und DEPT geben zu lassen. Im Where-Teil nutzen wir die Funktion NVL und prüfen, ob die empno = 100 ist.
Dazu wird eine Bindevariable :empno verwendet, der wir den Wert 100 zuweisen. Dies geschieht mit dem Parameter BIND_LIST beim erzeugen des Tuning Task. DECLARE
sqlstmt VARCHAR2(500);
task_id VARCHAR2(100);
BEGIN
sqlstmt := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';
task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => sqlstmt,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'SCOTT',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'join_tuning_task',
description => 'Tuning task für EMP/DEPT-Join');
DBMS_OUTPUT.put_line('Die vergebene Task_ID ist: ' ||task_id);
END;
/
Der nächste Schritt ist nun, den eben angelegten Tuning Task auszuführen. EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'join_tuning_task');
Die Ausführung des Task läuft asynchron, der Stand der Dinge kann in
DBA_ADVISOR_LOG geprüft werden. SELECT task_name, status FROM user_advisor_log WHERE owner = 'SCOTT';
TASK_NAME STATUS
------------------------------ -----------
emp_dept_tuning_task COMPLETED
Ist der Status nicht beendet, können sie die Analyse unterbrechen und auch später wieder aufsetzen. Dazu gibt es im Package die Methoden interrupt_tuning_task, resume_tuning_task oder auch cancel_tuning_task.
Nachdem der Task abgeschlossen ist, lassen wir uns das Ergebnis anzeigen. In diesem einfachen Fall empfiehlt der Advisor, einen function based index auf NVL zu legen, denn nur so kann der Ausdruck im Where-Teil mit einem Index unterstützt werden. Weiterhin informiert uns der Advisor, dass der bestehende Index auf der Spalte empno nicht verwendet werden kann, weil wir im WHERE-Teil mit der indizierten Spalte Berechnungen durchführen. Zuguterletzt präsentiert uns der Tuning Advisor die verschiedenen Ausführungspläne; einmal im Original und einmal den Plan, wie er nach der Durchführung der vorgeschlagenen Änderungen ermittelt werden würde.
set long 20000
set pagesize 10000
SELECT DBMS_SQLTUNE.report_tuning_task('join_tuning_task') AS tip FROM dual;
TIP
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : join_tuning_task
Tuning Task Owner : SCOTT
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 11/03/2007 19:12:32
Completed at : 11/03/2007 19:12:35
Number of Index Findings : 1
Number of SQL Restructure Findings: 1
-------------------------------------------------------------------------------
Schema Name: SCOTT
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- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design or creating the recommended index.
create index SCOTT.IDX$$_00160001 on SCOTT.EMP(NVL('EMPNO',0));
Rationale
---------
Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption.
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: 351108634
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti me |
-------------------------------------------------------------------------------- --------
| 0 | SELECT STATEMENT | | 1 | 57 | 4 (0)| 00 :00:01 |
| 1 | NESTED LOOPS | | 1 | 57 | 4 (0)| 00 :00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 37 | 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 |
-------------------------------------------------------------------------------- --------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("E"."EMPNO",0)=:EMPNO)
4 - access("E"."DEPTNO"="D"."DEPTNO")
2- Using New Indices
--------------------
Plan hash value: 183464275
-------------------------------------------------------------------------------- ---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time |
-------------------------------------------------------------------------------- ---------------
| 0 | SELECT STATEMENT | | 1 | 57 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 57 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX$$_00160001 | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------- ---------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP".???)
5 - access("E"."DEPTNO"="D"."DEPTNO")
-------------------------------------------------------------------------------
|
