Oracle统计信息收集深度解析

统计信息是Oracle优化器生成最优执行计划的核心依据，其准确性直接决定了SQL执行效率。在统计信息收集过程中，FOR ALL COLUMNS SIZE AUTO作为默认选项，在不同Oracle版本中表现出显著差异，尤其在柱状图收集逻辑上的动态调整特性，容易被DBA忽视。本文将全面剖析从9i到19c各版本的统计信息收集机制演变，特别聚焦11g和19c的关键增强，帮助您精准掌控统计信息管理策略。

一、统计信息收集的版本演进：从9i到19c

1. 版本差异概览

9i：仅收集基本列统计信息（唯一值、低值、高值等），不收集任何柱状图，优化器无法感知列值分布倾斜。

10g：引入动态列跟踪机制，默认参数_column_tracking_level=1，Oracle会监控列使用情况，对频繁使用的倾斜列自动收集柱状图（通常2个Bucket）。

11g：进一步增强AUTO模式，优化了自动柱状图收集策略，支持扩展统计（多列统计和表达式统计），引入延迟发布(PENDING)机制和增量收集特性。

19c：革命性地引入实时统计信息（Real-Time Statistics）和高频自动统计收集（默认15分钟间隔），大幅提升统计信息新鲜度，减少执行计划偏差。

2. 核心统计信息构成

统计信息分为两个层次，在各版本中管理方式存在差异：

统计类型	存储位置	内容	可删除性
基本统计信息	DBA_TAB_COL_STATISTICS	列唯一值(NUM_DISTINCT)、低值(LOW_VALUE)、高值(HIGH_VALUE)、密度(DENSITY)	不可删除（只能通过DBMS_STATS.DELETE_TABLE_STATS彻底删除表统计）
柱状图信息	DBA_TAB_HISTOGRAMS	列值分布（ENDPOINT_NUMBER、ENDPOINT_VALUE）	可删除（通过SIZE 1或DELETE_COLUMN_STATS）

二、11g统计信息收集增强特性详解

1. 增强的AUTO模式与柱状图收集

Oracle 11g对AUTO模式进行了优化，主要表现在：

• 更智能的柱状图决策：在10g基础上，进一步优化了"自动为倾斜列收集柱状图"的判断逻辑，减少误判概率

• 支持更多直方图类型：

  • Frequency Histograms（频率直方图）：为每个唯一值创建独立Bucket（适合唯一值少的列）
  • Height Balanced Histograms（高度平衡直方图）：Bucket数量固定，值分布更均匀
  • Hybrid Histograms（混合直方图）：11g引入，解决"几乎流行值"估计不准确问题，存储端点重复计数

• 自动收集的扩展：

  -- 11g中AUTO模式会自动收集已创建的多列统计和表达式统计
  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EYGLE', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');
  

2. 扩展统计：多列统计与表达式统计（11g新特性）

多列统计（Column Groups）：解决单例统计无法反映列间关系的问题，优化器可准确计算多列谓词组合的选择性

-- 显式创建多列统计
DECLARE
  l_cg_name VARCHAR2(30);
BEGIN
  l_cg_name := DBMS_STATS.CREATE_EXTENDED_STATS(
    ownname   => 'SCOTT',
    tabname   => 'EMP',
    extension => '(JOB, DEPTNO)'
  );
END;
/

表达式统计：收集函数转换后列值的分布信息，使优化器能准确评估函数查询的代价

-- 隐式创建表达式统计（收集UPPER(ENAME)的统计信息）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'SCOTT',
  'EMP',
  METHOD_OPT => 'FOR COLUMNS (UPPER(ENAME))'
);

3. 统计信息管理增强（11g关键特性）

1) 延迟发布机制（PENDING状态）：

在11g前，新收集的统计信息会立即发布并生效；11g引入PENDING状态，允许先测试新统计信息效果再正式发布：

-- 设置表统计为延迟发布
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMP', 'PUBLISH', 'FALSE');

-- 收集统计信息（此时处于PENDING状态）
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');

-- 验证并发布
SELECT * FROM DBA_TAB_PENDING_STATS WHERE TABLE_NAME = 'EMP';
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SCOTT', 'EMP');

2) 增量统计收集（针对分区表）：

11g为分区表引入增量统计收集，仅扫描变更分区，大幅提升大型分区表的统计收集效率：

-- 启用增量统计
EXEC DBMS_STATS.SET_TABLE_PREFS('MY_SCHEMA', 'MY_PART_TABLE', 'INCREMENTAL', 'TRUE');

