SQL Tuning Advisor and how it works

The SQL tuning advisor automates the entire SQL tuning process replacing manual SQL tuning.
It analyzes candidate SQL statement and executes a complete analysis of the statement including:

• Determining better or missing statistics

• Determining better execution plan

• Detecting better access path and objects required to satisfy them(indexes, materialized views)

• Restructuring SQL 

Running SQL tuning advisor using the DBMS_SQLTUNE package is two step process:

1. Create a SQL tuning task

2. Execute a SQL tuning task

How to create a tuning task

====================

1. Tuning task created for specific Sql id:

SET SERVEROUTPUT ON

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 for statement 19v5guvsgcd1v.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

2. Tuning task created for specific a statement from the AWR

SET SERVEROUTPUT ON

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 for statement 19v5guvsgcd1v in AWR.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

3. Tuning task created for a manually specified statement.

SET SERVEROUTPUT ON

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 for an EMP to DEPT join query.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

Executing the tuning task

===================

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

Displaying the recommendations

========================

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

Dropping a tuning set

=================

Once the tuning session is over the tuning task can be dropped using the 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;

/