做Oracle调优这些年我最大的体会是能不能快速看清一条SQL的执行计划直接决定了排查效率。尤其是生产环境里开发工具里按F5显示的计划和数据库真实跑出来的计划经常不是一回事——这里面的坑多半是没选对获取执行计划的方法。这篇博文就把我平时用的六种Oracle获取执行计划的方法全部梳理一遍从最基础的EXPLAIN PLAN到最能还原生产现场的DISPLAY_CURSOR再到深挖优化器决策的10053事件每一种我都会说明适用场景、操作步骤、输出怎么读以及我实际踩过的坑。不管你是刚入门的开发还是已经被慢SQL折磨过几轮的DBA照着这套思路去排查至少不会拿一个假计划当真相。1. 执行计划是什么为什么一种方法不够用1.1 执行计划是优化器的“决策结果”理解这六种方法之前先要把执行计划本身的来源搞清楚。Oracle的查询优化器接到一条SQL之后会根据表上的统计信息、系统参数、绑定变量情况、是否有hint等因素生成若干条可能的执行路径然后给每条路径估算成本最后选出它认为成本最低的那条。所谓执行计划就是优化器最终给出的那个“方案明细”先访问哪张表、走索引还是全表扫、表之间用什么连接方式、每一步估算处理多少行数据。关键要记住执行计划里绝大部分数字都是“估算值”。比如Rows列是基于统计信息算出来的不是说真的就只处理这么多行。如果统计信息过期或者SQL很复杂导致优化器取舍判断失误估算值和实际值可能差出几个数量级。所以我一直强调拿到执行计划之后一定要追问一个问题这是优化器估算的计划还是SQL真正跑过之后记录下来的实际执行计划这决定了后续所有判断的可靠性。1.2 六种方法的核心差异Oracle获取执行计划的方法远不止六种但日常工作中最常用的总结下来就是六个EXPLAIN PLAN FOR DBMS_XPLAN.DISPLAYSQL*Plus的AUTOTRACE查询V$SQL_PLAN / V$SQL视图DBMS_XPLAN.DISPLAY_CURSOR10046事件SQL Trace配合TKPROF10053事件优化器追踪这六个方法最本质的区别在于三点第一SQL到底执行了没有第二拿到的计划里有没有真实行数和真实耗时第三适用的生产安全隐患程度。比如EXPLAIN PLAN只会让优化器做“推演”不会真的去跑SQL好处是快、安全坏处是拿不到真实统计而DISPLAY_CURSOR是从共享池里取真实执行的计划如果再配合统计信息收集条件就能看到每一行步骤实际处理了多少行、消耗了多少逻辑读。搞清楚了这些差异你就能针对不同场景选对工具而不是一把锤子砸所有钉子。2. 方法一EXPLAIN PLAN FOR——开发环境验证的基石2.1 标准操作三步走EXPLAIN PLAN是Oracle最早提供的获取执行计划方式逻辑最简单把优化器生成的结果写进一张计划表然后查出来。常见的写法是EXPLAIN PLAN FOR SELECT e.emp_name, d.dept_name FROM emp e JOIN dept d ON e.dept_id d.dept_id WHERE e.salary 5000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);第二句是核心DBMS_XPLAN.DISPLAY会把计划表里的内容格式化成人能读懂的表格输出包括Id、Operation操作类型、Name对象名、Rows估算行数、Bytes估算字节数、Cost成本、Time估算耗时列。这个格式就是Oracle执行计划的通用显示标准学会了它后面所有方法输出的格式你都不会陌生。如果需要指定输出详略可以加format参数BASIC只显示最少信息TYPICAL是默认推荐级别ALL会多显示一些启发式信息和参数信息。日常开发验证用默认的TYPICAL就够。2.2 这个方法最大的坑它没跑SQL我在培训时经常强调一句话EXPLAIN PLAN是一条假执行计划它只让优化器“脑算”了一遍SQL本身一行数据都没碰。所以它适合用来回答“优化器认为会怎么跑”这类问题不适合回答“这条SQL当下到底慢在哪”。举一个真实案例开发反馈一条SQL走了全表扫描我用EXPLAIN PLAN一看确实是全表扫描但这个是估算计划。后来用DISPLAY_CURSOR去看真实执行计划发现实际走的是INDEX RANGE SCAN只是索引扫描消耗的CPU比全表扫还高所以真实计划里它有另一个隐藏的慢点。如果一直盯着EXPLAIN PLAN的结果分析方向整个就跑偏了。另外还有两个细节需要注意。一是EXPLAIN PLAN不能收集绑定变量的真实值如果SQL本身对绑定值敏感同一语句可能因为绑定值不同产生不同计划EXPLAIN PLAN根本反映不出来。二是如果SQL特别复杂EXPLAIN PLAN生成的计划可能和真实执行计划出现较大偏差因为优化器在做动态采样、自适应计划时有些决策是在实际执行过程中才会确定的。所以我的结论是这个方法适合开发初期验证索引是否命中、hint语法是否正确不适合生产环境慢SQL的最终定位。3. 方法二SQL*Plus AUTOTRACE——命令行里的顺手工具3.1 一条命令拿到计划加统计AUTOTRACE是SQL*Plus内置的功能比起EXPLAIN PLAN它有一个不可替代的优势这条SQL是真的会跑一遍的所以输出里不仅有执行计划还有这条语句真实产生的逻辑读、物理读、排序次数、行数等统计信息。用起来非常简单SET AUTOTRACE ON; SELECT * FROM emp WHERE dept_id 10; SET AUTOTRACE OFF;这样执行完SELECT之后SQL*Plus会先显示查询结果然后自动追加一段执行计划和一段统计信息。如果不想让查询结果刷屏可以用SET AUTOTRACE TRACEONLY只显示计划和统计如果只想看统计信息不想看计划用SET AUTOTRACE TRACEONLY STATISTICS。统计信息部分要重点看几个关键项consistent gets是逻辑读次数physical reads是物理读次数sorts是排序次数rows processed是最终返回行数。逻辑读高说明内存里做了大量数据访问物理读高说明大量访问走的是磁盘这两者结合执行计划里的访问路径基本能判断瓶颈在I/O还是SQL本身设计不合理。3.2 权限配置和容易忽略的细节AUTOTRACE不是所有人随手就能用的它需要plustrace角色或者相应权限。第一次使用报错“Cannot find the SET statement to execute”或者没有权限时需要用DBA账号执行一遍Oracle自带的脚本cd $ORACLE_HOME/sqlplus/admin sqlplus / as sysdba plustrce.sql GRANT PLUSTRACE TO your_user;如果要把权限放开给所有普通用户直接GRANT PLUSTRACE TO PUBLIC也可以生产环境不太建议这么干按用户授权更稳妥。使用AUTOTRACE时几个经验第一它反映的是当前会话里SQL实际执行后的统计这意味着如果你在生产库直接开AUTOTRACE跑一个大查询会真实产生I/O和undo务必评估好影响。第二它会统计SQL*Plus本身产生的那部分很有迷惑性的额外开销比如SQL*Net roundtrips是客户端交互的次数不是SQL本身的内部代价分析时要把这类指标排除掉。第三对于一些在PL/SQL内部执行的游标AUTOTRACE看不到因为AUTOTRACE只管当前会话中SQL*Plus直接发出的SQL语句。所以它的定位是开发机和测试环境里快速确认计划走向和资源消耗的顺手工具。4. 方法三直接查V$SQL_PLAN——把共享池里的计划捞出来4.1 先定位SQL_ID和CHILD_NUMBER第三种方法跟前两种逻辑完全不同它不主动触发优化器生成计划而是把已经缓存在库缓存Library Cache里的执行计划直接拿出来看。生产环境排查慢SQL这是很常用的第一步。前提是SQL已经运行过并且还在共享池里没有被age out。我们先用SQL的片段去V$SQL里找它的SQL_IDSELECT sql_id, child_number, sql_text FROM v$sql WHERE sql_text LIKE %FROM emp e JOIN dept d% AND sql_text NOT LIKE %v$sql%;注意SQL_TEXT字段默认只截取前1000个字符如果SQL很长要用V$SQL.SQL_FULLTEXT字段或者DBMS_SQLTUNE里的查询方式看全文。找到SQL_ID后接下来就是去V$SQL_PLAN里捞计划。这里有个小陷阱同一个SQL_ID下可能有多个CHILD_NUMBER。每当这条SQL的解析环境发生变化——比如NLS参数不同、优化器参数被改过、授权对象变化、绑定变量类型不匹配——Oracle都会为它生成一个新的child cursor。不同child的计划有可能完全不同。所以我习惯在第一步就同时把SQL_ID和CHILD_NUMBER一起记下来后面展示计划时指定具体的child避免拿错版本。4.2 用DBMS_XPLAN展示共享池里的计划拿到了SQL_ID和CHILD_NUMBER之后展示计划我一般用这段SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY( table_name V$SQL_PLAN, statement_id sql_id_child_number, format TYPICAL));statement_id这里要拼成“SQL_ID_CHILD_NUMBER”这种格式比如SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY( V$SQL_PLAN, 2d4k9zq1p0m1n_0, TYPICAL));输出和前面两个方法的格式一样但这里展示的是优化器当时生成并缓存的计划。注意它仍然不包含真实执行统计因为没有额外收集运行时统计信息Rows列依然只是优化器的估算。大多数情况下我更喜欢直接用DISPLAY_CURSOR来做这件事写法更简单还支持带出真实统计。V$SQL_PLAN的独立价值在于当你需要批量分析一个SQL_ID下面所有child的计划差异时直接对V$SQL_PLAN做SQL查询会更灵活比如找出所有成本估算差异巨大的child或者和DBA_HIST_SQL_PLAN做历史对比。这些都是DISPLAY_CURSOR不方便做的批量分析场景。5. 方法四DBMS_XPLAN.DISPLAY_CURSOR——真实执行计划的黄金标准5.1 从游标缓存里抓“现场”我平时生产环境定位慢SQL百分之八十都用DISPLAY_CURSOR可以说这是Oracle现有的获取执行计划方法里平衡了“真实”和“方便”的最优解。它直接读取游标缓存Cursor Cache里某个SQL的游标执行信息如果SQL还挂在共享池里你就能拿到它真实执行过的计划。最简单的用法直接查当前会话最后一条SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);大多数情况我们指定SQL_ID来查SELECT sql_id, child_number, sql_text FROM v$sql WHERE sql_text LIKE %你的SQL特征片段%; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(2d4k9zq1p0m1n, 0, TYPICAL));第二个参数是CHILD_NUMBER指定为0就只看第一个child。如果不确定可以直接传NULL或者不传它会显示这个SQL_ID下所有可用的child并在输出里用类似“2 instances of this statement were executed”这样的注释提示你有多个版本。5.2 用ALLSTATS拿到真实行数和耗时DISPLAY_CURSOR默认输出里Rows列依然是优化器估算值这还不够真实。想看到SQL每一步真正处理了多少行、消耗了多少缓冲区和时间需要先让这条SQL以收集统计信息的方式运行然后用ALLSTATS格式展示。操作流程是在开启统计收集的会话里重新执行一次SQLALTER SESSION SET STATISTICS_LEVEL ALL; -- 或者直接在SQL里加 hint SELECT /* gather_plan_statistics */ ...执行完之后再看真实计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT ALLSTATS LAST));ALLSTATS前面的ALL表示显示所有步骤的统计信息LAST表示只显示最后一次执行的统计。想要ALL之前的每次执行数据可以改成ALLSTATS ALL。此时输出里会比普通计划多出A-Rows实际行数、A-Time实际耗时、Buffers实际逻辑读、Reads实际物理读列。将A-Rows和E-Rows估算行数放在一起对比是校验统计信息是否准确、定位优化器误判的最快方式。如果E-Rows显示1000A-Rows只有10说明估算偏离严重接下来就该去查统计信息是不是过期或者有没有隐式转换导致索引失效。这个方法我也要提一个提醒如果SQL已经被挤出共享池DISPLAY_CURSOR就取不到了。遇到这种情况可以用DBMS_XPLAN.DISPLAY_AWR结合AWR快照里的历史计划来查看但AWR不一定保留所有SQL的完整计划所以最稳的做法还是发现慢SQL后尽早用DISPLAY_CURSOR固定现场。6. 方法五10046事件与TKPROF——深入每一行执行的代价6.1 开启10046 SQL Trace的完整步骤当DISPLAY_CURSOR拿到的信息已经不够用时——比如你想知道SQL执行过程中到底发生了什么等待事件、每一步到底耗了多少时间——就需要用到Oracle最强大的诊断工具之一10046事件也就是SQL Trace。它会把SQL执行过程中的解析、绑定变量、执行、关闭游标等每个阶段以及数据库等待的事件全部写进一个trace文件。我常用的开启方式ALTER SESSION SET tracefile_identifier my_trace; ALTER SESSION SET EVENTS 10046 trace name context forever, level 12; -- 在这里执行要分析的SQL SELECT ...; ALTER SESSION SET EVENTS 10046 trace name context off;level参数很关键1是最基本的SQL_TRACE只记录执行阶段4是1加绑定变量信息8是1加等待事件信息12是4和8的结合也是排查性能问题最常用的级别。注意level 4里绑定变量的值会被明文记录生产环境如果涉及敏感数据要评估隐私合规风险。trace文件的位置通过以下语句查最稳妥SELECT value FROM v$diag_info WHERE name Default Trace File;如果是在RAC环境还需要确认这条SQL跑在哪个节点去对应节点的diagnostic目录下找文件。拿到原始trace文件之后人肉读不现实通常用TKPROF格式化一下tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345_my_trace.trc /tmp/tkprof_output.txt sysno sortexeelasortexeela表示按执行耗时排序这样最耗时的SQL在最前面方便快速定位重点。6.2 TKPROF输出里我最关心的三个数字格式化后的报告里每个SQL语句下面会有Parse、Execute、Fetch三行分别代表解析、执行、取数三个阶段各自有该阶段耗时。然后把原SQL和TKPROF输出放在一起看优化前后变化。我最关心三组数第一组是Elapsed time和CPU time如果Elapsed time远大于CPU time说明大量时间花在等待事件上接下来就去查等待事件是什么第二组是query逻辑读和disk物理读的数量这直接反映数据访问量第三组是Rows也就是每一步实际返回的行数结合20000 rows vs 1 row这种差距去反推执行计划是否合理。10046事件使用上的注意事项我也提两句。第一不要在生产环境的会话里长时间开着level 12trace文件增长非常快以前我就遇到过一个会话开了一夜trace把整个文件系统写满的事故。第二如果SQL的量很大尽量先用DBMS_MONITOR或者DBMS_SESSION只对特定会话开启避免全局开启影响所有会话性能。第三拿到trace后的重点是和DISPLAY_CURSOR的结果互相印证很多时候10046的最大价值不在执行计划本身而是让你看清计划里每一步的等待消耗分配。7. 方法六10053事件——偷听优化器的内心独白7.1 什么时候才需要开10053如果说前五种方法都是“看结果”那10053事件是在“看过程”。它会把优化器解析SQL时尝试过的所有执行路径、成本计算过程、被淘汰的方案全部写进trace文件。当一条SQL明明有合适的索引优化器却非要全表扫或者连接顺序就是不对你想知道“为什么是这么选的”这时候再开10053。开启方式ALTER SESSION SET EVENTS 10053 trace name context forever, level 1; -- 执行需要分析的SQL SELECT ...; ALTER SESSION SET EVENTS 10053 trace name context off;trace文件位置也是通过v$diag_info查。和10046不同10053没有复杂的level分级level 1基本够用。需要特别注意的是10053必须在SQL硬解析时才会产生完整追踪如果这条SQL已经在共享池里要先想办法让它重新解析比如加一个不同的hint、改一句注释或者用ALTER SYSTEM FLUSH SHARED_POOL生产环境慎用把它挤出去。7.2 10053输出文件里怎么看重点10053的trace文件非常长动辄几百上千行直接读不现实。我通常用grep过滤几个关键段落grep -n Single Table Access Path trace_file.trc grep -n Join order trace_file.trc grep -n Best so far trace_file.trcSingle Table Access Path段落里会列出每张表的所有候选访问路径比如全表扫描成本多少、索引范围扫描成本多少为什么最终选了某一个。Join order段落里会看到不同连接顺序下系统估算的成本能直观看到优化器在纠结什么。Best so far段落会记录某个计划在某一步的表现。10053的价值在于它能揭示统计信息、系统参数、hint对优化器决策的具体影响。比如你发现优化器没用某个新建的索引开10053一看可能原因就是索引没有ANALYZE统计信息显示为空优化器干脆放弃它。又比如你发现连接方式从哈希连接变成了嵌套循环10053里通常能看到是因为小表统计行数被更新成了更大的值导致成本模型反转。这类信息在前五种方法里都看不到。不过10053必须是“最后手段”。原因有二一是它的输出太庞杂分析门槛高二是开10053本身也会带来额外开销尤其是在生产库上对复杂SQL做硬解析。我自己的习惯是先查统计信息是否新鲜、有没有隐式转换、有没有SPMSQL Plan Management干预排查完这些常规项之后还找不到原因再开10053去看优化器的完整思考过程。8. 六种方法横向对比与选用建议8.1 六种方法一览表方法SQL是否执行是否含真实统计典型使用场景关键命令/入口EXPLAIN PLAN否否开发环境验证索引、hintEXPLAIN PLAN FOR DBMS_XPLAN.DISPLAYAUTOTRACE是是会话级统计命令行快速看计划统计SET AUTOTRACE ONV$SQL_PLANSQL已跑过否估计算批量分析共享池中历史执行计划查V$SQL再DBMS_XPLAN.DISPLAYDISPLAY_CURSORSQL已跑过可含真实统计生产慢SQL定位最推荐DBMS_XPLAN.DISPLAY_CURSOR10046事件是是含等待事件深度分析等待事件和执行耗时ALTER SESSION SET EVENTS 10046...10053事件是硬解析否成本计算过程看优化器为什么不选某个计划ALTER SESSION SET EVENTS 10053...8.2 按照实际工作场景选择方法如果你是开发人员在测试环境里验证自己写的SQL有没有走索引直接EXPLAIN PLAN就够了五分钟之内能确认方向。如果你想顺便看看这条SQL消耗了多少逻辑读那就把AUTOTRACE打开一条SET命令的事。但到了生产环境你发现一条线上SQL突然变慢第一步我一定建议用DISPLAY_CURSOR从游标缓存中取出真实执行计划并检查是否能用ALLSTATS LAST带出真实行数。真实行数和估算行数的巨大差异通常就是问题的突破口。如果DISPLAY_CURSOR里看到A-Rows和E-Rows差距很大但统计信息看起来是新收集的那就需要再往前查一步是不是发生了隐式类型转换是不是绑定变量值严重倾斜是不是使用了自定义函数导致优化器无法评估基数。这些常规手段都用尽之后还不确定原因再考虑10046去观察SQL执行过程中的等待事件或者10053去看优化器在解析时的完整决策链条。另外还有一种常见场景是SQL已经不在共享池了但你想看它之前执行过什么计划。这个时候可以用AWR报告里SQL Statistics部分配合DBMS_XPLAN.DISPLAY_AWR从AWR快照中加载历史计划。虽然DISPLAY_AWR的执行计划不一定完整保留所有运行细节但至少能看个大概方向。我虽然没把它算进今天的六种方法里但它在“事后追溯”场景下是非常有效的数据来源。8.3 我踩过的坑和避坑心得第一个坑是误把EXPLAIN PLAN的计划当真实计划用。特别是生产环境做性能分析如果SQL还没有真正被执行过EXPLAIN PLAN的结果只能当作“预研”千万别基于它的Cost就断言“这条SQL不该慢”否则排查方向很容易被误导。第二个坑是DISPLAY_CURSOR看不到真实统计以为Rows就是真实行数。实际上必须配合ALTER SESSION SET STATISTICS_LEVELALL或者gather_plan_statistics hint并且在FORMAT参数里加上ALLSTATS输出才会包含A-Rows和A-Time。不然你看到的就是优化器的估算值分析半天可能都在跟假数字较劲。第三个坑是定位SQL_ID时没有注意CHILD_NUMBER。尤其是使用绑定变量偷看值、NLS环境不一致、不同schema下同名对象等场景同一个SQL_ID下多个child的计划可能大相径庭。我的习惯是查V$SQL时把CHILD_NUMBER一起查出来展示计划时也指定具体的CHILD_NUMBER。第四个坑是10046和生产环境的结合问题。以前我在一个高峰期会话上开了level 12追踪结果trace文件几个G最后影响到磁盘空间得不偿失。现在我的原则是生产环境优先用DISPLAY_CURSOR只有确实需要等待事件级别的信息才谨慎地、短时间地对单个会话开trace。最后分享一个我用了很久的调优流程先拿到“真计划”DISPLAY_CURSOR ALLSTATS确认步骤顺序和真实资源消耗再看“估算vs实际”的偏差定位优化器误判点如果误判原因不明确用10053看优化器怎么思考如果性能问题集中在等待事件上比如在某个索引块上等待特别久再上10046看具体的等待分布。这个流程走下来大多数SQL性能问题都能定位到根因而且每一步都有真实数据支撑不会陷入“我觉得应该走这个索引”的主观猜测里。