How To Run SQL Tuning Advisor
2 min readWhen we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations that can be used to improve performance. It might give suggestion to create few indexes or accepting a SQL profile.
As we all know running a slow query is a major performance issue in the database also has the biggest challenge for DBA to resolve this issue.
Resolving the slowness issue we are using an Oracle tool called SQL Advisor.
First we need to find the begin snap and end snap of the sql_id.
- AWR Report
- Query
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
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;
Find specific sql_id running from past 7 days.
select session_id,sql_id from dba_hist_active_sess_history where session_id=2429 and snap_id=33927;
-- GET STATEMENT
SET LONG 99999999999
select sql_text,sql_id from dba_hist_sqltext where sql_id='bj8t4kkjk5k7h';
Create Tuning Task
SET SERVEROUTPUT ON
-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(30000);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 33692,
end_snap => 33693,
sql_id => 'ft8x3ab8r3uu5',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => 'ft8x3ab8r3uu5_AWR_tuning_task',
description => 'Tuning task for statement ft8x3ab8r3uu5 in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Execute Tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'ft8x3ab8r3uu5_AWR_tuning_task');
Get the Tuning advisor report.
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 24
SELECT DBMS_SQLTUNE.report_tuning_task('ft8x3ab8r3uu5_AWR_tuning_task') AS recommendations FROM dual;
Get list of tuning task present in database:
SQL > SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;
Drop a tuning task:
execute dbms_sqltune.drop_tuning_task('ft8x3ab8r3uu5_AWR_tuning_task');
