Cost 13 的 SQL 跑了 349 秒：一次 Oracle 执行计划「成本反向」完整复盘

本文是一次真实生产排查的完整复盘：一条日终余额结转的 INSERT ... SELECT，在 AWR 里累计吃掉约 50.6 小时数据库时间，且同时存在四版执行计划。最戏剧性的结论是——Cost 最贵的计划实测最快，Cost 便宜两万多倍的计划反而是性能黑洞。文中表名、索引名、schema 与实例名均已脱敏，数字与结构保持原样。

一条 SQL，四版计划，50 小时

这条语句是典型的日终批处理：按账号区间分片调用，每个分片约 5000 个账户，把"昨日余额 + 当天借贷汇总"结转成今日余额行：

INSERT INTO biz.acct_balance_d (id, acct_date, ..., end_balance, ...)
SELECT SYS_GUID(), :b3, a.cust_type, a.cust_id, a.acct_id,
       a.end_balance,
       NVL(sub.c_amt,0), NVL(sub.d_amt,0),
       CASE WHEN b.schema_code ... THEN ... END,
       ...
  FROM biz.acct_balance_d a               -- 昨日余额（2.74 亿行，INTERVAL 分区）
  LEFT JOIN (SELECT acct_id,
                    SUM(DECODE(dr_cr,'C',trans_amt,0)) c_amt,
                    SUM(DECODE(dr_cr,'D',trans_amt,0)) d_amt, ...
               FROM biz.acct_trans_dtl    -- 当天交易明细（月度 RANGE 分区 139 个）
              WHERE trans_time >= :b3 AND trans_time < :b3 + 1
                AND acct_id > :b2 AND acct_id <= :b1
              GROUP BY acct_id) sub
    ON sub.acct_id = a.acct_id
  LEFT JOIN biz.acct_mast b ON b.acct_id = a.acct_id
 WHERE a.acct_date >= :b3 - 1 AND a.acct_date < :b3
   AND b.del_flg = 0
   AND a.acct_id > :b2 AND a.acct_id <= :b1;

业务方反馈日终窗口紧张。打开 AWR 一查，这条 sql_id 的历史统计让人睡不着觉：

同一句话，四版计划，慢的 349 秒、快的 14.5 秒——差 24 倍。而当前共享池里生效的，恰好不是最快的那个。

第一幕：执行计划本身就在撒谎

拿到当前计划的 DBMS_XPLAN 输出，第一眼就是成本模型失真：顶层 Cost 299,901、E-Time 显示 59:59（Oracle 的显示上限，实际 ≥1 小时），全部落在右侧嵌套循环上；而左支"当天交易明细的分区扫描 + 聚合"只被估成 Cost 6、E-Rows 1。

再看计划末尾一行小字：basic plan statistics not available——没有 A-Rows，所有数字都是估算。照着这份计划调优，等于跟着一个错误的模型走。

两个最可疑的结构性疑点：

• 明细表索引 IDX_TXN_TIME_1(trans_time, cust_id, acct_id) 以时间前导，acct_id 区间只能做 filter 做不了 access；
• 余额表走的单列全局哈希分区索引 IDX_BAL_ACCT(acct_id)，探测估出 71 行、回表后按账期过滤只剩 1 行——71 倍的随机回表是白读的。

插叙：access 与 filter 的差别

复合索引的条目排序是"逐列分层"的：先按 trans_time 排，trans_time 相同的再按 cust_id、acct_id 排。当条件是 trans_time >= :b3 AND trans_time < :b3+1（范围）时，这一段在索引里连续，可以做 access；但在这一段内部，条目是按 cust_id 再 acct_id 排序的——acct_id = 1002、4120、1777 互相隔开，acct_id > :b2 AND <= :b1 这个区间框不出一段连续位置。没有"从哪开始、到哪停"，Oracle 就只能把整个时间段里的条目全部读出来，每条再判断一次 acct_id——这正是计划里两行谓词的含义：

