★ FEATURED ARTICLE 干数据库运维这行久了有个问题被问到的频率特别高SQL明明没改为什么突然变慢了大多数时候答案都藏在一个不太起眼的东西里——执行计划。执行计划是Oracle优化器给SQL选的一条物理执行路径它决定了表怎么扫、索引怎么走、表之间按什么顺序怎么连。这篇文章就从Oracle执行计划分析入手结合两个我实际处理过的生产案例把读计划、查根因、做固化的完整链路讲清楚适合每天都跟慢SQL打交道的DBA、运维和开发人员参考。1. 执行计划是怎么被画出来的从语法树到物理执行路径1.1 一条SQL进入数据库后经历了什么很多人把执行计划理解成SQL的翻译结果其实不太准确。更贴切的说法是它是优化器在成本模型下算出来的一条物理执行路线。一条SQL进来Oracle大致要经历三个阶段解析、优化、执行。解析阶段做的是语法检查和语义检查通过之后会在库缓存里寻找相同文本、相同环境的游标。如果找不到就要做硬解析生成一棵解析树。这一步本身很消耗CPU和latch资源所以生产系统都强调游标共享鼓励使用绑定变量。优化阶段才是核心。优化器先做逻辑改写比如谓词推入predicate pushing、视图合并view merging、子查询去嵌套subquery unnesting接着做物理优化评估也就是CBO的核心逻辑——它对每个可能的访问路径全表扫描、索引范围扫描、索引唯一扫描等、连接顺序、连接方法嵌套循环、哈希连接、排序合并分别估算代价最后挑一个代价最小的组合作为执行计划。执行阶段不用多说优化器把选中的计划交给执行引擎按操作符一步步取数、连接、过滤。这一步就会真实消耗CPU、内存、IO等待事件也发生在这个阶段。理解了这三步你就能明白代码没动过、SQL也没改过、但性能变了这件事为什么会发生——改变的是优化器的输入数据。输入一变化它重新算出来的所谓最优计划就可能完全走样。1.2 执行计划里的关键要素字段、操作符和代价用DBMS_XPLAN或AWR拿出来的执行计划第一眼看上去是一棵带缩进的树。里面有几个字段必须看明白我整理了一张常用对照表字段含义判断要点ID操作符编号执行顺序不按ID大小而是按缩进深度Operation操作类型TABLE ACCESS FULL、INDEX RANGE SCAN、NESTED LOOPS等Name操作对象表名或索引名必须看清楚扫的是哪张表、哪个索引Rows优化器估算返回行数和实际行数对比是诊断的关键Cost(%CPU)代价成本相对值不是绝对时间Time估算执行时间同样是估算别当真Predicate Information谓词信息区分access和filter至关重要访问谓词access和过滤谓词filter的区别很多人会忽略。access表示通过索引定位时直接限定的范围比如INDEX RANGE SCAN的access predicate是索引列上的等值或范围条件filter则是在操作符内部逐行过滤的条件。如果本该走access的谓词变成了filter通常说明索引设计有问题或者优化器没法把谓词推到索引扫描中去。另外要特别留意Rows这一列。它写的是优化器的预估行数不是实际行数。如果预估出来后你和实际执行结果对不上那这个计划从根上就是错的后面所有连接的代价估算都会跟着歪。1.3 怎么正确读出执行顺序从最内层往外读读执行计划有个基本法则从最内层、缩进最深的操作符开始逐层向外同一缩进层级的节点从上往下执行。也就是说不能简单地按ID从小到大读。举个例子一个典型计划的缩进结构可能是这样第5行是索引唯一扫描第4行是回表第6行是另一张小表的全表扫描第3行是这两者做嵌套循环第2行是排序第1行是最终输出实际执行时先执行离根最远的叶子节点数据一层层向上返回根节点是最后输出的。很多人第一次看计划时从上往下读结果把顺序完全读反了这是新手最常见的问题。还有一点必须强调EXPLAIN PLAN FOR拿到的计划只是预测它不会真的执行SQL所以拿不到实际行数、实际耗时这些信息。想拿到真实执行统计要用SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, 0, ALLSTATS LAST));这样可以对比每一行的估算行数E-Rows和实际行数A-Rows还能看到实际耗时A-Time、物理读次数。这个习惯比单纯看计划文本有效太多后面案例里我会反复用到。2. 第一个生产案例一条对账SQL从3秒退化到20分钟2.1 故障表象与第一轮排查这个案例发生在一个电商类的数据分析平台。业务方报障说每日对账报表跑不出来了之前十几秒就出结果现在等了20多分钟还在转。这张报表挂在财务的晨间流程里而且只能串行跑越迟越影响后面一整套流程。我们接手后的第一步不是直接看SQL而是先确认数据库层面有没有异常。查了v$session发现不是锁等待没有日志写入问题也没有明显的资源争用单纯就是一条SQL在长时间执行。于是从v$session里抓到SQL_ID再点开AWR看它的执行历史。AWR的时间线非常直观过去7天这条SQL平均单次执行3秒左右从某天凌晨1点开始单次执行时间跳到15分钟以上。同时注意到那天凌晨正好有一个定时任务在跑内容是历史分区清理和表迁移。几乎所有数据变更的窗口都指向同一个时间点。到这里问题范围已经缩得很小了。2.2 用执行计划锁定根因我当时做了一件很关键的事不只拿当前执行计划还把历史执行计划也调出来。当前计划用DISPLAY_CURSOR历史计划从AWR里取。一对问题立刻现形改动前对账主表走某个二级索引的INDEX RANGE SCAN然后回表再和两张小表做嵌套循环整体代价约200。改动后同样的SQL对账主表变成了TABLE ACCESS FULL然后三张表做HASH JOIN代价直接飙到3万以上。接下来的问题是为什么原来好好的索引突然不走了得看两个点——索引还在不在统计信息正不正常。查DBA_INDEXES索引存在但查DBA_TAB_STATISTICS时发现问题主表的统计信息是空的LAST_ANALYZED为空NUM_ROWS显示为0。原因链条一下就通了凌晨的任务在迁移分区时用了TRUNCATE配合直接路径插入表被清空重建后自动统计信息任务还没来得及跑优化器拿到的是这张表0行的元数据。既然表是空的它当然觉得全表扫描最划算——一张空表扫起来能有多少代价可实际上表里有1000多万行于是优化器做出了一个灾难性的计划。这里要给个提醒分区清理脚本后面一定要带手动收集统计信息的步骤不能依赖夜间自动任务。很多生产环境的维护窗口会跳过自动统计或者表本身被排除在自动任务范围之外脚本里不主动收就容易踩这个坑。2.3 修复动作与效果验证修复动作分两步。第一步收集统计信息EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname 某用户, tabname 对账主表, method_opt FOR ALL COLUMNS SIZE AUTO, cascade true );收集完成后再看一次计划索引路径已经回来了代价回落到200附近。第二步重跑一次对账SQL耗时回到3秒左右。还有一个动作很重要把这条SQL的SQL_ID加入监控告警后续只要plan_hash_value发生变化立刻通知DBA核对。这类核心报表SQL一旦计划漂移业务影响是直接的事后补救永远不如事前发现。2.4 这个案例沉淀的三层判断框架通过这个案例我在内部复盘时总结了一个执行计划诊断的三层判断框架第一层看现象时长、等待事件、资源消耗先确定是不是SQL本身的问题排除锁、IO、归档等外围因素。第二层看计划当前计划对比历史计划重点关注plan_hash_value是否变化、访问路径是否变化、估算行数与实际行数是否严重偏离。第三层看数据环境统计信息是否新鲜、表数据量是否发生数量级变化、是否新增或删除了索引、分区结构是否有变动。这套框架后来被我用在好几次同类故障上命中率很高。别急着翻代码、改SQL先把这三层过一遍大多数执行计划问题的根因都在里面。3. 执行计划为什么会变卦统计信息、绑定变量与优化器的盲区3.1 统计信息过期是计划漂移的最大推手上一个案例本质上是统计信息缺失。但生产环境里更常见的是统计信息过期——表一直在写入数据量、数据分布都在变而DBA_TAB_STATISTICS里的LAST_ANALYZED还停在几个月前。判断统计信息是否过期可以查DBA_TAB_STATISTICS的STALE_STATS列。Oracle在启用自动统计任务时会持续跟踪表的数据变化量标记是否达到重新收集的阈值。你自己也可以对比表当前的实际行数和统计信息里的NUM_ROWS偏差超过5%到10%就该手动触发重新收集了。除了行数列级别的数据分布变化同样致命。举个例子某张订单表的订单状态列平时99%都是已完成只有1%是待处理。优化器基于旧的直方图猜测待处理只有几千行于是对这条谓词选择了索引扫描。等某天积压到几十万行时它可能还是走索引结果几十万次回表性能直接崩掉。这种情况单靠重新收集统计信息还不够还得确认直方图覆盖了这个列的倾斜分布收集时用FOR ALL COLUMNS SIZE AUTO或对关键列单独指定SIZE SKEWONLY。3.2 绑定变量窥视与游标共享的两难生产系统的SQL一般都会用绑定变量。绑定变量能提升游标共享、降低硬解析这是好事但它也给优化器带来了一个盲区——优化器在生成计划时并不知道这个绑定变量具体是什么值。早期版本的做法是窥视第一次执行时的值bind peeking拿这个值去估算过滤性生成一个计划然后共享给后续所有执行。问题来了第一次执行时传入的恰好是个小选择性的值计划选了INDEX RANGE SCAN第二次传入大数据量的值计划还是那个计划不愿意重新生成于是索引路径在数据量巨大的情况下照走不误性能雪崩。从11g开始Oracle引入了自适应游标共享Adaptive Cursor Sharing核心思路是同一个SQL文本根据不同绑定变量值和执行行为特征可以存在多个子游标每个子游标对应不同的执行计划并在执行后根据实际表现决定后续是否复用。DBA可以在V$SQL里看到IS_BIND_SENSITIVE、IS_BIND_AWARE、IS_SHAREABLE这些标记列。这给我们的实操提示是遇到同一个SQL_ID下出现多个plan_hash_value时别急着下结论说优化器疯了先检查是不是自适应游标共享在起作用对比每个计划的实际资源消耗把差计划用SPM禁止掉而不是粗暴地把绑定变量拆掉。3.3 基数估算错误会一路放大优化器的一切判断都建立在基数估算上——它预测某一步会返回多少行。一旦基数预估错了后面所有连接顺序、连接方法、访问路径都会跟着错而且错误会指数级放大。举个例子优化器估算某张表过滤后只剩1行实际有100万行。它可能因此选择嵌套循环用一张小表做驱动内层反复扫描大表100万次。从纸面上看这个计划代价极低实际执行却是灾难。为什么优化器会把基数估得这么离谱最常见的原因是列之间的相关性。比如一张表同时有城市和省份两个列优化器假设两个条件是独立的分别估算过滤率后相乘。实际上城市列的值基本由省份决定两个条件高度相关乘积后的结果会严重低估实际行数。这是典型的关联谓词被当成独立条件处理时需要用扩展统计信息Extended Statistics声明列组相关性或者干脆改写SQL把语义表达得更直接。4. 第二个生产案例三表关联被优化器带偏的完整复盘4.1 SQL逻辑与业务预期第二个案例来自某公司的仓储管理系统是一张某库存查询SQL。SQL本身不复杂三张表关联库存表大表几千万行仓库维度表小表几百行商品维度表中表几十万行业务逻辑是按仓库加商品编码查实时库存。期望的执行顺序是从仓库维度表开始先过滤掉大部分仓库然后拿仓库编码去库存表上走索引定位最后关联商品表补字段。这样每步中间结果集都很小整体就是毫秒到秒级。4.2 执行计划的异常点驱动表完全选反了实际拿到的执行计划是这样的驱动表变成了商品维度表而且做的是全表扫描优化器估算输出50万行商品表结果再和库存表做HASH JOIN库存表同样全表扫描最后才和仓库维度表做嵌套循环从纸面看这个计划前面的基数预估就有问题SQL里明明是几百个商品编码的IN列表优化器却把过滤后的输出行数估成了几十万行。基数一大它认为从商品维度表开始后面怎么走都合理于是选了这条路。实际执行时前面一步就吐出大量行后面再怎么优化也救不回来。这里暴露了一个常见现象IN列表的基数估算Oracle往往比实际要保守得多。尤其是IN列表里包含大量值时优化器对去重后的有效值个数预估不准导致驱动表的选择完全失控。4.3 用10053追踪优化器的决策过程为了确认优化器为什么做这个选择我开启了10053事件把这条SQL重新硬解析一次把优化器的决策日志拉出来看ALTER SESSION SET EVENTS 10053 trace name context forever, level 1;从trace里能看到两件事。一是各单表访问路径的代价评估二是所有连接顺序和连接方法的排列组合结果。排列组合部分会清晰列出商品表做驱动表和仓库维度表做驱动表两条路径各自的估算代价。因为商品维度表全表扫描的代价被算得很低索引路径的代价反而被高估最终优化器选了错的那条。10053的trace文件很长并不适合每次都读全但要想知道优化器为什么这么选它比任何工具都直接。我一般只看里面Join order和Cost相关行不纠结细节。4.4 干预手段改写SQL与Hint验证拿到结论后我实际做了两轮干预。第一轮用SQL改写把IN列表改成临时表的EXISTS关联并用WITH子句固定连接顺序让中间结果集先压缩。改完后计划明显好转执行时间从几十秒降到2秒以内。第二轮为了形成兜底同时测试Hint方案。加上/* LEADING(仓库维度表 库存表 商品维度表) USE_NL(库存表) */效果和改写后的计划一致驱动表回到了仓库维度表嵌套循环用的索引路径也恢复了。但我不建议把Hint直接写死在应用代码里。Hint写进SQL后后续代码迭代很容易被误删不同环境索引名不一致还会直接报错。更稳的做法是把固定计划放在数据库侧用SQL Plan Management固化。最终采取的组合方案是应用端使用改写后的SQL数据库侧把这个执行计划作为SPM基线。这样即使以后统计信息变化、优化器想换计划也只能在基线范围内选不会再跳回原来的坏计划。5. 把执行计划钉住的工程化手段Hint、Profile与SPM的取舍5.1 Hint不是救命稻草用错更麻烦很多开发遇到SQL慢第一反应是加Hint。但从故障处理经验看Hint应该放在最后面而不是第一步。原因有三。第一Hint会把执行计划冻死后续数据量变化、索引变化、统计信息刷新它都不会自动适应属于一次性的补丁。第二Hint的优先级很高一旦写错比如指向一个不存在的索引优化器会忽略该Hint有时干脆导致SQL解析失败。第三多个Hint之间存在交互LEADING指定了驱动表USE_NL又指定了另一组连接方法冲突时计划会变得莫名其妙。我的原则是先做统计信息、SQL改写、索引设计这三件事解决不了再考虑Hint。用Hint时尽量用意图式提示比如USE_NL、USE_HASH少用路径式提示如INDEX(t idx1)后者对索引名高度敏感换个环境就失效。5.2 SQL Profile与SPM的功能边界SQL Profile本质上是给优化器一个附加输入通过修正基数估算的调整系数影响计划选择它不会强制指定某个固定计划。统计数据变了Profile仍然可能失效。SPMSQL Plan Management更像一个计划白名单固定的是plan_hash_value级别的执行计划。优化器只在被接受的计划列表里挑选不会跑到列表之外。从生产实践看SPM是更可靠的治理手段。SPM的标准操作流程大致是这样从游标缓存中确认当前最优计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID, NULL, ALL));把当前计划加载为基线EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id SQL_ID, plan_hash_value 1234567890 );验证基线状态SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines;这里有个细节加载基线之后不等于立刻生效。如果启用了自动演进优化器会尝试新计划确认更优后自动接受如果关闭自动演进新计划会停留在不可接受状态需要人工验收。对核心SQL我建议关闭自动演进人工把关每一版计划变更。5.3 把执行计划治理前置到发布流程比较理想的做法是把执行计划检查纳入发布流程。每次新SQL上线先在预发布环境抓一次执行计划人工确认访问路径和基数估算是否合理。上线后持续观察AWR变化重点关注plan_hash_value是否发生切换。另外很推荐给核心SQL建一个计划基线登记表字段包括SQL_ID、业务描述、首次发布时间、已知可用的plan_hash_value集合。这样一旦线上出现性能退化第一件事就是对比当前plan_hash_value和登记表里的可用集合十分钟就能定位是不是计划漂移。这张表用普通业务表维护就行数据量不大但价值极高。6. 日常会诊习惯拿到一条慢SQL后我按什么顺序做事6.1 一张排查清单处理过的慢SQL多了我总结了一个固定的检查顺序算不上什么方法论就是踩坑踩出来的确认身份拿到SQL_ID、plan_hash_value、执行平均/最大耗时、执行频次。看等待事件它在等什么CPU、磁盘IO、网络还是锁。等待事件决定方向。看当前计划用DISPLAY_CURSOR拿真实执行统计对比A-Rows和E-Rows。找历史计划当前计划不对劲就去DBA_HIST_SQL_PLAN对比看是计划被换掉了还是本来就这样。查统计信息健康度表级STALE_STATS标记、列级直方图、分区数量。查绑定变量同一个SQL_ID下多个子游标确认是否自适应游标共享比较各子计划的资源消耗。最后才动手根据根因选手段能收集统计信息解决就不改写能改写就不加Hint能加Hint就不做SPM。这套顺序看起来很简单但真正出了问题很多人会跳过第2步直接去改SQL。改完可能确实快了一点但根因还在下一次换个数据量照样崩。6.2 几个随手就能跑的取数SQL查当前缓存里的执行统计SELECT sql_id, plan_hash_value, executions, elapsed_time / 1000000 AS seconds, buffer_gets, disk_reads, cpu_time / 1000000 AS cpu_seconds FROM v$sql WHERE sql_text LIKE %你的表名% ORDER BY elapsed_time DESC;查某条SQL当前缓存中的真实执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID, 0, ALLSTATS LAST));查历史执行计划来自AWRSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID, NULL, NULL, ALL));查统计信息新鲜度SELECT table_name, num_rows, last_analyzed, stale_stats FROM dba_tab_statistics WHERE owner 某用户 AND table_name 某表;这些SQL几乎每天都在用不管哪个环境都通用复制粘贴改个表名就能跑。建议存成一个工具脚本集放到团队共享目录里。6.3 规范化的执行计划传递方式团队协作中我强烈建议不要截图发执行计划。最规范的做法是发SQL_ID加plan_hash_value配合环境信息让同事自己用DBMS_XPLAN拉取。截图会丢失谓词信息和真实统计而且不同方式拿到的计划可能完全不一样。稍微熟练一点的同学可以把关键信息打成摘要例如SQL_ID: abc123 当前plan_hash_value: 1234567890 历史plan_hash_value: 0987654321 统计信息last_analyzed: 2024-01-01 表当前行数约: 1200万这样的信息有经验的DBA一看就知道问题大概率在哪也方便归档到前面说的计划基线登记表里。最后说一点个人体会。执行计划分析这件事技术上并不难难的是在故障压力下保持分析的顺序感。很多人一上来就改SQL、加索引结果往往是按下葫芦浮起瓢。我现在的习惯是不管多急先花几分钟把当前计划、历史计划、统计信息三样东西拿到手再决定下不下手。这套流程帮我少熬了很多夜也希望对你有点用。另外每次处理完一次故障记得把结论沉淀成一页纸的诊断记录下次遇到同类问题翻一下就能少走弯路。
阅读完成 · 觉得有帮助？
