Create Oracle SQL Tuning Task Manually

    An Oracle SQL Tuning Task is a logical container used by Oracle Database's built-in SQL Tuning Advisor to analyze and optimize specific SQL statements.

    When you create a tuning task, you register one or more SQL statements (retrieved from memory, the Automatic Workload Repository (AWR), or entered manually as text) to be evaluated. When executed, the database analyzes the execution path, identifies performance bottlenecks, and generates a detailed optimization report.

CREATE TUNING TASK

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '5mn47p0gv2q83',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 4000,
                          task_name   => 'Task_5mn47p0gv2q83',
                          description => 'Manual Tuning on 06 SEP 16');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXECUTE TUNING TASK

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Task_5mn47p0gv2q83');

CHECK STATUS OF TASK

SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'Task_5mn47p0gv2q83';

CHECK RECOMMENDATION

SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 1000;
SELECT DBMS_SQLTUNE.report_tuning_task('Task_5mn47p0gv2q83') AS recommendations FROM dual;