DB / Oracle / ACE 精选

Oracle 杨廷琨 统计信息与 CBO

2026.09.06

Oracle 杨廷琨 统计信息与 CBO

来源：杨廷琨 (AskTOM 中文 / 啖汤) 适用版本：Oracle Database 10g+ 文档版本：v1.0 / 2026-07-22

1. 概述

CBO（基于成本的优化器）依赖统计信息，杨廷琨有系列文章[1]。

详细见：Oracle CBO 优化器原理。

2. CBO 基础

2.1 工作原理

- 统计信息 → 成本计算 → 选择执行计划
- 成本 = CPU + I/O
- 选择最低成本

2.2 杨廷琨强调

- 统计信息准确 → CBO 正确
- 统计信息陈旧 → 执行计划差
- 关键

3. 统计信息类型

3.1 表

- num_rows
- blocks
- avg_row_len
- last_analyzed

3.2 列

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

3.3 索引

- blevel
- leaf_blocks
- clustering_factor
- num_rows

3.4 系统

- CPU 速度
- I/O 速度
- 单块/多块读时间

4. 收集统计

4.1 DBMS_STATS

-- 表
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',
  CASCADE=>TRUE, 
  METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',
  ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);

-- Schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

-- 数据库
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

4.2 METHOD_OPT

- FOR ALL COLUMNS SIZE AUTO：自动
- FOR ALL COLUMNS SIZE 1：无直方图
- FOR COLUMNS col SIZE 254：指定列

4.3 杨廷琨建议

- AUTO_SAMPLE_SIZE
- AUTO 直方图
- CASCADE=TRUE

5. 直方图

5.1 作用

- 数据分布
- 倾斜列
- CBO 决策

5.2 类型

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

5.3 杨廷琨分析

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

6. Clustering Factor

6.1 定义

- 索引顺序与表行顺序一致性
- 影响索引选择

6.2 查看

SELECT index_name, clustering_factor 
FROM user_indexes WHERE table_name='EMP';

6.3 杨廷琨分析

- CF 接近块数 → 好
- CF 接近行数 → 差
- 重建表（按索引列排序）

7. 统计信息陈旧

7.1 问题

- 数据变化大
- 统计未更新
- 执行计划差

7.2 监控

SELECT table_name, num_rows, last_analyzed 
FROM user_tables 
WHERE last_analyzed < SYSDATE-7;

7.3 杨廷琨建议

- 定期收集
- 自动任务
- 监控

8. 自动统计

8.1 自动任务

SELECT * FROM dba_autotask_client 
WHERE client_name='auto optimizer stats collection';

8.2 启用

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL, window_name => NULL);
END;
/

8.3 杨廷琨观点

- 默认开启
- 监控
- 必要时手动

9. 绑定变量窥探

9.1 11g 之前

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

9.2 11g ACS

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

9.3 杨廷琨分析

- 数据倾斜：ACS
- 监控
- 必要 HINT

详细见：Oracle 自适应游标共享详解。

10. 执行计划稳定性

10.1 SQL Plan Baseline

-- 捕获
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE;

-- 使用
ALTER SYSTEM SET optimizer_use_sql_plan_baselines=TRUE;

10.2 杨廷琨建议

- 11g+
- 稳定执行计划
- 防止回归

详细见：Oracle SQL Plan Baseline 详解。

11. 动态采样

11.1 作用

- 运行时采样
- 补充统计
- 临时表

11.2 设置

ALTER SESSION SET optimizer_dynamic_sampling=2;

11.3 杨廷琨分析

- 临时表有用
- 评估开销
- 监控

12. 案例分析

12.1 案例：执行计划突变

- 统计信息变化
- CBO 选错
- Baseline 固化

12.2 案例：全表扫描

- 索引存在
- CF 差
- CBO 选全表
- 重建表

12.3 案例：直方图问题

- 倾斜列
- 无直方图
- 收集

13. 杨廷琨方法论

13.1 步骤

1. 查看执行计划
2. 查看统计信息
3. 对比 E/A Rows
4. 找出偏差
5. 优化

13.2 工具

- DBMS_STATS
- DBMS_XPLAN
- 10053 事件

13.3 10053

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

14. 最佳实践

  1. 统计信息：准确
  2. 定期收集：自动
  3. 直方图：倾斜列
  4. CF：优化
  5. Baseline：稳定
  6. ACS：绑定
  7. 监控：陈旧
  8. 10053：诊断
  9. 测试：验证
  10. 文档：记录

15. 参考资料

[1] 杨廷琨, “统计信息与 CBO”, https://www.modb.co/u/yangtingkun [2] Oracle Database SQL Tuning Guide 19c