Run SQL Tuning Advisor from 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;
/
  • September 12, 2013 | 39 views