如果你接手过一个跑了几年的生产系统多半会遇到这样的怪事昨天还跑得飞快的SQL今天突然慢得像蜗牛执行计划里原本漂亮的索引扫描变成了让人头疼的全表扫描。查数据量没涨多少看CPU、内存、IO全都正常。折腾半天最后发现原因简单得让人想拍桌子——统计信息过期了。统计信息Statistics这个话题几乎每个Oracle DBA都听过但真正能把它讲清楚、用得溜的人不多。很多人知道要跑一下dbms_stats.gather_table_stats却不知道为什么要这么做、参数该怎么选、直方图什么时候该删、什么时候该锁。这篇就把这事儿掰开揉碎了说清楚。适合刚接触Oracle的开发者、准备考OCP的同行以及已经被执行计划折磨过几次、想系统补一补统计信息知识的DBA。1. 统计信息到底是什么——优化器的“视力表”1.1 优化器的工作方式成本估算Oracle里每条SQL执行之前优化器都要决定怎么跑是全表扫描还是走索引两个表关联谁做驱动表这一系列决策不是拍脑袋而是要算“成本”Cost。成本越小执行方式越优。问题来了成本怎么算总不能每条SQL先把表读一遍再决定吧Oracle的做法是“算命”——根据已经记录好的数据特征估算出这次操作大概要读多少个数据块、产生多少逻辑读。而这些“数据特征”就是统计信息。你可以把优化器想象成一个导航App。导航App要规划最快的路线得知道每条路的等级、限速、当前拥堵情况。如果这些数据是三天前的导航就会把你导到一条已经堵死的路上去。优化器一样它手里的“路况信息”就是统计信息。表当前有多少行、一个列里有多少个不同的值、数据是均匀分布还是集中在少数几个值上、索引有多少层、索引和表的物理存储顺序差多少……这些全部来自统计信息。所以统计信息本质上就是数据库对自身数据的“认知”。认知过期了决策就会出偏差。它不是SQL执行时的实时数据而是周期性或者按需收集的“快照”。1.2 四类核心统计指标做统计信息管理脑子里得有清晰的分类。Oracle的统计信息分为四大类表统计、列统计、索引统计、系统统计。每一类各管一块缺了哪个优化器都会“看不清”。表统计是最基础的。DBA_TABLES里能看到NUM_ROWS行数、BLOCKS已用数据块数、AVG_ROW_LEN平均行长。这些决定了一张表的“体量”。优化器判断要不要全表扫描靠的就是BLOCKS和行数——如果整表就几十个块全表扫描反而是最优解。这里有个常见的误解有人觉得“走了索引才是好的”其实对于小表索引扫描的成本往往高于全表扫描。靠体量数据优化器才能做出正确判断你不需要也不可能手工干预每一次选择。列统计存于DBA_TAB_COL_STATISTICS包含NUM_DISTINCT不同值个数、LOW_VALUE/HIGH_VALUE列最小值/最大值、NUM_NULLS空值数、DENSITY密度。列统计直接影响谓词评估。WHERE status CLOSED大概能过滤掉多少行优化器就是靠这些算出来的。如果列上有直方图还会额外记录频率分布信息。索引统计在DBA_IND_STATISTICS里关键字段是BLEVEL索引高度从根到叶子要跨多少层、LEAF_BLOCKS叶子块数、CLUSTERING_FACTOR聚簇因子。聚簇因子这条我要多说一句它描述的是索引顺序和表内数据物理顺序的接近程度。如果CF接近表块数说明物理有序回表效率高如果CF接近行数说明数据乱序每次回表都大概率是一个新的数据块回表成本很高。很多DBA做索引重建时会拿CF当参考虽然不能只看这一个指标但它确实是判断索引健康度的重要依据。系统统计是从Oracle 9i开始引入的记录CPU速度、IO读写耗时等。它让优化器的成本估算从“只看逻辑IO”升级为“综合CPU和物理IO”。如果你移动了数据库的物理主机或者存储从机械盘换成了SSD条件允许的话可以重新收集系统统计信息代价并不高。2. 统计信息收集实操DBMS_STATS 的正确用法2.1 DBMS_STATS 包的核心玩法统计信息收集现在唯一推荐的方式就是DBMS_STATS包。如果你在老文档里看到用ANALYZE TABLE ... COMPUTE STATISTICS那属于上个时代的遗产新项目别去学了。ANALYZE主要用于验证结构、回收空间这类工作不再用于收集优化器统计信息。最常用的三个入口GATHER_TABLE_STATS收集单表统计信息。GATHER_SCHEMA_STATS收集某个用户下所有对象的统计信息。GATHER_DATABASE_STATS全库收集一般用于迁移或升级后的重建。日常运维用得最多的是schema级收集。一个企业级系统里用户少则几个、多则几十个按schema管理比较合理。下面是一个比较落地的调用方式BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( ownname APP_USER, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree 8, cascade TRUE, granularity AUTO, options GATHER STALE ); END; /拆开讲每个参数的用意。ownname不用说目标schema。estimate_percent是采样率AUTO让Oracle自己根据表大小决定——小表全量统计大表自动抽个百分比。日常维护用AUTO基本靠谱别想当然地填1%或5%。有些场景确实要手动指定采样率比如超大表、收集时间窗口紧张下面细说。method_opt决定直方图的生成策略这是整条命令里最需要花心思的参数后面专门开一节讲。degree是并行度大表收集时并行能显著提速但生产环境要谨慎建议先评估当前系统负载再设。我个人的习惯是白天跑业务时用2或4凌晨维护窗口用8极端大表才用16。cascade表示收集表统计时顺带收集索引统计必须为TRUE。你很难想象只更新表统计、不动索引统计会带来什么后果——优化器拿着新行数配旧索引层数算出来的成本一定是个四不像。granularity只对分区表有意义。AUTO会让Oracle自己判断在哪个粒度收集全局、分区、还是二者都收集。options GATHER STALE表示只收集发生过变化的表相比全量收集省很多时间。配合后面要讲的增量统计机制这是生产环境效率最高的配置。2.2 自动收集任务默认机制与现代实践从Oracle 11g开始系统自带了一个自动维护任务叫 Auto Optimizer Stats Collection。它每天在维护窗口默认晚上10点到凌晨2点周末时间更长自动运行逻辑就是上面那条GATHER STALE的思路只收集“过期”的统计信息。很多人对这个自动任务又爱又恨。爱的是省心新部署的库基本不用管统计信息恨的是它有时候“帮倒忙”——某个大表正在被报表任务疯狂扫描自动收集偏偏这时候冲上去锁资源或者收集完统计信息之后执行计划变了SQL反而变慢。我的建议是自动任务别轻易关但关键大表要“摘出来”手工管理。具体做法是给大表锁定统计信息后面讲或者把关键表从自动收集中排除掉你自己定窗口去收集。另外夜里如果用批处理跑数据仓库任务和自动维护窗口冲突就应该把维护窗口时间调开别让两个重量级任务在同一时段打架。SELECT WINDOW_NAME, ENABLED, ACTIVE_START_TIME, ACTIVE_DURATION FROM DBA_AUTOTASK_WINDOW_CLIENTS;这条查询能看当前各维护窗口的状态。调窗口时间可以用DBMS_SCHEDULER.DISABLE/ENABLE配合改参数操作前先在测试库验证生产环境改窗口要挑个能接受的时段。2.3 采样率、并行度与资源控制统计信息收集本质上是扫描数据消耗资源是必然的。核心矛盾在于采样率越高统计越准确但耗时越长、耗IO越多。实践经验是分而治之。小表比如几百MB以内直接全量收集毫无压力。中大型表几GB到几十GBAUTO_SAMPLE_SIZE的智能采样基本够用。真正难搞的是几百GB甚至上TB的大表。这种表即使在凌晨收集全表扫描一次也要几十分钟到几小时。这时候有两种选择一是用增量统计分区表增量特性后面讲二是手动降低采样率到1%甚至更小。低采样率下统计信息不一定精确但总比过期强得多而且对不稳定执行的判断往往已经够用。并行度这块我踩过一次坑。有一回给核心业务大表收集开了16个并行本地测试机一点问题没有上线跑的时候直接把磁盘读穿连带影响业务。从那以后我给自己定了个规矩生产环境并行度不超过业务高峰期可用IO资源的四分之一。真要提速优先考虑分区并行DBMS_STATS配合分区级并行收集而不是盲目调degree。3. 直方图详解数据分布不均时的救命稻草3.1 均匀假设的陷阱Oracle对列数据的默认假设是“均匀分布”。什么意思如果一个列有100个不同值优化器估算WHERE c 某个值的时候会默认每个值大约占1%的行。但真实业务数据哪来这么均匀。随便举几个例子订单表里STATUS列99%是“已完成”1%是“待支付”用户表里REGION列一个城市可能占了40%日志表里ERROR_CODE大部分行是正常值偶尔几个异常码。这种情况下“均匀分布”这个假设错得离谱。优化器拿着错误的估算最典型的结果就是对“待支付”这个高频值走全表扫描或者选错驱动表。明明这个值只占了1%按默认假设应该走索引结果优化器以为它会返回很多行于是选了全表。SQL每跑一次就全表扫一次慢得让人崩溃。直方图就是用来打破均匀假设的。它会记录一个列里不同值出现的频率分布优化器据此能算出更接近现实的基数Cardinality。没有直方图的时候优化器是个“平均主义大师”有了合适的直方图它才能看清真实的“贫富差距”。3.2 直方图的类型演进直方图不是生来就有的。Oracle 8i时代连直方图都没有9i开始引入HEIGHT BALANCED频率图10g加入FREQUENCY。到11g又加了TOP-FREQUENCY和HYBRID12c之后基本以HYBRID为主流。FREQUENCY列出每个NDV不同值的频率。列的不同值少时最精确。HEIGHT BALANCED把值域分成等高的若干个桶。NDV很多时用细节有损。TOP-FREQUENCY只记录出现次数最多的前N个值占总行数阈值以上的其余归入桶底。HYBRID频率和等高结合自动对高频值单独建档低频值分组统计。日常使用你基本不用关心具体类型交给数据库自动选择就好。真正需要你决策的是哪些列要生成直方图、每个列多少个桶。3.3 method_opt 参数与实战设置method_opt是收集统计信息时最重要的参数没有之一。它直接决定哪些列会生成直方图、直方图的桶数量。推荐设置-- 生产常用方案1全部列自动判断 method_opt FOR ALL COLUMNS SIZE AUTO -- 生产常用方案2只对特定列强制生成直方图 method_opt FOR ALL COLUMNS SIZE 1, FOR COLUMNS STATUS SIZE 254 -- 生产常用方案3只重新收集已有的直方图不新增 method_opt FOR ALL COLUMNS SIZE REPEATSIZE AUTO是绝大多数情况的最优解。Oracle会根据实际数据分布和SQL负载自动决定哪些列需要直方图、桶数多少。SIZE 1基本等同于“不生成直方图”适合明确不需要直方图的高NDV列比如主键列、流水号列。SIZE REPEAT有特定用途如果你已经手工调优过一批直方图希望以后的自动收集保持现状就用它。注意REPEAT不会删除多余的直方图也不会新增只维护已有直方图的状态。另外一个实战经验对复合索引的前导列通常不需要单独建直方图但复合索引的非前导列在独立查询时直方图很有价值。关于SKEWONLY这个旧参数它的逻辑是只对数据分布明显倾斜的列生成直方图看起来很美但实际计算开销大而且没有考虑SQL使用情况现在已经被AUTO取代了。老DBA迁移脚本时遇到SKEWONLY可以放心替换成SIZE AUTO。需要反复强调的坑直方图不是越多越好。每多一个直方图解析SQL时的额外CPU成本和多出的存储不算大但直方图太多会增加“错误使用”的概率。尤其是当数据分布变化剧烈时直方图可能让优化器反复摇摆执行计划更不稳定。所以我一直建议默认用AUTO手工只在排查到明确问题时介入。4. 统计信息过期与刷新机制4.1 “过期”是怎么定义的很多新人以为统计信息是“永不过期”的反正不更新就一直那样。其实Oracle里有个“stale”概念直译过来就是“陈旧”。数据库默认监控表的DML变化量不是简单看行数增减而是按修改比例算。某张表累积的增删改行数超过表总行数的10%默认阈值可通过参数调整这张表的统计信息就被标记为“陈旧”。想知道哪些表过期了最稳的查询是SELECT table_name, stale_stats, last_analyzed FROM dba_tab_statistics WHERE owner APP_USER AND stale_stats YES;DBA_TAB_STATISTICS里的LAST_ANALYZED字段会告诉你上次收集时间STALE_STATS是 YES/NO。这套机制背后依赖的是DBA_TAB_MODIFICATIONS也就是表修改监控记录。注意如果要查实时的行数变化需要先开启表的监控默认开启然后可以DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO把内存里的修改记录刷到字典表再查。10%这个阈值合理吗对大多数应用合理但分区表要小心。一个128个分区的月分区表如果只改了最近两个分区整体修改比例可能远低于10%自动收集就不会触发但出问题的恰恰是刚加载完数据的几个新分区。这种情况靠“过期度”检测是滞后的需要配合后面讲的分区增量特性。4.2 手动干预什么场景必须立刻收集自动任务再智能它也按窗口跑。总有一些场景必须立刻手工收集不能干等晚上窗口。最典型的几个场景批量加载/ETL完成之后。一张表下午导入了5000万行晚上自动窗口才跑中间这几个小时所有相关SQL全在裸奔。上线脚本里应该在导入完成后主动GATHER_TABLE_STATS。大表数据剧烈变化时。数据量一天翻一倍、十天翻十倍的业务常规周期收集根本跟不上需要针对核心大表定制更短的收集周期比如每小时一次再配合锁存避免自动任务重复跑。新索引刚创建完。索引统计不会“自动生成”于索引创建瞬间应该显式收集。有些版本里创建索引时可能带统计信息但依赖DBMS_STATS统一管理更稳妥。分区维护操作之后。TRUNCATE或DROP PARTITION会让全局统计与局部实际严重不符。分区维护后必须有意识地刷新相关表的统计信息。手工收集不是越多越好。统计信息收集本身是写操作而且消耗资源频繁全量收集会引发“统计信息抖动”——上次收集A方案这次收集B方案执行计划跟着左右横跳。合理的管理思路是区分核心表和普通表核心表设置固定策略、固定采样率、必要时锁存普通表交给自动任务。4.3 分区表增量统计与锁定统计信息分区表的统计信息收集一直是个难点。早期方案是对每个分区分别收集然后全局汇总。几百个分区的表收集一次全局统计可能要把所有分区扫一遍时间长影响面大。Oracle 11g引入了INCREMENTAL统计专门解决这个问题。开启之后收集分区表全局统计时不会全表重扫而是基于每个分区已经收集好的统计信息做增量汇总。代价小、速度快非常推荐。BEGIN DBMS_STATS.SET_TABLE_PREFS(APP_USER, BIG_PART_TABLE, INCREMENTAL, TRUE); END; /开了增量之后新加的分区只要自己收集过分区级统计全局统计就能自动汇总出来。这个特性对按月/按日分区的大表是“救命级”的提升强烈建议所有分区表都评估开启。锁定统计信息是另一项重要手段。有些核心小表数据变化极少但自动收集还是会隔几天扫一下纯属折腾。可以用BEGIN DBMS_STATS.LOCK_TABLE_STATS(APP_USER, DICT_SMALL_TABLE); END; /锁上之后Oracle不再对这个表执行自动收集统计信息维持锁定瞬间的状态。要释放就UNLOCK_TABLE_STATS。对于“我手动收集过且效果很好、不希望你自动改”的表这个功能极其有用。我就是这样把一批核心查询表的统计信息全锁了彻底告别“半夜自动收集完第二天执行计划飞了”的糟心事。5. 常见问题与排查技巧实录5.1 执行计划突然变差先查统计信息遇到生产SQL性能下降第一反应不要是加索引、加hint先查执行计划和统计信息。简单三步定位第一步确认SQL对应的表和索引统计信息是否新鲜SELECT table_name, stale_stats, last_analyzed, num_rows FROM dba_tab_statistics WHERE owner APP_USER AND table_name IN (TARGET_TABLE, TARGET_TABLE_2);第二步如果stale是YES看修改监控数据判断到底变了多少SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications WHERE table_name IN (TARGET_TABLE, TARGET_TABLE_2);第三步看当前执行计划和历史执行计划对比。历史计划可以查DBA_HIST_SQL_PLAN和DBA_HIST_SQLSTAT也可以借助AWR/ADDM报告。如果确认是统计信息变化引起的可以直接用SQL Plan Baseline固定正确执行计划然后重新收集统计信息。我见过太多同行跳过排查直接加hint硬拧执行计划的结果就是把问题从“优化器选错计划”变成“你永远不知道哪天数据量变化让hint计划变得比默认计划更差”。统计信息没问题再谈调优手段顺序不能乱。5.2 收集完统计信息后SQL反而变慢这个坑也常见。新统计信息让优化器认为某个索引选择度变高了于是从全表扫描切成了索引扫描结果因为回表太多、索引扫描反而更慢。这种情况常常和直方图过期、绑定变量窥探Bind Peeking叠加出现。绑定变量是Oracle 9i的特性SQL第一次硬解析时优化器会“偷看”绑定变量的实际值来决定执行计划。问题在于这个计划会被后续所有相同SQL复用哪怕绑定变量值变了。比如WHERE status :v_status第一次执行时:v_status 待支付优化器看到有直方图算出这个是高频值选择了全表扫描后面所有绑定变量沿着用这个计划。可如果实际业务里90%的时间传入的都是低频值那这个计划就是灾难。对策有几种。最简单的是把直方图策略调整得更保守比如对该列设置SIZE 1不做直方图让优化器回到平均估算激进一点可以改SQL、换成字面值再高级一点Oracle 11g开始已经成了自适应游标Adaptive Cursor Sharing会自动感知不同绑定变量值是否需要不同计划。所以升级到较新版本后这类问题已经缓解不少老版本才尤其需要手工介入。另外收集完统计信息后执行计划批量变化的连锁反应也是真事。一个大表统计更新可能导致十多个关联SQL执行计划同一天变掉。建议每次手工收集完大表统计后立刻盯一眼关键SQL的运行趋势AWR报告或自定义监控都行发现问题及时固定计划。5.3 统计信息查询速查表把日常最常用的查询整成一张速查表随时可以翻查询需求视图关键字段表统计信息DBA_TAB_STATISTICSNUM_ROWS, BLOCKS, LAST_ANALYZED, STALE_STATS列统计信息DBA_TAB_COL_STATISTICSNUM_DISTINCT, LOW_VALUE, HIGH_VALUE, HISTOGRAM直方图明细DBA_TAB_HISTOGRAMSENDPOINT_NUMBER, ENDPOINT_VALUE索引统计信息DBA_IND_STATISTICSBLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR系统统计信息DBA_TAB_SYS_STATS / AUX_STATS$CPSPEED, IOSEEKTIM, IOTFRSPEED表修改监控DBA_TAB_MODIFICATIONSINSERTS, UPDATES, DELETES统计信息收集日志DBA_OPTSTAT_OPERATIONSSTART_TIME, STATUS, OPERATION已锁定的统计信息DBA_TAB_STATLOCKED / DBA_IND_STATLOCKED对象名列表查询列直方图时特别提醒ENDPOINT_VALUE在字符串列时会显示成转换成数值后的编码别直接拿它当原值看。真需要看列值范围和分布先查DBA_TAB_COL_STATISTICS的LOW_VALUE/HIGH_VALUE再用ENDPOINT_VALUE手动解码或者用系统函数转换比较绕。DBA_OPTSTAT_OPERATIONS这条很好用。如果你怀疑凌晨自动收集出了问题回去查这张表能看到当时收集了什么、花了多久、有没有报错。很多“为什么状态异常”的悬案在这里都能找到答案。6. 这些年踩坑后留下的几条管理习惯核心经验就一条统计信息管理要当成一个持续运营的工作而不是一次性的手工操作。第一大表和小表分开管理。小表全量收集、低频率刷新大表用增量统计、控制采样率、错峰收集。一张策略表打天下的想法不现实。第二上线流程里必须带统计收集这一步。数据迁移、新表上线、批量导入之后别等自动任务主动收集一次并且观察24小时内的执行计划变化。第三核心SQL的执行计划要管到“计划基线”级别。统计信息抖动是难免的但影响核心业务时必须有兜底。我个人的做法是重要查询上线前提前生成SQL Plan Baseline固定计划统计信息照常收集但执行计划不随便漂移。第四监控比优化重要。定期跑一下第5节的速查表SQL看哪些表STALE、哪些表LAST_ANALYZED异常、哪些表统计被锁了。统计信息管理的本质不是某一秒做得多完美而是长期保持“数据库对自身数据的认知”不落伍。这套做下来绝大多数执行计划的妖孽问题都会在源头被掐掉。