Создать и выполнить задачу SQL Tuning Advisor и прочитать её рекомендации

Команда

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

Что делает

Запускает оптимизатор в расширенном режиме "tuning mode" для SQL и выдаёт рекомендации по отсутствующей статистике, новым индексам, SQL profile, альтернативным планам и переработке SQL; проверяет рекомендацию по профилю тестовыми выполнениями.

Когда использовать

Когда для одного проблемного SQL нужно быстрое второе мнение или когда ищется исправление регрессии плана через SQL profile без изменения кода.

Внимание

Требует лицензии Tuning Pack. Область COMPREHENSIVE может выполнять SQL для тестирования (для DML — только часть запроса); в production задавайте time_limit. Рекомендации по индексам не учитывают остальную нагрузку; оценивайте стоимость DML отдельно. Пользователю нужна привилегия ADVISOR.