Oracle CBO为何选择错误执行计划？统计信息是关键

发布于 2025-10-29 分类: 数据库
系列文章: Oracle性能之心：统计信息管理与优化实战
第 1 部分: Oracle CBO为何选择错误执行计划？统计信息是关键 (当前) 第 2 部分: Oracle自动统计信息不更新原因排查：深入GATHER_STATS_JOB工作原理 第 3 部分: 实战排错：解决Oracle ORA-20005统计信息锁定导致的执行计划错误 第 4 部分: Oracle锁定表统计信息防止执行计划改变：何时与如何操作？ 第 5 部分: Oracle DBA高级指南：构建高效的数据库统计信息收集策略

引言：看不见的“指挥家”

在Oracle数据库的世界里，我们每天都在编写和执行SQL。当一条SQL语句提交给数据库时，背后有一个极其智能的组件——成本优化器（Cost-Based Optimizer, CBO），它就像一位经验丰富的交响乐团指挥家。它的任务是在成千上万种可能的数据访问路径中，选择一个成本最低、效率最高的“演奏方案”，我们称之为执行计划（Execution Plan）。

然而，这位指挥家并非凭空想象来做决策。他严重依赖一份详尽的“乐谱”——统计信息（Statistics）。这份“乐谱”详细记录了数据库中每个对象（表、索引等）的规模、数据分布等特性。如果乐谱是准确的，指挥家就能奏出华美的乐章（高效的执行计划）；反之，如果乐谱陈旧不堪，就会直接导致 Oracle CBO选择错误的执行计划，引发一场性能灾难。

本篇作为系列教程的开篇，我们将聚焦于以下三个核心问题：

  1. CBO的智慧之源：CBO到底是如何工作的？
  2. 统计信息是什么：这份“乐谱”里都记录了些什么？
  3. 蝴蝶效应：陈旧的统计信息，如何导致CBO做出灾难性决策？

（一）CBO的智慧之源：从“固定规则”到“成本估算”

早期的Oracle使用基于规则的优化器（Rule-Based Optimizer, RBO）。RBO像一本古老的法典，对任何SQL都遵循一套固定的、预设的规则（例如：“有索引就一定用索引”）。这种方式简单直接，但在复杂场景下往往做出非常糟糕的选择。

为了解决这个问题，CBO应运而生。它不再依赖死板的规则，而是为每一种可能的执行路径计算一个预估的“成本值”（通常与I/O、CPU消耗相关），然后选择成本最低的那个。这个过程的好坏，直接决定了最终执行计划的效率。

CBO决策过程可以类比为使用地图App导航：

特性 地图App导航 Oracle CBO
目标 从A点到B点找到最快路线 高效地执行一条SQL查询
决策依据 实时路况、道路限速、距离、红绿灯数量 统计信息（表的行数、数据分布、索引情况等）
可选路径 走高速、走国道、穿小路 全表扫描、索引扫描、嵌套循环、哈希连接等
最终选择 预估时间最短的路线 预估“成本”最低的执行计划

这个决策过程的核心，就是统计信息。没有准确的统计信息，CBO的成本估算就成了无源之水、无本之木，其“智慧”也就无从谈起，最终极易选择错误的执行计划。

（二）统计信息是什么：解构CBO的“情报网络”

统计信息是CBO进行成本估算的数据基础。它不是实时计算的，而是通过特定作业（自动或手动）收集并存储在数据字典中。以下是CBO最关心的一些核心情报：

1. 表的统计信息 (DBA_TABLES)

  • NUM_ROWS：表的总行数。这是最基础、最重要的信息。CBO用它来估算全表扫描的成本。
  • BLOCKS：表占用的数据块数量。结合NUM_ROWS，可以计算出数据的密集程度。
  • AVG_ROW_LEN：平均行长度。

2. 列的统计信息 (DBA_TAB_COL_STATISTICS)

  • NUM_DISTINCT (也称基数/Cardinality)：列中不重复值的数量。这个值至关重要，它直接影响CBO对 WHERE col = 'value' 这类条件的过滤性（selectivity）的判断。
  • NUM_NULLS：列中NULL值的数量。
  • LOW_VALUE / HIGH_VALUE：列中的最小值和最大值。

3. 索引的统计信息 (DBA_INDEXES)

  • LEAF_BLOCKS：索引叶块的数量。CBO用它来估算索引扫描的成本。
  • CLUSTERING_FACTOR (集群因子)：一个非常关键的指标。它表示基于该索引键排序的数据行在表中的物理存储离散程度。值越接近表的BLOCKS数，说明数据物理存储越有序，索引扫描后访问表数据的成本越低；值越接近表的NUM_ROWS数，说明数据存储越离散，索引扫描效率越低。

4. 直方图 (Histograms - DBA_HISTOGRAMS)

当一列的数据分布严重不均匀（数据倾斜）时，仅靠NUM_DISTINCT无法让CBO做出准确判断。此时，直方图就像是为这列数据绘制的一张详细的“人口普查地图”，告诉CBO哪个值出现的频率高，哪个值出现的频率低。

（三）蝴蝶效应：统计信息如何导致CBO选择错误的执行计划

理论是苍白的，让我们通过一个生动的案例来感受统计信息的巨大威力。

