How Can we Run SQL Tuning Advisor For A SQL ID In Oracle Database


We can 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 various recommendations like gather stats/Index Rebuild and accepting a SQL profile

My sql id is – 4g5ah8zr6thnb.

You can get sqlid from various methods from AWR report or sql details queries.

Method when the sql is running and available in Cursor.

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 => '4g5ah8zr6thnb',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '4g5ah8zr6thnb_tuning_task_cur',
description => 'Tuning task_cur for statement 4g5ah8zr6thnb');
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 => '4g5ah8zr6thnb_tuning_task_cur');

3. Get the Tuning advisor report.

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('4g5ah8zr6thnb_tuning_task_cur') 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='4g5ah8zr6thnb_tuning_task_cur' ;

5. Drop a tuning task:
execute dbms_sqltune.drop_tuning_task('4g5ah8zr6thnb_tuning_task_cur');


Method when the sql is not running. We can get the information from the AWR snaps when the query was ran.

SQL_ID =4g5ah8zr6thnb

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 -> 6377
end_snap -> 6380

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  => 6377,
                          end_snap    => 6380,
                          sql_id      => '4g5ah8zr6thnb',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 600,
                          task_name   => '4g5ah8zr6thnb_AWR_tuning_task',
                          description => 'Tuning task for statement 4g5ah8zr6thnb  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 => '4g5ah8zr6thnb_AWR_tuning_task');

3. Get the tuning task recommendation report

SET LONG 10000000;
SET PAGESIZE 100000000
SET PAGESIZE 24
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('4g5ah8zr6thnb_AWR_tuning_task') AS recommendations FROM dual;




If you like please follow and comment