Oracle执行计划变差了？利用AWR SQL Plan Baseline排查

如何诊断SQL执行计划漂移：先查AWR历史基线，再验证基线状态与参数

SQL性能突然下降？先检查AWR历史执行计划是否稳定

Oracle数据库SQL性能下降，执行计划漂移是常见原因。统计信息更新、绑定变量窥探或数据库版本升级都可能导致优化器生成次优计划。但性能变慢不一定就是计划问题。第一步，需要确认该SQL是否存在一个历史上稳定且高效的执行计划。请注意，dba_sql_plan_baselines视图仅记录手动加载或自动捕获的SQL计划基线。而完整的执行历史记录保存在AWR报告相关的dba_hist_sqlstat和dba_hist_sql_plan视图中，它们是排查问题的关键。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

如何查询？核心是分析最近7天内SQL执行计划的变化情况：

SELECT plan_hash_value, COUNT(*), MIN(sample_time), MAX(sample_time)
FROM dba_hist_sql_plan p
JOIN dba_hist_sqlstat s USING (sql_id, plan_hash_value)
WHERE sql_id = 'your_sql_id'
  AND sample_time > SYSDATE - 7
GROUP BY plan_hash_value
ORDER BY MIN(sample_time);

• 若查询返回多个不同的plan_hash_value，则表明执行计划确实发生了漂移。
• 若仅有一个plan_hash_value，则问题可能源于I/O性能、锁竞争或资源等待，而非执行路径本身。
• 重要提示：即使plan_hash_value相同，执行效率也可能不同。务必计算elapsed_time_total / executions_total的平均执行时间，观察是否有显著增长。
• 另外需注意，dba_hist_sql_plan默认仅保留每条SQL最近的1000个执行计划（受隐含参数_cursor_plan_cache_threshold控制），高频SQL的早期计划可能已被覆盖。

如何从AWR提取高效计划并固化为SQL计划基线？

想将AWR中记录的高效执行计划固定下来？不能直接创建，需先将其还原为完整的执行计划信息。核心方法是：使用DBMS_XPLAN.DISPLAY_AWR提取计划详情，再通过DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE或LOAD_PLANS_FROM_SQLSET将其加载为基线。

具体操作分为三个步骤：

• 首先，从dba_hist_sqlstat中定位目标plan_hash_value对应的sql_id和dbid。
• 其次，使用DBMS_XPLAN.DISPLAY_AWR('sql_id', plan_hash_value, db_id)验证计划完整性，重点关注filter、access谓词及cardinality预估是否合理。
• 最后，执行以下PL/SQL块加载基线（需具备ADMINISTER SQL MANAGEMENT OBJECT权限）：

  BEGIN
    DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
      sql_id => 'your_sql_id',
      plan_hash_value => 1234567890,
      fixed => 'YES',
      enabled => 'YES'
    );
  END;

⚠️ 关键限制：LOAD_PLANS_FROM_CURSOR_CACHE仅能加载当前仍驻留在库缓存（v$sql）中的计划。若高效计划已被挤出缓存，则需改用替代方案——先用DBMS_SQLTUNE.CREATE_SQLSET从AWR导出计划至SQLSET，再调用LOAD_PLANS_FROM_SQLSET完成加载。

基线已加载但SQL未走预期计划？检查ACCEPTED与ENABLED状态

成功加载SQL计划基线后，优化器就一定会采用吗？不一定。一个基线能否被选用，取决于两个核心状态：enabled（是否启用）和accepted（是否被接受）。常见疏漏是：手动加载时设置了fixed => 'YES'，却未同步将enabled设为'YES'，导致基线未被激活。

如何检查状态？运行以下查询：

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%your_keyword%';

• 若enabled = 'NO'，表示基线被禁用，执行ALTER BASELINE ... ENABLE即可启用。
• 若accepted = 'NO'，说明该计划未通过验证（例如自动演进失败）。此时即使enabled = 'YES'，也不会被优化器选用。
• origin字段也需关注：'MANUAL-LOAD'代表手动导入，'AUTO-CAPTURE'表示自动捕获。后者默认accepted = 'YES'，但fixed = 'NO'。

若发现accepted = 'NO'但希望强制使用该计划，可执行DBMS_SPM.ALTER_SQL_PLAN_BASELINE将状态改为'YES'。但操作前务必确认该计划在当前数据分布下依然高效，否则可能固化一个劣质计划。

启用基线后执行计划仍不稳定？排查OPTIMIZER_USE_SQL_PLAN_BASELINES参数

SQL计划基线功能并非绝对可靠。实例级参数optimizer_use_sql_plan_baselines默认为TRUE，但若被显式设置为FALSE（例如在PDB级别执行ALTER SYSTEM SET optimizer_use_sql_plan_baselines=FALSE），则基线功能将完全失效。

• 检查当前参数值：SHOW PARAMETER optimizer_use_sql_plan_baselines
• 多租户环境需特别注意：必须在目标PDB内确认该参数，CDB$ROOT的设置不会自动继承至PDB。
• 即使参数为TRUE，若SQL中使用了/*+ OPT_PARAM('optimizer_use_sql_plan_baselines' 'FALSE') */优化器提示，也会绕过基线控制。
• 本质限制：基线仅约束执行计划的“形状”（如连接顺序、访问路径），无法消除绑定变量具体值带来的谓词选择性差异。因此，同一基线下，不同的变量值仍可能导致基数（cardinality）估算错误，引发性能波动。

最后，最易被忽视的一点：基线绑定的是SQL文本的精确哈希值。这意味着它对空格、换行符甚至注释都极其敏感。应用程序动态拼接SQL时，即便多出一个空格，sql_id就会改变，导致基线无法匹配。这是确保基线生效的关键细节。