在 11g 及以前，DBA 需要手工指定哪些列需要直方图，既枯燥又容易遗漏。 从 Oracle 12c 开始，METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' 引入了一种「按需创建直方图」的机制： 只有当某列真正被查询用到，并且优化器认为直方图会改善执行计划时，才在下次收集统计信息时自动为该列创建直方图。
原理速览
| 阶段 | 动作 | 关键数据字典 |
|---|---|---|
| 1 | 使用 SIZE AUTO 收集统计信息 | 无直方图，但标记了“可自动创建” |
| 2 | 应用发出带谓词的 SQL | 优化器把谓词写入 SYS.COL_USAGE$ |
| 3 | 再次 GATHER_*_STATS | DBMS_STATS 读取 COL_USAGE$，若发现某列“值得”直方图，则 HISTOGRAM_ONLY 创建之 |
实验：5 分钟复现官方 Demo
1. 准备测试表
-- 外部表 sh_ext 已存在，结构与 sh.sales 相同
CREATE TABLE sales2 AS SELECT * FROM sh_ext;
-- 创建常规索引
CREATE INDEX sh_12c_idx1 ON sales2(prod_id);
CREATE INDEX sh_12c_idx2 ON sales2(cust_id, time_id);
2. 初次收集统计信息（无直方图）
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'SALES2',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
END;
/
查看结果——所有列均为 NONE：
SELECT column_name, notes, histogram
FROM user_tab_col_statistics
WHERE table_name = 'SALES2';
3. 执行一次“生产查询”
SELECT COUNT(*)
FROM sales2
WHERE prod_id = 42;
该 SQL 会把 prod_id 记录到 SYS.COL_USAGE$。
4. 再次收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'SALES2',
options => 'GATHER AUTO');
END;
/
5. 验证直方图已出现
SELECT column_name, notes, histogram
FROM user_tab_col_statistics
WHERE table_name = 'SALES2';
| COLUMN_NAME | NOTES | HISTOGRAM |
|---|---|---|
| PROD_ID | HISTOGRAM_ONLY | FREQUENCY |
| … | … | NONE |
常见问题
- 为什么数据没变，执行计划却变了？ 因为
COL_USAGE$中记录了新的谓词，下次收集时直方图被创建/删除，优化器有了更精确的 NDV 分布信息，可能选择全表扫或索引扫。 - 如何查看列使用率？ SELECT * FROM sys.col_usage$ WHERE obj# = (SELECT object_id FROM user_objects WHERE object_name=’SALES2′);需 DBA 权限。
- 能否关闭该功能？ 把
METHOD_OPT改为SIZE 1或REPEAT，或者使用FOR ALL COLUMNS SIZE SKEWONLY以回到 11g 行为。 - 分区表是否适用？ 适用。
GATHER AUTO在分区级统计信息收集时同样会检查COL_USAGE$。
踩坑与最佳实践
| 场景 | 建议 |
|---|---|
| 性能测试环境 | 在跑 TPCH/生产回放脚本前，先 GATHER AUTO 一次，避免后期直方图突变导致计划抖动。 |
| ETL 批量加载 | 先 DBMS_STATS.SET_TABLE_PREFS 把 METHOD_OPT 临时改为 SIZE 1，加载后再改回 SIZE AUTO。 |
| 升级后体检 | 升级 12c 或 19c 后，跑 DBMS_STATS.GATHER_DICTIONARY_STATS 并观察 DBA_TAB_COL_STATISTICS.HISTOGRAM 变化。 |
把直方图的创建权交给 Oracle，让统计信息随真实工作负载“自适应”——这就是 SIZE AUTO 的魅力与风险。 理解 COL_USAGE$ 的生命周期，才能真正玩转 12c+ 的统计信息自动驾驶模式。