Oracle SQL Tuning Advisor steps

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