Run a SQL Tuning Advisor For A given Sql_id

When we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations that can be done that query to improve performance.
 It might give suggestion to create few indexes or accepting a SQL profile.

Diagnostic and Tuning license is required to use this feature

In this below tutorial we will explain how to run sql tuning advisor against sql_Id.

Suppose the sql id is – dtj3d4das6a9a

a) Create Tuning Task

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'dtj3d4das6a9a',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => 'dtj3d4das6a9a_tuning_task11',
                          description => 'Tuning task1 for statement dtj3d4das6a9a');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

b). Executing Tuning task:
begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'dtj3d4das6a9a_tuning_task11'); end;

c). Get the Tuning advisor report.
select dbms_sqltune.report_tuning_task('dtj3d4das6a9a_tuning_task11') from dual;

d). Get list of tuning task present in database:
We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;

e).Drop a tuning task:
execute dbms_sqltune.drop_tuning_task('dtj3d4das6a9a_tuning_task11');

What if the sql_id is not present in the cursor , but present in AWR snap?
SQL_ID =0u676p5cvfxz4

First we need to find the begin snap and end snap of the sql_id.
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;

From here we can get the begin snap and end snap of the sql_id.

begin_snap -> 870
end_snap -> 910

1. Create the tuning task:
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 870,
                          end_snap    => 910,
                          sql_id      => '0u676p5cvfxz4',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '0u676p5cvfxz4_AWR_tuning_task',
                          description => 'Tuning task for statement 0u676p5cvfxz4  in AWR');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
 2. Execute the tuning task:
 EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '0u676p5cvfxz4_AWR_tuning_task');
 3. Get the tuning task recommendation report
 SELECT DBMS_SQLTUNE.report_tuning_task('0u676p5cvfxz4_AWR_tuning_task') AS recommendations FROM dual;