Oracle 19c含800亿条记录的哈希分区表统计信息收集优化求助

针对800亿行单哈希分区表统计收集耗时久、频繁出现快照过旧的问题，结合Oracle 19c特性，给出以下优化建议：

1. 并行度调优 #

显式设置更高的并行度，充分利用服务器CPU资源加速数据扫描：

• 可根据服务器CPU核心数设置具体值（如64，建议不超过核心数的80%），或使用DBMS_STATS.MAX_DEGREE让Oracle自动适配最优并行度。
  示例代码：

BEGIN
  dbms_stats.gather_table_stats(ownname => 'ABC', tabname =>'table1', 
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
    cascade => DBMS_STATS.AUTO_CASCADE, 
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
    degree => 64);
END;
/

2. 优化采样与列选择策略 #

• 降低采样百分比：放弃默认的AUTO_SAMPLE_SIZE，显式设置低采样率（如0.1%）。对于800亿行的超大表，低采样率已能保证统计信息的准确性，大幅减少扫描数据量。
• 仅收集业务关键列：通过method_opt指定仅收集过滤、连接等业务场景中用到的列，避免无意义的计算。
  示例代码：

BEGIN
  dbms_stats.gather_table_stats(ownname => 'ABC', tabname =>'table1', 
    estimate_percent => 0.1, 
    cascade => DBMS_STATS.AUTO_CASCADE, 
    method_opt => 'FOR COLUMNS SIZE AUTO col1 col2 col3', -- 替换为实际关键列
    degree => 64);
END;
/

3. 解决快照过旧问题 #

• 调整UNDO配置：临时增大UNDO表空间数据文件，或设置UNDO_RETENTION为更大值（如36000秒/10小时），确保收集过程中UNDO数据不被覆盖，完成后再调回原值。
• 启用在线统计收集：添加online => TRUE参数，允许收集统计时不阻塞DML操作，同时降低快照过旧的触发概率。
• 设置NO_INVALIDATE参数：添加no_invalidate => FALSE，让统计信息更新时立即失效相关游标，减少事务持有时间。
  示例代码：

BEGIN
  dbms_stats.gather_table_stats(ownname => 'ABC', tabname =>'table1', 
    estimate_percent => 0.1, 
    cascade => DBMS_STATS.AUTO_CASCADE, 
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
    degree => 64,
    online => TRUE,
    no_invalidate => FALSE);
END;
/

4. 利用19c增量统计特性 #

启用增量统计，仅收集表中变化数据的统计信息，避免全表扫描：

• 先开启表的增量统计设置，再执行收集：

-- 开启增量统计
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(ownname => 'ABC', tabname => 'table1', 
    pname => 'INCREMENTAL', pvalue => 'TRUE');
  DBMS_STATS.SET_TABLE_PREFS(ownname => 'ABC', tabname => 'table1', 
    pname => 'PUBLISH', pvalue => 'TRUE');
END;
/

-- 增量收集统计
BEGIN
  dbms_stats.gather_table_stats(ownname => 'ABC', tabname =>'table1', 
    estimate_percent => 0.1, 
    cascade => DBMS_STATS.AUTO_CASCADE, 
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', 
    degree => 64);
END;
/

5. 其他辅助优化 #

• 清理表碎片：收集统计前执行ALTER TABLE table1 SHRINK SPACE COMPACT或ALTER TABLE table1 MOVE，减少扫描时的IO开销。
• 错峰执行：选择业务低峰期启动统计收集，降低DML操作对收集过程的干扰，减少快照过旧的发生概率。
• 异步统计收集：设置全局参数DBMS_STATS.SET_GLOBAL_PREFS('ASYNC_STATS', 'TRUE')，让Oracle后台自动完成统计收集，避免前台长时间阻塞。

内容的提问来源于stack exchange，提问作者Mansi Raval