Oracle统计信息失准导致SQL变慢的排查与维护策略

做了几年的数据库开发和运维几乎每天都要和Oracle打交道。如果你也经常被SQL性能问题折磨大概率遇到过这种场景一条SQL前几天还跑得飞快今天突然慢成PPT数据量没怎么涨执行计划却从索引扫描变成了全表扫描。大多数情况下问题出在统计信息Statistic上。统计信息在Oracle里扮演的角色本质上是给优化器CBO提供的一幅“地图”——优化器依据它估算返回行数决定走索引还是全表、用嵌套循环还是哈希连接。地图错了再好的车手也会开进沟里。这篇博文我会从统计信息的存储结构、收集方式、常见故障和维护策略几个维度展开结合我实际遇到过的案例尽量把“统计信息”这个看似基础、实则暗藏大量坑的主题讲透。内容不追求面面俱到但每一个点都从实战角度出发适合正在学习Oracle性能调优的开发者也适合被优化器“坑”过的运维同事。1. 统计信息到底是什么优化器的“视力表”1.1 统计信息的核心分类与存储位置Oracle的统计信息并不是一个单一概念而是覆盖了从表、列到索引、系统的一整套数据画像。通常我们说的统计信息主要指这几类表统计记录表的行数NUM_ROWS、占用的数据块数BLOCKS、平均行长度AVG_ROW_LEN等。列统计记录列的NDV不同值数量、NULL值数量、平均列长度以及直方图信息。索引统计记录索引的高度BLEVEL、叶子块数LEAF_BLOCKS、聚集因子CLUSTERING_FACTOR等。系统统计记录CPU速度、I/O吞吐量等硬件相关数据影响优化器对成本的综合计算。这些数据存放在数据字典表里最常用的是这几个视图视图对应内容DBA_TABLES表级统计信息DBA_TAB_COL_STATISTICS列级统计信息DBA_HISTOGRAMS直方图数据DBA_INDEXES索引统计信息DBA_TAB_STATISTICS表统计信息的收集历史查询的时候我习惯一次性把表、列、索引的统计信息全部捞出来放在一个结果集里对比看这样能避免频繁切换视图。1.2 优化器是怎么“看”统计信息的理解统计信息价值的最好方式是看优化器如何做成本估算。以最简单的单表查询为例SELECT * FROM orders WHERE customer_id 100;优化器估算这条SQL返回多少行依赖的是列customer_id的统计信息。如果该列的NDV是1000表总行数是100万行优化器会估算返回约1000行如果NDV是10则估算返回10万行。这个估算结果直接决定了是走索引扫描还是全表扫描。这里有个容易被忽视的点优化器估算的不是“实际有多少行”而是“根据统计信息推断有多少行”。统计信息一旦失真推断就失真执行计划自然也跟着跑偏。1.3 统计信息不是缓存而是决策输入我之前带过一个同事在处理SQL变慢问题时第一反应就是清缓存、刷新共享池。这个思路不能说错但没有触及问题的根源。统计信息是长期持久化的决策依据不是短期的运行缓存。打个比方导航软件里的地图数据过期了你把导航App缓存清掉、重新打开路还是那条错路。必须更新地图本身导航才能给出正确路线。统计信息就是Oracle的“地图数据”需要主动更新而不是被动地等它失效。2. 统计信息的收集方式自动任务与手动命令的取舍2.1 默认自动收集任务是怎么运作的Oracle从10g开始引入了自动收集统计信息的任务默认在维护窗口期运行。维护窗口通常是工作日的22点到次日2点以及周末的全天时间。这个机制对大多数系统是友好的——它保证统计信息不会长期缺失。但是“自动”不代表“省心”。实际生产环境中自动收集存在两个常见问题收集时间不够某些维护窗口只有4小时如果库里有几十张大表自动任务可能跑不完。数据突变被错过上周一张表还是1000万行这周因为批量导入涨到1亿行而自动任务刚刚跑完接下来7天系统都会基于过期的统计信息做决策。我遇到过最典型的情况是业务方在周五傍晚做了一次大规模数据迁移周六早上报表SQL大面积变慢。原因是迁移过程修改了大量数据但统计信息还停留在周四晚上自动收集的状态。2.2 手动收集的推荐写法与参数选择所以只靠自动任务不够关键业务表在数据发生大幅变化后应该立刻手动收集。我最常用的手动收集写法如下BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname APP_USER, tabname ORDERS, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree 8, cascade TRUE, no_invalidate FALSE ); END; /这里的参数逐个解释一下estimate_percent采样比例。AUTO_SAMPLE_SIZE让Oracle自己决定采样多少通常比人为指定固定百分比更合理。method_opt直方图收集策略。FOR ALL COLUMNS SIZE AUTO表示由Oracle自动判断哪些列需要直方图、需要多少桶这是最省心的选择。degree并行度。大表建议设置4到8小表不设置也行。并行度过高会占用过多I/O资源生产环境要谨慎。cascade收集表统计时连带收集索引统计一般必须开启。no_invalidate设为FALSE表示收集完成后立即让相关游标失效这样新SQL会使用新统计信息。生产高峰期设为TRUE可以避免硬解析风暴但缺点是统计信息生效有延迟。2.3 全库收集真的有必要吗有些同事习惯了跑GATHER_DATABASE_STATS一次把整个库的统计信息全部更新。我个人不建议在生产环境这么干除非是刚完成数据迁移或者新上线系统。全库收集的问题在于耗时极长、系统负载高而且很多冷表的统计信息可能根本没有变化纯属白白消耗资源。更合理的做法是大表、业务核心表手动指定表名分批次收集。普通表交给自动维护窗口处理。数据剧烈变化的表单独评估必要时加急收集。比如有一张流水表每天新增几百万行我会写一个定时脚本每两小时对这张表跑一次GATHER_TABLE_STATS并且只收集表和索引统计不重建直方图。直方图太重频繁重建反而容易引发执行计划抖动。3. 直方图与列统计执行计划偏差的高发地带3.1 直方图分类Frequence、Height Balanced与Hybrid直方图是列统计中的重要组成部分它描述了列中数据值的分布情况。Oracle的直方图主要有三类类型特点适用场景FREQUENCY每个不同值一个桶精确记录分布NDV较小默认254以下HEIGHT BALANCED按高度等分桶适合NDV大但分布不均匀的情况NDV很大且数据有倾斜HYBRID12c引入结合前两者优点桶边界更灵活NDV很大且需要较高精度这个参数通过method_opt来指定。如果没有特殊需求SIZE AUTO是较好的默认选择。但有些场景必须手动干预比如某列的数据分布极其不均匀而Oracle没有识别出来导致估算严重偏差。我之前处理过一个实际案例订单表里status列有90%的行是FINISHED只有0.5%是ERROR查询WHERE status ERROR应该走索引扫描。但因为统计信息里ERROR对应的直方图信息没有及时更新优化器以为ERROR也占了很多行强行走了全表扫描。手动指定FOR COLUMNS status SIZE 254收集后执行计划立刻正常了。3.2 组合列统计解决多列相关性问题有一个经典误区两列各自都有准确的统计信息优化器就能准确估算两列联合过滤后的行数。问题是如果这两列之间存在相关性独立估算就会出错。举个例子省和城市两列。每个城市属于唯一一个省份两列强相关。优化器假设两列独立估算WHERE 省 某省 AND 城市 某市时会把两个过滤率相乘得出一个偏低的估算值进而可能选择错误的执行计划。Oracle 11g之后提供了多列统计信息Extended Statistics可以通过以下方式创建BEGIN DBMS_STATS.CREATE_EXTENDED_STATS( ownname APP_USER, tabname USERS, extension (PROVINCE, CITY) ); END; /创建后再次收集统计信息优化器就会为这个组合列单独生成统计数据和可能的直方图关联查询的估算准确度能提升不少。如果线上表的数据分布复杂而且查询条件经常是固定的几个列组合非常推荐用这个功能。3.3 NULL值统计一个容易被低估的干扰项NULL值的统计信息看似不起眼实际上对JOIN和IN子查询的执行计划影响非常大。Oracle的直方图通常不统计NULL值所以当某列大量为NULL时优化器可能严重低估实际参与JOIN的行数。举个例子A表和B表通过某个列关联A表该列有30%是NULLB表则没有NULL。在没看统计信息前优化器估算行数时可能默认NULL值也参与关联导致结果偏差过大。我处理过一个分页查询的性能问题排查到最后发现就是因为关联列上NULL值过多优化器错误地选择了一个嵌套循环连接执行时间从0.2秒涨到了40秒。解决办法是改写SQL显式过滤掉NULL值SELECT ... FROM a JOIN b ON a.key b.key WHERE a.extra_col IS NOT NULL;改写之后优化器的估算恢复准确执行计划也回到了哈希连接。4. 统计信息不准时的故障排查两个有代表性的案例4.1 案例一绑定变量环境下直方图失效11g之后的Oracle引入了自适应游标共享Adaptive Cursor Sharing理论上绑定变量和直方图可以共存。但实际运行中问题依然存在。我遇到过一个系统核心查询使用了绑定变量SELECT * FROM payment WHERE channel_id :cid AND pay_time :start_time;channel_id列上有一个10000行的大客户和一个只有几条数据的小渠道。SQL第一次是用小渠道的绑定变量值执行的优化器选择了索引扫描。后来大客户也开始走这条SQL因为绑定变量的值没有变化优化器复用旧计划继续走索引扫描结果扫描了几万行索引数据性能严重下降。排查步骤是查询DBA_HIST_SQLSTAT找到该SQL的历史执行记录确认同一SQL不同执行周期的性能差异。查询DBA_HIST_SQLTEXT定位SQL全文。用DBMS_XPLAN.DISPLAY_AWR对比不同执行ID的成本估算和实际行数。最终解决方案是对SQL使用/* NO_BIND_VIE * /提示或者改用字面值SQL。另一个方案是为列channel_id一列强制生成直方图并开启绑定变量感知12c的Bind Aware机制更完善。每个系统的数据分布不一样具体选择需要结合业务场景。4.2 案例二分区表增量收集后全局统计失真Oracle对分区表的统计信息收集分为三个层级分区级PARTITION、全局级GLOBAL和增量合并级INCREMENTAL。默认情况下GATHER_TABLE_STATS会同时收集分区级和全局级统计信息。但某些系统中为了节省收集时间启用了INCREMENTAL增量收集模式。这确实能大幅缩短收集时间——只统计数据发生变化的分区然后从已有统计信息中合并出全局统计。问题在于如果某个分区的数据被TRUNCATE后又重新装载但分区统计信息没有正确更新合并出来的全局统计就会出现严重偏差。我处理过一张按月份分区的流水表4月份的分区被TRUNCATE后重新导入了少量数据但全局统计信息还是基于旧的分区数据估算的。结果4月份的查询全部走了全表扫描每个月报表跑不出来。排查时关键一步是看全局统计和分区统计的对比SELECT partition_name, num_rows FROM dba_tab_partitions WHERE table_name BILL_DETAIL; SELECT num_rows FROM dba_tables WHERE table_name BILL_DETAIL;如果分区级行数之和与全局行数存在明显差异基本可以断定全局统计失真。解决办法是强制重新收集全局统计BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname APP_USER, tabname BILL_DETAIL, granularity GLOBAL ); END; /granularity参数的值可以是PARTITION、GLOBAL、ALL、APPROX_GLOBAL等需要根据实际情况指定。从这以后我形成了一条经验对分区表做批量数据操作后手动收集时显式指定granularity不要依赖默认行为。4.3 快速判断统计信息状态的SQL排查统计问题我通常会先跑下面这组SQL判断统计信息“新不新”“全不全”-- 查看表最近收集时间 SELECT table_name, last_analyzed, num_rows, blocks FROM dba_tables WHERE table_name PAYMENT; -- 查看列统计信息 SELECT column_name, num_distinct, nulls, density, histogram, num_buckets FROM dba_tab_col_statistics WHERE table_name PAYMENT ORDER BY column_id; -- 查看统计信息收集历史 SELECT table_name, partition_name, job_name, status, start_time, end_time FROM dba_optstat_operation_history WHERE table_name PAYMENT ORDER BY start_time DESC;这里重点看三个指标last_analyzed与当前时间是否严重脱节。num_rows是否与实际表行数严重不符。histogram和num_buckets是否为空或明显小于NDV。如果三个指标同时异常几乎可以确定是统计信息问题接下来再查执行计划细节就够了。5. 统计信息维护策略锁统计、导出导入与巡检节奏5.1 什么情况应该锁统计信息锁统计信息很多人不敢用担心锁住了会出问题。其实锁统计信息是一个非常有用的保护机制特别是在以下两类场景数据基本稳定的维表比如省份表、币种表、用户等级表数据量常年不变。自动收集任务每次更新这些表的统计信息纯属浪费资源还可能引入元数据抖动。已知特定计划的表现优于默认计划如果一张表的统计信息虽然可能不是最新但基于它的执行计划非常稳定且已被验证性能最优那就不希望它被自动收集更新。锁定的方式很简单BEGIN DBMS_STATS.LOCK_TABLE_STATS(APP_USER, DIM_REGION); END; /解锁则用UNLOCK_TABLE_STATS。锁住之后自动收集任务会跳过这张表统计信息保持锁定状态。不过要记住如果之后对这张表做了大规模数据变更并希望重新收集统计信息必须先解锁否则新增数据不会反映到统计信息里。5.2 统计信息的导出与迁移数据库迁移、版本升级、克隆环境准备时统计信息的处理是一个非常关键的步骤。对于已有的生产环境直接从源库导出统计信息到测试环境比在测试环境重新收集更合理——后者的采样策略、直方图生成逻辑未必与生产一致。我常用的导出导入方式如下-- 导出 BEGIN DBMS_STATS.EXPORT_TABLE_STATS( ownname APP_USER, tabname PAYMENT, stattab STATS_TEMP, statown APP_USER ); END; / -- 导入 BEGIN DBMS_STATS.IMPORT_TABLE_STATS( ownname APP_USER, tabname PAYMENT, stattab STATS_TEMP, statown APP_USER, force TRUE ); END; /这里必须注意stattab对应的表需要先创建创建脚本用DBMS_STATS.CREATE_STAT_TABLE完成BEGIN DBMS_STATS.CREATE_STAT_TABLE(APP_USER, STATS_TEMP); END; /迁移后最好抽查几张核心表的last_analyzed和直方图信息确认数据完整。5.3 我建议的统计信息巡检节奏统计信息维护不是出问题时才做的事。我在团队内部定的巡检节奏是每日观察自动维护窗口的完成情况确认没有超时、没有报错。重点看大表、分区表的收集是否正常结束。每周抽查核心业务表的last_analyzed和行数变化对比是否存在明显偏差。这里可以写一个简单的监控SQL把每周跑出的结果和上次收集时的行数做对比超过20%就告警。每次大版本变更更新统计信息后对比变更前后核心SQL的执行计划变化必要时采用SQL Plan Management固定关键执行计划。跑批和日常OLTP系统对统计信息变化的敏感度差别很大。OLTP系统对执行计划稳定性要求极高统计信息的每一次变动都可能在尖峰时刻引发硬解析震荡。所以OLTP库里我甚至会为一小部分核心SQL开启no_invalidate TRUE用空间换稳定性。在统计信息这件事上投入精力的产出比是很高的学到的知识能直接用在每一个SQL性能问题上。统计信息看似平淡无奇但真正理解它的人在优化器选择执行计划、SQL出现性能滑坡时会有更清晰的排查思路。这篇文章里的很多内容都来自我处理过的真实问题希望能给正在被Oracle性能问题困扰的同行一些启发。