1. 项目概述为什么数据库需要“自动”收集统计信息在数据库运维和性能调优的日常工作中我们经常会遇到一些“诡异”的SQL性能问题昨天还跑得飞快的报表今天突然就慢如蜗牛一个简单的查询执行计划却选择了全表扫描导致系统负载飙升。很多时候这些问题的根源并不在于代码逻辑而在于数据库优化器“看错了”数据。优化器如何“看”数据它依赖的就是统计信息——关于表、索引、列的数据分布、数据量、唯一值数量等一系列元数据。当统计信息过时或缺失时优化器就如同一个拿着过时地图的导航员很容易做出错误的路径选择生成低效的执行计划。Oracle数据库的“自动收集统计信息”功能就是为了解决这个问题而生的自动化运维任务。它本质上是一个后台的、周期性的作业由Oracle Scheduler调度在预设的维护窗口通常是夜间业务低峰期自动运行对数据库中发生变化的对象进行统计信息收集和更新。这个功能将DBA从繁琐、重复的手动收集工作中解放出来是保障数据库长期稳定运行、SQL性能可预测性的基石。对于任何规模的Oracle数据库环境理解并合理配置自动收集统计信息都是DBA和开发者的必备技能。2. 自动收集统计信息的核心机制与默认行为Oracle的自动收集统计信息功能主要由一个名为GATHER_STATS_JOB的自动化作业来实现。这个作业是Oracle 10g及以后版本中自动任务框架AutoTask的一部分。要理解它我们需要拆解几个核心组件。2.1 维护窗口与调度机制自动收集统计信息并非24小时不间断运行它被绑定在预定义的“维护窗口”上。默认情况下Oracle会创建两个维护窗口WEEKNIGHT_WINDOW周一至周五晚上10点至次日凌晨2点具体时间可能因版本和操作系统时区略有不同。WEEKEND_WINDOW周六和周日全天开放。GATHER_STATS_JOB作业会在这些窗口打开时自动启动并在窗口关闭时结束。这种设计巧妙地避开了业务高峰时段减少了对在线业务的影响。你可以通过以下查询查看当前数据库的维护窗口设置SELECT window_name, repeat_interval, duration, enabled FROM dba_scheduler_windows;2.2 收集策略哪些对象会被收集自动收集并非盲目地对所有对象进行全量收集那样既低效又浪费资源。它采用了一种智能的、基于变化量的策略。核心判断依据是对象的“数据变化量”。Oracle通过监控表上的DML操作INSERT, UPDATE, DELETE来跟踪数据变化当变化量超过某个阈值时该对象才会被标记为需要收集。这个阈值通常是对象总行数的一个百分比。例如在Oracle 11g及以后版本中默认的阈值大致是当表中有超过10%的行发生变化时具体算法更复杂涉及表大小等因素该表就会被纳入下次自动收集的范围。这种机制确保了资源只用在“刀刃”上。2.3 默认配置与潜在问题虽然自动收集功能开箱即用但默认配置并非放之四海而皆准。在以下几种典型场景中默认行为可能带来问题超大规模表对于一个拥有数十亿行数据的表即使只收集10%样本的统计信息也可能耗时极长占用大量I/O和CPU资源可能无法在维护窗口内完成甚至影响其他任务。频繁变化的维表或配置表一些较小的、但被频繁更新的表如状态表、配置表可能因为变化频繁而反复被收集造成不必要的开销。特殊业务系统对于7x24小时运行的业务系统默认的夜间维护窗口可能依然存在业务活动自动收集带来的资源竞争可能导致业务性能抖动。统计信息回退在极少数情况下新收集的统计信息可能导致某个关键SQL的执行计划变差。虽然Oracle有执行计划稳定性机制如SQL Plan Baseline但并非万能。因此一个合格的DBA不会完全依赖默认配置而是需要根据自身数据库的特点进行审视和调整。3. 监控、诊断与手动干预在启用自动收集后我们首先需要知道它是否在正常工作以及效果如何。监控是第一步。3.1 如何监控自动收集任务的状态你可以通过以下数据字典视图来获取自动收集任务的执行历史、当前状态和详细信息查看作业历史DBA_SCHEDULER_JOB_LOG和DBA_SCHEDULER_JOB_RUN_DETAILS视图可以查询GATHER_STATS_JOB的历史运行记录包括开始时间、结束时间、运行状态SUCCEEDED/FAILED等。查看任务详情DBA_AUTOTASK_CLIENT和DBA_AUTOTASK_OPERATION视图提供了自动任务客户端包括统计信息收集的详细配置和历史。查看收集操作详情DBA_OPTSTAT_OPERATIONS视图记录了所有统计信息收集操作包括自动和手动的详细信息如操作ID、开始/结束时间、涉及的对象数量等是排查收集时长问题的关键视图。一个常用的监控查询用于检查最近一次自动收集的概况SELECT operation, target, start_time, end_time, (end_time - start_time) * 24 * 60 as duration_mins, status FROM dba_optstat_operations WHERE operation LIKE gather_database_stats% ORDER BY start_time DESC FETCH FIRST 10 ROWS ONLY;3.2 识别统计信息问题导致的性能劣化当出现SQL性能问题时如何判断是否是统计信息过时引起的以下是一些排查思路检查执行计划突变对比同一SQL历史执行计划和当前执行计划。如果计划突然改变如索引扫描变为全表扫描且SQL文本和绑定变量未变统计信息问题嫌疑很大。可以使用AWR报告、SQL Monitor报告或DBMS_XPLAN包进行分析。检查对象最后分析时间查询DBA_TABLES、DBA_INDEXES视图中的LAST_ANALYZED字段。如果一个频繁变更的大表LAST_ANALYZED时间远早于当前时间就需要警惕。观察“陈旧”统计信息Oracle 11g引入了STALE_PERCENT的概念可以在DBA_TAB_STATISTICS视图中查看。但注意自动收集的判断逻辑比单纯查询这个视图更复杂。注意LAST_ANALYZED为NULL并不一定代表没有统计信息。对于某些特殊对象如外部表、固定表或者统计信息被锁定Locked时此字段也可能为空。判断时需结合NUM_ROWS等字段综合考量。3.3 何时及如何进行手动收集自动收集虽好但遇到以下情况时手动干预是必要的上线或迁移后批量导入大量数据后应立即手动收集相关对象的统计信息因为监控数据变化量的机制有延迟。紧急性能问题当确认某个SQL因统计信息过时导致性能问题且业务无法等待下一个维护窗口时。特殊需求需要采用非默认参数如更大的样本率、并行度收集统计信息时。手动收集主要使用DBMS_STATS包。以下是几个关键过程收集表统计信息EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname SCOTT, tabname EMP, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, -- 自动决定样本率 degree 4, -- 设置并行度 cascade TRUE -- 同时收集索引统计信息 );收集模式统计信息EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname SCOTT, options GATHER AUTO, -- 只收集过时对象 degree 4 );收集数据库统计信息谨慎使用EXEC DBMS_STATS.GATHER_DATABASE_STATS( options GATHER AUTO, degree 8 );关键参数解析estimate_percent采样百分比。DBMS_STATS.AUTO_SAMPLE_SIZE是推荐值Oracle会自动计算一个能保证统计信息质量的样本量通常比老版本的固定值如10%更科学。degree并行度。对于大表设置合理的并行度可以大幅缩短收集时间但会增加当前系统负载。cascade是否级联收集索引统计信息。通常设为TRUE。optionsGATHER收集所有对象。GATHER AUTO只收集过时Stale对象。这是手动收集时最常用的选项。GATHER STALE同GATHER AUTO。GATHER EMPTY只收集没有统计信息的对象。4. 高级配置与定制化策略对于生产环境直接使用默认的自动收集配置往往不够。我们需要根据业务特点进行精细化的定制。4.1 调整维护窗口如果默认的维护窗口时间与你的业务高峰冲突可以修改窗口的时间或禁用自动收集改为在自定义的时间段手动调度。-- 禁用默认的WEEKNIGHT_WINDOW BEGIN DBMS_SCHEDULER.DISABLE(name SYS.WEEKNIGHT_WINDOW); END; / -- 创建自定义窗口示例凌晨3点到5点 BEGIN DBMS_SCHEDULER.CREATE_WINDOW( window_name CUSTOM_STATS_WINDOW, resource_plan NULL, start_date SYSTIMESTAMP, repeat_interval FREQDAILY;BYHOUR3;BYMINUTE0;BYSECOND0, end_date NULL, duration INTERVAL 120 MINUTE, comments Custom window for gathering stats ); END; / -- 将自动收集任务关联到自定义窗口需先禁用默认的自动收集4.2 为特定对象设置个性化策略通过DBMS_STATS.SET_TABLE_PREFS等过程可以为单个表设置独立的收集策略覆盖全局默认值。这是处理特殊对象的利器。排除特定表对于静态的代码表、日志表仅插入或者使用动态采样更合适的表可以将其从自动收集中排除。EXEC DBMS_STATS.SET_TABLE_PREFS(SCOTT, AUDIT_LOG, PUBLISH, FALSE); -- 设置PUBLISH为FALSE收集的统计信息只存于私有区域不影响生产 -- 或者直接锁定统计信息 EXEC DBMS_STATS.LOCK_TABLE_STATS(SCOTT, AUDIT_LOG);调整大表的收集参数为超大规模表设置更低的采样比例、更高的并行度或指定只收集关键列的统计信息。EXEC DBMS_STATS.SET_TABLE_PREFS(SH, SALES, ESTIMATE_PERCENT, 1); -- 设置1%采样 EXEC DBMS_STATS.SET_TABLE_PREFS(SH, SALES, DEGREE, 16); -- 设置并行度16 EXEC DBMS_STATS.SET_TABLE_PREFS(SH, SALES, METHOD_OPT, FOR ALL COLUMNS SIZE SKEWONLY); -- 仅对数据倾斜的列创建直方图控制直方图直方图用于描述列的数据分布对于等值查询、范围查询的优化至关重要但过多的直方图也会增加收集开销和优化器负担。METHOD_OPT参数用于控制直方图的创建。-- 只为CUST_ID和PROD_ID列创建直方图桶数为254 EXEC DBMS_STATS.SET_TABLE_PREFS(SH, SALES, METHOD_OPT, FOR COLUMNS CUST_ID, PROD_ID SIZE 254);4.3 处理分区表的统计信息对于分区表统计信息收集策略更为复杂。你需要决定是收集全局统计信息、分区级统计信息还是两者都收集。GRANULARITY参数ALL收集全局、分区、子分区所有级别的统计信息。最全面但也最耗时。GLOBAL只收集全局统计信息。适用于查询大多带有分区键且需要跨分区聚合的场景。PARTITION只收集分区级统计信息。适用于查询通常只访问单个分区的场景。AUTO由Oracle自动决定。这是默认值通常也是最佳选择。对于按时间范围如按天、按月分区的历史表一个常见的策略是只对新数据发生变化的分区进行增量收集并定期如每周收集一次全局统计信息。这可以极大减少收集开销。-- 增量收集分区表统计信息需表有增量统计信息特性支持 EXEC DBMS_STATS.GATHER_TABLE_STATS(SH, SALES, granularity INCREMENTAL, degree 8);5. 实战中的常见“坑”与最佳实践基于多年的运维经验自动收集统计信息功能虽然强大但踩坑的经历也不少。下面分享几个典型案例和应对策略。5.1 坑一自动收集导致性能抖动场景一个OLTP系统在每晚10:05左右偶尔会出现短暂的业务响应时间飙升。通过AWR报告对比发现该时间段内DB CPU和User I/O等待事件明显增高主要SQL是gather_table_stats。根因分析虽然维护窗口设置在业务低峰期但系统中有几个巨大的核心业务表。自动收集这些表时即使采用采样也会产生大量的全表扫描或快速全索引扫描操作消耗大量Buffer Cache和I/O资源与尚未结束的零星业务请求产生资源竞争。解决方案隔离资源使用Oracle Resource Manager为维护任务创建独立的资源消费者组限制其最大CPU和I/O使用率确保业务线程的资源不被过度挤占。分而治之使用DBMS_STATS.SET_TABLE_PREFS为这几个巨表设置独立的、更保守的收集策略。例如将ESTIMATE_PERCENT设为更小的固定值如0.1将DEGREE设为1禁用并行减少CPU争用并将METHOD_OPT设置为只收集关键列的直方图。错峰收集创建一个自定义Scheduler Job在维护窗口的更后期如凌晨1点再单独收集这些巨表与其他对象的收集时间错开。5.2 坑二统计信息“回退”与执行计划翻转场景周一早上一个核心报表SQL突然变慢。检查发现执行计划从高效的索引范围扫描变成了全表扫描。对比统计信息发现周日晚间自动收集后某个关键列的NDVNumber of Distinct Values唯一值数量估算值发生了较大偏差。根因分析自动收集采用的采样算法在特定数据分布下如极端倾斜、新批量加载特定值可能产生误差。优化器基于有误差的统计信息错误地判断全表扫描的成本更低。解决方案启用SQL计划基线SQL Plan Baseline这是预防执行计划突变的终极武器。对于已知性能良好的关键SQL在其执行计划稳定时可以将其固定。-- 从游标缓存中加载一个SQL的执行计划作为基线 DECLARE v_plans_loaded PLS_INTEGER; BEGIN v_plans_loaded : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id g8uxy6c4s5b2a ); END; /还原统计信息Oracle提供了DBMS_STATS.RESTORE_TABLE_STATS功能可以将统计信息回退到之前的某个时间点。这相当于一个“后悔药”。-- 查看可恢复的历史时间点 SELECT stats_update_time FROM dba_tab_stats_history WHERE ownerSCOTT AND table_nameEMP; -- 恢复到特定时间点 EXEC DBMS_STATS.RESTORE_TABLE_STATS(SCOTT, EMP, as_of_timestamp SYSTIMESTAMP-1);采用测试环境验证对于重大变更如大批量数据迁移先在测试环境手动收集统计信息并运行核心业务SQL进行验证确认无误后再在生产环境操作。5.3 坑三直方图引发的绑定变量窥视问题场景一个使用绑定变量的查询有时快有时慢。检查发现该列上存在直方图统计信息。根因分析这是Oracle优化器一个经典问题——“绑定变量窥视”。在SQL第一次硬解析时优化器会“窥视”传入的绑定变量具体值并结合该列的直方图为该特定值生成一个执行计划。如果后续传入的值数据分布差异巨大例如第一次是查询占比1%的‘ACTIVE’状态第二次是查询占比50%的‘HISTORY’状态但Oracle可能仍沿用第一个计划导致性能不佳。解决方案评估直方图的必要性不是所有列都需要直方图。只有数据分布高度不均匀、且该列是查询条件中的常用过滤列时直方图才有正面收益。对于分布均匀的列如主键、序列生成的ID或者仅有少量唯一值的状态列创建直方图可能弊大于利。可以使用DBMS_STATS.DELETE_COLUMN_STATS删除不必要的直方图。使用自适应游标共享确保数据库参数CURSOR_SHARING设置为FORCE或SIMILAR已废弃的时代已过去现在更依赖ADAPTIVE_PLAN和ADAPTIVE_CURSOR_SHARING特性。但直方图与绑定变量的配合仍需谨慎。考虑字面量或SQL Profile对于极其关键且变量值选择性差异大的SQL有时不得不考虑放弃绑定变量有SQL注入和安全风险需权衡或使用SQL Profile来引导优化器。5.4 最佳实践总结监控先行定期检查自动收集作业的运行时长、状态以及关键对象的LAST_ANALYZED时间。将其纳入日常巡检。默认配置是起点不是终点务必根据数据库实际负载、对象大小和业务特点调整维护窗口、并行度、采样率等参数。区别对待对超大规模表、频繁变化的小表、静态表、分区表采用不同的统计信息管理策略。善用DBMS_STATS.SET_*_PREFS进行个性化设置。备份与回退在进行大规模手动收集或修改策略前使用DBMS_STATS.EXPORT_*_STATS导出重要对象的当前统计信息。知道如何使用RESTORE功能。与开发协同让开发者了解统计信息的重要性。在代码上线、大批量数据操作ETL后明确统计信息收集的流程和责任。利用新特性如果使用Oracle 12c及以上版本关注如“实时统计信息”、“自动重新优化”、“SQL计划管理”等高级特性它们能与自动收集形成更好配合。统计信息管理是数据库性能调优中一项静水深流的工作。一个配置得当的自动收集策略就像一位默默无闻的守护者能在很大程度上避免许多突如其来的性能风暴。它需要的是DBA对自身数据库业务的深刻理解以及精细化的配置和持续的监控调整。
