- 04 Sep, 2023
- 0 Comments
- 2 Mins Read
How To Run SQL Tuning Advisor For A Sql_id
How To Run SQL Tuning Advisor For A Sql_id
When we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations to improve performance. It might give suggestion to create few indexes or accepting a SQL profile.
Suppose the sql id is – 27s5z2sspsg77
1. Create Tuning Task :
1 2 3 4 5 6 7 8 9 10 11 12 | DECLAREl_sql_tune_task_id VARCHAR2(100);BEGINl_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (sql_id => '27s5z2sspsg77',scope => DBMS_SQLTUNE.scope_comprehensive,time_limit => 500,task_name => '27s5z2sspsg77_tuning_task11',description => 'Tuning task1 for statement 27s5z2sspsg77');DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);END;/ |
2. Execute Tuning task:
1 | EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '27s5z2sspsg77_tuning_task11'); |
3. Get the Tuning advisor report.
1 2 3 4 | set long 65536set longchunksize 65536set linesize 100select dbms_sqltune.report_tuning_task('27s5z2sspsg77_tuning_task11') from dual; |
4. Get list of tuning task present in database:
1 2 | We can get the list of tuning tasks present in database from DBA_ADVISOR_LOGSELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ; |
5. Drop a tuning task:
1 | execute dbms_sqltune.drop_tuning_task('27s5z2sspsg77_tuning_task11'); |
What if the sql_id is not present in the cursor , but present in AWR snap?
SQL_ID =24pzs2d6a6b13
First we need to find the begin snap and end snap of the sql_id.
1 2 3 4 5 | 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 bwhere sql_id='&sql_id' and a.snap_id=b.snap_idand a.instance_number=b.instance_numberorder by snap_id desc, a.instance_number; |
From here we can get the begin snap and end snap of the sql_id.
begin_snap -> 235
end_snap -> 240
- Create the tuning task:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DECLARE l_sql_tune_task_id VARCHAR2(100);BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => 235, end_snap => 240, sql_id => '24pzs2d6a6b13', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => '24pzs2d6a6b13_AWR_tuning_task', description => 'Tuning task for statement 24pzs2d6a6b13 in AWR'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);END;/ |
7. Execute the tuning task:
1 | EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '24pzs2d6a6b13_AWR_tuning_task'); |
8. Get the tuning task recommendation report :
1 2 3 4 5 6 | SET LONG 10000000;SET PAGESIZE 100000000SET LINESIZE 200SELECT DBMS_SQLTUNE.report_tuning_task('24pzs2d6a6b13_AWR_tuning_task') AS recommendations FROM dual;SET PAGESIZE 24 |