创建并运行 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);

作用

以扩展的"调优模式"针对该 SQL 运行优化器，给出缺失统计信息、新索引、SQL profile、替代计划和 SQL 重构方面的建议；通过测试执行验证 profile 建议。

适用场景

当单条问题 SQL 需要快速获得第二意见，或在不修改代码的情况下为计划回归寻找 SQL profile 修复方案时。

注意

需要 Tuning Pack 许可。COMPREHENSIVE 范围可能会为测试而执行该 SQL（DML 只执行查询部分）；在生产环境中请设置 time_limit。索引建议不考虑其余负载；请单独评估 DML 成本。用户需要 ADVISOR 权限。