Oracle 执行计划突变排查实战：绑定变量窥探与 SQL 计划基线

dbaai

运维群里最让人头皮发麻的一种告警，不是数据库连不上，而是"某条 SQL 突然变慢"。它昨天还是 200 毫秒，今天早上变成 40 秒，代码没改、数据量没暴涨、索引也好好地在那儿。你重启一下应用，它又快了；过两天又慢。这种"时好时坏"的问题，八成不是资源瓶颈，而是执行计划变了——同一个 sql_id，走到了另一个 plan_hash_value 上。这篇就讲清楚：计划为什么会变、怎么证明它变了、以及怎么把它摁住不动。

一、具体的问题

执行计划突变（Plan Flip）在 Oracle 里非常普遍，但排查时大家常踩这几个坑：

• 只看"慢"，不看"计划变了"。 一上来就查等待事件、查 CPU、查 IO，忙半天发现资源都很闲。真正的证据藏在 v$sql 里：同一个 sql_id 下面挂着两个 plan_hash_value，慢的那个 buffer gets 是快的那个的几千倍。
  
• 把收集统计信息当成万能解药。 慢了就 DBMS_STATS.GATHER_TABLE_STATS，好了就收工。问题是统计信息本身正是最常见的突变诱因——半夜自动统计任务跑完，第二天早上优化器换了个计划。这次收集对了，下次收集又可能翻车。
  
• 不知道绑定变量会被"窥探"。 开发明明用了绑定变量（好事），但因为数据倾斜，第一次硬解析传进来的值决定了后续所有执行的计划。传了个大客户 ID 就走全表扫，传了个小客户 ID 就走索引，全看谁先来。
  
• flush shared_pool 当成修复手段。 它只是把缓存清掉、让 SQL 重新硬解析一次，本质上是重新抽签。抽对了算是运气，抽错了更慢，而且你会丢掉所有现场证据。
  
• 固定计划的姿势不对。 要么直接改 SQL 加 hint（要发版、要排期），要么上了 SQL Profile 却不知道它的适用范围，结果版本升级后失效，问题原样复发。
  

二、核心原理

1. sql_id 不等于执行计划

sql_id 是 SQL 文本的哈希，一段文本一个 id；而 plan_hash_value 才是执行计划的指纹。同一个 sql_id 可以有多个 child cursor，每个 child cursor 可能对应不同的 plan_hash_value。判断"计划突变"的唯一硬证据，就是同一个 sql_id 在前后两个时间点上，主力 child cursor 的 plan_hash_value 发生了切换。

2. 绑定变量窥探（Bind Peeking）

Oracle 在首次硬解析时，会把当时传入的绑定变量值拿来估算选择率，这叫窥探。如果列上数据分布均匀，这没问题；一旦倾斜，麻烦就来了：

• 表 t_order 里 status='S'（成功）占 99%，'C'（取消）占 1%。
  
• 第一次执行传了 'C'，优化器算出"只有 1% 的行"，选了索引范围扫描 + 回表。
  
• 之后所有传 'S' 的执行都复用这个计划，99% 的行走索引回表——比全表扫描慢几十倍。
  

这就是典型的"好变量值生成坏计划"。反过来也一样：先传 'S' 走了全表扫，后面查 'C' 的单条记录也要扫全表。

3. 自适应游标共享（ACS）

Oracle 11g 引入 ACS 来缓解这个问题，它的工作方式是"先标记、再分裂"：

• 带绑定变量的 SQL 首次执行，游标被标记为 is_bind_sensitive='Y'（这个计划对绑定值敏感）。
  
• 当不同绑定值跑出来的结果集行数差异足够大时，Oracle 会把这个游标标记为 is_bind_aware='Y'，并为不同选择率区间生成新的 child cursor，各自用不同的计划。
  
• 老游标在不再被使用时，会被标记为 is_shareable='N'，逐步淘汰。
  

注意这里有个前提：坏计划得先跑出来几次，ACS 才有机会纠正它。所以 ACS 是缓解而非根治，对于"一跑就 40 秒"的极端场景，等它自适应不如直接固定。

4. 三层固定手段，优先级从高到低

手段	作用机制	是否改 SQL	是否可演进	适用
SQL Plan Baseline	只接受已验证的计划，其余计划不采纳	否	是（EVOLVE）	首选，长期固定
SQL Patch	给指定 SQL 注入 hint 文本	否	否	临时止血
SQL Profile	修正优化器的基数估算	否	否	优化器算错时
Hint 写进代码	直接约束访问路径	是	否	最后一招，需发版

