Complete run commands
-- define the task
variable stmt_task VARCHAR2(64);
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK ( begin_snap => 14523, end_snap => 14524, sql_id => 'dpzmstpc2cxmh' , scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'STA:dpzmstpc2cxmh' );
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM USER_ADVISOR_LOG WHERE task_name = 'STA:dpzmstpc2cxmh';
-- run the task
Exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'STA:dpzmstpc2cxmh');
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM USER_ADVISOR_LOG WHERE task_name = 'STA:dpzmstpc2cxmh';
-- check the progress
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'STA:dpzmstpc2cxmh';
-- show the results
set long 50000
set longchunksize 500000
SET LINESIZE 150
Set pagesize 5000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA:dpzmstpc2cxmh') FROM DUAL;
-- drop the task
Exec DBMS_SQLTUNE.drop_tuning_task(task_name => 'STA:dpzmstpc2cxmh');
Example
DECLARE
my_task_name VARCHAR2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7a6b4442j5pcz',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'STA:7a6b4442j5pcz',description => '7a6b4442j5pcz');
end;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'STA:7a6b4442j5pcz');
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7a6b4442j5pcz') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7A6B4442J5PCZ')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------------------------------
---
Tuning Task Name : STA:7a6b4442j5pcz
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 10/16/2012 19:47:
27
Completed at : 10/16/2012 19:47:54
Number of SQL Profile Findings : 1
----------------------------------------------------------
FINDINGS SECTION (1 finding)
--------------------------------------------------------------------
-----------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for thi
s statement.
Recommendation (estimated benefit: 99.94%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_nam
e => 'STA:7a6b4442j5pcz',
replace => TRUE);