SQL Tuning Advisor for a SQL ID

When a SQL process is running for a long time/running more than usual time, we may handle this situation by running sql tuning advisor for that particular query. Based on the report generated by SQL Tuning advisor, DBA's shall advise the technical team to tune the query. 

Below is the step by step approach to run SQL Tuning advisor for a sql process.

select sesion.sid,

       sesion.username,

       optimizer_mode,

       hash_value,

       address,

       cpu_time,

       elapsed_time,

       sql_text

  from v$sqlarea sqlarea, v$session sesion

 where sesion.sql_hash_value = sqlarea.hash_value

   and sesion.sql_address    = sqlarea.address

   and sesion.username is not null

   order by elapsed_time desc;

select SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,SQL_ID from V$SESSION where SID='902';

Here the above SQL id is different, but the sql id used below is different. The above process completed normally, so captured the remaining process for a different sql id.

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '1016uhs5kf8j4',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 500,

                          task_name   => '1016uhs5kf8j4_tuning_task',

                          description => 'Tuning task1 for statement 1016uhs5kf8j4');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1016uhs5kf8j4_tuning_task');

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('1016uhs5kf8j4_tuning_task') from dual;

spool off;

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='1016UHS5KF8J4_TUNING_TASK';

execute dbms_sqltune.drop_tuning_task('1016uhs5kf8j4_tuning_task');

Suppose sql_id is not present in cursor but available in AWR Report, then follow the below steps to run tuning advisor.

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;

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => 1587,

                          end_snap    => 1589,

                          sql_id      => 'bpyxu7n89yqun',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => 'bpyxu7n89yqun3_AWR_tuning_task',

                          description => 'Tuning task for statement bpyxu7n89yqun  in AWR');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'bpyxu7n89yqun3_AWR_tuning_task');

spool bpyxu7n89yqun3_AWR_tuning_task.txt

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('bpyxu7n89yqun3_AWR_tuning_task') AS recommendations FROM dual;

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='bpyxu7n89yqun3_AWR_tuning_task';

execute dbms_sqltune.drop_tuning_task('bpyxu7n89yqun3_AWR_tuning_task');

                           ****************************நன்றி****************************