企业官网建设流程全解析

先说明一下，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方法：适合还没有实际执行的SQL

EXPLAIN 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 三种方法怎么选，我总结了一张表

一句话总结：能看真实的绝不只看估算的，能看当前的绝不只看历史的。三者配合使用，覆盖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-Rows=1000, A-Rows=500000)

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 status='1' 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 Predicates，Access表示能直接通过索引定位，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这个工具用熟了，确实能让整个排查过程从“猜”变成“看”，从“用时间换结果”变成“用结构判断定位问题”，这也是我认为它最值得花时间去掌握的原因。