CONN sys/password AS SYSDBA
GRANT ADVISOR TO scott;
 

Wanneer je de SQL tuning advisor wilt gebruiken moet je eerst een nieuwe tuning task aanmaken met de CREATE_TUNING_TASK functie. Het statement wat je wilt analyseren kan verkregen worden uit de Automatic Workload Repository (AWR), de cursor cache en SQL tuning set of handmatig opgegeven worden:

SET SERVEROUTPUT ON
 
 -- Tuning task gecreëerd voor een specifiek a statement uit de 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 voor statement 19v5guvsgcd1v in AWR.');
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 END;
 /
 
 -- Tuning task gecreëerd voor een specifiek a statement uit de 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 voorstatement 19v5guvsgcd1v.');
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 END;
 /
 
 -- Tuning task gecreëerd uit een 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 gecreëerd voor een handmatig gespecificeerd 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 voor een EMP naar DEPT join query.');
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 END;
 /
 

Als de TASK_NAME parameter wordt gespecificeerd zal deze waarde te zien zijn als de SQL tune task identifier. Als deze niet gegeven wordt dan zal een door het systeem gegenereerde naam "TASK_1478" te zien zijn. Als de SCOPE parameter wordt gezet naar scope_limited zal de SQL profiling analyse ontbreken. De TIME_LIMIT parameter geeft simpelweg de tijd aan dat de optimizer kan besteden om aanbevelingen te geven. De volgende voorbeelden zullen refereren aan de laatste tuning set omdat het geen externe afhankelijkheden heeft anders dan het SCOTT schema. De NVL uit het SQL statement is daarin gezet om een reactie van de optimizer te forceren. Als extra aanmoediging kunnen we nog de statistieken van een van de tabellen verwijderen.

EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP'); 
 

Met het definiëren van de tuning task zal de volgende stap die uitgevoerd wordt, gebruik maken van de EXECUTE_TUNING_TASK procedure:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

Gedurende de executie fase kan je de task pauzeren en herstarten,afbreken of hem resetten om hem weer opnieuw
te kunnen executen:

-- Onderbreken en herstarten van een 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');
 
 -- Een tuning task afbereken.
 EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');
 
 -- Een tuning task resetten om hem opnieuw te kunnen laten executen.
 EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

De status van de tuning task kan bekeken worden met de 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.

ALs de tuning task eenmaal succesvol heeft gelopen kunnen de aanbevelingen bekeken worden met
de REPORT_TUNING_TASK function:

SET LONG 10000;
 SET PAGESIZE 1000
 SET LINESIZE 200
 SET PAGESIZE 24 
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual;
--   In dit geval zal de output eruit zien als volgt:    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.

Als de tuning sessie eenmaal over is, kan de tuning task verwijderd worden met
de DROP_TUNING_TASK procedure:

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');
   DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task');
   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
   DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task');
END;
/