Oracle Tuning Advisor - Running SQL Tuning advisor steps

 1. Create Tuning Task

 

DECLARE l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '4us78thunhn00',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 500,

                          task_name   => '4us78thunhn00_tuning_task1',

                          description => 'Tuning task1 for statement 4us78thunhn00');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

 

 

SQL> begin   

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => '4us78thunhn00_tuning_task1', parameter => 'TIME_LIMIT', value => 15000);   

end;   

PL/SQL procedure successfully completed. 

 

2. Execute Tuning task:

 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4us78thunhn00_tuning_task1');

 

3. Get the Tuning advisor report.

set long 65536

set longchunksize 65536

set linesize 600

set pagesize 9000

select dbms_sqltune.report_tuning_task('4us78thunhn00_tuning_task1') from dual;

 

 

4. Get list of tuning task present in database:

 

We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG

 

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;

5. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('4us78thunhn00_tuning_task1');