Tuning SQL statements using SQL Tuning advisory in oracle database

Step-1 : Create tuning task

Here you need :

 1) AWR snap ids begin snap (1001) 
 2) AWR end snap (1002) during sql execution time
 3) sql id of your sql statement (sqlidxxxxxx) from your AWR report  

SET SERVEROUTPUT ON
-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 1001,
end_snap => 1002,
sql_id => 'sqlidxxxxxx',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'ABC_tune_task001',
description => 'Tuning task for statement sqlidxxxxxx in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Step-2 : Execute tuning task

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

Step-3 : Get report/recommendation from tuning task

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 1000
SELECT DBMS_SQLTUNE.report_tuning_task('ABC_tune_task001') AS recommendations FROM dual;

 

Leave a comment

Your email address will not be published. Required fields are marked *