Oracle 直方图与统计信息

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

统计信息 是 CBO 决策基础[1]，直方图 处理数据倾斜。

2. 统计信息类型

2.1 表统计

SELECT 
  table_name,
  num_rows,
  blocks,
  avg_row_len,
  last_analyzed
FROM user_tables
WHERE table_name = 'EMPLOYEES';

2.2 列统计

SELECT 
  column_name,
  num_distinct,
  density,
  num_nulls,
  low_value,
  high_value,
  histogram
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';

2.3 索引统计

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

3. 收集统计信息

3.1 DBMS_STATS

-- 表
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 4
);

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

-- 数据库
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

3.2 method_opt

选项	说明
FOR ALL COLUMNS SIZE AUTO	自动（默认）
FOR ALL COLUMNS SIZE 1	无直方图
FOR ALL COLUMNS SIZE 254	全部直方图
FOR COLUMNS col SIZE 254	指定列

3.3 estimate_percent

-- 自动
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

-- 指定
estimate_percent => 30  -- 30%

4. 直方图

4.1 类型

类型	说明	适用
FREQUENCY	频率	NDV < 254
HEIGHT BALANCED	高度平衡	NDV >= 254
TOP-FREQUENCY	Top 频率（12c+）	高频值
HYBRID	混合（12c+）	推荐

4.2 查看

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

4.3 直方图详情

SELECT 
  column_name,
  endpoint_number,
  endpoint_value,
  endpoint_actual_value
FROM user_tab_histograms
WHERE table_name = 'EMPLOYEES'
  AND column_name = 'DEPT_ID';

4.4 创建

-- 指定列创建
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'EMPLOYEES',
  method_opt => 'FOR COLUMNS dept_id SIZE 254'
);

5. 直方图适用

5.1 适用条件

数据倾斜
等值查询
列 NDV 较少

5.2 不适用

均匀分布
唯一列（NDV = 行数）
不查询的列

6. 统计信息管理

6.1 锁定

-- 锁定表
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMPLOYEES');

-- 解锁
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT', 'EMPLOYEES');

6.2 备份

-- 创建表
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT', 'STAT_BACKUP');

-- 导出
EXEC DBMS_STATS.EXPORT_TABLE_STATS('SCOTT', 'EMPLOYEES', NULL, 'STAT_BACKUP');

-- 导入
EXEC DBMS_STATS.IMPORT_TABLE_STATS('SCOTT', 'EMPLOYEES', NULL, 'STAT_BACKUP');

6.3 删除

EXEC DBMS_STATS.DELETE_TABLE_STATS('SCOTT', 'EMPLOYEES');

6.4 恢复

-- 恢复到之前
EXEC DBMS_STATS.RESTORE_TABLE_STATS('SCOTT', 'EMPLOYEES', 
  sysdate - 1/24);  -- 1 小时前

7. 偏好设置

7.1 设置

-- 表级
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMPLOYEES', 
  'METHOD_OPT', 'FOR ALL COLUMNS SIZE 254');

-- Schema 级
EXEC DBMS_STATS.SET_SCHEMA_PREFS('SCOTT', 'CASCADE', 'TRUE');

-- 数据库级
EXEC DBMS_STATS.SET_GLOBAL_PREFS('ESTIMATE_PERCENT', 'DBMS_STATS.AUTO_SAMPLE_SIZE');

7.2 查看

SELECT * FROM user_tab_stat_prefs WHERE table_name = 'EMPLOYEES';

8. 自动收集

8.1 自动任务

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

8.2 窗口

SELECT * FROM dba_autotask_window_clients;

8.3 启用/禁用

-- 启用
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(
  client_name => 'auto optimizer stats collection',
  operation => NULL,
  window_name => NULL
);

-- 禁用
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(...);

9. 动态采样

9.1 启用

ALTER SESSION SET optimizer_dynamic_sampling = 2;
-- 0-11

9.2 HINT

SELECT /*+ DYNAMIC_SAMPLING(e 4) */ * FROM employees e;

9.3 适用

临时表
缺失统计
复杂查询

10. 延迟统计

9.1 概述

11g+：数据加载后批量收集
减少单次 DML 开销

ALTER TABLE employees SET STATISTICS = 'DELAYED';

11. 统计信息查看

11.1 全局

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

11.2 列

SELECT 
  table_name,
  column_name,
  num_distinct,
  density,
  histogram,
  last_analyzed
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';

12. 常见坑与排错

12.1 CBO 选错计划

-- 1. 检查统计信息
SELECT last_analyzed, stale_stats FROM user_tab_statistics WHERE ...;

-- 2. 收集
EXEC DBMS_STATS.GATHER_TABLE_STATS(...);

-- 3. 直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS(..., method_opt => 'FOR COLUMNS col SIZE 254');

12.2 统计信息过期

-- 启用自动收集
-- 或手动收集
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(...);

12.3 数据倾斜

-- 添加直方图
method_opt => 'FOR COLUMNS dept_id SIZE 254'

13. 最佳实践

定期收集：自动任务
大表全表：estimate_percent AUTO
倾斜列直方图：精确
绑定变量：减少解析
锁定关键表：避免坏统计
备份统计：回滚
监控 stale：及时
动态采样补充：临时表
测试计划：验证
历史对比：趋势

14. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “Optimizer Statistics” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics.html