How to run sql tuning expert for particular sql -d in the Cursor cache



Last updated on July 17th, 2015 at 06:05 pm

Steps for running  sql tuning  on the particular sql -d in the Cursor cache

Create Tuning Task

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ”,
scope => ‘COMPREHENSIVE’,
time_limit => 3600,
task_name => ”,
description => ”);
end;
/

Execute Tuning Task:

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘example -1’);
end;
/

Get the summary of recommendation
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘example -1’) from DUAL;

How to accept the profile recommended by the sql tuning expert
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name =>’example -1′,
name => ‘my_sql_profile’);
END;
/


Leave a Reply