Oracle DBMS_STATS.GATHER_DATABASE_STATS 存储过程

Oracle DBMS_STATS.GATHER_DATABASE_STATS 存储过程

DBMS_STATS.GATHER_DATABASE_STATS 是 Oracle 数据库中用于收集整个数据库统计信息的关键存储过程，这些统计信息对于优化器生成高效执行计划至关重要。

一、基本语法

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

二、核心参数解析

1. 关键参数说明

2. 常用参数组合示例

-- 基本收集（使用自动采样）
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- 指定并行度
EXEC DBMS_STATS.GATHER_DATABASE_STATS(degree => 4);

-- 只收集过时统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS(options => 'GATHER STALE');

-- 不收集系统表统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS(gather_sys => FALSE);

三、执行策略与最佳实践

1. 执行时机建议

• 定期维护窗口：在业务低峰期执行
• 数据大量变更后：ETL过程或大批量DML操作后
• 性能问题出现时：当发现执行计划异常时

2. 推荐配置

-- 生产环境推荐配置
BEGIN
  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         => DBMS_STATS.AUTO_CASCADE,
    options         => 'GATHER AUTO',
    no_invalidate   => DBMS_STATS.AUTO_INVALIDATE,
    gather_sys      => FALSE,
    force           => TRUE);
END;
/

3. 监控统计信息状态

-- 查看需要收集统计信息的表
SELECT owner, table_name, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE stale_stats = 'YES' AND owner NOT IN ('SYS','SYSTEM');

四、高级用法

1. 增量统计信息收集（分区表）

-- 对分区表启用增量统计
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    'SCHEMA_NAME', 
    'TABLE_NAME', 
    'INCREMENTAL', 
    'TRUE');
END;
/

-- 然后执行常规收集
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

2. 保存和恢复统计信息

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

-- 导出当前统计信息
EXEC DBMS_STATS.EXPORT_DATABASE_STATS('DB_STATS_202308');

-- 恢复统计信息
EXEC DBMS_STATS.IMPORT_DATABASE_STATS('DB_STATS_202308');

五、注意事项

1. 系统影响：收集过程会消耗大量CPU和I/O资源
2. 锁问题：可能导致短暂的库级锁
3. 统计信息稳定性：避免频繁收集导致执行计划波动
4. 系统表处理：通常不需要收集SYS/SYSTEM模式下的统计信息
5. 历史比较：重大变更前建议备份统计信息

六、与PostgreSQL的对比

Oracle的DBMS_STATS包提供了比PostgreSQL更精细的统计信息控制：

PostgreSQL中类似功能的实现是通过ANALYZE命令，但功能相对简单：

-- PostgreSQL中的全库分析
ANALYZE VERBOSE;