Oracle Database

--
-- Create the Tuning Task
--
 
    DECLARE
      v_sql_text               VARCHAR2(1000);
      v_sql_tune_task_id       VARCHAR2(100);
    BEGIN
      v_sql_text := 'SELECT DIFFICULT, PAINFUL, SLEEPLESS, NIGHTS FROM MY_TERRIBLE_TABLE WHERE 1=0 JOIN ON EVERYTHING';
      v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                              sql_text    => v_sql_text,
                              user_name   => 'MYUSER',
                              scope       => DBMS_SQLTUNE.scope_comprehensive,
                              time_limit  => 600,
                              task_name   => 'STT_8d65f5452jkjr',
                              description => 'Tuning task for a Single Query');
      DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
    END;
    /

--
-- Run the Tuning Task
--
 
EXECUTE DBMS_SQLTUNE.execute_tuning_task(task_name => 'STT_8d65f5452jkjr')
/

--
-- 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 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'