Performance Tuning

SQL Tuning Advisor using the command line

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Wednesday, 20 April 2011 15:35
Last Updated on Monday, 12 March 2012 13:54
Published on Wednesday, 20 April 2011 15:35
Written by Guy Lambregts
Hits: 6547

SQL Tuning Advisor using the command line ( DBMS_SQLTUNE )

 

 

set serveroutput on;
set long 20000;
set linesize 200;

DECLARE
l_sql               VARCHAR2(3000);
l_sql_tune_task_id  VARCHAR2(100);
BEGIN
l_sql := q'!SELECT distinct x,y,z FROM
owner.T1 A,
owner.T2 B  
WHERE
B.ID = A.ID AND
AND NOT EXISTS (SELECT 'x' FROM OWNER.T2 WHERE STATUS = 'LOADED' AND ID3 = A.ID4)!'; -- This is the statement we would like to tune

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text    => l_sql,
user_name   => 'OWNER',
scope       => DBMS_SQLTUNE.scope_comprehensive,
task_name   => 'OWNER_TUNING_TASK_01',
description => 'TUNING TASK FOR OWNER WITH SELECT DISTINCT');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/



begin
DBMS_SQLTUNE.execute_tuning_task(task_name => 'OWNER_TUNING_TASK_01');
end;
/

PL/SQL procedure successfully completed.



select task_name,status from dba_advisor_log where task_name like 'OWNER%';

TASK_NAME                      STATUS
------------------------------ -----------
OWNER_TUNING_TASK_01           COMPLETED




SELECT DBMS_SQLTUNE.report_tuning_task('OWNER_TUNING_TASK_01') AS recommendations FROM dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : OWNER_TUNING_TASK_01
Tuning Task Owner  : DBA
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/15/2011 13:42:13
Completed at       : 04/15/2011 13:47:06

-------------------------------------------------------------------------------
Schema Name: OWNER
SQL ID     : fgc98dja16ksg
SQL Text   : SELECT distinct x,y,z FROM
owner.T1 A,
owner.T2 B  
WHERE
B.ID = A.ID AND
AND NOT EXISTS (SELECT 'x' FROM OWNER.T2 WHERE STATUS = 'LOADED' AND ID3 = A.ID4)

-------------------------------------------------------------------------------                                                                                                                                                                                                                             
FINDINGS SECTION (1 finding)                                                                                                                                                                                                                                                                                
-------------------------------------------------------------------------------                                                                                                                                                                                                                             

1- SQL Profile Finding (see explain plans section below)                                                                                                                                                                                                                                                    
--------------------------------------------------------                                                                                                                                                                                                                                                    
A potentially better execution plan was found for this statement.                                                                                                                                                                                                                                         

Recommendation (estimated benefit: 98.19%)                                                                                                                                                                                                                                                                
------------------------------------------                                                                                                                                                                                                                                                                
- Consider accepting the recommended SQL profile.                                                                                                                                                                                                                                                         
execute dbms_sqltune.accept_sql_profile(task_name => 'OWNER_TUNING_TASK_01', replace => TRUE);  

 

 

How to accept a SQL profile




DECLARE
l_sql_tune_task_id  VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => 'OWNER_TUNING_TASK_01',
name      => 'OWNER_TUNING_TASK_01');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


PL/SQL procedure successfully completed.

 

 

How to drop a SQL profile


 

BEGIN

DBMS_SQLTUNE.drop_sql_profile ( name   => 'OWNER_TUNING_TASK_01');

END;

/