Oracle Jonathan Lewis CBO 优化器原理

来源：Jonathan Lewis / jonathanlewis.wordpress.com 适用版本：Oracle Database 8i+ 文档版本：v1.0 / 2026-07-22

1. 关于 Jonathan Lewis

Jonathan Lewis，Oracle ACE Director，世界知名 Oracle 性能专家[1]。

著有《Cost-Based Oracle Fundamentals》《Oracle Core》
CBO 优化器权威
博客：jonathanlewis.wordpress.com

2. CBO 基础

2.1 工作原理

- 解析 SQL
- 收集统计信息
- 计算成本
- 选择最低成本执行计划

2.2 成本模型

- Cost = CPU + I/O
- 9i：I/O 为主
- 10g+：CPU + I/O
- 12c+：增强

2.3 Jonathan 观点

- CBO 不神秘
- 理解原理
- 统计信息关键

3. 统计信息

3.1 表

- num_rows
- blocks
- avg_row_len

3.2 列

- num_distinct
- density
- num_nulls
- low/high_value
- 直方图

3.3 索引

- blevel
- leaf_blocks
- clustering_factor
- num_rows
- distinct_keys

3.4 Jonathan 强调

- 统计信息准确 = CBO 正确
- 理解每个字段
- 监控

4. 基数计算

4.1 单表

- 等值：1 / num_distinct
- 范围：(range_size) / (high - low)
- LIKE：固定比例

4.2 直方图

- Frequency：精确
- Height Balanced：评估
- Hybrid：12c+

4.3 Jonathan 公式

- 选择率 × num_rows = cardinality
- 理解选择率
- CBO 基础

5. 连接成本

5.1 Nested Loop

- 外表基数 × 内表成本
- 索引访问
- 小数据

5.2 Hash Join

- 构建表 Build
- 探测表 Probe
- 内存
- 大数据

5.3 Merge Join

- 排序
- 合并
- 已排序数据

5.4 Jonathan 分析

- 不同连接不同成本
- CBO 选择
- 理解

6. Clustering Factor

6.1 定义

- 索引与表行物理顺序一致性
- 0 - num_blocks（最佳）
- 0 - num_rows（最差）

6.2 影响

- CF 接近块数 → 索引高效
- CF 接近行数 → 索引低效
- 影响 CBO 决策

6.3 Jonathan 建议

- 重建表（按索引列排序）
- IOT
- 评估

7. 直方图

7.1 何时使用

- 数据倾斜
- 等值查询
- 范围查询

7.2 类型

- Frequency：≤254 distinct
- Height Balanced：>254
- Top-Frequency：12c+
- Hybrid：12c+

7.3 Jonathan 分析

- 倾斜列必要
- 均匀列不必要
- 监控

8. Bind Peeking

8.1 9i+

- 第一次窥探绑定值
- 决定执行计划
- 后续复用

8.2 问题

- 数据倾斜
- 第一次值影响
- 不稳定

8.3 11g ACS

- Adaptive Cursor Sharing
- 多个执行计划
- 自适应

8.4 Jonathan 观点

- Bind Peeking 双刃剑
- 评估业务
- ACS 改进

9. 10053 事件

9.1 启用

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

9.2 trace 内容

- 统计信息
- 成本计算
- 执行计划
- CBO 决策

9.3 Jonathan 推荐

- 诊断 CBO
- 理解决策
- 高级

10. 执行计划稳定性

10.1 SQL Profile

- 自动调优
- 辅助信息
- 11g+

10.2 SQL Plan Baseline

- 11g+
- 捕获历史
- 防止回归

10.3 Jonathan 建议

- Baseline 优先
- 防止突变
- 监控

11. 常见 CBO 问题

11.1 全表扫描

- 索引不存在
- CF 差
- 统计信息陈旧

11.2 错误连接

- 统计信息不准
- HINT
- 评估

11.3 评估不准

- 直方图缺失
- 动态采样
- 收集

12. Jonathan 案例

12.1 案例：评估偏差

- E-Rows 1
- A-Rows 1000000
- 直方图
- 收集

12.2 案例：连接错误

- NL 连接大数据
- Hash Join 优化
- HINT

12.3 案例：CF 问题

- CF 接近行数
- 重建表
- 性能提升

13. Jonathan 著作

- 《Cost-Based Oracle Fundamentals》
- 《Oracle Core: Essential Internals for DBAs》
- 《Oracle Insights》

14. Jonathan 方法论

14.1 思维

- 理解 CBO 原理
- 数据驱动
- 测试验证

14.2 工具

- DBMS_XPLAN
- 10053
- DBMS_STATS
- 测试用例

14.3 步骤

1. 获取执行计划
2. 检查统计信息
3. 对比 E/A Rows
4. 分析 CBO 决策
5. 优化

15. 最佳实践

统计信息：准确
直方图：倾斜列
CF：优化
10053：诊断
Baseline：稳定
E/A 对比：偏差
测试：验证
案例：学习
原理：理解
持续：学习

16. 参考资料

[1] Jonathan Lewis, https://jonathanlewis.wordpress.com [2] Jonathan Lewis, “Cost-Based Oracle Fundamentals”, Apress [3] Jonathan Lewis, “Oracle Core”, Apress