SQL Tuning Advisor for SQL_ID

1. Create SQL Tuning Advisor task

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;

/

2. Run Task 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'STA:7a6b4442j5pcz');

3. View results 

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