Run SQL Tuning advisor in SQL Prompt

Run SQL Tuning advisor in SQL Prompt

Get SQL ID
===========
select distinct hash_value, sql_id, sql_text
from v$sql
where sql_text like '%pktable_cat%'
and sql_text not like 'select distinct hash_value, sql_id%';


Create Tuning Task:
===================
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '0j3dqg3a8aj8c',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/


SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100

--***************************
--TO GET SUMMARY INFORMATION
--***************************

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

--***************************
--TO GET DETAILED INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;


-- Accept the profile <<< If you get one...
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_1',
name => 'my_sql_profile');
END;
/


###

If necessary you can drop the tuning task.

begin
DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task_1');
end;
/

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

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE ('MY_SQL_TUNING_TASK_1');
END;
/