执行计划突变需通过AWR中DBA_HIST_SQLSTAT与DBA_HIST_SQL_PLAN跨快照比对锁定:先筛选SQL_ID在不同快照中PLAN_HASH_VALUE变化且ELAPSED_TIME_DELTA飙升超200%,再用DISPLAY_AWR确认具体变更路径及时间点。

直接结论:执行计划突变不是靠“猜”出来的,而是通过AWR中DBA_HIST_SQLSTAT和DBA_HIST_SQL_PLAN两个视图的跨快照比对锁定的——重点不是看单次慢,而是看同一SQL_ID在不同快照里PLAN_HASH_VALUE是否变化、且ELAPSED_TIME_DELTA同步飙升。
查DBA_HIST_SQLSTAT定位“性能退化+计划变更”双指标SQL
AWR本身不存执行计划,但DBA_HIST_SQLSTAT记录了每个快照周期内每条SQL的资源消耗和计划哈希值。只要PLAN_HASH_VALUE变了,且耗时/逻辑读/物理读明显上涨,就是强嫌疑对象。
- 必须同时过滤
PLAN_HASH_VALUE变化 +ELAPSED_TIME_DELTA增幅 > 200%(OLTP建议阈值) - 别只查最近一个快照——至少对比连续两个基线快照(如快照ID 1000 vs 1005),避免把采样抖动当突变
- RAC环境加
INSTANCE_NUMBER条件,否则可能把节点A的计划变更误判为全局问题 - 示例SQL(找过去24小时内计划变且耗时翻倍的TOP 10):
SELECT sql_id, plan_hash_value, SUM(elapsed_time_delta)/1000000 AS et_sec, COUNT(*) AS snap_count FROM dba_hist_sqlstat WHERE snap_id BETWEEN &start_snap AND &end_snap GROUP BY sql_id, plan_hash_value HAVING COUNT(DISTINCT plan_hash_value) > 1 AND SUM(elapsed_time_delta) > ( SELECT 2 * SUM(elapsed_time_delta) FROM dba_hist_sqlstat s2 WHERE s2.sql_id = dba_hist_sqlstat.sql_id AND s2.snap_id BETWEEN &start_snap-10 AND &start_snap ) ORDER BY et_sec DESC FETCH FIRST 10 ROWS ONLY;
用DBA_HIST_SQL_PLAN确认变更细节和时间点
拿到可疑SQL_ID后,不能只看当前缓存计划——要回溯它在不同快照里实际走的路径。同一SQL_ID对应多个PLAN_HASH_VALUE,说明优化器在不同时期选择了不同策略。
- 执行
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id', NULL, NULL, 'TYPICAL'))时,必须指定plan_hash_value参数,否则默认只显示最新计划 - 重点关注
Operation列:从INDEX RANGE SCAN变成FULL TABLE SCAN是典型索引失效;从NESTED LOOPS变成HASH JOIN可能是统计信息不准或优化器模型差异 - 如果
DBA_HIST_SQL_PLAN查不到某次快照的计划,大概率是该SQL未被AWR捕获(_cursor_plan_unload_time过短或共享池老化),此时需查v$sql_plan补位 - 注意11g的ACS(自适应游标共享):同一
SQL_ID下CHILD_NUMBER不同,计划可能不同,务必用DBA_HIST_SQLSTAT确认哪个子游标贡献了高耗时
排除统计信息和绑定变量干扰
计划突变常被误认为是Bug,其实80%以上源于统计信息陈旧或绑定变量窥探失准。在归因前必须验证这两点。
- 用
DBA_TAB_STATISTICS查目标表LAST_ANALYZED时间,若迁移后未重收集,立刻执行DBMS_STATS.GATHER_TABLE_STATS - 检查
V$SQL中该SQL的IS_BIND_SENSITIVE和IS_BIND_AWARE字段:若为YES但EXECUTIONS低,说明绑定变量导致计划“分裂”,需用DBMS_STATS.SET_TABLE_PREFS关闭窥探或改用直方图 - 对比新旧环境
OPTIMIZER_MODE、OPTIMIZER_FEATURES_ENABLE参数是否一致,11g默认启用adaptive_statistics,可能让计划更“动态” - 临时固定计划验证:用
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE把旧计划导入基线,观察性能是否恢复——这是最直接的因果验证
真正难的不是查到PLAN_HASH_VALUE变了,而是判断这个变化是优化器“学聪明了”还是“认错人了”。同一个SQL_ID在不同快照里出现3个以上PLAN_HASH_VALUE,基本可以确定是绑定变量或统计信息漂移导致的非预期震荡,这时候盯DBA_HIST_SQLBIND里的变量值分布,比盯执行计划本身更有效。











