《收获，不止SQL优化》 - 获取执行计划的方法对比

旧城等待， 2022-03-01 09:42 179阅读 0赞

这是杂货铺的第450篇文章

曾经写过关于如何得到Oracle执行计划的文章，《[一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法][-]》，其中介绍了各种能得到SQL执行计划的方法，梁老师的书《收获，不止SQL优化》，对这几种方法，从获取步骤、优缺点、应用场景等方面，做了系统整理，可以比较学习，

<table style="width:554px;"> 
 <tbody> 
 <tr> 
 <td>方法</td> 
 <td>获取步骤</td> 
 <td>优点</td> 
 <td>缺点</td> 
 <td>应用场景</td> 
 </tr> 
 <tr> 
 <td>explain plan for</td> 
 <td>步骤1：explain plan for 要执行的SQL 步骤2：select * from table(dbms_xplan.display()); P.S. 为了显示方便，可以设置格式，例如： set long 10000 set pagesize 10000 set linesize 10000</td> 
 <td>无须真正执行，快捷方便</td> 
 <td>1. 没有输出运行时的相关统计信息(产生多少逻辑读，多少次递归调用，多少次物理读的情况)； 2. 无法判断处理了多少行； 3. 无法判断表被访问了多少次；</td> 
 <td>如果某SQL执行时间很长时间猜出结果或返回不了结果</td> 
 </tr> 
 <tr> 
 <td>set autotrace on</td> 
 <td>步骤1：set autotrace on 步骤2：在此处执行你的SQL</td> 
 <td>1. 可以输出运行时的相关统计信息(产生多少逻辑读，多少次递归调用，多少次物理读的情况)； 2. 虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出；</td> 
 <td>1. 必须要等到语句真正执行完毕后，才可以出结果； 2. 无法看到表被访问了多少次；</td> 
 <td>想粗略知道recursive calls递归调用次数，用这个方法，详细用10046 trace方法</td> 
 </tr> 
 <tr> 
 <td>statistics_level=all</td> 
 <td>步骤1：alter session set statistics_level=all； 步骤2：在此处执行你的SQL； 步骤3：select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));</td> 
 <td>1. 可以清晰地从STARTS得出表被访问多少次； 2.可以清晰地从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确； 3. 虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的数值；</td> 
 <td>1. 必须要等到语句真正执行完毕后，才可以出结果； 2. 无法控制输出记录展现与否，而autotrace和traceonly可以控制不将输出记录打屏； 3. 看不出递归调用的次数，看不出物理读的数值；</td> 
 <td>要想获取表被访问的次数，只能使用方法3</td> 
 </tr> 
 <tr> 
 <td>dbms_xplan.display_cursor</td> 
 <td>select * from table(dbms_xplan.display_cursor('&amp;sql_id'));，该方法是从共享池中得到</td> 
 <td>1. 知道sql_id立即可得到执行计划，和explain plan for一样无须执行； 2. 可以得到真实的执行计划；</td> 
 <td>1. 没有输出运行时的相关统计信息(产生多少逻辑读，多少次递归调用，多少次物理读的情况)； 2. 无法判断处理了多少行； 3. 无法判断表被访问了多少次；</td> 
 <td>观察某条SQL有多条执行计划的情况</td> 
 </tr> 
 <tr> 
 <td>事件10046 trace跟踪</td> 
 <td>步骤1： alter session set events '10046 trace name context forever, level 12';(开启跟踪) 步骤2：执行你的语句； 步骤3：alter session set events '10046 trace name context off';(关闭跟踪)； 步骤4：找到跟踪后产生的文件； 步骤5： tkprof trc文件 目标文件；</td> 
 <td>1. 可以看出SQL语句对应的等待事件； 2. 如果SQL语句中有函数调用，SQL中有SQL，都将会被列出，无处遁形； 3. 可以方便地看出处理的行数，产生的物理逻辑读； 4. 可以方便地看出解析时间和执行时间； 5. 可以跟踪整个程序包；</td> 
 <td>1. 步骤繁琐，比较麻烦； 2. 无法判断表被访问了多少次； 3. 执行计划中的谓词部分不能清晰地展现出来；</td> 
 <td>如果SQL中含函数，函数中又套SQL等，即存在多层调用，想准确分析只能用该方法</td> 
 </tr> 
 <tr> 
 <td>awrsqrpt.sql</td> 
 <td>步骤1： @?/rdbms/admin/awrsqrpt.sql； 步骤2：选择你要的断点(begin snap和end snap)； 步骤3：输入你的sql_id；</td> 
 <td>可以方便地看到多个执行计划；</td> 
 <td>获取的过程比较麻烦；</td> 
 <td>想观察某条SQL的多个执行计划用该方法</td> 
 </tr> 
 </tbody> 
</table>

为了获得最准确的执行计划，最重要的一点，就是这条SQL是否真正执行过，只有真正执行过，相应的执行计划才是最真实的，否则通过预估得到的，就会存在偏差的可能，

为了方便编辑，整理了图片格式，以及excel格式，各位可以下载，

**1. 图片格式**

https://github.com/bisal-liu/oracle/blob/master/explain/%E6%89%A7%E8%A1%8C.png

**2. excel格式**

https://github.com/bisal-liu/oracle/blob/master/explain/%E6%89%A7%E8%A1%8C.xlsx

《收获，不止SQL优化》历史文章：

《[《收获，不止SQL优化》 - 调优信息一键生成脚本学习][SQL_ -]》

10046的历史文章：

《[如何生成其他会话的10046？][10046]》

执行计划相关的历史文章：

《[返璞归真 - Oracle 9i的RBO执行计划案例][- Oracle 9i_RBO]》

《[Oracle CBO选错执行计划的一种场景][Oracle CBO]》

《[一次有意思的错选执行计划问题定位][Link 1]》

[-]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247483768&idx=1&sn=ed11fd689ce89c7fb588988fd4718e8b&chksm=ea81448bddf6cd9d2dc3ac9ba53ee685512773be6d97b6e314f451d7e46e63f78739bafd3d28&scene=21#wechat_redirect
[SQL_ -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247488420&idx=1&sn=ca3781cfdc87c5a290eb0beb649f5444&chksm=ea815657ddf6df419282bda2b1d0e1af2c5a1b52efaf4bf9710cd732b7f8d9f0570851fc17be&scene=21#wechat_redirect
[10046]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247485573&idx=1&sn=326699b1c843154c8f12c05a16bab838&chksm=ea814d76ddf6c46094b3dabd86845dbd0e6cdf840ffe73a5092495b8adba5efd3e6d11991e13&scene=21#wechat_redirect
[- Oracle 9i_RBO]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247486548&idx=1&sn=92379ff5a155ada64decab097738d355&chksm=ea8149a7ddf6c0b12ca3750bea725ef49fc7d524fedb9ed0352cc69142c795f0154596a9b536&scene=21#wechat_redirect
[Oracle CBO]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247485348&idx=1&sn=4fc3ee688e3ebc4f767bbc315f685e80&chksm=ea814257ddf6cb410c71c1bc0a6e6f160acad89a3d3b103774b65bd6b3f28e3fe33209e338fe&scene=21#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247483796&idx=1&sn=3bb50929387d42d1f3c34e6859ce5b8b&chksm=ea814467ddf6cd711d512911eae87931a33577d9113fcb1be8433e9912ad7a53010c322c9914&scene=21#wechat_redirect