How to run Oracle SQL tuning advisor for a sql_id in Oracle Database

How to run SQL tuning advisor for a SQL_ID or SQL Statement

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.

Suppose the sql id is – bxmcbgxkf9sd1

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 => '87s8z2zzpsg88',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'bxmcbgxkf9sd1_tune_task1',
description => 'SQL Tune task1 for statement bxmcbgxkf9sd1');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

2. Execute Tuning task

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

3. Get the Tuning advisor report

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('bxmcbgxkf9sd1_tune_task1') from dual;

4. 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 ;

5. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('bxmcbgxkf9sd1_tune_task1');

if some scenario the sql_id is not present in the database , but present in AWR snap?

SQL_ID =bxmcbgxkf9sd1

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 -> 127

end_snap -> 130

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  => 127,

                          end_snap    => 130,

                          sql_id      => 'bxmcbgxkf9sd1',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => 'bxmcbgxkf9sd1_AWR_tune_task1',

                          description => 'Tuning task for sql statement bxmcbgxkf9sd1  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 => 'bxmcbgxkf9sd1_AWR_tune_task1');

3. Get the tuning task recommendation report

SET LONG 100000000;

SET PAGESIZE 1000000000;

SET LINESIZE 300;

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

SET PAGESIZE 24;

4. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('bxmcbgxkf9sd1_AWR_tune_task1');