access:  "trans_time" >= :B3 AND "trans_time" < :B3+1
filter:  "acct_id" > GREATEST(:B2,:B2) AND "acct_id" <= LEAST(:B1,:B1)

由此得到一条通用规则：复合索引中，第一个出现范围条件（>、<、BETWEEN、LIKE 前缀）的列之后的列，都只能做 filter；等值条件（=）不破坏排序，可以一路 access 下去。 这就是为什么 (acct_id, trans_time)（等值在前、范围在后）两列都能 access，而 (trans_time, cust_id, acct_id) 只有第一列能。本例更亏的是中间还卡着一个本语句根本用不上的 cust_id，排序离 acct_id 更远——结果就是每个分片都要把当天切片扫一遍，再逐条过滤。

第二幕：收完统计信息，计划真的换了

先收了账户主表和余额表的统计信息。按 no_invalidate 默认的 AUTO_INVALIDATE 机制，依赖游标会在 0~5 小时内滚动失效——这次连 PURGE 都没用，一小时后的快照直接给出了答案：

20:00 的快照里，坏计划还在跑（299.62 秒/次、单次 3,216 万逻辑读）；21:00 的快照里，计划已经切到 554147421——每次 17.85 秒，逻辑读 2.8 万次，降了三个数量级。

机理值得说透：坏计划之所以被"估"得便宜，是因为外层账户/余额侧被估成 1 行（VIEW PUSHED PREDICATE 只按重跑一次计费）。这两张表的基数修正后，CBO 第一次算出了"4,997 次重跑"的真实代价，坏计划自然出局。这正是收统计最理想的效果：零 DDL，让优化器自己改对主意。

一个遗留项：排查中发现明细表当期分区从未收集统计信息——PART2026_09 起的分区 num_rows 全为空，当月分区已 STALE=YES。这是坏计划反复被生成的"土壤"（优化器曾多次从失真的基数里把它"估"成最便宜），根治动作见后文"处置"一节。

第三幕：把四版计划挖出来，用"每次执行"重新算账

从 AWR 把四版计划的结构挖出来对比：

如果只看 Cost，答案显而易见：钉 13 那版。但先把归一化做了再说。第一步校验可比性——四版计划的 rows_per_exec = 4978.7 / 4976.8 / 4997.4 / 4925.9，极差仅 1.5%，说明各计划执行时分片规模一致，可以直接横比。

然后是本文最核心的一张表（按每次执行耗时排序）：

成本模型在两个方向上全部反向：

• Cost 13 的计划，每次 349 秒，占 AWR 总耗时 86%；
• Cost 299,901 的计划，每次只有 14.5 秒，反而是最快的——它的 20 万次逻辑读几乎全被 buffer cache 吸收（物理读仅 1,439 次，命中率 99.3%）。

顺带一个副产物：rows_per_exec 全部约等于 5000 而不是 0，说明这条 INSERT 确实在插数据，"绑定变量传 NULL 导致静默插 0 行"的风险可以排除。

机理：Cost 13 为什么跑了 349 秒

把 2375852073 的计划骨架摆出来，账就清楚了：

id 3  NESTED LOOPS OUTER          E-Rows = 1   Cost 13
id 5    余额表 A（驱动侧）          E-Rows = 1   Cost  6   ← 被估成 1 行
id 10   VIEW PUSHED PREDICATE      E-Rows = 1   Cost  5   ← 聚合被推进循环
id 15     INDEX RANGE SCAN IDX_TXN_TIME_1       Cost  4

VIEW PUSHED PREDICATE 把聚合子查询变成了参数化子查询：外层每出 1 行，整个"当天切片扫描 + 聚合"就重跑一次。

CBO 的账本：外层估 1 行，内层只算 1 次，1 × 5 + 8 ≈ 13。

实际的账本：外层实际约 4,997 行/分片，内层被重跑 4,997 次；每次重跑都沿 IDX_TXN_TIME_1 扫当天切片——第三列 acct_id 只能 filter 不能 access——单次约 5,737 次逻辑读，合计 2,856 万 LIO。而物理读只有 5,132 次：这些逻辑读几乎全部命中 buffer cache，不等磁盘，全在烧 CPU（CPU 320.7s / 349.2s = 92%）。

