Oracle数据库统计信息方法详解

统计信息概述

Oracle数据库的统计信息是优化器(CBO)生成高效执行计划的基础，主要包括： - 表统计信息(行数、块数、行平均长度等) - 列统计信息(不同值数量、空值数量、数据分布等) - 索引统计信息(索引层级、聚簇因子等) - 系统统计信息(I/O和CPU性能指标)

统计信息收集方法

1. DBMS_STATS包

这是Oracle推荐的标准方法，提供了灵活的参数控制：

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SCHEMA_NAME',
    tabname          => 'TABLE_NAME',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => DBMS_STATS.AUTO_DEGREE,
    cascade          => TRUE,
    granularity      => 'AUTO'
);

-- 收集模式统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'SCHEMA_NAME',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => DBMS_STATS.AUTO_DEGREE,
    cascade          => TRUE,
    options          => 'GATHER'
);

-- 收集数据库统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => DBMS_STATS.AUTO_DEGREE,
    cascade          => TRUE,
    options          => 'GATHER'
);

2. 自动统计信息收集任务

Oracle默认配置了自动统计信息收集任务(Maintenance Window)： - 通过DBA_AUTOTASK_CLIENT视图查看状态 - 使用DBMS_AUTO_TASK_ADMIN包进行管理

-- 查看自动统计信息收集任务
SELECT client_name, status FROM DBA_AUTOTASK_CLIENT 
WHERE client_name = 'auto optimizer stats collection';

-- 启用/禁用自动统计信息收集
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/

3. 增量统计信息收集

对于大型分区表，可以使用增量统计信息收集：

-- 启用表的增量统计信息
EXEC DBMS_STATS.SET_TABLE_PREFS('SCHEMA', 'TABLE', 'INCREMENTAL', 'TRUE');

-- 设置全局增量统计信息
EXEC DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL', 'TRUE');

统计信息管理

1. 锁定统计信息

对于稳定的表，可以锁定统计信息防止被自动更新：

-- 锁定表统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCHEMA', 'TABLE');

-- 解锁表统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCHEMA', 'TABLE');

2. 导出/导入统计信息

在测试和生产环境间迁移统计信息：

-- 创建统计信息表
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCHEMA', 'STATS_TABLE');

-- 导出统计信息
EXEC DBMS_STATS.EXPORT_TABLE_STATS('SCHEMA', 'TABLE', stattab => 'STATS_TABLE');

-- 导入统计信息
EXEC DBMS_STATS.IMPORT_TABLE_STATS('SCHEMA', 'TABLE', stattab => 'STATS_TABLE');

3. 统计信息历史管理

Oracle自动维护统计信息历史：

-- 查看统计信息历史保留天数
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

-- 设置统计信息历史保留天数
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(30);

-- 恢复历史统计信息
EXEC DBMS_STATS.RESTORE_TABLE_STATS('SCHEMA', 'TABLE', as_of_timestamp => SYSDATE-1);

最佳实践

1. 收集频率：

   • 高DML操作的表：每天或每周收集
   • 静态表：每月或按需收集
   • 大型表：使用增量统计信息收集

2. 采样比例：

   • 使用DBMS_STATS.AUTO_SAMPLE_SIZE让Oracle自动决定
   • 对于大型表，可手动设置较低比例(5-10%)

3. 并行度：

   • 使用DBMS_STATS.AUTO_DEGREE自动决定
   • 大型表可手动设置更高并行度

4. 监控统计信息：

   -- 查看最后收集时间
   SELECT table_name, last_analyzed FROM DBA_TABLES 
   WHERE owner = 'SCHEMA_NAME';
   
   -- 查看统计信息是否过期
   SELECT table_name, stale_stats FROM DBA_TAB_STATISTICS 
   WHERE owner = 'SCHEMA_NAME' AND stale_stats = 'YES';
   

5. 系统统计信息：

   -- 收集系统统计信息
   EXEC DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');
   -- 或
   EXEC DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 60);
   

通过合理收集和管理统计信息，可以确保Oracle优化器生成高效的执行计划，提升数据库性能。