Oracle Advisors - Tuning leichtgemacht.


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.


Überblick

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.

Beispiel

Im ersten Schritt wird eine Tuningtask definiert. Hier können folgende Möglichkeiten genutzt werden:

  • Tuning Task für ein spezielles Statement aus dem AWR-Report
  • Tuning Task für ein spezielles Statement aus dem Cursor Cache
  • Tuning Task für ein SQL Tuningset
  • Tuning Task für ein manuell definiertes SQL Statement

Tuning Task für ein spezielles Statement aus dem 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 für ein spezielles Statement aus dem 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 für ein SQL Tuningset

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 für ein manuell definiertes 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;
/

Ausführung der Tuning Task

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