一句话总结机理：嵌套循环的 Cost = 外层基数 × 内层单次成本。基数被估错多少倍，Cost 就错多少倍。 而"估 1 行"的来源，正是第二幕的遗留项——明细表热分区无统计信息，加上 INDEX SKIP SCAN 天然的低估倾向。

这也回答了一个经典疑问：Cost (%CPU)=100 只说明成本构成以 CPU 为主，与绝对快慢毫无关系。

处置：钉住最优计划，补统计根治

第一步（零 DDL、零窗口、秒级可回退）：用 SQL Plan Baseline 把实测最优的计划钉住，防止计划再次回漂。

第二幕里收统计后优化器自动选中的 554147421，逻辑读最低（2.8 万/次）、CPU 最省（2.37 s/次），比靠 99.3% 缓存命中率撑着的 4034499000（20 万逻辑读/次）更抗压——钉它：

-- 目标计划此刻就在共享池，直接从游标缓存抓成基线
DECLARE n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         sql_id => '<SQL_ID>', plan_hash_value => 554147421);
  DBMS_OUTPUT.PUT_LINE('loaded plans = ' || n);
END;
/

⚠️ 两个必须注意的关卡：

• LOAD_PLANS_FROM_CURSOR_CACHE 只读本实例游标缓存，不读 AWR。指定的 plan_hash_value 不在池中时不报错、只返回 0——此时必须改走 AWR → SQL Tuning Set → LOAD_PLANS_FROM_SQLSET 路线。
• 如果走 STS 路线，它会把 AWR 里的全部计划一起载入，包括 Cost 12,968 的那版——基线内部是按 Cost 比较的，不禁用它，基线等于白建。必须"留一个、禁其余"：目标计划 ENABLED=YES + ACCEPTED=YES，其余 ENABLED=NO + ACCEPTED=NO 双保险（防止被 evolve 自动接受）。

载入后清游标（DBMS_SHARED_POOL.PURGE('address,hash','C')）让它立即生效，验证 v$sql 的 plan_hash_value 与 DISPLAY_CURSOR 输出 Note 段的 SQL plan baseline ... used。

第二步（根治）：补明细表热分区统计，让基数不再塌陷（第二幕的遗留项）。

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'BIZ',
     tabname=>'ACCT_TRANS_DTL', partname=>'PART2026_09',
     granularity=>'PARTITION', cascade=>TRUE, no_invalidate=>FALSE);

基数修对之后，CBO 自己就能算出"4997 × 单次成本"的真实代价，坏计划根本不会被生成——这比钉基线更治本。

第三步（看护）：选定的计划也要留个心眼——本例各版计划的快慢对缓存条件很敏感（4034499000 就全靠 99.3% 命中率撑着，缓存压力上来会向 30 秒方向漂移）。设一条告警红线：cpu_s_per_exec > 50 或 gets_per_exec > 1,000,000 就回来看计划结构，八成是坏计划回来了。

写在最后

这次排查最有价值的不是某个具体参数，而是两个认知修正：

Cost 是模型输出，不是运行结果。 它的每一个乘数都是估算基数——基数因统计缺失而塌陷时，Cost 可以错出几个数量级，而且两个方向都可能反向。Cost 能解释"优化器为什么选错"，不能预测"哪个跑得更快"。

排错顺序比优化技巧重要。 先用 AWR 算清楚"谁在吃时间"（占比排序），再用归一化数据横比候选计划，最后才是索引与改写。顺序反了，就会像我们前几轮那样，在只值 6 秒的节点上精算，而放跑了 43 小时的黑洞。

注：文中表名、索引名、schema、实例名与 sql_id 均已脱敏，plan_hash_value 为文本哈希无业务含义，予以保留；所有数字均为 AWR 实测。

专注于 Oracle / MySQL / PostgreSQL / 达梦等主流数据库的运维实战与架构分享，欢迎关注，一起做靠谱的数据库人。