Oracle SQL Explain Plan 解读：用 DBMS_XPLAN.display_cursor 定位执行计划偏差

1. 为什么测试跑得飞快生产却慢成蜗牛同一句 SQL在测试库 0.2 秒出结果上了生产要跑 8 秒这种场景做 Oracle 调优的人几乎都遇到过。很多人第一反应是「生产数据量大」但数据量只差几倍、耗时却差几十倍时问题往往不在数据本身而在执行计划发生了偏差。执行计划Explain Plan是 Oracle 优化器为一条 SQL 选出的访问路径描述走不走索引、用嵌套循环还是哈希连接、驱动表是谁、预估返回多少行。测试和生产即使表结构完全一致只要统计信息、绑定变量、优化器参数、数据分布有任何一处不同优化器就可能选出两条完全不同的路径。更麻烦的是你用EXPLAIN PLAN FOR看到的计划是「假设执行」推演出来的未必是真正跑过的那条计划。所以定位偏差的正确姿势是直接去库缓存Library Cache里捞出真实执行过的游标计划而DBMS_XPLAN.DISPLAY_CURSOR就是干这个的。它读的是V$SQL_PLAN里已经落地的计划还能带上真实的行数、耗时统计这是EXPLAIN PLAN给不了的。这篇就围绕这个函数把「怎么捞、怎么看、怎么对比」讲清楚适合已经会写 SQL、但被执行计划漂移卡住的开发和 DBA。2. 前置准备让 DISPLAY_CURSOR 能吐出真实统计DISPLAY_CURSOR本身不需要额外安装它是 Oracle 自带的DBMS_XPLAN包里的函数9i R2 引入10g、11g 逐步增强。但有个关键前提想看到真实的执行行数和耗时A-Time、Buffers 这些必须让 SQL 在运行时收集执行统计。默认情况下这些列是空的很多人第一次用发现 A-Time 一栏全是空白就是踩了这个坑。开启方式有三种按场景选第一种是会话级最省事适合临时排查ALTER SESSION SET statistics_level ALL;第二种是在 SQL 里加提示只对当前语句生效不影响其他会话SELECT /* gather_plan_statistics */ e.ename, d.dname FROM emp e, dept d WHERE e.deptno d.deptno AND e.empno 7499;第三种是系统级statistics_levelALL影响面大生产环境慎用一般不建议为了排查一条 SQL 就全局打开。注意statistics_levelALL会带来额外的统计收集开销生产上优先用gather_plan_statistics提示把影响控制在单条语句。另外要清楚一个边界DISPLAY_CURSOR读的是库缓存里的计划如果这条 SQL 已经被挤出共享池比如被 age out或者实例重启过那就捞不到了。所以排查要趁热SQL 刚跑完就去查V$SQL。3. 可复制配置捞 SQL_ID、读真实计划、对比偏差3.1 先拿到 SQL_ID 和子游标号DISPLAY_CURSOR的两个核心入参就是sql_id和cursor_child_no。sql_id是父游标标识child_number是子游标序号——同一句 SQL 因为绑定变量、优化器环境不同可能产生多个子游标每个子游标对应一条独立的执行计划偏差往往就藏在不同的 child 里。SELECT sql_id, child_number, plan_hash_value, executions, buffer_gets, elapsed_time/1000 AS elapsed_ms FROM v$sql WHERE sql_text LIKE %from emp e ,dept d where e.deptnod.deptno% AND sql_text NOT LIKE %v$sql%;这里几个字段值得盯字段含义排查用途sql_id父游标标识传给 DISPLAY_CURSOR 的第一个参数child_number子游标序号传给第二个参数定位具体哪条计划plan_hash_value计划哈希值两个环境对比时值不同即计划不同executions执行次数判断是不是高频 SQLbuffer_gets逻辑读真实开销的核心指标elapsed_time总耗时微秒除以 executions 得单次耗时如果plan_hash_value在测试和生产不一致基本可以确认计划漂移了。这一步是整个排查的锚点。3.2 读取真实执行计划拿到sql_id和child_number后直接读计划SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR(9uz917qhd6dv0, 0, ALLSTATS LAST) );第三个参数format决定输出内容常用的几个值TYPICAL默认值显示操作 ID、名称、选项、预估行数、字节数、优化器成本够日常看。ALLSTATS LAST在 TYPICAL 基础上加上真实执行统计A-Rows、A-Time、Buffers排查偏差必用这个。ALL信息最全包含别名、投影、谓词等输出较长。BASIC只留操作 ID、名称、对象最精简。不传任何参数时DISPLAY_CURSOR会返回当前会话最后一条 SQL 的计划调试时很方便SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);3.3 关键字段解读清单计划输出出来后重点看这几列它们直接指向瓶颈| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | |----|--------------------|------|--------|--------|--------|------------|---------| | 0 | SELECT STATEMENT | | 1 | | 1 | 00:00:00.01| 12 | | 1 | NESTED LOOPS | | 1 | 1 | 1 | 00:00:00.01| 12 | | 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | 1 | 00:00:00.01 | 4 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | 1 | 00:00:00.01 | 2 | | 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 1 | 1 | 00:00:00.01 | 4 |Id操作步骤编号缩进层级代表父子关系从 0 往下读。Operation操作类型TABLE ACCESS FULL全表扫描、INDEX RANGE SCAN索引范围扫描、NESTED LOOPS嵌套循环、HASH JOIN哈希连接路径差异就体现在这里。E-Rows vs A-Rows预估行数 vs 实际行数。这两个值差一个数量级以上就是统计信息不准或绑定变量窥探出问题的信号优化器基于错误的预估选了错误的连接方式。A-Time该步骤实际耗时累计值看哪一步耗时占比最高。Buffers逻辑读次数越高说明扫描的数据块越多是判断开销的硬指标。提示E-Rows和A-Rows的偏差是执行计划漂移最常见的根因。看到某一步预估 1 行、实际返回 10 万行基本就能锁定问题。4. 验证请求一次执行计划偏差的完整复现光看字段还不够得动手验证一次偏差。下面用测试和生产两个环境对比的思路走一遍。先在测试库执行目标 SQL并开启统计收集ALTER SESSION SET statistics_level ALL; SELECT /* gather_plan_statistics */ e.ename, d.dname FROM emp e, dept d WHERE e.deptno d.deptno AND e.empno 7499; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST));记下测试库的plan_hash_value和关键步骤的A-Rows、Buffers。然后到生产库对同一句 SQL 做同样操作拿到生产的plan_hash_value。如果两个值不同把两份计划并排看。典型偏差长这样测试库走INDEX UNIQUE SCANNESTED LOOPSBuffers 只有 12生产库却变成TABLE ACCESS FULLHASH JOINBuffers 飙到几万。原因通常是生产库的统计信息过期优化器以为全表扫描更划算。验证动作可以这样收口——查一下两边的统计信息时间SELECT table_name, num_rows, last_analyzed FROM user_tab_statistics WHERE table_name IN (EMP, DEPT);如果生产库的last_analyzed是很久以前或者num_rows和实际行数严重不符那偏差根因就找到了。重新收集统计信息后再跑一次DISPLAY_CURSOR观察plan_hash_value是否回到预期路径EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, EMP, cascade TRUE);这一步做完再对比A-Time和Buffers如果逻辑读从几万降到几十说明计划已经纠正。整个过程不需要改 SQL只是把优化器的判断依据修正了。5. 本篇常见错排查报错一A-Time、A-Rows 全是空白。这是最高频的问题原因是没开统计收集。回到第 2 节用statistics_levelALL或gather_plan_statistics提示重新执行 SQL再读计划。报错二ORA-01460或返回空结果。多半是sql_id传错或者这条 SQL 已经被挤出库缓存。先用V$SQL确认sql_id还在且sql_text匹配。如果查不到说明计划已 age out只能重新执行 SQL 后再捞。报错三只看到父游标看不到具体子游标。cursor_child_no传了NULL会返回所有子游标但有时你只想看某一个。先查V$SQL的child_number列把具体数字传进去比如DISPLAY_CURSOR(9uz917qhd6dv0, 2, ALLSTATS LAST)。报错四format参数写错导致输出异常。format是字符串多个修饰符用逗号分隔比如ALLSTATS LAST、TYPICAL PEEKED_BINDS。写成ALLSTATS,LAST这种带逗号的形式在部分版本会报错按官方写法用空格分隔。报错五对比时发现plan_hash_value相同但性能差很多。这种情况计划结构一致但执行时的数据分布或绑定变量值不同导致实际行数差异巨大。重点看A-Rows和E-Rows的偏差以及Buffers的分布问题往往在绑定变量窥探或直方图上。排查完这些基本能覆盖DISPLAY_CURSOR使用中的绝大多数坑。真正难的不是函数本身而是养成「先捞真实计划、再对比字段、最后定位根因」的习惯而不是一上来就改 SQL 或加索引。6. 把工具用顺接入和验证各走各的路执行计划排查是个反复验证的活捞计划、对比、改统计、再验证每一步都要有稳定的环境支撑。如果你在本地或测试环境想快速验证某条 SQL 的计划差异可以直接用模型对话把 SQL 和计划贴进去让它帮你逐字段分析E-Rows和A-Rows的偏差原因省去自己翻文档的时间https://taotoken.net/models?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewrite如果是要把这类排查能力固化到日常开发流程里比如写脚本自动捞V$SQL、批量对比plan_hash_value那更适合用 Coding Plan 来搭工具链把重复的查询和对比动作沉淀成可复用的脚本https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewrite至于 API Key 的申请和接入文档走这两个入口就行配置好之后就能把上面的查询脚本接到自己的监控或巡检流程里https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewritehttps://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewrite我自己的习惯是每次生产出现慢 SQL先不急着改先把DISPLAY_CURSOR的输出存下来和上一次正常的计划做 diffplan_hash_value一变方向就清晰了。统计信息、绑定变量、直方图这三样按顺序查八成的问题都能定位到。