Criar e executar uma tarefa do SQL Tuning Advisor e ler suas recomendações

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

O que faz

Executa o otimizador em um "tuning mode" estendido para o SQL e gera recomendações sobre estatísticas ausentes, novos índices, um SQL profile, planos alternativos e reestruturação do SQL; valida a recomendação de profile com execuções de teste.

Quando usar

Quando um único SQL problemático precisa de uma segunda opinião rápida ou ao buscar uma correção via SQL profile para uma regressão de plano sem mudar o código.

Atenção

Exige a licença do Tuning Pack. O escopo COMPREHENSIVE pode executar o SQL para teste (apenas a parte de consulta em DML); defina time_limit em produção. As recomendações de índice ignoram o restante da carga; avalie o custo de DML separadamente. O usuário precisa do privilégio ADVISOR.