Crear y ejecutar una tarea de SQL Tuning Advisor y leer sus recomendaciones

Comando

-- 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);

Qué hace

Ejecuta el optimizador en un "tuning mode" extendido para el SQL y genera recomendaciones sobre estadísticas faltantes, índices nuevos, un SQL profile, planes alternativos y reestructuración del SQL; valida la recomendación del profile con ejecuciones de prueba.

Cuándo usarlo

Cuando un único SQL problemático necesita una segunda opinión rápida, o al buscar una corrección mediante SQL profile para una regresión de plan sin cambiar el código.

Precaución

Requiere la licencia de Tuning Pack. El alcance COMPREHENSIVE puede ejecutar el SQL para pruebas (solo la parte de consulta en DML); defina time_limit en producción. Las recomendaciones de índices ignoran el resto de la carga de trabajo; evalúe por separado el costo en DML. El usuario necesita el privilegio ADVISOR.