执行计划看不懂？一文理清 Oracle CBO 优化器工作原理

很多同学打开 Oracle 执行计划，看到 TABLE ACCESS FULL、INDEX RANGE SCAN、NESTED LOOPS 就头大，更别提那些 Cost、Cardinality、Bytes 列了。其实，执行计划不是天书，而是 Oracle CBO（Cost-Based Optimizer，基于成本的优化器）根据你的 SQL 和数据库现状，算出来的一份“最优执行方案”。本文带你从零理清 CBO 的工作原理，让你下次看执行计划时，心里有底。

一、先搞懂：为什么需要 CBO？

在 Oracle 早期，使用的是 RBO（Rule-Based Optimizer，基于规则的优化器），它有一套固定的优先级规则，比如：

走索引永远比全表扫描好
单表访问优于多表连接

这套规则简单粗暴，但严重脱离实际。比如一张表只有 10 行数据，走索引的成本可能比全表扫描还高。于是，Oracle 引入了 CBO，核心逻辑只有一个：谁的成本低，就选谁。

CBO 会为每条可能的执行路径计算一个“成本值”（Cost），然后选择成本最低的方案。这个成本，本质上是估算的 I/O、CPU 和内存资源的综合消耗。

二、CBO 的核心输入：统计信息

CBO 不是算命的，它依赖统计信息来了解数据和环境。没有准确的统计信息，CBO 就是“盲人摸象”。

1. 统计信息包含什么？

表统计信息：行数（Num Rows）、块数（Blocks）、平均行长度
列统计信息：唯一值数量（NDV）、空值数量、数据分布直方图（Histogram）
索引统计信息：索引层级（B-Level）、叶子块数（Leaf Blocks）、聚簇因子（Clustering Factor）
系统统计信息：CPU 速度、单块读/多块读平均耗时

2. 统计信息从哪来？

通过 DBMS_STATS 包收集：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    cascade => TRUE  -- 同时收集索引统计信息
  );
END;
/

⚠️ 注意：统计信息过时，是执行计划变差的头号原因。比如你刚往表里灌了 100 万数据，但统计信息还是 1000 行，CBO 会误以为全表扫描很快，结果就悲剧了。

三、CBO 如何计算成本？拆解 Cost 的构成

执行计划里的 Cost 列，是 CBO 对执行路径的“打分”。这个分数主要由三部分组成：

1. I/O 成本（占比最高）

单块读（Single Block Read） ：随机读，比如通过索引回表，成本高。
多块读（Multi Block Read） ：顺序读，比如全表扫描，一次读多个块，成本低。
CBO 会根据系统统计信息中的 SREADTIM（单块读时间）和 MREADTIM（多块读时间）来换算。

2. CPU 成本

解析 SQL、计算谓词、排序、哈希连接等操作消耗的 CPU 时间。
会被转换为等效的 I/O 成本，统一度量。

3. 网络成本（分布式查询时）

跨数据库链路传输数据的成本，通常忽略不计，除非是分布式查询。

简化公式：

总成本 ≈ I/O 成本 + CPU 成本

四、CBO 的“解题思路”：从 SQL 到执行计划

当你提交一条 SQL，CBO 会经历以下几个阶段：

1. 解析与验证

检查语法、语义、权限。
生成解析树。

2. 逻辑优化（Query Transformation）

视图合并（View Merging） ：把视图里的 SQL 合并到主查询，减少嵌套。
谓词推进（Predicate Pushing） ：把外层条件推到视图内部，提前过滤。
子查询展开（Subquery Unnesting） ：把 IN、EXISTS 子查询转成连接，便于优化。
星型转换（Star Transformation） ：针对星型模型，把维度条件转成事实表上的位图索引访问。

这些转换的目标是：让 SQL 更容易被优化，生成更多候选执行路径。

3. 物理优化（Plan Generation）

这是 CBO 的核心工作：枚举所有可能的执行路径，计算成本，选最优解。

单表访问路径：全表扫描 vs 索引扫描（唯一扫描、范围扫描、全索引扫描等）。
多表连接顺序：3 张表有 6 种连接顺序，4 张表有 24 种，CBO 会用动态规划剪枝，避免穷举。
连接方法：
- 嵌套循环（Nested Loops） ：适合小结果集，驱动表返回一行，被驱动表走索引。
- 哈希连接（Hash Join） ：适合大结果集，把小表建哈希表，大表去探测。
- 排序合并连接（Sort Merge Join） ：适合非等值连接或索引有序的场景。

