Create and run a SQL Tuning Advisor task and read its recommendations

Command

-- 1) Create and run a task for a SQL in the cursor cache:
VAR task VARCHAR2(64)
BEGIN
  :task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
             sql_id      => '<sql_id>',
             scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
             time_limit  => 300,
             task_name   => 'STA_<sql_id>',
             description => 'Slow report query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => :task);
END;
/
-- For a historical SQL in AWR: begin_snap => <b>, end_snap => <e>, sql_id => '<sql_id>'

-- 2) Status and report:
SELECT task_name, status FROM dba_advisor_log WHERE task_name = :task;

SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 200 PAGESIZE 0
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:task) FROM dual;

-- 3) Get the recommendations as an executable script:
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:task) FROM dual;

-- 4) When done:
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => :task);

What it does

Runs the optimizer in an extended "tuning mode" for the SQL and produces recommendations for missing statistics, new indexes, a SQL profile, alternative plans and SQL restructuring; validates the profile recommendation with test executions.

When to use it

When a single problematic SQL needs a quick second opinion, or when looking for a SQL profile fix for a plan regression without changing code.

Caution

Requires the Tuning Pack license. COMPREHENSIVE scope may execute the SQL for testing (only the query part for DML); set time_limit in production. Index recommendations ignore the rest of the workload; evaluate DML cost separately. The user needs the ADVISOR privilege.