DBA Hub

📋Steps in this guide1/3

How To Run SQL Tuning Advisor For A Sql_id

Imported guide

oracle configurationintermediate
by OracleDba
15 views
1

Overview

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_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;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '27s5z2sspsg77_tuning_task11');

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

We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;
2

Section 2

SQL_ID =24pzs2d6a6b13 First we need to find the begin snap and end snap of the sql_id. From here we can get the begin snap and end snap of the sql_id. begin_snap -> 235 end_snap -> 240

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
execute dbms_sqltune.drop_tuning_task('27s5z2sspsg77_tuning_task11');

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;
3

Section 3

- Create the tuning task:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '24pzs2d6a6b13_AWR_tuning_task');

SET LONG 10000000;
SET PAGESIZE 100000000

SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('24pzs2d6a6b13_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24

Comments (0)

Please to add comments

No comments yet. Be the first to comment!