4. 优化器与成本模型
Oracle 的 SQL 执行计划由优化器生成，而优化器的工作方式决定了同一条 SQL 在不同环境、不同统计信息、不同优化器模式下可能得到完全不同的执行路径。理解优化器，核心是理解它如何把一条 SQL 变成一棵可执行的计划树，以及它凭什么在多个候选计划中挑出“最优”的那一个。
一、CBO 的工作流程
基于成本的优化器（Cost-Based Optimizer，CBO）并不是简单地“看 SQL 选索引”，它遵循一条相对固定的处理链：
- 解析与转换：SQL 先经过语法、语义检查，生成解析树，再由查询转换器做等价改写，例如视图合并、子查询展开、谓词推入、星型转换等。转换后的形态会直接影响后续可选的访问路径。
- 生成候选计划：优化器枚举可能的访问路径（全表扫描、索引范围扫描、索引全扫描、ROWID 访问等）和连接方式（嵌套循环、哈希连接、排序合并连接），并考虑连接顺序。
- 估算选择率与基数：对每个谓词估算选择率（selectivity），再结合表、索引、列的统计信息推算每个操作返回的行数（cardinality）。
- 计算成本：根据 I/O 代价、CPU 代价、网络代价等模型，把基数、块数、排序量等换算成统一成本值。
- 比较并选出最优计划：在候选计划中取总成本最低者，交给行源生成器执行。
这条链路中，统计信息是输入，选择率与基数是中间量，成本是最终比较依据。任何一环失真，都会让优化器“算错账”。
二、可扩展优化器
Oracle 的优化器并非一个封闭黑盒，它提供了可扩展机制，让用户把业务规则注入优化过程。常见手段包括：
- 提示（Hint）：如
/*+ INDEX(t idx_t) */、/*+ USE_HASH(t1 t2) */，直接干预访问路径或连接方式。 - SQL 计划基线（SQL Plan Baseline）：把已验证的良好计划固定下来，避免统计信息变化导致计划回退。
- SQL Profile：在不修改 SQL 的前提下，为优化器补充或修正选择率、基数估算。
- 扩展统计信息：对存在函数依赖或相关性的多列创建列组统计，改善多谓词选择率估算。
这些机制的共同点是：当默认成本模型估算不准时，用外部信息帮助优化器做出更接近实际的判断。
三、选择率与基数估算
选择率表示一个谓词过滤后剩余行数的比例，取值在 0 到 1 之间。基数则是选择率作用在输入行数上的结果。以示例表 orders 为例，假设统计信息显示总行数为 1,000,000，列 status 的不同值数量（NDV）为 5，且数据分布均匀：
-- 示例：等值谓词选择率与基数估算
-- 表 orders 行数 = 1,000,000
-- 列 status NDV = 5
-- 查询：SELECT * FROM orders WHERE status = 'PAID';
选择率 = 1 / NDV = 1 / 5 = 0.2
基数 = 1,000,000 × 0.2 = 200,000 行
如果 status 数据严重倾斜，例如 'PAID' 占 90%，而优化器仍按均匀假设估算为 20%，就会低估实际返回行数，可能错误地选择索引范围扫描而非全表扫描。此时直方图或扩展统计信息就非常关键。
对于多谓词组合，优化器默认按独立假设相乘。例如 WHERE status='PAID' AND city='BJ'，若 city NDV 为 100，则组合选择率约为 0.2 × 0.01 = 0.002，基数约 2,000 行。若两列实际相关，这个估算就会偏离。
四、优化器模式对执行计划的影响
优化器模式决定了成本比较时的“目标函数”，从而改变最终计划：
| 模式 | 含义 | 对计划的影响 |
|---|---|---|
RULE |
基于规则，不使用成本模型 | 按固定优先级选路径，通常偏向索引，易产生次优计划，已不推荐 |
CHOOSE |
有统计信息走 CBO，否则走 RULE | 行为不确定，统计信息缺失时可能突然退化为规则优化 |
ALL_ROWS |
默认模式，最小化语句总资源消耗 | 倾向全表扫描、哈希连接，适合批处理、报表类整体吞吐 |
FIRST_ROWS_n |
以最快返回前 n 行为目标 | 倾向索引扫描、嵌套循环，适合交互式分页、快速响应场景 |
例如同一张订单表，在 ALL_ROWS 下可能选择哈希连接加全表扫描，一次处理大量数据；而在 FIRST_ROWS_10 下，优化器会认为“先拿到 10 行”更重要，可能改为嵌套循环加索引扫描。两者没有绝对优劣，取决于业务是“要全部结果”还是“要前几行尽快出现”。
SHOW PARAMETER optimizer_mode;SELECT name, value FROM v$parameter WHERE name LIKE 'optimizer%';执行计划可通过
EXPLAIN PLAN FOR ... 或 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 查看。
RULE 或 CHOOSE。现代 Oracle 版本中，ALL_ROWS 是默认且推荐模式；若需要快速返回，应通过 FIRST_ROWS_n 提示或 SQL 级设置局部控制，而不是全局切换。
总结来看，CBO 的核心是“用统计信息估算选择率与基数，再用成本公式比较候选计划”。优化器模式改变的是比较目标，可扩展优化器提供的是修正手段。真正稳定的执行计划，来自准确的统计信息、合理的模式选择，以及对基数偏差的持续观察与修正。