小伙伴们，有没有因为统计信息不准，导致了业务卡顿，各种状况频出，这几天在实践和实操的过程中，时不时就需要进行统计信息的收集。同时统计信息收集的动作也是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点及周末全天
- 优先级策略：先处理缺失统计信息对象，再处理陈旧度高的对象
--启用命令BEGINDBMS_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
-----------------00高聚簇因子（接近表块数）表明索引效率低，需结合统计信息调整索引策略。
四、举例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自动收集任务
BEGINDBMS_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收集等高直方图：
BEGINDBMS_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中需重点关注：
- 分区表增量统计：降低90%收集开销，尤其对时间分区字段（如SALES.TIME_ID）。
- 偏斜字段直方图：对金额/数量等高基数列（AMOUNT_SOLD、QUANTITY_SOLD）定制桶数。
- 混合收集策略：
- 自动任务处理日常变更
- ETL后对SALES/COSTS手动刷新
- 使用 PENDING STATS 测试：
EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','PUBLISH','FALSE');
--
PL/SQL procedure successfully completed.TIPS：
- 通过统计信息收集，可精准验证统计信息对查询优化的实际影响，实现从理论到高效运维的闭环
- 在进行大量数据加载或数据修改后，及时重新收集统计信息是非常重要的，以确保优化器能基于最新数据做出正确的决策。
- 在生产环境中，建议定期监控统计信息的准确性和完整性，必要时进行手动或自动的调整和重新收集
- 过度频繁地收集统计信息可能会影响系统性能，因为这会增加数据库的负载。因此，应根据实际需要平衡收集频率和系统性能。
与超声波测距)
