先说明一下DBMS_XPLAN在Oracle里的定位很简单它就是一个获取执行计划的工具包属于Oracle自带的、不收钱的、几乎所有版本都能用的基础工具。但就是这么个基础工具很多朋友并没有真正用透大多数时候只是EXPLAIN PLAN FOR SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) 一套组合拳打完就撤遇到复杂SQL性能问题还是两眼一抹黑。这篇就老老实实把这个包的所有常用姿势、参数细节和踩坑点盘一遍给对执行计划还处于一知半解状态、或者在性能调优路上经常卡壳的朋友一个完整的操作手册。我自己做Oracle运维这些年最大的感受是SQL慢不慢加索引只是最后一步棋真正决定怎么走棋的是你能不能完整看懂执行计划。而看懂执行计划的第一步就是先把DBMS_XPLAN这个工具玩明白。所以这篇文章适合三类人刚接触Oracle优化、想系统学执行计划的开发同学在生产环境处理过几次慢SQL但全靠猜的运维同行以及想知道除了DISPLAY之外还有哪些高级用法的老手。1. 先把执行计划这件事想清楚1.1 为什么很多SQL问题都卡在执行计划上写SQL的人脑子里想的是一套逻辑我要从订单表查出所有金额大于1000的订单然后关联用户表取出姓名。但Oracle数据库收到这条SQL之后并不会老老实实按你写SQL的方式去执行它会先让优化器把这套逻辑翻译成一个具体的物理执行步骤比如“先全表扫订单表再对每一行去用户表根据主键回表查姓名”或者“先索引扫描定位到金额大于1000的订单再嵌套循环关联用户表”。这套物理步骤就是执行计划。问题就出在这里同样一条SQL翻译成不同的物理执行步骤实际跑出来的时间可能差几十倍甚至几百倍。全表扫描可能扫了500万行才筛出100条而索引扫描可能只扫了200个索引条目就拿到结果了。优化器选计划主要靠的是统计信息给出的估算如果统计信息不准确或者数据分布特殊它完全可能给你选中一个灾难级别的执行计划。所以排查任何SQL性能问题第一件事不是加索引、不是改SQL而是先把这条SQL实际用的执行计划拿出来看。这是整个性能调优的地基地基没打好后面全是白忙。1.2 最简单的获取方式先知道有这几种工具Oracle生态里能看执行计划的方式其实不少AUTOTRACE、EXPLAIN PLAN、V$SQL_PLAN、DBMS_XPLAN还有可视化工具自带的执行计划展示。它们的共同底层逻辑都是读取优化器生成的计划但各有各的局限性。比如AUTOTRACE需要你在SQL*Plus环境里重新执行一次SQL如果那条SQL跑了二十分钟你再跑一次成本就太高了EXPLAIN PLAN FOR不会真的执行SQL它只是让优化器基于统计信息估算一份计划所以拿到的计划有时候和真实执行情况对不上。DBMS_XPLAN这个包最大的价值就是它把各种来源的执行计划信息统一封装成了好读的表格格式而且几乎可以在任何Oracle客户端环境里调用又能直接读取共享池里已经跑过的真实执行计划。换句话说你不需要等SQL再跑一遍就能把刚才那条慢SQL的执行计划从内存里拽出来看。这个能力在生产环境排障时有多重要谁用谁知道。2. DBMS_XPLAN三种最常用的打开方式2.1 DISPLAY方法适合还没有实际执行的SQLEXPLAIN PLAN FOR SELECT * FROM t_order WHERE order_id 1001; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());这是最入门级的用法。EXPLAIN PLAN FOR把优化器估算出来的执行计划写入默认的PLAN_TABLE然后再用DISPLAY把它读出来。这里有一个关键认知必须记住EXPLAIN PLAN FOR并不真正执行SQL所以它展示的只是优化器“认为”这个SQL该怎么跑。如果统计信息不准或者有绑定变量窥探问题那份计划很可能不是SQL真正跑起来的样子。所以在我的使用习惯里DISPLAY主要用于开发阶段验证SQL写法、查看新增索引是否生效这类场景。比如你刚建了一个组合索引想确认优化器认不认识它用EXPLAIN PLAN FOR快速看一眼是最高效的。而在生产环境排查真实慢SQL时我几乎不用这个方法因为它的“预测”属性太强了容易误导人。2.2 DISPLAY_CURSOR方法直接看真实执行过的SQL计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());不加参数的时候它返回当前会话刚刚执行过的最后一条SQL的执行计划。这个行为在PL/SQL Developer、Navicat这些图形工具里特别好使你选中一段SQL执行完之后马上跑这句就能看到它真实使用的执行计划以及真实资源消耗。在生产环境里我更常用的是带上SQL_ID和CHILD_NUMBER参数的方式SELECT sql_id, child_number, sql_text FROM v$SQL WHERE sql_text LIKE %t_order%; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(ga5k3z7p9w1ab, 0, ALLSTATS LAST));当应用里报了一条SQL特别慢我们通常先通过V$SQL或V$SESSION锁定它的SQL_ID然后直接用DISPLAY_CURSOR把它的真实执行计划取出来。这个方法的威力在于它读的是共享池里SQL的实际执行计划包含每一行的真实返回行数A-Rows、实际执行次数A-Time这些是EXPLAIN PLAN永远拿不到的东西。SQL只要还在内存里哪怕它现在没在跑你也能把它的计划和执行统计翻出来。2.3 DISPLAY_AWR方法SQL已经被挤出内存之后的补救方案有一种很扎心的场景半夜有一条SQL跑得很慢等你早上上班想把它的执行计划拿出来看看它早就被挤出共享池了V$SQL里查无此SQL。这时候DISPLAY_CURSOR就无能为力了但只要AWR自动负载仓库保留了这个SQL的统计信息和执行计划快照DISPLAY_AWR就能帮上忙SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(ga5k3z7p9w1ab));它从一个高峰期抓到另一个高峰期的时间窗口里找到这条SQL的执行计划数据并展示出来。注意一个细节DISPLAY_AWR得到的计划是AWR快照采集时刻优化器生成的计划它代表的是那时候优化器的选择和SQL实际跑起来可能还是有细微差别。但作为事后分析这已经是唯一可用的手段了。而且AWR的计划快照本身就是定期从V$SQL_PLAN里抓来的对一些高频SQL来说记录保存的概率还挺高的。如果你在DISPLAY_AWR里查不到计划大概率是AWR没抓到这条SQL的快照或者SQL文本SQL_ID对不上。2.4 三种方法怎么选我总结了一张表方法适用场景关键注意点DISPLAY开发阶段验证SQL写法、测试索引是否生效不真正执行SQL计划是估算的未必等于真实计划DISPLAY_CURSOR生产环境排查正在跑或刚跑完的慢SQL需要SQL还在共享池中建议带ALLSTATS LAST参数看真实统计DISPLAY_AWR事后追溯已经不在内存中的历史SQL依赖AWR快照是否保留该SQL信息是事后分析手段一句话总结能看真实的绝不只看估算的能看当前的绝不只看历史的。三者配合使用覆盖SQL分析的完整时间线。3. FORMAT参数才是真正拉开差距的地方3.1 从BASIC到ALL每一档加出来什么内容很多朋友写DBMS_XPLAN从来只写DISPLAY_CURSOR()不带参这样默认走的是TYPICAL格式。TYPICAL格式已经能显示最常见的列比如操作类型、对象名、行数估算、字节数、代价Cost等。但真正要定位复杂问题时TYPICAL远远不够就需要通过FORMAT参数来做精细化控制。FORMAT支持BASIC、TYPICAL、ALL三个预设级别还支持在这些级别基础上追加或排除选项。BASIC只显示最基础的操作和对象信息连Cost和行数都没有平时基本用不上。ALL则会额外显示出投影列、别名、过滤谓词和访问谓词等信息量大了很多分析复杂SQL时很有价值。但只看ALL还不够真正让我愿意强烈推荐的是这样一组组合写法SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST));ALLSTATS这个选项会在计划输出里增加几列关键数据A-Rows实际返回行数、A-Time实际执行时间、Buffers逻辑读、Reads物理读。这几列最大的价值是能直接和优化器估算的E-Rows计划里的Rows做对比一旦发现估算和实际差了一个数量级你基本就能锁定是统计信息问题或者基数估算偏差的锅这是SQL调优里最核心的判断依据。再来单独说说LAST这个关键字。ALLSTATS LAST表示只看最后一次执行的统计信息而不是把同一个SQL多次执行的统计汇总平均。为什么要LAST因为一个游标可以被反复执行多次平均统计会把不同执行环境下的表现抹平反而掩盖问题。LAST能精准反映最新一次执行的情况这对排查“为什么这次跑得慢”非常关键。3.2 高级选项PEEKED_BINDS、OUTLINE、ADDRESS除了ALLSTATS LAST还有几个选项在特定场景下能救命。第一个是PEEKED_BINDS它会在计划输出里显示优化器在硬解析时窥探到的绑定变量值。这个对排查绑定变量窥探导致的执行计划不稳定特别有用。举个例子一条SQL第一次执行时传入的绑定变量值正好命中了极少部分数据优化器据此生成了一个嵌套循环计划后来业务传入的值实际要返回几十万行嵌套循环就变成灾难了。如果你在计划里看不到Peeked Binds信息很难解释为什么计划会这么糟糕。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, TYPICAL PEEKED_BINDS));第二个是OUTLINE。这个选项会显示执行计划对应的outline data也就是Oracle为了固定计划而记录的一组内部提示。如果你想用SQL Profile或者SPM来稳定某个执行计划OUTLINE信息几乎就是标准配置。实际操作中我会先跑一个ALLSTATS LAST拿到优化后的计划再跑一次带OUTLINE的格式把outline提取出来为后续SQL Plan Management基线绑定做准备。第三个是ADDRESS它会在计划输出里显示每个操作的内部哈希地址主要用于追踪V$SQL_PLAN里的具体记录辅助定位子游标之间的计划差异。这个选项小众但排查游标变异问题时很有用。3.3 组合选项的正确姿势FORMAT参数可以同时指定多个选项它们用空格分隔就行。比如我想看真实统计加绑定变量附加信息可以这样写SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST PEEKED_BINDS ADDRESS));这里有个小经验ALLSTATS本身已经隐含了IOSTATS和MEMSTATS两套统计所以不需要再单独加IOSTATS。而如果还想看分区裁剪情况可以追加PARTITION。做并行SQL分析就追加PARALLEL。总之先明确你想解决什么问题再决定要哪些选项别一股脑全堆上去。因为选项越多输出越杂反而容易忽略真正关键的信息。4. 用真实场景串一遍完整排查流程4.1 现场一条报表SQL从40秒优化到0.3秒某天同事反馈一条统计SQL在生产环境跑了40多秒应用侧已经超时。我先从V$SESSION抓到该会话正在执行的SQL_ID因为SQL还在跑用DISPLAY_CURSOR拿到的就是实时计划。命令执行完计划里最显眼的一行是TABLE ACCESS FULL T_ORDER (E-Rows1000, A-Rows500000)E-Rows优化器估算1000行和A-Rows实际返回50万行差了整整500倍。这就是典型的统计信息过期表中数据量已经大幅增长但字典里的统计信息还停留在很久之前导致优化器错误地认为全表扫描的成本很低实际上却扫出了一个巨大的中间结果。这个案例里最快的修复不是加索引而是先刷新统计信息再重新看计划EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, T_ORDER);统计信息刷新后同样的SQL再次执行执行计划从全表扫描变成了索引范围扫描加嵌套循环SQL总耗时从40秒降到了0.3秒。整个过程我连一行SQL都没改就是靠ALLSTATS LAST里的E-Rows/A-Rows对比快速锁定问题再刷新统计信息解决。4.2 另一个场景绑定变量窥探让执行计划“劫持”了业务还有一个案例也很有代表性。某段代码用了绑定变量SQL在第一次硬解析时传入的绑定变量值是一个极小范围的客户编号优化器窥探到这个值后生成了嵌套循环的计划。但后续业务传入的客户编号对应数据量很大嵌套循环计划每次执行都要循环几十万次SQL直接卡死。我定位问题时就是在计划输出里看到了Peeked Binds区域显示的绑定变量值确认是绑定变量窥探导致的计划选择偏差。这种问题的处理思路不一定是禁掉绑定变量窥探那会让系统里大量SQL重新硬解析反而引发更大的性能风暴。更稳妥的做法是用HINT或者改写SQL引导优化器选择一个更通用的计划再做SQL Profile固定计划。如果确实希望完全消除对绑定变量值的依赖还可以在11g以后通过自适应游标共享Adaptive Cursor Sharing机制让优化器根据实际返回行数动态调整计划但前提是统计信息和直方图得建得足够准确。4.3 还没执行过的SQL怎么快速验证方案开发阶段或者间隙窗口内验证方案时我会先用EXPLAIN PLAN FOR DISPLAY看一下优化器给的预估计划。比如新加了一个复合索引想确认某条SQL会不会走这个索引直接EXPLAIN PLAN FOR SELECT * FROM t_order WHERE status1 AND create_date SYSDATE-7; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());如果计划走了INDEX SKIP SCAN或者INDEX RANGE SCAN说明索引策略生效。如果还是全表扫描我就要先检查这个索引是否建对比如列顺序是否考虑到等值条件优先再看看要不要在SQL里用INDEX HINT。但这个阶段看到的全是估算等SQL真正在某次执行里挂掉了还是要回到DISPLAY_CURSOR看真实情况。5. 常见问题和排查技巧实录5.1 DISPLAY_CURSOR返回空结果怎么回事这是所有DBMS_XPLAN初学者最容易撞到的问题。DISPLAY_CURSOR返回空通常有几种原因SQL已经不在共享池里了传入的SQL_ID或CHILD_NUMBER不对当前用户没有权限访问底层的V$视图。排查顺序是先确认SQL_ID是否正确V$SQL和V$SQLAREA都有再看SQL有没有被age out如果确实被挤出去了就改用DISPLAY_AWR从AWR快照里找历史计划。有一点值得注意如果SQL只是很小的一段文本且执行频率极低被age out的概率很高建议今后遇到关键SQL第一时间就抓计划别等慢SQL彻底没影了才想起来分析。5.2 EXPLAIN PLAN的计划和DISPLAY_CURSOR对不上这种情况屡见不鲜。EXPLAIN PLAN FOR生成计划时只是基于当前统计信息做静态估算而DISPLAY_CURSOR拿到的是实际执行的计划。只要统计信息不新、SQL里有绑定变量、或者系统启用了执行计划管理SPM两者就很可能不一样。发生对不上的时候永远以DISPLAY_CURSOR真实执行计划为准EXPLAIN PLAN的结果只建议作为策略验证参考。如果一个生产环境里这两份计划经常对不上第一反应该是去检查统计信息收集作业有没有正常完成其次看看SPM基线是否干预了计划选择。5.3 计划里全是全表扫描但索引明明存在这是最常见的优化误区。建了索引不代表优化器就该一定走索引。当查询要返回的结果占全表比例过高时优化器算来算去觉得索引回表反而更贵选择全表扫描反而是聪明决策。但如果你确认返回行数很少却还是全表扫描那重点排查两件事第一WHERE条件列上有没有函数包裹或隐式类型转换导致索引失效第二统计信息里的表行数是不是严重偏小导致优化器低估了全表扫描的成本。另外还有一种情况是SQL文本里用了SELECT *回表代价太高优化器就算知道索引能定位行也评估出全表扫描更便宜。这时候可以试试覆盖索引组合索引包含查询需要的列来降低回表代价。5.4 每次执行计划都不一样怎么稳定如果同一个SQL在不同时间飘来飘去有时候快有时候慢多半是统计信息在持续变化或者绑定变量窥探导致不同子游标有不同的计划。稳定执行计划的方案优先级建议是这样先确保统计信息收集策略是稳定的频率合理其次给关键SQL使用OUTLINE或SQL Profile锁定计划最后在19c及以上版本可以考虑SPM演进机制让计划管理更自动化。你可以先用DBMS_XPLAN的OUTLINE选项拿到当前最优计划的outline然后用DBMS_SQLTUNE.CREATE_SQL_PROFILE把它固化下来这样就算统计信息变化计划也不会随便乱飘。5.5 从计划里反推出SQL改写方向最后分享一个我经常用的技巧拿到DISPLAY_CURSOR输出后我会先看三个地方。一是看驱动表排最上面、缩进最少的操作选得是否合理一般应该让小表或者过滤后行数最少的表做驱动二是看被驱动表的连接方式如果是NESTED LOOPS但被驱动表每行都要回表扫全表那基本就是连接列缺索引三是看Access Predicates和Filter PredicatesAccess表示能直接通过索引定位Filter表示只能把数据捞出来再过滤两者差距很大。如果Filter里面出现了本该能走索引定位的列那就是SQL写法或索引设计的问题顺着这个方向改写SQL往往立竿见影。6. 工具选型的一些个人体会DBMS_XPLAN在任何图形化客户端里其实都能用关键是不要过度依赖工具自带的“执行计划查看”按钮——那玩意的底层实现五花八门有的只是EXPLAIN PLAN有的是抓V$SQL_PLAN视图数据来源不一样显示的内容和准确性也有偏差。在PL/SQL Developer、Navicat、DBeaver这类工具里最可靠的方式就是直接把DBMS_XPLAN的SQL跑一遍自己看输出文本。这样不管是在哪个客户端环境分析口径都统一。另外有一个从11g开始就很好用的进步DBMS_XPLAN可以直接显示SQL Monitor报告前提是启用了SQL监控默认对消耗超过阈值或并行的SQL会开启SELECT * FROM TABLE(DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id ga5k3z7p9w1ab));这个报告和DBMS_XPLAN取数来源一样但呈现更丰富包括每一步的实际行数、消耗时间、内存用量。对于并行度高的重型SQL我把SQL Monitor报告当作DBMS_XPLAN的升级补充来用。团队里如果有新同事入职我通常给的建议就是两个练习找十条生产真实慢SQL用DBMS_XPLAN把执行计划抓出来尝试用ALLSTATS LAST分析A-Rows和E-Rows的差距再挑三条计划极端不合理的SQL结合SQL改写和索引策略把计划掰回正轨。这两件事做完对Oracle SQL优化的理解基本就入门了。也正因如此这篇文章从头到尾没有讲什么深奥的算法核心就是工具用法加实战判断但DBMS_XPLAN这个工具用熟了确实能让整个排查过程从“猜”变成“看”从“用时间换结果”变成“用结构判断定位问题”这也是我认为它最值得花时间去掌握的原因。
先说明一下DBMS_XPLAN在Oracle里的定位很简单它就是一个获取执行计划的工具包属于Oracle自带的、不收钱的、几乎所有版本都能用的基础工具。但就是这么个基础工具很多朋友并没有真正用透大多数时候只是EXPLAIN PLAN FOR SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()) 一套组合拳打完就撤遇到复杂SQL性能问题还是两眼一抹黑。这篇就老老实实把这个包的所有常用姿势、参数细节和踩坑点盘一遍给对执行计划还处于一知半解状态、或者在性能调优路上经常卡壳的朋友一个完整的操作手册。我自己做Oracle运维这些年最大的感受是SQL慢不慢加索引只是最后一步棋真正决定怎么走棋的是你能不能完整看懂执行计划。而看懂执行计划的第一步就是先把DBMS_XPLAN这个工具玩明白。所以这篇文章适合三类人刚接触Oracle优化、想系统学执行计划的开发同学在生产环境处理过几次慢SQL但全靠猜的运维同行以及想知道除了DISPLAY之外还有哪些高级用法的老手。1. 先把执行计划这件事想清楚1.1 为什么很多SQL问题都卡在执行计划上写SQL的人脑子里想的是一套逻辑我要从订单表查出所有金额大于1000的订单然后关联用户表取出姓名。但Oracle数据库收到这条SQL之后并不会老老实实按你写SQL的方式去执行它会先让优化器把这套逻辑翻译成一个具体的物理执行步骤比如“先全表扫订单表再对每一行去用户表根据主键回表查姓名”或者“先索引扫描定位到金额大于1000的订单再嵌套循环关联用户表”。这套物理步骤就是执行计划。问题就出在这里同样一条SQL翻译成不同的物理执行步骤实际跑出来的时间可能差几十倍甚至几百倍。全表扫描可能扫了500万行才筛出100条而索引扫描可能只扫了200个索引条目就拿到结果了。优化器选计划主要靠的是统计信息给出的估算如果统计信息不准确或者数据分布特殊它完全可能给你选中一个灾难级别的执行计划。所以排查任何SQL性能问题第一件事不是加索引、不是改SQL而是先把这条SQL实际用的执行计划拿出来看。这是整个性能调优的地基地基没打好后面全是白忙。1.2 最简单的获取方式先知道有这几种工具Oracle生态里能看执行计划的方式其实不少AUTOTRACE、EXPLAIN PLAN、V$SQL_PLAN、DBMS_XPLAN还有可视化工具自带的执行计划展示。它们的共同底层逻辑都是读取优化器生成的计划但各有各的局限性。比如AUTOTRACE需要你在SQL*Plus环境里重新执行一次SQL如果那条SQL跑了二十分钟你再跑一次成本就太高了EXPLAIN PLAN FOR不会真的执行SQL它只是让优化器基于统计信息估算一份计划所以拿到的计划有时候和真实执行情况对不上。DBMS_XPLAN这个包最大的价值就是它把各种来源的执行计划信息统一封装成了好读的表格格式而且几乎可以在任何Oracle客户端环境里调用又能直接读取共享池里已经跑过的真实执行计划。换句话说你不需要等SQL再跑一遍就能把刚才那条慢SQL的执行计划从内存里拽出来看。这个能力在生产环境排障时有多重要谁用谁知道。2. DBMS_XPLAN三种最常用的打开方式2.1 DISPLAY方法适合还没有实际执行的SQLEXPLAIN PLAN FOR SELECT * FROM t_order WHERE order_id 1001; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());这是最入门级的用法。EXPLAIN PLAN FOR把优化器估算出来的执行计划写入默认的PLAN_TABLE然后再用DISPLAY把它读出来。这里有一个关键认知必须记住EXPLAIN PLAN FOR并不真正执行SQL所以它展示的只是优化器“认为”这个SQL该怎么跑。如果统计信息不准或者有绑定变量窥探问题那份计划很可能不是SQL真正跑起来的样子。所以在我的使用习惯里DISPLAY主要用于开发阶段验证SQL写法、查看新增索引是否生效这类场景。比如你刚建了一个组合索引想确认优化器认不认识它用EXPLAIN PLAN FOR快速看一眼是最高效的。而在生产环境排查真实慢SQL时我几乎不用这个方法因为它的“预测”属性太强了容易误导人。2.2 DISPLAY_CURSOR方法直接看真实执行过的SQL计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());不加参数的时候它返回当前会话刚刚执行过的最后一条SQL的执行计划。这个行为在PL/SQL Developer、Navicat这些图形工具里特别好使你选中一段SQL执行完之后马上跑这句就能看到它真实使用的执行计划以及真实资源消耗。在生产环境里我更常用的是带上SQL_ID和CHILD_NUMBER参数的方式SELECT sql_id, child_number, sql_text FROM v$SQL WHERE sql_text LIKE %t_order%; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(ga5k3z7p9w1ab, 0, ALLSTATS LAST));当应用里报了一条SQL特别慢我们通常先通过V$SQL或V$SESSION锁定它的SQL_ID然后直接用DISPLAY_CURSOR把它的真实执行计划取出来。这个方法的威力在于它读的是共享池里SQL的实际执行计划包含每一行的真实返回行数A-Rows、实际执行次数A-Time这些是EXPLAIN PLAN永远拿不到的东西。SQL只要还在内存里哪怕它现在没在跑你也能把它的计划和执行统计翻出来。2.3 DISPLAY_AWR方法SQL已经被挤出内存之后的补救方案有一种很扎心的场景半夜有一条SQL跑得很慢等你早上上班想把它的执行计划拿出来看看它早就被挤出共享池了V$SQL里查无此SQL。这时候DISPLAY_CURSOR就无能为力了但只要AWR自动负载仓库保留了这个SQL的统计信息和执行计划快照DISPLAY_AWR就能帮上忙SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(ga5k3z7p9w1ab));它从一个高峰期抓到另一个高峰期的时间窗口里找到这条SQL的执行计划数据并展示出来。注意一个细节DISPLAY_AWR得到的计划是AWR快照采集时刻优化器生成的计划它代表的是那时候优化器的选择和SQL实际跑起来可能还是有细微差别。但作为事后分析这已经是唯一可用的手段了。而且AWR的计划快照本身就是定期从V$SQL_PLAN里抓来的对一些高频SQL来说记录保存的概率还挺高的。如果你在DISPLAY_AWR里查不到计划大概率是AWR没抓到这条SQL的快照或者SQL文本SQL_ID对不上。2.4 三种方法怎么选我总结了一张表方法适用场景关键注意点DISPLAY开发阶段验证SQL写法、测试索引是否生效不真正执行SQL计划是估算的未必等于真实计划DISPLAY_CURSOR生产环境排查正在跑或刚跑完的慢SQL需要SQL还在共享池中建议带ALLSTATS LAST参数看真实统计DISPLAY_AWR事后追溯已经不在内存中的历史SQL依赖AWR快照是否保留该SQL信息是事后分析手段一句话总结能看真实的绝不只看估算的能看当前的绝不只看历史的。三者配合使用覆盖SQL分析的完整时间线。3. FORMAT参数才是真正拉开差距的地方3.1 从BASIC到ALL每一档加出来什么内容很多朋友写DBMS_XPLAN从来只写DISPLAY_CURSOR()不带参这样默认走的是TYPICAL格式。TYPICAL格式已经能显示最常见的列比如操作类型、对象名、行数估算、字节数、代价Cost等。但真正要定位复杂问题时TYPICAL远远不够就需要通过FORMAT参数来做精细化控制。FORMAT支持BASIC、TYPICAL、ALL三个预设级别还支持在这些级别基础上追加或排除选项。BASIC只显示最基础的操作和对象信息连Cost和行数都没有平时基本用不上。ALL则会额外显示出投影列、别名、过滤谓词和访问谓词等信息量大了很多分析复杂SQL时很有价值。但只看ALL还不够真正让我愿意强烈推荐的是这样一组组合写法SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST));ALLSTATS这个选项会在计划输出里增加几列关键数据A-Rows实际返回行数、A-Time实际执行时间、Buffers逻辑读、Reads物理读。这几列最大的价值是能直接和优化器估算的E-Rows计划里的Rows做对比一旦发现估算和实际差了一个数量级你基本就能锁定是统计信息问题或者基数估算偏差的锅这是SQL调优里最核心的判断依据。再来单独说说LAST这个关键字。ALLSTATS LAST表示只看最后一次执行的统计信息而不是把同一个SQL多次执行的统计汇总平均。为什么要LAST因为一个游标可以被反复执行多次平均统计会把不同执行环境下的表现抹平反而掩盖问题。LAST能精准反映最新一次执行的情况这对排查“为什么这次跑得慢”非常关键。3.2 高级选项PEEKED_BINDS、OUTLINE、ADDRESS除了ALLSTATS LAST还有几个选项在特定场景下能救命。第一个是PEEKED_BINDS它会在计划输出里显示优化器在硬解析时窥探到的绑定变量值。这个对排查绑定变量窥探导致的执行计划不稳定特别有用。举个例子一条SQL第一次执行时传入的绑定变量值正好命中了极少部分数据优化器据此生成了一个嵌套循环计划后来业务传入的值实际要返回几十万行嵌套循环就变成灾难了。如果你在计划里看不到Peeked Binds信息很难解释为什么计划会这么糟糕。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, TYPICAL PEEKED_BINDS));第二个是OUTLINE。这个选项会显示执行计划对应的outline data也就是Oracle为了固定计划而记录的一组内部提示。如果你想用SQL Profile或者SPM来稳定某个执行计划OUTLINE信息几乎就是标准配置。实际操作中我会先跑一个ALLSTATS LAST拿到优化后的计划再跑一次带OUTLINE的格式把outline提取出来为后续SQL Plan Management基线绑定做准备。第三个是ADDRESS它会在计划输出里显示每个操作的内部哈希地址主要用于追踪V$SQL_PLAN里的具体记录辅助定位子游标之间的计划差异。这个选项小众但排查游标变异问题时很有用。3.3 组合选项的正确姿势FORMAT参数可以同时指定多个选项它们用空格分隔就行。比如我想看真实统计加绑定变量附加信息可以这样写SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST PEEKED_BINDS ADDRESS));这里有个小经验ALLSTATS本身已经隐含了IOSTATS和MEMSTATS两套统计所以不需要再单独加IOSTATS。而如果还想看分区裁剪情况可以追加PARTITION。做并行SQL分析就追加PARALLEL。总之先明确你想解决什么问题再决定要哪些选项别一股脑全堆上去。因为选项越多输出越杂反而容易忽略真正关键的信息。4. 用真实场景串一遍完整排查流程4.1 现场一条报表SQL从40秒优化到0.3秒某天同事反馈一条统计SQL在生产环境跑了40多秒应用侧已经超时。我先从V$SESSION抓到该会话正在执行的SQL_ID因为SQL还在跑用DISPLAY_CURSOR拿到的就是实时计划。命令执行完计划里最显眼的一行是TABLE ACCESS FULL T_ORDER (E-Rows1000, A-Rows500000)E-Rows优化器估算1000行和A-Rows实际返回50万行差了整整500倍。这就是典型的统计信息过期表中数据量已经大幅增长但字典里的统计信息还停留在很久之前导致优化器错误地认为全表扫描的成本很低实际上却扫出了一个巨大的中间结果。这个案例里最快的修复不是加索引而是先刷新统计信息再重新看计划EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, T_ORDER);统计信息刷新后同样的SQL再次执行执行计划从全表扫描变成了索引范围扫描加嵌套循环SQL总耗时从40秒降到了0.3秒。整个过程我连一行SQL都没改就是靠ALLSTATS LAST里的E-Rows/A-Rows对比快速锁定问题再刷新统计信息解决。4.2 另一个场景绑定变量窥探让执行计划“劫持”了业务还有一个案例也很有代表性。某段代码用了绑定变量SQL在第一次硬解析时传入的绑定变量值是一个极小范围的客户编号优化器窥探到这个值后生成了嵌套循环的计划。但后续业务传入的客户编号对应数据量很大嵌套循环计划每次执行都要循环几十万次SQL直接卡死。我定位问题时就是在计划输出里看到了Peeked Binds区域显示的绑定变量值确认是绑定变量窥探导致的计划选择偏差。这种问题的处理思路不一定是禁掉绑定变量窥探那会让系统里大量SQL重新硬解析反而引发更大的性能风暴。更稳妥的做法是用HINT或者改写SQL引导优化器选择一个更通用的计划再做SQL Profile固定计划。如果确实希望完全消除对绑定变量值的依赖还可以在11g以后通过自适应游标共享Adaptive Cursor Sharing机制让优化器根据实际返回行数动态调整计划但前提是统计信息和直方图得建得足够准确。4.3 还没执行过的SQL怎么快速验证方案开发阶段或者间隙窗口内验证方案时我会先用EXPLAIN PLAN FOR DISPLAY看一下优化器给的预估计划。比如新加了一个复合索引想确认某条SQL会不会走这个索引直接EXPLAIN PLAN FOR SELECT * FROM t_order WHERE status1 AND create_date SYSDATE-7; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());如果计划走了INDEX SKIP SCAN或者INDEX RANGE SCAN说明索引策略生效。如果还是全表扫描我就要先检查这个索引是否建对比如列顺序是否考虑到等值条件优先再看看要不要在SQL里用INDEX HINT。但这个阶段看到的全是估算等SQL真正在某次执行里挂掉了还是要回到DISPLAY_CURSOR看真实情况。5. 常见问题和排查技巧实录5.1 DISPLAY_CURSOR返回空结果怎么回事这是所有DBMS_XPLAN初学者最容易撞到的问题。DISPLAY_CURSOR返回空通常有几种原因SQL已经不在共享池里了传入的SQL_ID或CHILD_NUMBER不对当前用户没有权限访问底层的V$视图。排查顺序是先确认SQL_ID是否正确V$SQL和V$SQLAREA都有再看SQL有没有被age out如果确实被挤出去了就改用DISPLAY_AWR从AWR快照里找历史计划。有一点值得注意如果SQL只是很小的一段文本且执行频率极低被age out的概率很高建议今后遇到关键SQL第一时间就抓计划别等慢SQL彻底没影了才想起来分析。5.2 EXPLAIN PLAN的计划和DISPLAY_CURSOR对不上这种情况屡见不鲜。EXPLAIN PLAN FOR生成计划时只是基于当前统计信息做静态估算而DISPLAY_CURSOR拿到的是实际执行的计划。只要统计信息不新、SQL里有绑定变量、或者系统启用了执行计划管理SPM两者就很可能不一样。发生对不上的时候永远以DISPLAY_CURSOR真实执行计划为准EXPLAIN PLAN的结果只建议作为策略验证参考。如果一个生产环境里这两份计划经常对不上第一反应该是去检查统计信息收集作业有没有正常完成其次看看SPM基线是否干预了计划选择。5.3 计划里全是全表扫描但索引明明存在这是最常见的优化误区。建了索引不代表优化器就该一定走索引。当查询要返回的结果占全表比例过高时优化器算来算去觉得索引回表反而更贵选择全表扫描反而是聪明决策。但如果你确认返回行数很少却还是全表扫描那重点排查两件事第一WHERE条件列上有没有函数包裹或隐式类型转换导致索引失效第二统计信息里的表行数是不是严重偏小导致优化器低估了全表扫描的成本。另外还有一种情况是SQL文本里用了SELECT *回表代价太高优化器就算知道索引能定位行也评估出全表扫描更便宜。这时候可以试试覆盖索引组合索引包含查询需要的列来降低回表代价。5.4 每次执行计划都不一样怎么稳定如果同一个SQL在不同时间飘来飘去有时候快有时候慢多半是统计信息在持续变化或者绑定变量窥探导致不同子游标有不同的计划。稳定执行计划的方案优先级建议是这样先确保统计信息收集策略是稳定的频率合理其次给关键SQL使用OUTLINE或SQL Profile锁定计划最后在19c及以上版本可以考虑SPM演进机制让计划管理更自动化。你可以先用DBMS_XPLAN的OUTLINE选项拿到当前最优计划的outline然后用DBMS_SQLTUNE.CREATE_SQL_PROFILE把它固化下来这样就算统计信息变化计划也不会随便乱飘。5.5 从计划里反推出SQL改写方向最后分享一个我经常用的技巧拿到DISPLAY_CURSOR输出后我会先看三个地方。一是看驱动表排最上面、缩进最少的操作选得是否合理一般应该让小表或者过滤后行数最少的表做驱动二是看被驱动表的连接方式如果是NESTED LOOPS但被驱动表每行都要回表扫全表那基本就是连接列缺索引三是看Access Predicates和Filter PredicatesAccess表示能直接通过索引定位Filter表示只能把数据捞出来再过滤两者差距很大。如果Filter里面出现了本该能走索引定位的列那就是SQL写法或索引设计的问题顺着这个方向改写SQL往往立竿见影。6. 工具选型的一些个人体会DBMS_XPLAN在任何图形化客户端里其实都能用关键是不要过度依赖工具自带的“执行计划查看”按钮——那玩意的底层实现五花八门有的只是EXPLAIN PLAN有的是抓V$SQL_PLAN视图数据来源不一样显示的内容和准确性也有偏差。在PL/SQL Developer、Navicat、DBeaver这类工具里最可靠的方式就是直接把DBMS_XPLAN的SQL跑一遍自己看输出文本。这样不管是在哪个客户端环境分析口径都统一。另外有一个从11g开始就很好用的进步DBMS_XPLAN可以直接显示SQL Monitor报告前提是启用了SQL监控默认对消耗超过阈值或并行的SQL会开启SELECT * FROM TABLE(DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id ga5k3z7p9w1ab));这个报告和DBMS_XPLAN取数来源一样但呈现更丰富包括每一步的实际行数、消耗时间、内存用量。对于并行度高的重型SQL我把SQL Monitor报告当作DBMS_XPLAN的升级补充来用。团队里如果有新同事入职我通常给的建议就是两个练习找十条生产真实慢SQL用DBMS_XPLAN把执行计划抓出来尝试用ALLSTATS LAST分析A-Rows和E-Rows的差距再挑三条计划极端不合理的SQL结合SQL改写和索引策略把计划掰回正轨。这两件事做完对Oracle SQL优化的理解基本就入门了。也正因如此这篇文章从头到尾没有讲什么深奥的算法核心就是工具用法加实战判断但DBMS_XPLAN这个工具用熟了确实能让整个排查过程从“猜”变成“看”从“用时间换结果”变成“用结构判断定位问题”这也是我认为它最值得花时间去掌握的原因。
