Steps to run SQL Tuning Advisor For a sql_id

To get the tuning recommendations we run SQL tuing advisor for a sql statement or a query.
It might give suggestion to create few indexes or accepting a SQL profile also.

Diagnostic and Tuning license would be required for the same.

Here are the steps how to run sql tuning advisor against sql_Id.

Suppose the sql id is – 97s9z1zzpsp99

Step 1. Create Tuning Task

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (sql_id => '97s9z1zzpsp99',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '97s9z1zzpsp99_tuning_sid1',

description => 'Tuning task1 for statement 97s9z1zzpsp99');

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

END;

/

Step 2. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '97s9z1zzpsp99_tuning_sid1');

Step 3. Get the Tuning advisor report.

set long 6553600

set longchunksize 10000

set linesize 1000

select dbms_sqltune.report_tuning_task('97s9z1zzpsp99_tuning_sid1') from dual;

Step 4: List of tuning task present in database:

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;

Step 5: Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('97s9z1zzpsp99_tuning_sid1');

IF SQL_ID is not present in cursor use AWR 

Step 1: Need to find begin and end snap:

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, 

abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 

+ extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,

executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" 

from dba_hist_SQLSTAT a, dba_hist_snapshot b

where sql_id='&sql_id' and a.snap_id=b.snap_id

and a.instance_number=b.instance_number

order by snap_id desc, a.instance_number;

begin_snap -> 180

end_snap -> 200

Step 2. Create Tuning Task

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (begin_snap  => 180,

                          end_snap    => 200,

  sql_id => '97s9z1zzpsp99',

  scope => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit => 500,

                          task_name => '97s9z1zzpsp99_tuning_sid1',

                          description => 'Tuning task1 for statement 97s9z1zzpsp99');

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

                          END;

/

Step 3. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '97s9z1zzpsp99_tuning_sid1');

Step 4. Get the Tuning advisor report.

set long 6553600

set longchunksize 10000

set linesize 1000

select dbms_sqltune.report_tuning_task('97s9z1zzpsp99_tuning_sid1') from dual;