Oracle 执行计划突变排查实战：绑定变量窥探与 SQL 计划基线
运维群里最让人头皮发麻的一种告警，不是数据库连不上，而是"某条 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. 先证明"计划变了"
-- 同一 sql_id 下的多个 child cursor 与计划，按性能排序
SELECT child_number, plan_hash_value, executions,
ROUND(elapsed_time/1000000/GREATEST(executions,1), 3) AS sec_per_exec,
ROUND(buffer_gets/GREATEST(executions,1)) AS gets_per_exec,
is_bind_sensitive, is_bind_aware, is_shareable,
last_active_time
FROM v$sql
WHERE sql_id = '&sql_id'
ORDER BY gets_per_exec DESC;
-- 看历史快照里 plan_hash_value 的切换（需要 AWR 许可）
SELECT s.snap_id, TO_CHAR(b.end_interval_time,'MM-DD HH24:MI') AS snap_time,
s.plan_hash_value, s.executions_delta,
ROUND(s.elapsed_time_delta/1000000/GREATEST(s.executions_delta,1),3) AS sec_per_exec,
ROUND(s.buffer_gets_delta/GREATEST(s.executions_delta,1)) AS gets_per_exec
FROM dba_hist_sqlstat s, dba_hist_snapshot b
WHERE s.sql_id = '&sql_id' AND s.snap_id = b.snap_id
ORDER BY b.end_interval_time;
只要看到某两个快照之间 plan_hash_value 变了、gets_per_exec 从几百跳到几十万，结论就锁定了。
2. 看清两个计划的差异
-- 内存中的真实计划（含窥探到的绑定值）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_no, 'ADVANCED +PEEKED_BINDS'));
-- 已经被刷出内存的历史计划
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. 复现一次绑定变量窥探
-- 构造倾斜数据
CREATE TABLE t_order (
id NUMBER, status VARCHAR2(2), amount NUMBER, create_time DATE);
INSERT INTO t_order
SELECT ROWNUM, 'S', 100, SYSDATE-ROWNUM/1440 FROM dual CONNECT BY LEVEL <= 990000;
INSERT INTO t_order
SELECT 990000+ROWNUM, 'C', 100, SYSDATE-ROWNUM/1440 FROM dual CONNECT BY LEVEL <= 10000;
COMMIT;
CREATE INDEX idx_order_status ON t_order(status);
-- 只收集基础统计信息，不建直方图，模拟最常见的事故现场
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T_ORDER',
cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1');
END;
/
-- 先传 'C'（1%），走索引
VARIABLE v_status VARCHAR2(2);
EXEC :v_status := 'C';
SELECT COUNT(amount) FROM t_order WHERE status = :v_status;
-- 再传 'S'（99%），复用同一计划，回表 99 万行
EXEC :v_status := 'S';
SELECT COUNT(amount) FROM t_order WHERE status = :v_status;
-- 观察游标状态
SELECT child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(amount) FROM t_order%';
补上直方图后再跑一遍，对比效果：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T_ORDER',
method_opt=>'FOR COLUMNS SIZE AUTO status');
END;
/
4. 用 SQL Plan Baseline 把好计划钉住
先把好计划加载到基线（假设 plan_hash_value=1234567890 是好的那个）：
VARIABLE n NUMBER;
BEGIN
:n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '&sql_id',
plan_hash_value => 1234567890,
fixed => 'YES');
END;
/
PRINT n;
-- 查看基线
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin, created
FROM dba_sql_plan_baselines;
-- 让基线随数据演进（建议在维护窗口做）
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'&sql_handle') FROM dual;
如果好计划已经被刷出内存、只留在 AWR 里，就走 SQL Tuning Set 这条路：
-- 建 SQLSET，从 AWR 里把该 sql_id 的两个快照区间捞出来
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET('sst_plan_fix');
DBMS_SQLTUNE.LOAD_SQLSET('sst_plan_fix',
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap, &end_snap,
q''));
END;
/
-- 从 SQLSET 加载计划到基线
SELECT DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name=>'sst_plan_fix', basic_filter=>q'') FROM dual;
加载完记得把没被 accepted 的计划确认一遍，只保留你验证过的那个，避免基线里塞进一堆垃圾计划。
5. 临时止血：SQL Patch（不改一行代码）
线上等不了变更窗口时，用 SQL Patch 直接注入 hint：
BEGIN
DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id => '&sql_id',
hint_text=> 'FULL(t_order)',
name => 'patch_hotfix_order_full',
description=> '临时强制全表扫，待基线固定后删除');
END;
/
它是应急手段，不是终点。基线固定好之后，记得 DBMS_SQLDIAG.DROP_SQL_PATCH(name=>'patch_hotfix_order_full')，否则半年后没人记得这玩意儿，数据分布变了它还在强制全表扫。
6. 前后对比
某次真实治理的效果（订单查询主 SQL，19c，单实例）：
指标突变后固定基线后
plan_hash_value2 个交替出现稳定 1 个
单次逻辑读1,842,3004,120
平均耗时38.6 秒0.19 秒
日执行次数12,40012,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 体面得多，也稳得多。
页:
[1]