DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, September 12, 2013

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;
/

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.