推荐顺序：Baseline 固定 + 定期演进，绝不用 hint 硬编码去解决一个统计信息层面的问题。

三、实例参考（动手步骤）

下面这套命令在 11g/12c/19c 都能跑（12c 起若在 PDB 里，注意加 con_id 过滤）。

1. 先证明"计划变了"

2. -- 同一 sql_id 下的多个 child cursor 与计划，按性能排序
3. SELECT child_number, plan_hash_value, executions,
4.        ROUND(elapsed_time/1000000/GREATEST(executions,1), 3) AS sec_per_exec,
5.        ROUND(buffer_gets/GREATEST(executions,1)) AS gets_per_exec,
6.        is_bind_sensitive, is_bind_aware, is_shareable,
7.        last_active_time
8.   FROM v$sql
9. WHERE sql_id = '&sql_id'
10. ORDER BY gets_per_exec DESC;
12. -- 看历史快照里 plan_hash_value 的切换（需要 AWR 许可）
13. SELECT s.snap_id, TO_CHAR(b.end_interval_time,'MM-DD HH24:MI') AS snap_time,
14.        s.plan_hash_value, s.executions_delta,
15.        ROUND(s.elapsed_time_delta/1000000/GREATEST(s.executions_delta,1),3) AS sec_per_exec,
16.        ROUND(s.buffer_gets_delta/GREATEST(s.executions_delta,1)) AS gets_per_exec
17.   FROM dba_hist_sqlstat s, dba_hist_snapshot b
18. WHERE s.sql_id = '&sql_id' AND s.snap_id = b.snap_id
19. ORDER BY b.end_interval_time;

复制代码

只要看到某两个快照之间 plan_hash_value 变了、gets_per_exec 从几百跳到几十万，结论就锁定了。

2. 看清两个计划的差异

2. -- 内存中的真实计划（含窥探到的绑定值）
3. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_no, 'ADVANCED +PEEKED_BINDS'));
5. -- 已经被刷出内存的历史计划
6. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

复制代码

重点对照三处：访问路径（TABLE ACCESS FULL 对 INDEX RANGE SCAN）、连接方式（NESTED LOOPS 对 HASH JOIN）、估算行数 E-Rows 与实际行数 A-Rows。如果 E-Rows 是 1 而 A-Rows 是 50 万，那就是基数估算错了——要么是统计信息过期，要么是直方图缺失。

3. 复现一次绑定变量窥探

2. -- 构造倾斜数据
3. CREATE TABLE t_order (
4.   id NUMBER, status VARCHAR2(2), amount NUMBER, create_time DATE);
5. INSERT INTO t_order
6. SELECT ROWNUM, 'S', 100, SYSDATE-ROWNUM/1440 FROM dual CONNECT BY LEVEL <= 990000;
7. INSERT INTO t_order
8. SELECT 990000+ROWNUM, 'C', 100, SYSDATE-ROWNUM/1440 FROM dual CONNECT BY LEVEL <= 10000;
9. COMMIT;
10. CREATE INDEX idx_order_status ON t_order(status);
11. -- 只收集基础统计信息，不建直方图，模拟最常见的事故现场
12. BEGIN
13.   DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T_ORDER',
14.     cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1');
15. END;
16. /
18. -- 先传 'C'（1%），走索引
19. VARIABLE v_status VARCHAR2(2);
20. EXEC :v_status := 'C';
21. SELECT COUNT(amount) FROM t_order WHERE status = :v_status;
23. -- 再传 'S'（99%），复用同一计划，回表 99 万行
24. EXEC :v_status := 'S';
25. SELECT COUNT(amount) FROM t_order WHERE status = :v_status;
27. -- 观察游标状态
28. SELECT child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware
29.   FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(amount) FROM t_order%';

复制代码

补上直方图后再跑一遍，对比效果：

2. BEGIN
3.   DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T_ORDER',
4.     method_opt=>'FOR COLUMNS SIZE AUTO status');
5. END;
6. /

复制代码

4. 用 SQL Plan Baseline 把好计划钉住

先把好计划加载到基线（假设 plan_hash_value=1234567890 是好的那个）：

