Performance Tuning
SQL Tuning Advisor using the command line
User Rating: / 0
- Details
-
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: 6909
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;
/