-- 仅收集变更分区（未变更分区使用已有统计）
EXEC DBMS_STATS.GATHER_TABLE_STATS('MY_SCHEMA', 'MY_PART_TABLE');

3) 自动采样增强：

11g改进了AUTO_SAMPLE_SIZE算法，使其更精准、更高效：

-- 11g强烈推荐使用AUTO_SAMPLE_SIZE（默认值）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'MYSCHEMA', 
  'BIG_TABLE', 
  ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
);

三、19c统计信息收集的革命性突破

1. 实时统计信息（Real-Time Statistics）：19c里程碑特性

特性说明：19c将统计收集扩展到所有常规DML操作（INSERT/UPDATE/DELETE/MERGE），在数据变更同时自动收集最关键的优化器统计信息

-- 19c中执行DML时，会自动触发统计信息收集
INSERT INTO MY_TABLE SELECT * FROM LARGE_TABLE;

-- 执行计划中可见"OPTIMIZER STATISTICS GATHERING"操作
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

实现机制：

• 仅在Exadata平台或Oracle Database Enterprise Edition on Engineered Systems上默认启用
• 可通过NO_GATHER_OPTIMIZER_STATISTICS Hint禁用单个SQL的实时统计收集
• 统计信息会标记为"STATS_ON_CONVENTIONAL_DML"，与传统统计区分

适用场景：

• 数据仓库中频繁加载的事实表
• OLTP系统中数据变更频繁的核心业务表
• 需要立即反映数据变化的报表查询场景

2. 高频自动统计收集：19c另一重大创新

特性说明：19c引入独立于传统夜间维护窗口的轻量级统计收集任务，默认每15分钟运行一次，专门收集过期对象的统计信息

与传统自动收集的区别：

• 运行频率：传统每天一次（维护窗口）vs 高频每15分钟一次
• 任务轻量：仅收集过期对象，不执行统计清理或优化器统计顾问
• 系统影响：资源消耗低，对业务影响小
• 适用场景：数据频繁变更的OLTP环境，减少统计信息过期时间

3. 19c的其他统计增强

1) 直方图增强：

19c进一步优化了直方图算法，支持更多类型：

• Top Frequency Histograms：特别优化高频率值的表示
• 增强的Hybrid Histograms：更精确处理"几乎流行"的值，避免估算偏差

2) 动态统计监控：

19c引入DBA_TAB_COL_STAT_MODELS视图，实时监控列统计模型（包括实时统计），便于DBA诊断统计信息质量

四、版本对比：method_opt='FOR ALL COLUMNS SIZE AUTO'行为差异

1. 9i - 10g - 11g对比

版本	收集内容	柱状图行为	特殊说明
9i	仅基本列统计	不收集任何柱状图	所有列只有基本统计，无DBA_TAB_HISTOGRAMS记录
10g	基本统计+动态收集柱状图	对频繁使用的倾斜列自动收集2个Bucket的基础柱状图	_column_tracking_level=1控制跟踪级别
11g	基本统计+智能柱状图+扩展统计	优化自动柱状图收集，对倾斜列收集更多Bucket（最多254），支持多列/表达式统计	自动检测列使用模式，更精准判断是否需要柱状图

2. 11g - 19c关键差异

特性	11g	19c
统计新鲜度	依赖每日自动任务或手动收集	实时统计+高频收集(15分钟)，几乎无延迟
收集触发条件	统计过期(默认10%数据变更)	除传统触发外，DML操作也会触发实时统计
柱状图智能性	基于使用频率和倾斜度判断	增加更多直方图类型，更精准表示数据分布
分区表支持	增量收集(需启用)	增强增量收集+自动并行处理
内存优化	标准内存使用	优化内存占用，适合更大数据集

五、实战：11g和19c环境下的统计信息收集演示

1. 11g环境测试

场景： 在11g中创建测试表，观察AUTO模式下的统计收集行为

-- 创建测试表
CREATE TABLE EYGLE AS SELECT * FROM DBA_OBJECTS;

-- 执行默认统计收集（11g的FOR ALL COLUMNS SIZE AUTO）
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EYGLE');

-- 验证OWNER列的统计信息（倾斜列，仅11个唯一值）
SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE 
FROM DBA_TAB_HISTOGRAMS 
WHERE TABLE_NAME = 'EYGLE' AND COLUMN_NAME = 'OWNER'
ORDER BY ENDPOINT_NUMBER;

11g输出特点：