场景设定：
我们有一个 ORDERS (订单) 表，包含1000万条记录。其中有一个ORDER_STATUS (订单状态) 字段，并在此字段上创建了索引 IDX_ORDER_STATUS。

订单状态分布极不均匀：

  • 'COMPLETED' (已完成): 9,990,000 条 (占比 99.9%)
  • 'PENDING' (待处理): 10,000 条 (占比 0.1%)

Case 1: 统计信息准确无误

当统计信息准确时，CBO知道ORDER_STATUS列的数据分布。

查询A：SELECT * FROM ORDERS WHERE ORDER_STATUS = 'COMPLETED';

  • CBO的决策过程：
    1. 评估索引扫描成本：ORDER_STATUS = 'COMPLETED' 会命中999万条记录。使用索引意味着要先读取大量索引块，然后根据索引中的rowid，对表进行999万次单块读（逻辑I/O），这会产生巨大的I/O成本。成本极高！
    2. 评估全表扫描成本：直接读取表的所有数据块，一次性将999万条记录过滤出来。虽然读取了整个表，但大多是连续的多块读，效率远高于999万次随机读。成本相对较低。
    3. 最终决策：选择全表扫描 (Full Table Scan)。这是一个正确的执行计划。

查询B：SELECT * FROM ORDERS WHERE ORDER_STATUS = 'PENDING';

  • CBO的决策过程：
    1. 评估索引扫描成本：ORDER_STATUS = 'PENDING' 只会命中1万条记录。通过索引快速定位这1万条记录的rowid，然后回表查询。成本很低。
    2. 评估全表扫描成本：仍然需要读取整张千万级的表。成本很高。
    3. 最终决策：选择索引范围扫描 (Index Range Scan)。这同样是正确的执行计划。

决策过程可视化：

graph TD
    subgraph CBO决策 (统计信息准确)
        A[SQL: ...WHERE ORDER_STATUS = 'COMPLETED'] --> B{评估路径};
        B --> C[索引扫描<br/>成本: 1,000,000];
        B --> D[全表扫描<br/>成本: 10,000];
        D --> E((选择正确计划: 全表扫描));

        F[SQL: ...WHERE ORDER_STATUS = 'PENDING'] --> G{评估路径};
        G --> H[索引扫描<br/>成本: 100];
        G --> I[全表扫描<br/>成本: 10,000];
        H --> J((选择正确计划: 索引扫描));
    end

Case 2: 统计信息陈旧或缺失

假设这张表很久没有收集统计信息了，CBO的“情报”还停留在表刚创建时只有100条记录的阶段。这是导致Oracle CBO选择错误执行计划最常见的原因。

查询A：SELECT * FROM ORDERS WHERE ORDER_STATUS = 'COMPLETED';

  • CBO的决策过程：
    1. 错误的情报：CBO认为表总共只有100行。
    2. 错误的估算：它会猜测'COMPLETED'状态的记录可能只有几十条。对于几十条记录，走索引当然是最佳选择。
    3. 灾难性决策：CBO选择了索引范围扫描。
    4. 实际执行：Oracle开始使用索引，结果发现需要回表999万次！这导致了海量的随机I/O，数据库CPU飙升，查询执行几小时都无法返回结果。这就是一个典型的、由陈旧统计信息导致的性能灾难。

决策过程可视化：

graph TD
    subgraph CBO决策 (统计信息陈旧)
        A[SQL: ...WHERE ORDER_STATUS = 'COMPLETED'] --> B{评估路径 (基于错误信息: 表只有100行)};
        B --> C[索引扫描<br/>预估成本: 5];
        B --> D[全表扫描<br/>预估成本: 10];
        C --> E((灾难性决策: 错误地选择索引扫描));
        E -- 实际执行 --> F{性能雪崩<br/>(999万次回表)};
    end

这个例子清晰地展示了，CBO的决策完全建立在统计信息之上。准确的统计信息是CBO做出正确决策的生命线。

（四）如何查看统计信息：DBA的基础侦察技能

当怀疑CBO选择了错误的执行计划时，第一步就是检查相关的统计信息，特别是 LAST_ANALYZED (上次收集时间)，来判断信息是否陈旧。

1. 查看表的统计信息：

SELECT 
    OWNER,
    TABLE_NAME,
    NUM_ROWS,
    BLOCKS,
    TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ANALYZED
FROM 
    DBA_TABLES
WHERE 
    TABLE_NAME = 'YOUR_TABLE_NAME' AND OWNER = 'YOUR_SCHEMA_NAME';
  • 关注点：LAST_ANALYZED 时间是否过旧？NUM_ROWS 是否与实际行数严重不符？

2. 查看列的统计信息：

SELECT 
    COLUMN_NAME,
    NUM_DISTINCT,
    NUM_NULLS,
    TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_ANALYZED
FROM 
    DBA_TAB_COL_STATISTICS
WHERE 
    TABLE_NAME = 'YOUR_TABLE_NAME' AND OWNER = 'YOUR_SCHEMA_NAME';
  • 关注点：NUM_DISTINCT 是否符合业务逻辑？对于数据倾斜的列，这个值可能具有误导性。

-- 感谢阅读 --