How to run sql tuning advisor for particular sql -id in the Cursor cache



Many times a sql may be running long and we may need to run sql tuning advisor to get the recommendation to resolve the long running issue.In this post, we would be looking at the steps to achieve it
Steps for running  sql tuning  advisor on the particular sql -id in the Cursor cache
Create sql 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 sql Tuning Task:

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

Get the summary of recommendation of sql tuning task

SET LONG 10000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘example -1’) from DUAL;

Get the detailed recommendation of sql tuning task

SET LONG 10000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘example -1′,’TEXT’,’ALL’,’ALL’) 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;
/

If necessary you can drop the sql tuning task.

begin
DBMS_SQLTUNE.DROP_TUNING_TASK(‘example -1’);
end;
/

If necessary (the profile does not help),
you can drop the profile.

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (‘my_sql_profile’);
END;
/


Leave a Reply