• OWNER列会被识别为倾斜列，收集多个Bucket（通常4-8个）的柱状图，而非仅2个
• 执行查询后，若频繁访问OWNER='SYS'等特定值，再次收集时会自动增加Bucket数量（最多254）
• 11g的AUTO模式比10g更智能，能更好地识别"真正需要"柱状图的列

2. 19c环境测试

场景： 19c中测试实时统计特性和高频收集机制

-- 确认实时统计是否启用（仅Exadata或特定平台默认启用）
SELECT DBMS_STATS.GET_PREFS('REAL_TIME_STATS') FROM DUAL;

-- 执行大数据量DML操作
INSERT INTO EYGLE SELECT * FROM EYGLE;
COMMIT;

-- 检查执行计划，查看是否包含统计收集操作
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

19c输出特点：

• INSERT语句执行计划中会显示"OPTIMIZER STATISTICS GATHERING"操作
• USER_TAB_STATISTICS视图中，EYGLE表的NOTES列会显示"STATS_ON_CONVENTIONAL_DML"
• 高频收集任务会在15分钟后自动运行，更新所有过期统计信息

六、统计信息管理最佳实践（11g/19c）

1. 11g最佳实践

1) 利用延迟发布机制验证新统计

-- 对关键表启用PENDING状态
EXEC DBMS_STATS.SET_TABLE_PREFS('CRITICAL_SCHEMA', 'CRITICAL_TABLE', 'PUBLISH', 'FALSE');

-- 收集统计并测试
EXEC DBMS_STATS.GATHER_TABLE_STATS('CRITICAL_SCHEMA', 'CRITICAL_TABLE');
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;
-- 执行关键查询测试性能
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = FALSE;

-- 确认效果后正式发布
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('CRITICAL_SCHEMA', 'CRITICAL_TABLE');

2) 分区表使用增量统计

-- 对大型分区表启用增量统计
EXEC DBMS_STATS.SET_TABLE_PREFS('DW_SCHEMA', 'FACT_SALES', 'INCREMENTAL', 'TRUE');

-- 仅收集变更分区（大幅提升性能）
EXEC DBMS_STATS.GATHER_TABLE_STATS('DW_SCHEMA', 'FACT_SALES');

2. 19c最佳实践

1) 充分利用实时统计（如环境支持）

-- 在OLTP系统中对频繁更新的核心表
ALTER TABLE ORDERS ENABLE MONITORING; -- 确保表监控启用（11g起默认启用）

-- 执行DML后无需手动收集，统计自动更新
UPDATE ORDERS SET STATUS = 'PROCESSED' WHERE ORDER_DATE < SYSDATE - 7;
COMMIT;

-- 查询会立即使用最新统计信息，生成更优执行计划
SELECT * FROM ORDERS WHERE STATUS = 'PROCESSED';

2) 高频统计收集配置优化

-- 查看高频统计收集设置
SELECT * FROM DBA_AUTO_STAT_JOB;

-- 调整收集间隔（默认为15分钟）
BEGIN
  DBMS_AUTO_TASK_ADMIN.SET_TASK_PARAM(
    'auto stats', 
    'frequency', 
    'FREQ=MINUTELY; INTERVAL=20'
  ); -- 改为20分钟间隔
END;
/

七、总结：版本特性速查表

版本	核心特性	适用场景	推荐配置
9i	基本统计，无柱状图	简单OLTP，无复杂查询	手动收集+定期维护
10g	动态柱状图收集	含倾斜列的中型系统	_column_tracking_level=1(默认)
11g	延迟发布+增量统计+扩展统计	数据仓库+大型分区表	启用PENDING验证+增量统计
19c	实时统计+高频收集	数据频繁变更的OLTP+数据仓库	启用实时统计(如支持)+高频收集

Oracle统计信息收集机制从9i的简单模式发展到19c的智能全自动体系，体现了优化器对执行计划精准度的不断追求。作为DBA，理解各版本统计收集特性，特别是11g的增强AUTO模式和19c的实时统计功能，能够帮助您在性能调优中事半功倍。

关键建议：

1. 11g环境：充分利用延迟发布和增量统计特性，对倾斜列使用METHOD_OPT='FOR COLUMNS column_name SIZE REPEAT'强化柱状图收集
2. 19c环境：尽可能启用实时统计(如平台支持)和高频收集，减少统计过期导致的执行计划偏差

统计信息是数据库性能的"晴雨表"，精准管理才能让优化器发挥最大效能。