36-Oracle Statistics Gathering（统计信息收集）

小伙伴们，有没有因为统计信息不准，导致了业务卡顿，各种状况频出，这几天在实践和实操的过程中，时不时就需要进行统计信息的收集。同时统计信息收集的动作也是OCM必考内容。

数据库中的数据是地图，统计信息是导航仪，而优化器则是驾驶策略的制定者，CBO依赖统计信息做出最优成本和路径选择。

一、统计信息功能

在Oracle数据库管理中，统计信息收集（Statistics Gathering）是非常非常重要的环节，直接影响着数据库的性能优化和查询效率。

优化查询性能：

数据库优化器使用统计信息来评估执行计划的成本，选择最优的执行路径。可以根据列的分布、表的大小、索引的选择性等信息来决定是全表扫描还是索引扫描。

自动调整执行计划：

统计信息帮助数据库自动调整执行计划。随着数据的变化，统计信息会更新，这可以确保数据库持续使用最优的执行策略。避免因数据分布变化导致的执行计划突变（Plan Flip）

提高查询的准确性：

统计信息提供了关于数据的精确度，这对于估算查询结果的行数非常关键。在执行聚合查询（如COUNT、SUM等）时，准确的统计信息可以确保返回的结果更加准确。

改善成本估算：

数据库优化器通过统计信息来估算各种操作的成本，如扫描行数、连接操作的开销等。这些估算用于选择最佳的查询执行计划。减少不必要的I/O和CPU消耗，降低全表扫描风险

支持分区和物化视图：

对于使用分区表和物化视图的数据库设计，统计信息对于优化器的决策至关重要。分区表的选择性统计可以帮助优化器更有效地决定使用哪个分区。

支持高级功能：

同样支持高级功能，自动SQL调优、数据仓库优化器这些也将依赖于准确的统计信息来提供最佳的性能。

二、收集方式

1. ​自动收集机制​

Oracle通过GATHER_STATS_JOB自动任务实现智能收集：

• ​触发条件​：当DBA_TAB_MODIFICATIONS中记录的DML量 > 表行数的10%时标记为陈旧（Stale）
• ​时间窗口​：默认工作日晚10点-早6点及周末全天
• ​优先级策略​：先处理缺失统计信息对象，再处理陈旧度高的对象

--启用命令

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END; 
/
--PL/SQL procedure successfully completed.
--直接用表名和用户名可以做粗略收集，其他按照列等选项收集，需要细化
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('用户名');
EXEC DBMS_STATS.GATHER_TABLE_STATS('用户名', '表名');

2. ​手动收集原则​

以下场景需手动干预：

• ​ETL作业后​：避免优化器使用陈旧信息
• ​数据分布倾斜​：存在极端值的列需特殊处理
• ​性能敏感对象​：核心业务表结构变更后
• 导出到导入：必要收集统计信息

三、统计信息的要点

下面用SH Schema验证，统计信息直接决定分区表SALES、大表CUSTOMERS的查询效率：

• ​优化器决策依据​统计信息为CBO提供数据分布特征。举例：

-- 查看SALES表统计信息（关键字段）
SELECT num_rows, blocks, avg_row_len 
FROM dba_tables 
WHERE owner='SH' AND table_name='SALES';
--
  NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------
         0          0           0

若num_rows严重偏离实际值（如因数据迁移未更新），优化器可能错误选择全表扫描而非分区裁剪。

• ​资源消耗优化​准确的索引统计信息（如CUSTOMERS_PK的聚簇因子）可避免低效索引扫描：

SELECT clustering_factor 
FROM dba_indexes 
WHERE index_name='CUSTOMERS_PK';
--
CLUSTERING_FACTOR
-----------------
                0
                0

高聚簇因子（接近表块数）表明索引效率低，需结合统计信息调整索引策略。 

四、举例SH Schema的统计信息收集方法

自动收集策略优化

启用增量收集降低分区表开销：

-- 开启SALES表增量统计
EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'INCREMENTAL', 'TRUE');
--PL/SQL procedure successfully completed.
-- 验证设置
SELECT preference_value 
FROM dba_tab_stat_prefs 
WHERE owner='SH' AND table_name='SALES' AND preference_name='INCREMENTAL';
--效果​：仅收集数据变更的分区。
PREFERENCE_VALUE
--------------------------------------------------------------------------
TRUE

