DB / Oracle / ACE 精选

Oracle Jonathan Lewis 统计信息详解

2026.09.06

Oracle Jonathan Lewis 统计信息详解

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

1. 概述

Jonathan Lewis 是统计信息权威[1]。

详细见：Oracle 杨廷琨-统计信息与 CBO。

2. 统计信息重要性

2.1 CBO 基础

- CBO 依赖统计
- 准确 → 正确计划
- 陈旧 → 错误计划

2.2 Jonathan 观点

- 统计信息是性能关键
- 理解每个字段
- 监控

3. 表统计

3.1 字段

- num_rows：行数
- blocks：块数
- empty_blocks：空块
- avg_space：平均空闲
- chain_cnt：行链
- avg_row_len：平均行长
- last_analyzed：最后收集

3.2 查看

SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed 
FROM user_tables;

3.3 Jonathan 分析

- num_rows 准确
- blocks 影响 I/O 成本
- avg_row_len 影响评估

4. 列统计

4.1 字段

- num_distinct：distinct 值
- density：密度
- num_nulls：NULL 数
- low_value：最小值
- high_value：最大值
- avg_col_len：平均列长
- histogram：直方图

4.2 查看

SELECT column_name, num_distinct, density, num_nulls, 
  low_value, high_value, histogram
FROM user_tab_columns WHERE table_name='EMP';

4.3 density

- 1/num_distinct（无直方图）
- 直方图不同
- 影响选择率

4.4 Jonathan 公式

- 选择率 = density
- 基数 = num_rows × density

5. 索引统计

5.1 字段

- blevel：B-Tree 层级
- leaf_blocks：叶子块
- clustering_factor：聚簇因子
- num_rows：行数
- distinct_keys：distinct 键
- avg_leaf_blocks_per_key
- avg_data_blocks_per_key

5.2 查看

SELECT index_name, blevel, leaf_blocks, 
  clustering_factor, num_rows, distinct_keys
FROM user_indexes WHERE table_name='EMP';

5.3 blevel

- B-Tree 层级
- 影响 I/O 次数
- 0/1/2/3+

5.4 clustering_factor

- 索引与表行物理顺序一致性
- 接近块数：好
- 接近行数：差
- 影响索引选择

5.5 Jonathan 强调

- CF 是关键
- 影响 CBO 决策
- 评估优化

6. 直方图

6.1 作用

- 数据分布
- 倾斜列
- 精确选择率

6.2 类型

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

6.3 查看

SELECT column_name, histogram, num_buckets 
FROM user_tab_col_statistics WHERE table_name='EMP';

6.4 Jonathan 建议

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

7. 收集方法

7.1 DBMS_STATS

-- 表
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => '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;

7.2 METHOD_OPT

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

7.3 ESTIMATE_PERCENT

- AUTO_SAMPLE_SIZE：推荐
- 100：精确但慢
- 评估

7.4 Jonathan 建议

- AUTO_SAMPLE_SIZE
- AUTO 直方图
- CASCADE=TRUE

8. 自动收集

8.1 自动任务

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

8.2 窗口

- 默认夜间
- 维护窗口
- 评估

8.3 Jonathan 观点

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

9. 统计信息锁

9.1 锁定

EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT','EMP');

9.2 解锁

EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT','EMP');

9.3 Jonathan 用途

- 关键表锁定
- 防止突变
- 评估

10. 备份恢复

10.1 备份表

EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT','STAT_BACKUP');
EXEC DBMS_STATS.EXPORT_TABLE_STATS('SCOTT','EMP','STAT_BACKUP');

10.2 恢复

EXEC DBMS_STATS.IMPORT_TABLE_STATS('SCOTT','EMP','STAT_BACKUP');

10.3 Jonathan 强调

- 备份统计
- 升级前
- 可恢复

11. 延迟统计

11.1 在线统计

- 12c+
- CTAS/IAS 自动
- 实时

11.2 同步

- 立即
- 准确

11.3 Jonathan 观点

- 12c+ 改进
- 实时统计
- 评估

12. 监控

12.1 陈旧

SELECT table_name, num_rows, last_analyzed, stale_stats
FROM user_tab_statistics WHERE stale_stats='YES';

12.2 直方图

SELECT table_name, column_name, histogram 
FROM user_tab_col_statistics 
WHERE histogram != 'NONE';

12.3 历史

SELECT * FROM dba_optstat_operations;

13. 案例

13.1 评估不准

- E-Rows vs A-Rows 偏差
- 收集统计
- 验证

13.2 索引不选

- CF 差
- 重建表
- 或 HINT

13.3 直方图缺失

- 倾斜列
- 收集直方图
- 验证

14. Jonathan 方法论

14.1 原则

- 准确统计
- 合理直方图
- 监控

14.2 工具

- DBMS_STATS
- DBMS_XPLAN
- 10053

14.3 测试

- 测试用例
- 复现
- 验证

15. 最佳实践

  1. DBMS_STATS：使用
  2. AUTO：采样
  3. 直方图：倾斜列
  4. CF：优化
  5. 锁定：关键表
  6. 备份：统计
  7. 监控：陈旧
  8. 自动任务：启用
  9. 测试：验证
  10. 原理：理解

16. 参考资料

[1] Jonathan Lewis, “Statistics”, https://jonathanlewis.wordpress.com [2] Jonathan Lewis, “Cost-Based Oracle Fundamentals”, Apress [3] Oracle Database SQL Tuning Guide 19c