Running SQL Tuning Advisor Manually

 

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '6x2xfudxngmmj',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 1200,

                          task_name   => '6x2xfudxngmmj_AWR_tuning_task',

                          description => 'Tuning task for query 6x2xfudxngmmj in AWR.');

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

END;

/

begin

 DBMS_SQLTUNE.execute_tuning_task(task_name => '6x2xfudxngmmj_AWR_tuning_task');

END;

/

SELECT task_name, status 

  FROM dba_advisor_log 

 WHERE owner = 'SYSTEM';

 SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('6x2xfudxngmmj_AWR_tuning_task') AS recommendations 

  FROM dual;

-- Interrupting

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '6x2xfudxngmmj_AWR_tuning_task');

EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '6x2xfudxngmmj_AWR_tuning_task'); 

-- Cancel

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => '6x2xfudxngmmj_AWR_tuning_task'); 

-- Resume 

EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => '6x2xfudxngmmj_AWR_tuning_task')