SQL Tuning Advisor for SQL_ID

Here we are going to discuss how to do 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.Check if the task has been created

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = ‘TEST’;

3. Run Task 

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

3.Checking the Status

SELECT status FROM DBA_ADVISOR_TASKS WHERE task_name = ‘STA:7a6b4442j5pcz’;
SELECT sofar, totalwork, username FROM V$ADVISOR_PROGRESS ;

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

4.Delete task.

BEGIN

DBMS_SQLTUNE.drop_tuning_task (task_name => 'STA:7A6B4442J5PCZ');

END;

/

hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!