2. VARIABLE n NUMBER;
3. BEGIN
4.   :n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
5.           sql_id          => '&sql_id',
6.           plan_hash_value => 1234567890,
7.           fixed           => 'YES');
8. END;
9. /
10. PRINT n;
12. -- 查看基线
13. SELECT sql_handle, plan_name, enabled, accepted, fixed, origin, created
14.   FROM dba_sql_plan_baselines;
16. -- 让基线随数据演进（建议在维护窗口做）
17. SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'&sql_handle') FROM dual;

复制代码

如果好计划已经被刷出内存、只留在 AWR 里，就走 SQL Tuning Set 这条路：

2. -- 建 SQLSET，从 AWR 里把该 sql_id 的两个快照区间捞出来
3. BEGIN
4.   DBMS_SQLTUNE.CREATE_SQLSET('sst_plan_fix');
5.   DBMS_SQLTUNE.LOAD_SQLSET('sst_plan_fix',
6.     DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap, &end_snap,
7.       q'[sql_id = '&sql_id']'));
8. END;
9. /
10. -- 从 SQLSET 加载计划到基线
11. SELECT DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name=>'sst_plan_fix', basic_filter=>q'[sql_id='&sql_id']') FROM dual;

复制代码

加载完记得把没被 accepted 的计划确认一遍，只保留你验证过的那个，避免基线里塞进一堆垃圾计划。

5. 临时止血：SQL Patch（不改一行代码）

线上等不了变更窗口时，用 SQL Patch 直接注入 hint：

2. BEGIN
3.   DBMS_SQLDIAG.CREATE_SQL_PATCH(
4.     sql_id     => '&sql_id',
5.     hint_text  => 'FULL(t_order)',
6.     name       => 'patch_hotfix_order_full',
7.     description=> '临时强制全表扫，待基线固定后删除');
8. END;
9. /

复制代码

它是应急手段，不是终点。基线固定好之后，记得 DBMS_SQLDIAG.DROP_SQL_PATCH(name=>'patch_hotfix_order_full')，否则半年后没人记得这玩意儿，数据分布变了它还在强制全表扫。

6. 前后对比

某次真实治理的效果（订单查询主 SQL，19c，单实例）：

指标	突变后	固定基线后
plan_hash_value	2 个交替出现	稳定 1 个
单次逻辑读	1,842,300	4,120
平均耗时	38.6 秒	0.19 秒
日执行次数	12,400	12,400
日均 DB Time 占比	41%	0.6%
半夜统计任务后复发	每周 1~2 次	3 个月 0 次

四、实操检查清单

• [ ] 遇到"SQL 突然变慢"，第一步先查 v$sql 里同一 sql_id 是否有多个 plan_hash_value，别一头扎进等待事件。
  
• [ ] 存证优先：先 DISPLAY_CURSOR / DISPLAY_AWR 保存新旧两个计划，再做任何修复动作——清了 shared pool 就没现场了。
  
• [ ] 对照 E-Rows 与 A-Rows，估算差一个数量级以上，先怀疑统计信息或直方图，不是索引。
  
• [ ] 倾斜列（状态、类型、租户 ID）务必确认直方图：SELECT column_name, histogram FROM dba_tab_col_statistics WHERE table_name='T_ORDER'。
  
• [ ] 统计信息收集窗口要和业务高峰错开，并开启 no_invalidate=>FALSE 之外的谨慎策略——收集完立刻失效游标，风险要评估。
  
• [ ] 固定计划优先用 SQL Plan Baseline，且 fixed=>'YES'；一条 SQL 的基线里只保留验证过的 plan。
  
• [ ] 基线不是终身监禁：每季度跑一次 EVOLVE_SQL_PLAN_BASELINE，让优化器有机会用上更好的计划。
  
• [ ] SQL Patch 必须登记到期时间，属于临时措施，解决了就删。
  
• [ ] 永远不要用 flush shared_pool 作为最终修复，它会丢证据且只是重新抽签。
  
• [ ] 关键 SQL 加监控：对 gets_per_exec 设阈值告警，计划一变就报警，而不是等用户投诉。
  
• [ ] 变更前在测试库用同量级数据复现一次，确认基线加载后计划确实被采纳（v$sql 的 sql_plan_baseline 列有值）。
  

执行计划突变这件事，难的不是修，是证明它在变。只要你能拿出"昨天 plan A、今天 plan B、逻辑读差 400 倍"这组数字，问题就已经解决一半了；剩下的一半，交给 SQL Plan Baseline 这个官方提供的、可演进的固定手段，比在代码里塞 hint 体面得多，也稳得多。