Oracle中六种获取执行计划的方法

提示

一、explain plan for

优点：

无需真正执行，快捷方便。

缺点：

sql并没有真正执行，从历史执行计划中读取.
没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）。
无法判断是处理了多少行。
无法判断表被访问了多少次。

用法：

方法1： explain plan for "SQL语句";
方法2： select * from table(dbms_xplan.display()); 

# 举例
set linesize 9999
set pagesize 9999

explain plan for
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(12,14);
select * from table(dbms_xplan.display());

二、set autotrace on

优点：

可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出。

缺点：

必须要等到语句真正执行完毕后，才可以出结果；
无法看到表被访问了多少次。

用法：

步骤1：set autotrace on 
步骤2：在此处执行你的SQL即可，后续自然会有结果输出

另，有如下几种方式：
set autotrace on                 (得到执行计划，输出运行结果）
set autotrace traceonly          (得到执行计划，不输出运行结果）
set autotrace traceonly explain  (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分）
set autotrace traceonl statistics(不输出运行结果和执行计划部分，仅展现统计信息部分）

# 举例：
set autotrace on 
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(12,13);

三、直接通过sql_id获取

优点：

知道sql_id可取得执行计划，同explain plan for一样无需执行。
可以得到真实的执行计划。

缺点：

没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）。
无法判断是处理了多少行。

用法：

步骤1： 
select  * from table(dbms_xplan.display_cursor('&sq_id')); （该方法是从shared pool里得到）
select  * from table(dbms_xplan.display_awr('&sq_id'));（该方法是从awr里得到）


2. 如果有多执行计划，可以用类似方法查出
    select * from table(dbms_xplan.display_cursor('sql_id',0));
    select * from table(dbms_xplan.display_cursor('sql_id',1));
    select * from table(dbms_xplan.display_cursor('sql_id'));

四、awrsqrpt.sql

步骤1：@?/rdbms/admin/awrsqrpt.sql
步骤2：选择你要的断点（begin snap 和end snap)
步骤3：输入你的sql_id  

五、10046 trace

优点：

可以看出SQL语句对应的等待事件
如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。
可以方便的看出处理的行数，产生的物理逻辑读。
可以方便的看出解析时间和执行时间。
可以跟踪整个程序包
更详细的信息。
会有等待事件的信息。
如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5

缺点：

步骤繁琐，比较麻烦
无法判断表被访问了多少次。
执行计划中的谓词部分不能清晰的展现出来。

用法：

步骤1：alter session set events '10046 trace name context  forever,level 12'; （开启跟踪）
      oradebug setmypid;
步骤2：执行你的语句
步骤3：alter session set events '10046 trace name context off';   （关闭跟踪）
步骤4：找到跟踪后产生的文件 oradebug tracefile_name
步骤5：tkprof  trc文件  目标文件  sys=no sort=prsela,exeela,fchela  （格式化命令）    

# 示例
set autotace off
alter session set statistics_level=typical;     
alter session set events '10046 trace name context  forever,level 12';
 
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(17,22);   
   
alter session set events '10046 trace name context off';   
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
      from v$mystat m,v$session s, v$process p
      where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
      (select t.INSTANCE
       FROM v$thread t,v$parameter v
       WHERE v.name='thread'
       AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
       (select value
       from v$parameter
       where name='user_dump_dest') d;
exit  
    
tkprof xxx_ora_34562.trc  xxx_ora_34562.txt  sys=no sort=prsela,exeela,fchela   

六、statistics_level=all

要想获取表被访问的次数，只能使用方法三。
执行计划中"表访问次数” 是关键指标，这只能靠方法3的方式获取。
一般推荐使用。

优点：

可以清晰的从STARTS得出表被访问多少。
可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。
虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的多少。

缺点：

必须要等到语句真正执行完毕后，才可以出结果。
无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。
看不出递归调用的次数，看不出物理读的多少（不过逻辑读才是重点）。

用法：

步骤1：alter session set statistics_level=all ;
步骤2：在此处执行你的SQL
步骤3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

注：
1. 如果你用 + gather_plan_statistics 的方法，可以省略步骤1，直接步骤2,3。

2. 关键字解读：
Starts为该sql执行的次数。
E-Rows为执行计划预计的行数。
A-Rows为实际返回的行数。
A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
Buffers为每一步实际执行的逻辑读或一致性读。
Reads为物理读。
OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的。
1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)
OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。
0/1/M 为最优/one-pass/multipass执行的次数。Used-Mem耗的内存  

set autotrace off          
alter session set statistics_level=all ;
SELECT  *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(12,15);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

适用场景

如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法一。
跟踪某条SQL最简单的方法是方法一，其次就是方法二。
如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法五。
要想确保看到真实的执行计划，不能用方法一和方法二。
要想获取表被访问的次数，只能使用方法三。

一、explain plan for​

二、set autotrace on​

三、直接通过sql_id获取​

四、awrsqrpt.sql​

五、10046 trace​

六、statistics_level=all​

适用场景​

一、explain plan for

二、set autotrace on

三、直接通过sql_id获取

四、awrsqrpt.sql

五、10046 trace

六、statistics_level=all

适用场景