Steps To Run SQL Tuning Advisor For A Sql_id

Sql_id used for example : 5qw0w762nh6sq

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      => '5qw0w762nh6sq',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '5qw0w762nh6sq_tuning_task',
                          description => 'Tuning task1 for statement 5qw0w762nh6sq');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Step 2 : Execute the task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5qw0w762nh6sq_tuning_task');

Step 3 : Get the report for the created task

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('5qw0w762nh6sq_tuning_task') from dual;

List Tuning task present in database :

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME=task_name;

Command to drop tuning task :

execute dbms_sqltune.drop_tuning_task('5qw0w762nh6sq_tuning_task');

If Sql_id is not found from the cache,

It can be found from the AWR snapshots by below command :

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4qjrrcuycnwsb')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

Create Tuning Task for the snapshot we got from above query :

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 4325,
                          end_snap    => 4328,
                          sql_id      => '4qjrrcuycnwsb',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '4qjrrcuycnwsb_AWR_tuning_task',
                          description => 'Tuning task for statement 4qjrrcuycnwsb in AWR');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execute the task :

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4qjrrcuycnwsb_AWR_tuning_task');

Get the report for the task :

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

• Tweet
• Share
• Share
• Share
• Share

Related Post