DBMS_SQLTUNE

The DBMS_SQLTUNE package helps us tune the SQL statements.One can run the SQL Tuning Advisor using DBMS_SQLTUNE package.The steps involved are:-

1. Create the Tuning Task.

2. Execute the Tuning Task.

3. Report Tuning Task.

The tuning task can be created from the AWR

SET SERVEROUTPUT ON

DECLARE
l_sql_tune_task_id  VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap  => 3312,
end_snap    => 3313,
sql_id      => ‘f7k789upgmkw0’,
scope       => DBMS_SQLTUNE.scope_comprehensive,
time_limit  => 60,
task_name   => ‘f7k789upgmkw0_AWR_tuning_task’,
description => ‘Tuning task for statement f7k789upgmkw0 in AWR.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

The tuning task can be created from the sql_id

column sql_text for a50 wrap

select sql_id, sql_text, executions, child_latch from v$sqlarea where upper(sql_text) like ‘SELECT A.ROWID,B.ROWID%’;

Note:- After ‘like’ write the first few words of the query you want to find sql_id for, with % sign

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => ‘f7k789upgmkw0’,
scope => DBMS_SQLTUNE.scope_limited,
time_limit => 60,
task_name => ‘tuning_sql’,
description => ‘Tuning SQL’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

Other method is also avaliable to create the tuning task where you can use the whole SQL TEXT in DBMS_SQLTUNE.create_tuning_task.

Note:- If tuning task_name already exists then one can drop it using,

exec DBMS_SQLTUNE.drop_tuning_task(task_name   => ‘tuning_sql’);

– After the tuning task is created, the next step is to execute it using EXECUTE_TUNING_TASK

exec DBMS_SQLTUNE.execute_tuning_task(task_name => ‘tuning_sql’);

– To check the progress of the tuning task

select OWNER,TASK_NAME,TASK_ID,STATUS from dba_advisor_log where owner=’XYZ’;

– Once the execution has completed successfully, the recommendations can be displayed using REPORT_TUNING_TASK

SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100

select DBMS_SQLTUNE.report_tuning_task(‘tuning_sql’) as recommendation from dual;

References:-

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b2841 /d_sqltun.htm#CHDGBCDB

http://tonguc.wordpress.com/2007/01/05/expanded-supplied-packages-with-10g-part-3/

Advertisements

One thought on “DBMS_SQLTUNE

  1. When i am running the above query, I am getting below error.
    PLS-00201: identifier ‘DBMS_SQLTUNE.CREATE_TUNING_TASK’ must be declared

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s