1. 项目概述为什么数据库需要“自动体检”做DBA的尤其是负责Oracle数据库的估计没人没被统计信息不准的问题坑过。早上业务跑得好好的下午一个报表突然就慢成蜗牛一查执行计划发现索引没走全表扫描了。再一看原来是表的数据量在凌晨ETL任务后暴涨了十倍但统计信息还是昨天的老数据。这种场景就是典型的统计信息陈旧导致的性能“跳水”。统计信息是什么简单说它就是数据库优化器CBO的“眼睛”和“地图”。优化器要决定怎么执行一条SQL比如是先关联A表还是B表是用索引还是全表扫描全靠这些统计信息来估算每一步的“成本”。如果地图是错的——比如它以为某个表只有100行实际上有100万行——那优化器制定的“最优”路线很可能就是一场灾难。所以及时、准确的统计信息是SQL性能稳定的基石。“自动收集统计信息”这个功能就是Oracle数据库内置的一个“自动体检”机制。它会在后台自动探测哪些对象的统计信息已经过时比如数据变化量超过阈值然后在预设的维护窗口通常是业务低峰期自动发起收集任务。这相当于给数据库请了个24小时在线的“健康管家”目标是减少因统计信息陈旧引发的性能问题把DBA从手动收集、监控的重复劳动中解放出来。但别以为开了自动收集就万事大吉。我见过太多案例自动收集要么没生效要么收集得不对反而引发了新的性能问题。比如在错误的时段运行影响了在线业务或者对超大规模分区表采用了不合适的采样比例导致收集的统计信息质量很差。这个“管家”用好了是得力助手用不好就是“猪队友”。接下来我们就深入拆解它的工作机制、如何正确配置以及那些只有踩过坑才知道的实操要点。2. 核心机制与策略深度解析Oracle的自动收集统计信息功能其核心是一个名为GATHER_STATS_JOB的自动化作业在11g及之前或者是由SYS用户下的一系列调度作业在12c及之后与Auto Task深度集成。它的运行逻辑并非蛮干而是有一套精密的策略。2.1 触发收集的“传感器”监控与判断逻辑自动收集不是定时全库扫描而是基于监控Monitoring状态。当参数STATISTICS_LEVEL设置为TYPICAL或ALL时Oracle会监控所有执行了DML操作增删改的表。它会记录自上次收集统计信息以来该表发生的DML操作总量。其判断是否过时的核心逻辑是一个变化阈值。这个阈值不是固定值而是一个与表数据量相关的函数。大致规则是对于数据量很小的表很少量的变化如10%的行就会触发重新收集对于超大规模的表则需要更大比例的变动才会触发。这种设计是合理的因为大表少量数据的变化对统计信息整体分布的影响可能微乎其微频繁收集反而浪费资源。注意这个监控机制只对设置了MONITORING属性的表有效。虽然现代Oracle版本默认行为已改变但了解这一点有助于排查“为什么我的表数据变了很多统计信息却没自动更新”的问题。2.2 执行收集的“手术刀”DBMS_STATS 包与策略偏好自动收集任务在后台调用的就是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC过程。这个过程内部非常智能它会根据对象类型、数据量、上次收集时间等因素采用不同的策略增量收集Incremental Statistics这是针对分区表的利器。对于设置了INCREMENTAL属性的分区表自动收集只会刷新那些数据发生变化的分区的统计信息然后通过一种特殊的“概要统计”Synopsis技术高效地推导出全局表的统计信息。这避免了每次都要扫描全部分区的巨大开销。并发收集自动收集作业可以利用并行执行Parallel Execution来加速其并行度通常取决于系统参数JOB_QUEUE_PROCESSES以及表本身的并行度设置。智能采样对于非常大的表自动收集会采用采样ESTIMATE_PERCENT而非全扫。采样比例是自适应的Oracle会尝试在统计信息准确性和收集时间之间取得平衡。自动收集的行为受到一系列“策略偏好”Preferences的调控。这是DBA进行精细控制的关键。例如你可以通过DBMS_STATS.SET_GLOBAL_PREFS设置全局偏好或通过DBMS_STATS.SET_TABLE_PREFS为特定表设置个性化偏好。常用的偏好包括CASCADE是否同时收集索引的统计信息。DEGREE收集时使用的并行度。ESTIMATE_PERCENT采样百分比。METHOD_OPT直方图Histogram的收集策略。直方图对于数据分布不均匀的列至关重要。2.3 运行时段与资源管控维护窗口自动收集作业被设计在预定义的“维护窗口”Maintenance Window中运行。默认窗口通常是工作日的晚上10点到凌晨2点以及周末的整天。这些窗口由Oracle调度器Scheduler的WEEKNIGHT_WINDOW和WEEKEND_WINDOW定义。为什么是窗口核心是为了避让业务高峰。收集统计信息是资源密集型操作会消耗CPU、I/O并可能持有锁影响在线业务。将作业限制在低峰期是保证业务稳定性的关键设计。你可以查看和修改这些窗口SELECT window_name, repeat_interval, duration, enabled FROM dba_scheduler_windows;3. 配置、管理与监控实操指南了解了原理我们来看看具体怎么操作。自动收集功能默认是开启的但“能用”和“用好”之间隔着巨大的鸿沟。3.1 检查与启停自动收集首先确认自动收集任务的状态-- 查看自动任务状态12c及以上常用 SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client WHERE client_name auto optimizer stats collection; -- 查看调度作业状态传统方法 SELECT job_name, enabled, last_start_date, next_run_date FROM dba_scheduler_jobs WHERE job_name LIKE %GATHER_STATS% OR job_name BSLN_MAINTAIN_STATS_JOB;如果状态是ENABLED则表示正在运行。如果需要临时关闭例如进行大规模数据迁移期间-- 关闭自动统计信息收集 BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name auto optimizer stats collection, operation NULL, window_name NULL ); END; / -- 重新开启 BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name auto optimizer stats collection, operation NULL, window_name NULL ); END; /实操心得不要长期禁用自动收集我见过有DBA因为一次收集影响了业务就直接关掉了这个功能结果一个月后数据库性能全面劣化排查成本极高。正确的做法是调整和优化而非直接关闭。3.2 关键参数配置与优化默认配置不一定适合你的生产环境。以下是一些需要重点关注的配置点调整维护窗口如果默认窗口与你的批处理任务或备份任务冲突需要调整。-- 先禁用窗口 BEGIN DBMS_SCHEDULER.DISABLE(name WEEKNIGHT_WINDOW); END; / -- 修改窗口时间例如改为凌晨1点到5点 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name WEEKNIGHT_WINDOW, attribute REPEAT_INTERVAL, value FREQDAILY;BYHOUR1;BYMINUTE0;BYSECOND0 ); DBMS_SCHEDULER.SET_ATTRIBUTE( name WEEKNIGHT_WINDOW, attribute DURATION, value NUMTODSINTERVAL(4, HOUR) ); END; / -- 重新启用 BEGIN DBMS_SCHEDULER.ENABLE(name WEEKNIGHT_WINDOW); END; /设置全局统计信息偏好这是精细化管理的核心。-- 设置全局并行度为4采样比例为自动AUTO_SAMPLE_SIZE BEGIN DBMS_STATS.SET_GLOBAL_PREFS(pname DEGREE, pvalue 4); DBMS_STATS.SET_GLOBAL_PREFS(pname ESTIMATE_PERCENT, pvalue DBMS_STATS.AUTO_SAMPLE_SIZE); -- 对于大表收集所有列和索引的直方图桶数为254 DBMS_STATS.SET_GLOBAL_PREFS(pname METHOD_OPT, pvalue FOR ALL COLUMNS SIZE AUTO); -- 级联收集索引统计信息 DBMS_STATS.SET_GLOBAL_PREFS(pname CASCADE, pvalue DBMS_STATS.AUTO_CASCADE); END; /AUTO_SAMPLE_SIZE是Oracle推荐的设置它会根据对象大小智能决定采样比例通常比固定值如10%更可靠。为特殊表设置个性化偏好对于某些“问题表”需要特殊照顾。-- 假设有一个超大的历史表 HISTORY_LOG我们只希望收集最基本的统计信息不收集直方图 BEGIN DBMS_STATS.SET_TABLE_PREFS( ownname APP_USER, tabname HISTORY_LOG, pname METHOD_OPT, pvalue FOR ALL COLUMNS SIZE 1 -- SIZE 1 表示不收集直方图 ); -- 固定采样比例为1%以加快收集速度 DBMS_STATS.SET_TABLE_PREFS( ownname APP_USER, tabname HISTORY_LOG, pname ESTIMATE_PERCENT, pvalue 1 ); END; /3.3 监控自动收集活动与效果配置好了还得知道它干得怎么样。查看历史执行情况-- 查看最近自动收集任务的执行详情 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;识别统计信息陈旧的表-- 查看数据变化量大的表需要监控已开启 SELECT table_owner, table_name, inserts, updates, deletes, timestamp FROM dba_tab_modifications WHERE table_owner NOT IN (SYS, SYSTEM) ORDER BY (inserts updates deletes) DESC;将这个查询结果与dba_tables的LAST_ANALYZED时间对比就能找出需要关注的对象。检查统计信息锁定有时为了防止统计信息被自动作业更改会对表进行锁定。这会导致自动收集跳过该表。SELECT owner, table_name, stattype_locked FROM dba_tab_statistics WHERE stattype_locked IS NOT NULL;解锁命令EXEC DBMS_STATS.UNLOCK_TABLE_STATS(OWNER, TABLE_NAME);4. 高级场景与疑难问题处理自动收集在大多数情况下工作良好但遇到一些复杂场景就需要DBA手动介入。4.1 分区表与增量统计信息对于每天新增一个分区的超大型事实表增量统计信息是救命稻草。启用方法-- 1. 启用表的增量统计信息属性 BEGIN DBMS_STATS.SET_TABLE_PREFS( ownname DW_USER, tabname SALES_FACT, pname INCREMENTAL, pvalue TRUE ); END; / -- 2. 设置全局偏好以便自动收集也能利用增量特性12c后更推荐 BEGIN DBMS_STATS.SET_GLOBAL_PREFS(INCREMENTAL, TRUE); END; / -- 3. 为分区表设置分区级统计信息偏好例如只对新分区进行全量收集 BEGIN DBMS_STATS.SET_TABLE_PREFS(DW_USER, SALES_FACT, INCREMENTAL_LEVEL, PARTITION); END; /启用后自动收集作业会识别到哪些分区是新的或变化大的只刷新这些分区的统计信息然后合并到全局效率提升几个数量级。4.2 系统统计信息与固定对象统计信息自动收集主要针对用户数据对象。还有两类特殊的统计信息需要定期手动收集系统统计信息System Statistics描述CPU和I/O性能如cpuspeed、iotfrspeed等。这有助于优化器在CPU密集型和I/O密集型操作间做出更好选择。建议在系统硬件负载具有代表性时收集一次之后除非硬件变更否则无需频繁收集。EXEC DBMS_STATS.GATHER_SYSTEM_STATS(interval, interval 60); -- 收集60分钟内的负载情况固定对象统计信息Fixed Objects Statistics指动态性能视图如V$SQLV$SESSION底层X$表的统计信息。这些视图频繁被AWR报告、监控工具查询。如果统计信息不准会影响这些诊断查询的性能。建议在数据库稳定运行一段时间后收集。EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;4.3 当自动收集“失灵”常见问题排查问题自动收集作业运行了但某些大表的统计信息还是旧的。排查首先检查该表的统计信息是否被锁定stattype_locked。其次检查表的监控是否开启。最后查看自动收集作业的日志看是否因超时或错误而跳过该表。解决如果表特别大自动收集的采样可能不足以获得高质量统计信息。考虑为该表设置更长的维护窗口或手动使用更高的采样比例和并行度进行收集。问题自动收集期间业务系统出现短暂卡顿。排查检查自动收集作业的运行时间是否与业务高峰重叠。检查DBA_SCHEDULER_JOB_RUN_DETAILS视图看作业的实际耗时。同时检查AWR报告确认卡顿时段是否有高并发的gather_table_stats操作。解决调整维护窗口时间确保完全避开业务高峰。对于核心大表可以将其统计信息收集策略设置为“仅在全局级别收集”避免在维护窗口内进行全表扫描。问题启用增量统计信息后全局统计信息仍然不准。排查检查INCREMENTAL偏好是否确实已生效。检查分区表是否有大量分区被标记为STALE陈旧。增量统计依赖于分区级别的变化跟踪如果大量分区同时变化开销可能依然很大。解决确保INCREMENTAL和PUBLISH偏好正确设置。对于历史分区不再变化的情况可以考虑将其统计信息锁定避免重复计算。问题如何验证新收集的统计信息没有导致性能回退最佳实践在12c及以上版本利用统计信息回滚Restore和SQL计划基线SQL Plan Baseline功能。在收集重要表的统计信息前先备份旧的-- 备份当前统计信息 EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname SYS, stattab MY_STATS_BACKUP); EXEC DBMS_STATS.EXPORT_TABLE_STATS(ownname APP_USER, tabname ORDERS, stattab MY_STATS_BACKUP); -- 收集新统计信息... -- 如果发现关键SQL变慢立即回滚 EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname APP_USER, tabname ORDERS, stattab MY_STATS_BACKUP);同时确保关键SQL的执行计划已被SQL计划基线固定这样优化器即使有了新的统计信息也会优先使用已知的良好计划。5. 制定你的统计信息管理策略完全依赖自动收集并非万全之策。根据我的经验一个稳健的生产环境统计信息管理策略应该是“自动为主手动为辅重点监控”。分级管理核心交易表数据变化快对性能敏感。采用自动收集但为其设置更优的个性化偏好如合适的直方图、采样率并密切监控其LAST_ANALYZED时间。超大分区表/数据仓库表强烈推荐启用增量统计信息。可以适当放宽自动收集的触发阈值或安排在周末长时间窗口进行补充性手动收集。静态参考表/代码表数据几乎不变。可以在一次全量收集后直接锁定其统计信息避免自动作业浪费资源。特殊负载表例如白天大量查询凌晨批量删除/插入的表。需要仔细评估自动收集窗口防止在数据量“真空期”批量删除后插入前收集到无代表性的统计信息。建立监控告警监控DBA_OPTSTAT_OPERATIONS如果自动收集作业连续失败需要告警。监控关键表的LAST_ANALYZED时间如果超过设定的阈值如3天告警通知DBA检查。定期如每周检查AWR报告中“Top SQL by Elapsed Time”的变化如果出现新的低效SQL且与统计信息收集时间点吻合需立即分析。变更管理任何对全局或重要表统计信息偏好的修改都应在测试环境验证并记录在案。在进行大规模数据加载、归档或迁移操作前后应手动管理统计信息删除旧统计信息加载后立即收集新统计信息而不是等待自动作业。自动收集统计信息是Oracle提供的一个强大工具但它的本质是一个通用化的自动化流程。要让它真正为你的数据库性能保驾护航离不开DBA对其原理的深刻理解、对自身业务数据特点的把握以及一套主动的监控和管理策略。记住没有一劳永逸的配置只有持续观察、调整和优化的过程。把自动收集当作你团队里一位需要培训和指导的新成员而不是一个可以完全放任不管的黑盒你的数据库性能之路才会走得更加平稳。 Oracle数据库自动收集统计信息：原理、配置与实战优化指南
1. 项目概述为什么数据库需要“自动体检”做DBA的尤其是负责Oracle数据库的估计没人没被统计信息不准的问题坑过。早上业务跑得好好的下午一个报表突然就慢成蜗牛一查执行计划发现索引没走全表扫描了。再一看原来是表的数据量在凌晨ETL任务后暴涨了十倍但统计信息还是昨天的老数据。这种场景就是典型的统计信息陈旧导致的性能“跳水”。统计信息是什么简单说它就是数据库优化器CBO的“眼睛”和“地图”。优化器要决定怎么执行一条SQL比如是先关联A表还是B表是用索引还是全表扫描全靠这些统计信息来估算每一步的“成本”。如果地图是错的——比如它以为某个表只有100行实际上有100万行——那优化器制定的“最优”路线很可能就是一场灾难。所以及时、准确的统计信息是SQL性能稳定的基石。“自动收集统计信息”这个功能就是Oracle数据库内置的一个“自动体检”机制。它会在后台自动探测哪些对象的统计信息已经过时比如数据变化量超过阈值然后在预设的维护窗口通常是业务低峰期自动发起收集任务。这相当于给数据库请了个24小时在线的“健康管家”目标是减少因统计信息陈旧引发的性能问题把DBA从手动收集、监控的重复劳动中解放出来。但别以为开了自动收集就万事大吉。我见过太多案例自动收集要么没生效要么收集得不对反而引发了新的性能问题。比如在错误的时段运行影响了在线业务或者对超大规模分区表采用了不合适的采样比例导致收集的统计信息质量很差。这个“管家”用好了是得力助手用不好就是“猪队友”。接下来我们就深入拆解它的工作机制、如何正确配置以及那些只有踩过坑才知道的实操要点。2. 核心机制与策略深度解析Oracle的自动收集统计信息功能其核心是一个名为GATHER_STATS_JOB的自动化作业在11g及之前或者是由SYS用户下的一系列调度作业在12c及之后与Auto Task深度集成。它的运行逻辑并非蛮干而是有一套精密的策略。2.1 触发收集的“传感器”监控与判断逻辑自动收集不是定时全库扫描而是基于监控Monitoring状态。当参数STATISTICS_LEVEL设置为TYPICAL或ALL时Oracle会监控所有执行了DML操作增删改的表。它会记录自上次收集统计信息以来该表发生的DML操作总量。其判断是否过时的核心逻辑是一个变化阈值。这个阈值不是固定值而是一个与表数据量相关的函数。大致规则是对于数据量很小的表很少量的变化如10%的行就会触发重新收集对于超大规模的表则需要更大比例的变动才会触发。这种设计是合理的因为大表少量数据的变化对统计信息整体分布的影响可能微乎其微频繁收集反而浪费资源。注意这个监控机制只对设置了MONITORING属性的表有效。虽然现代Oracle版本默认行为已改变但了解这一点有助于排查“为什么我的表数据变了很多统计信息却没自动更新”的问题。2.2 执行收集的“手术刀”DBMS_STATS 包与策略偏好自动收集任务在后台调用的就是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC过程。这个过程内部非常智能它会根据对象类型、数据量、上次收集时间等因素采用不同的策略增量收集Incremental Statistics这是针对分区表的利器。对于设置了INCREMENTAL属性的分区表自动收集只会刷新那些数据发生变化的分区的统计信息然后通过一种特殊的“概要统计”Synopsis技术高效地推导出全局表的统计信息。这避免了每次都要扫描全部分区的巨大开销。并发收集自动收集作业可以利用并行执行Parallel Execution来加速其并行度通常取决于系统参数JOB_QUEUE_PROCESSES以及表本身的并行度设置。智能采样对于非常大的表自动收集会采用采样ESTIMATE_PERCENT而非全扫。采样比例是自适应的Oracle会尝试在统计信息准确性和收集时间之间取得平衡。自动收集的行为受到一系列“策略偏好”Preferences的调控。这是DBA进行精细控制的关键。例如你可以通过DBMS_STATS.SET_GLOBAL_PREFS设置全局偏好或通过DBMS_STATS.SET_TABLE_PREFS为特定表设置个性化偏好。常用的偏好包括CASCADE是否同时收集索引的统计信息。DEGREE收集时使用的并行度。ESTIMATE_PERCENT采样百分比。METHOD_OPT直方图Histogram的收集策略。直方图对于数据分布不均匀的列至关重要。2.3 运行时段与资源管控维护窗口自动收集作业被设计在预定义的“维护窗口”Maintenance Window中运行。默认窗口通常是工作日的晚上10点到凌晨2点以及周末的整天。这些窗口由Oracle调度器Scheduler的WEEKNIGHT_WINDOW和WEEKEND_WINDOW定义。为什么是窗口核心是为了避让业务高峰。收集统计信息是资源密集型操作会消耗CPU、I/O并可能持有锁影响在线业务。将作业限制在低峰期是保证业务稳定性的关键设计。你可以查看和修改这些窗口SELECT window_name, repeat_interval, duration, enabled FROM dba_scheduler_windows;3. 配置、管理与监控实操指南了解了原理我们来看看具体怎么操作。自动收集功能默认是开启的但“能用”和“用好”之间隔着巨大的鸿沟。3.1 检查与启停自动收集首先确认自动收集任务的状态-- 查看自动任务状态12c及以上常用 SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client WHERE client_name auto optimizer stats collection; -- 查看调度作业状态传统方法 SELECT job_name, enabled, last_start_date, next_run_date FROM dba_scheduler_jobs WHERE job_name LIKE %GATHER_STATS% OR job_name BSLN_MAINTAIN_STATS_JOB;如果状态是ENABLED则表示正在运行。如果需要临时关闭例如进行大规模数据迁移期间-- 关闭自动统计信息收集 BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name auto optimizer stats collection, operation NULL, window_name NULL ); END; / -- 重新开启 BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name auto optimizer stats collection, operation NULL, window_name NULL ); END; /实操心得不要长期禁用自动收集我见过有DBA因为一次收集影响了业务就直接关掉了这个功能结果一个月后数据库性能全面劣化排查成本极高。正确的做法是调整和优化而非直接关闭。3.2 关键参数配置与优化默认配置不一定适合你的生产环境。以下是一些需要重点关注的配置点调整维护窗口如果默认窗口与你的批处理任务或备份任务冲突需要调整。-- 先禁用窗口 BEGIN DBMS_SCHEDULER.DISABLE(name WEEKNIGHT_WINDOW); END; / -- 修改窗口时间例如改为凌晨1点到5点 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name WEEKNIGHT_WINDOW, attribute REPEAT_INTERVAL, value FREQDAILY;BYHOUR1;BYMINUTE0;BYSECOND0 ); DBMS_SCHEDULER.SET_ATTRIBUTE( name WEEKNIGHT_WINDOW, attribute DURATION, value NUMTODSINTERVAL(4, HOUR) ); END; / -- 重新启用 BEGIN DBMS_SCHEDULER.ENABLE(name WEEKNIGHT_WINDOW); END; /设置全局统计信息偏好这是精细化管理的核心。-- 设置全局并行度为4采样比例为自动AUTO_SAMPLE_SIZE BEGIN DBMS_STATS.SET_GLOBAL_PREFS(pname DEGREE, pvalue 4); DBMS_STATS.SET_GLOBAL_PREFS(pname ESTIMATE_PERCENT, pvalue DBMS_STATS.AUTO_SAMPLE_SIZE); -- 对于大表收集所有列和索引的直方图桶数为254 DBMS_STATS.SET_GLOBAL_PREFS(pname METHOD_OPT, pvalue FOR ALL COLUMNS SIZE AUTO); -- 级联收集索引统计信息 DBMS_STATS.SET_GLOBAL_PREFS(pname CASCADE, pvalue DBMS_STATS.AUTO_CASCADE); END; /AUTO_SAMPLE_SIZE是Oracle推荐的设置它会根据对象大小智能决定采样比例通常比固定值如10%更可靠。为特殊表设置个性化偏好对于某些“问题表”需要特殊照顾。-- 假设有一个超大的历史表 HISTORY_LOG我们只希望收集最基本的统计信息不收集直方图 BEGIN DBMS_STATS.SET_TABLE_PREFS( ownname APP_USER, tabname HISTORY_LOG, pname METHOD_OPT, pvalue FOR ALL COLUMNS SIZE 1 -- SIZE 1 表示不收集直方图 ); -- 固定采样比例为1%以加快收集速度 DBMS_STATS.SET_TABLE_PREFS( ownname APP_USER, tabname HISTORY_LOG, pname ESTIMATE_PERCENT, pvalue 1 ); END; /3.3 监控自动收集活动与效果配置好了还得知道它干得怎么样。查看历史执行情况-- 查看最近自动收集任务的执行详情 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;识别统计信息陈旧的表-- 查看数据变化量大的表需要监控已开启 SELECT table_owner, table_name, inserts, updates, deletes, timestamp FROM dba_tab_modifications WHERE table_owner NOT IN (SYS, SYSTEM) ORDER BY (inserts updates deletes) DESC;将这个查询结果与dba_tables的LAST_ANALYZED时间对比就能找出需要关注的对象。检查统计信息锁定有时为了防止统计信息被自动作业更改会对表进行锁定。这会导致自动收集跳过该表。SELECT owner, table_name, stattype_locked FROM dba_tab_statistics WHERE stattype_locked IS NOT NULL;解锁命令EXEC DBMS_STATS.UNLOCK_TABLE_STATS(OWNER, TABLE_NAME);4. 高级场景与疑难问题处理自动收集在大多数情况下工作良好但遇到一些复杂场景就需要DBA手动介入。4.1 分区表与增量统计信息对于每天新增一个分区的超大型事实表增量统计信息是救命稻草。启用方法-- 1. 启用表的增量统计信息属性 BEGIN DBMS_STATS.SET_TABLE_PREFS( ownname DW_USER, tabname SALES_FACT, pname INCREMENTAL, pvalue TRUE ); END; / -- 2. 设置全局偏好以便自动收集也能利用增量特性12c后更推荐 BEGIN DBMS_STATS.SET_GLOBAL_PREFS(INCREMENTAL, TRUE); END; / -- 3. 为分区表设置分区级统计信息偏好例如只对新分区进行全量收集 BEGIN DBMS_STATS.SET_TABLE_PREFS(DW_USER, SALES_FACT, INCREMENTAL_LEVEL, PARTITION); END; /启用后自动收集作业会识别到哪些分区是新的或变化大的只刷新这些分区的统计信息然后合并到全局效率提升几个数量级。4.2 系统统计信息与固定对象统计信息自动收集主要针对用户数据对象。还有两类特殊的统计信息需要定期手动收集系统统计信息System Statistics描述CPU和I/O性能如cpuspeed、iotfrspeed等。这有助于优化器在CPU密集型和I/O密集型操作间做出更好选择。建议在系统硬件负载具有代表性时收集一次之后除非硬件变更否则无需频繁收集。EXEC DBMS_STATS.GATHER_SYSTEM_STATS(interval, interval 60); -- 收集60分钟内的负载情况固定对象统计信息Fixed Objects Statistics指动态性能视图如V$SQLV$SESSION底层X$表的统计信息。这些视图频繁被AWR报告、监控工具查询。如果统计信息不准会影响这些诊断查询的性能。建议在数据库稳定运行一段时间后收集。EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;4.3 当自动收集“失灵”常见问题排查问题自动收集作业运行了但某些大表的统计信息还是旧的。排查首先检查该表的统计信息是否被锁定stattype_locked。其次检查表的监控是否开启。最后查看自动收集作业的日志看是否因超时或错误而跳过该表。解决如果表特别大自动收集的采样可能不足以获得高质量统计信息。考虑为该表设置更长的维护窗口或手动使用更高的采样比例和并行度进行收集。问题自动收集期间业务系统出现短暂卡顿。排查检查自动收集作业的运行时间是否与业务高峰重叠。检查DBA_SCHEDULER_JOB_RUN_DETAILS视图看作业的实际耗时。同时检查AWR报告确认卡顿时段是否有高并发的gather_table_stats操作。解决调整维护窗口时间确保完全避开业务高峰。对于核心大表可以将其统计信息收集策略设置为“仅在全局级别收集”避免在维护窗口内进行全表扫描。问题启用增量统计信息后全局统计信息仍然不准。排查检查INCREMENTAL偏好是否确实已生效。检查分区表是否有大量分区被标记为STALE陈旧。增量统计依赖于分区级别的变化跟踪如果大量分区同时变化开销可能依然很大。解决确保INCREMENTAL和PUBLISH偏好正确设置。对于历史分区不再变化的情况可以考虑将其统计信息锁定避免重复计算。问题如何验证新收集的统计信息没有导致性能回退最佳实践在12c及以上版本利用统计信息回滚Restore和SQL计划基线SQL Plan Baseline功能。在收集重要表的统计信息前先备份旧的-- 备份当前统计信息 EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname SYS, stattab MY_STATS_BACKUP); EXEC DBMS_STATS.EXPORT_TABLE_STATS(ownname APP_USER, tabname ORDERS, stattab MY_STATS_BACKUP); -- 收集新统计信息... -- 如果发现关键SQL变慢立即回滚 EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname APP_USER, tabname ORDERS, stattab MY_STATS_BACKUP);同时确保关键SQL的执行计划已被SQL计划基线固定这样优化器即使有了新的统计信息也会优先使用已知的良好计划。5. 制定你的统计信息管理策略完全依赖自动收集并非万全之策。根据我的经验一个稳健的生产环境统计信息管理策略应该是“自动为主手动为辅重点监控”。分级管理核心交易表数据变化快对性能敏感。采用自动收集但为其设置更优的个性化偏好如合适的直方图、采样率并密切监控其LAST_ANALYZED时间。超大分区表/数据仓库表强烈推荐启用增量统计信息。可以适当放宽自动收集的触发阈值或安排在周末长时间窗口进行补充性手动收集。静态参考表/代码表数据几乎不变。可以在一次全量收集后直接锁定其统计信息避免自动作业浪费资源。特殊负载表例如白天大量查询凌晨批量删除/插入的表。需要仔细评估自动收集窗口防止在数据量“真空期”批量删除后插入前收集到无代表性的统计信息。建立监控告警监控DBA_OPTSTAT_OPERATIONS如果自动收集作业连续失败需要告警。监控关键表的LAST_ANALYZED时间如果超过设定的阈值如3天告警通知DBA检查。定期如每周检查AWR报告中“Top SQL by Elapsed Time”的变化如果出现新的低效SQL且与统计信息收集时间点吻合需立即分析。变更管理任何对全局或重要表统计信息偏好的修改都应在测试环境验证并记录在案。在进行大规模数据加载、归档或迁移操作前后应手动管理统计信息删除旧统计信息加载后立即收集新统计信息而不是等待自动作业。自动收集统计信息是Oracle提供的一个强大工具但它的本质是一个通用化的自动化流程。要让它真正为你的数据库性能保驾护航离不开DBA对其原理的深刻理解、对自身业务数据特点的把握以及一套主动的监控和管理策略。记住没有一劳永逸的配置只有持续观察、调整和优化的过程。把自动收集当作你团队里一位需要培训和指导的新成员而不是一个可以完全放任不管的黑盒你的数据库性能之路才会走得更加平稳。