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');
****************************நன்றி****************************
