Create a Tuning Task for a SQL ID in the AWR
Create the Tuning Task
-- -- Create the Tuning Task -- DECLARE v_sql_tune_task_id VARCHAR2(100); BEGIN v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => 23876, end_snap => 23976, sql_id => '8d65f5452jkjr', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 600, task_name => 'STT_8d65f5452jkjr', description => 'Tuning task for a set of AWR Snapshots'); DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id); END; /
Run the Tuning Task
-- -- Run the Tuning Task -- EXECUTE DBMS_SQLTUNE.execute_tuning_task(task_name => 'STT_8d65f5452jkjr') /
View the Tuning Task Results
-- -- View the Tuning Task Results -- sqlplus / as sysdba SQL> SET LONG 1000 SQL> SET LONGCHUNKSIZE 1000 SQL> SET LINESIZE 100 SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STT_8d65f5452jkjr' ) FROM DUAL;
Cancel, Pause, Resume and Monitor
-- -- Cancel a Tuning Task -- EXECUTE DBMS_SQLTUNE.cancel_tuning_task(task_name => 'STT_8d65f5452jkjr'); -- -- Pause a Tuning Task -- EXECUTE DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'STT_8d65f5452jkjr'); -- -- Resume a Tuning Task -- EXECUTE DBMS_SQLTUNE.resume_tuning_task (task_name => 'STT_8d65f5452jkjr'); -- -- Monitor a Tuning Task -- SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE UPPER(TASK_NAME)='STT_8D65F5452JKJR'
Published 1st November 2021