Oracle 优化器统计信息管理

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

1. 概述

优化器统计信息管理[1]：

关键：

准确
及时
稳定

2. 收集策略

2.1 自动收集

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

-- 默认夜间窗口

2.2 手动收集

-- 表
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', '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',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade => TRUE,
  degree => 4);

-- 数据库
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

3. 偏好设置

3.1 表级

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

EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMPLOYEES', 
  'ESTIMATE_PERCENT', 'DBMS_STATS.AUTO_SAMPLE_SIZE');

EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMPLOYEES', 
  'CASCADE', 'TRUE');

EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMPLOYEES', 
  'DEGREE', '4');

3.2 Schema 级

EXEC DBMS_STATS.SET_SCHEMA_PREFS('SCOTT', 'INCREMENTAL', 'TRUE');

3.3 全局

EXEC DBMS_STATS.SET_GLOBAL_PREFS('ESTIMATE_PERCENT', 'DBMS_STATS.AUTO_SAMPLE_SIZE');

3.4 查看

SELECT * FROM dba_tab_stat_prefs WHERE table_name = 'EMPLOYEES';

4. 增量统计

4.1 12c+

-- 启用
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'SALES', 'INCREMENTAL', 'TRUE');

-- 仅变化分区
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'SALES');

4.2 优势

大表快速
仅变化分区

5. Pending 统计

5.1 启用

-- 表
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMPLOYEES', 'PUBLISH', 'FALSE');

-- 收集到 pending
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');

5.2 测试

ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
-- 测试 SQL

5.3 发布

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

5.4 删除

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

6. 锁定统计

6.1 锁定

EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.LOCK_SCHEMA_STATS('SCOTT');

6.2 解锁

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

6.3 应用

关键表稳定计划
防止自动任务影响

7. 备份恢复

7.1 创建表

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

7.2 导出

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

7.3 导入

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

7.4 恢复

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

8. 直方图

8.1 类型

类型	适用
FREQUENCY	NDV < 254
HEIGHT BALANCED	NDV >= 254
TOP-FREQUENCY	高频值（12c+）
HYBRID	通用（12c+）

8.2 创建

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES',
  method_opt => 'FOR COLUMNS dept_id SIZE 254');

8.3 查看

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

9. 动态采样

9.1 启用

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

9.2 HINT

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

9.3 适用

临时表
缺失统计
复杂查询

10. 监控

10.1 统计状态

SELECT 
  table_name,
  num_rows,
  last_analyzed,
  stale_stats
FROM user_tab_statistics;

10.2 Stale 检查

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

10.3 历史

SELECT * FROM dba_optstat_operations;

11. 常见坑与排错

11.1 CBO 选错计划

-- 1. 检查统计
SELECT last_analyzed, num_rows FROM user_tables WHERE ...;

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

-- 3. 直方图

11.2 统计过期

-- 1. 自动收集
-- 2. 手动收集
-- 3. 监控 stale

11.3 数据倾斜

-- 直方图
method_opt => 'FOR COLUMNS col SIZE 254'

12. 最佳实践

自动收集：基础
大表 AUTO_SAMPLE：平衡
倾斜列直方图：精准
增量统计：分区大表
Pending 测试：稳定
锁定关键表：稳定
备份统计：回滚
监控 stale：及时
动态采样补充：临时
定期复审：调整

13. 参考资料

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