How To Run SQL Tuning Advisor For A Sql_id
2 min readWhen 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. Suppose the sql id is – 72nggna3640qk 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 => '72nggna3640qk',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '72nggna3640qk_tuning_task11',
description => 'Tuning task1 for statement 72nggna3640qk');
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 => '72nggna3640qk_tuning_task11');
3. Tuning advisor report.
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('72nggna3640qk_tuning_task11') from dual;
4. List of tuning task present in database:
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;
5. Drop a tuning task:
execute dbms_sqltune.drop_tuning_task('72nggna3640qk_tuning_task11');
If sql_id is not present in the cursor , but present in AWR snap:
SQL_ID =70ua2g7vskhwv
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;
Follow above steps once snap_id is captured from above query. 1. Create the tuning task. 2. Execute the tuning task. 3. Get the tuning task recommendation report.
Hope it worked !! 🙂