4. 生成执行计划

最终，CBO 把选中的路径转成执行计划，交给执行引擎。

五、看懂执行计划的关键指标

拿到执行计划，别只看操作类型，重点看这几列：

列名	含义	怎么看
Id	操作序号	从里往外看，缩进最多的先执行
Operation	操作类型	`TABLE ACCESS FULL` 全表扫描，`INDEX RANGE SCAN` 索引范围扫描
Name	对象名	表名或索引名
Rows (Cardinality)	估算返回行数	偏差大说明统计信息不准或数据倾斜
Bytes	估算数据量	辅助判断 I/O 大小
Cost (%CPU)	总成本及 CPU 占比	成本越低越好，%CPU 高说明 CPU 消耗大
Time	估算执行时间	仅供参考，实际受系统负载影响

一个关键认知：Rows 是 CBO 所有计算的基础。如果 Rows 估算错了，后面的成本、连接方法、访问路径大概率都会错。

六、为什么 CBO 会选错执行计划？常见“翻车”场景

即使 CBO 很智能，也会因为以下原因“翻车”：

1. 统计信息过时或不准确

刚批量导入数据，没重新收集统计信息。
数据倾斜严重（比如 status 字段 99% 是 'N'，1% 是 'Y'），没建直方图。

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

第一次硬解析时，CBO 会“偷看”绑定变量的值，生成执行计划并缓存。
如果第一次传入的值选择性很差（比如查 'N'），后续即使传入高选择性的 'Y'，也会复用这个低效计划。

3. 复杂谓词导致估算偏差

OR 条件、LIKE '%xxx'、函数索引、CASE WHEN 等，会让 CBO 难以准确估算 Rows。

4. 参数设置不合理

OPTIMIZER_MODE：设为 ALL_ROWS（默认，侧重吞吐量）还是 FIRST_ROWS_n（侧重响应速度）。
DB_FILE_MULTIBLOCK_READ_COUNT：影响全表扫描的成本计算。

5. 索引聚簇因子（Clustering Factor）过高

索引列的存储顺序和表行的物理顺序差异很大，导致回表时大量随机 I/O，CBO 可能因此放弃索引。

七、实战：如何验证 CBO 的决策？

当你怀疑 CBO 选错了，可以用以下方法“窥探”它的内心：

1. 查看详细执行计划（带 A-TIME）

ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- 执行 SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

重点关注 A-Rows（实际行数）和 E-Rows（估算行数）的偏差。

2. 使用 10053 事件跟踪 CBO

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
-- 执行 SQL
ALTER SESSION SET EVENTS '10053 trace name context off';

跟踪文件会记录 CBO 的每一步计算、统计信息、成本比较，是终极排错手段。

3. 使用 SQL Tuning Advisor

DECLARE
  l_task VARCHAR2(64);
BEGIN
  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'your_sql_id');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task);
  DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(l_task));
END;
/

它会给出优化建议，比如收集统计信息、创建索引、接受 SQL Profile 等。

八、写给开发者的 CBO 友好型 SQL 编写指南

保持统计信息新鲜：大批量操作后主动收集统计信息。
避免对索引列做运算：WHERE TRUNC(create_time) = ... 会让索引失效。
慎用 SELECT * ：只查需要的列，减少 I/O 和内存消耗。
用绑定变量，但注意倾斜数据：对倾斜列，考虑使用直方图或 SQL Patch。
控制事务大小：长事务会持有锁，影响 CBO 对并发成本的判断。
给 CBO 留足选择空间：不要过早用 ROWNUM、DISTINCT 限制结果集，让 CBO 有机会优化连接顺序。

九、总结：执行计划是 CBO 的“答卷”

执行计划不是凭空产生的，它是 CBO 基于统计信息、系统参数和 SQL 结构，经过严密计算后给出的最优解。看懂执行计划，本质是理解 CBO 的决策逻辑：

统计信息是地基，不准就全错。
成本是标尺，I/O 和 CPU 是核心。
Rows 是源头，估算偏差会传导到整个计划。
连接顺序和方法决定了多表查询的性能天花板。

下次再看执行计划，不妨带着这些问题：

CBO 估算的行数和实际差多少？
它为什么选了全表扫描而不是索引？
连接顺序是不是最优的？

当你开始这样思考，就已经跨过了 Oracle 性能优化的第一道门槛。