设置用户SH自动收集任务 

 

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'gather_stats_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''SH''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- 例如每天执行一次
    enabled         => TRUE,
    comments        => '自动收集统计信息'
  );
END;
/

手动收集关键场景

​1. 直方图精准控制​

对偏斜字段SALES.AMOUNT_SOLD收集等高直方图：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SH',
    tabname    => 'SALES',
    method_opt => 'FOR COLUMNS SIZE 254 AMOUNT_SOLD', -- 254桶数
    degree     => 4
  );
END;
/
--
PL/SQL procedure successfully completed.
SYS@FREE>
-- 验证直方图
SELECT column_name, histogram, num_buckets 
FROM dba_tab_cols 
WHERE owner='SH' AND table_name='SALES' AND column_name='AMOUNT_SOLD';
--
COLUMN_NAME    HISTOGRAM       NUM_BUCKETS
______________ ____________ ______________
AMOUNT_SOLD    NONE                      0

​判定​：若HISTOGRAM=HEIGHT BALANCED且NUM_BUCKETS>=100，则有效反映数据分布。 

​2. 分区级统计验证​

检测分区SALES_Q4_2001的陈旧状态--提前确认有分区： 

SELECT partition_name, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE owner='SH' 
  AND table_name='SALES'
  AND partition_name='SALES_Q4_2024'
  AND stale_stats='YES'; 
  -- 陈旧状态检测

处理​：若返回记录，需对该分区单独收集： 

--提前检测确认分区
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',partname=>'SALES_Q4_2024');

三、可验证监控脚本（举例SH Schema）

准确性验证

对比COSTS表统计行数 vs 实际行数：

WITH actual AS (
    SELECT /*+ DYNAMIC_SAMPLING(4) */ COUNT(*) actual_rows 
    FROM sh.costs
)
SELECT 
    t.num_rows "统计行数",
    a.actual_rows "实际行数",
    ROUND(ABS((t.num_rows - a.actual_rows)/NULLIF(a.actual_rows,0))*100,2) diff_pct
FROM dba_tables t, actual a
WHERE t.owner='SH' AND t.table_name='COSTS'
  AND ABS(t.num_rows - a.actual_rows) > 10000; 
  -- 差异>1万行告警
  no rows selected

​阈值建议​：diff_pct > 5% 时需手动刷新统计。

自动任务健康监测

检查自动任务状态及失败历史：

SELECT 
    job_name, enabled, last_start_date,
    (SELECT COUNT(*) FROM dba_scheduler_job_run_details 
     WHERE job_name='GATHER_STATS_JOB' AND status='FAILED') fail_count
FROM dba_scheduler_jobs 
WHERE job_name='GATHER_STATS_JOB'
UNION ALL
-- 检查SH模式下统计信息锁定
SELECT 'STATS_LOCK', NULL, NULL, COUNT(*)
FROM dba_tab_statistics 
WHERE owner='SH' AND locked='YES';
JOB_NAME      ENABLED    LAST_START_DATE       FAIL_COUNT
_____________ __________ __________________ _____________
STATS_LOCK                                           4890

​处理逻辑​：

• fail_count > 0 → 检查 dba_scheduler_job_log
• STATS_LOCK > 0 → 使用 DBMS_STATS.UNLOCK_TABLE_STATS 解锁。

四、实操体会

在举例SH Schema中需重点关注：

1. ​分区表增量统计​：降低90%收集开销，尤其对时间分区字段（如SALES.TIME_ID）。
2. ​偏斜字段直方图​：对金额/数量等高基数列（AMOUNT_SOLD、QUANTITY_SOLD）定制桶数。
3. ​混合收集策略​：

• 自动任务处理日常变更
• ETL后对SALES/COSTS手动刷新
• 使用 PENDING STATS 测试：

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','PUBLISH','FALSE');
--
PL/SQL procedure successfully completed.

TIPS：

• 通过统计信息收集，可精准验证统计信息对查询优化的实际影响，实现从理论到高效运维的闭环
• 在进行大量数据加载或数据修改后，及时重新收集统计信息是非常重要的，以确保优化器能基于最新数据做出正确的决策。
• 在生产环境中，建议定期监控统计信息的准确性和完整性，必要时进行手动或自动的调整和重新收集
• 过度频繁地收集统计信息可能会影响系统性能，因为这会增加数据库的负载。因此，应根据实际需要平衡收集频率和系统性能。