oracle各种执行计划优缺点

一. 获取oracle执行计划的方法有6种，各自的优缺点如下，根据实际情况进行选择使用：

1. explain plan for 方式

步骤1：explain plan for 后跟着SQL语句
步骤2：select * from table(dbms_xplan.display());

优点： 1.不需要真正的去执行语句，快捷方便
缺点： 1.虽然快捷但是因为没有真正去运行，所以没有输出运行时的相关统计信息（逻辑读，递归调用，物理读）
2.不知道被处理的行数
3.不知道表被访问的次数

2. set autotrace on 方式
   步骤1：set autotrace on
   步骤2：sql语句

set autotrace有四种不同的模式

set autotrace on (得到执行计划，输出运行结果）
set autotrace traceonly (得到执行计划，不输出运行结果）
set autotrace traceonly explain (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分，如果是select查询则不会执行语句）
set autotrace traceonl statistics(不输出运行结果和执行计划部分，仅展现统计信息部分）

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

缺陷：1.必须要等到语句执行完毕后，才能出结果；
2.无法看到表被访问的次数。

3. statistics level=all 方式

步骤1：alter session set statistics_level=all（如果之前有将 set autotrace on，需要先将set autotrace off） ;
步骤2：执行SQL语句（如果在sql语句中加hint /+ gather_plan_statistics /，可以不用设置步骤一）
步骤3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Starts 是语句实际执行次数，E-Rows为执行计划预计行数，A-Rows为实际返回行数，A-Time为每一步实际执行的时间，Buffers为每一步实际执行的逻辑读，这几个目前比较有用

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

缺陷：1.必须要等到语句真正执行完毕后，才可以出结果。
2.记录必须得输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。
3.看不出递归调用的次数，看不出物理读的多少

4. 带入sql_id 方式

步骤1： select from table(dbms_xplan.display_cursor('&sql_id')); （该方法是从共享池里得到，这个语句必须得跑过一次）
另一方法： select from table(dbms_xplan.display_awr('&sq_id'));（这是awr性能视图里获取到的）

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

优点：1.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；
2.可以得到真实的执行计划。而方法1与2的则是预估

缺陷 1.没有输出运行时的相关统计信息（逻辑读，递归调用，物理读）；
2.无法判断是处理的行数；
3.无法判断表被访问的次数。

5. 10046TRACE 方式

步骤1：alter session set events '10046 trace name context forever,level 12';
步骤2：执行sql语句
步骤3：alter session set events '10046 trace name context off';
步骤4：找到跟踪后产生的文件

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

步骤5：tkprof trc文件路径 目标txt文件 sys=no sort=prsela,exeela,fchela

优点：1.可以看出SQL语句对应的等待事件
2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出。
3.可以方便的看出处理的行数，产生的物理逻辑读。
4.可以方便的看出解析时间和执行时间。
5.可以跟踪整个程序包

缺陷: 1.步骤繁琐，比较麻烦
2.无法判断表被访问了多少次。
3.执行计划中的条件语句不能清晰的展现出来。

6. awrsqrpt.sql 方式

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

优点：1.同样也可以获取到多条执行计划，并可在报表输出
2.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；
3.可以得到真实的执行计划。而方法1与2的则是预估

缺陷: 1.步骤繁琐，比较麻烦，还需要查snap的时间
2.没有输出运行时的相关统计信息（逻辑读，递归调用，物理读）；
3.无法判断是处理的行数；
4.无法判断表被访问的次数。

适用情况总结：

1.如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就用explain plan for或者set autotrace traceonly statistics（前提是select）；
2.跟踪某条SQL最简单的方法是explain plan for，其次就是set autotrace on（traceonly）；
3.如果想观察到某条SQL有多条执行计划的情况，只能通过dbms_xplan.display_cursor输入sql_id参数直接获取和查看awrsqrpt.sql；
4.如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能用10046 trace跟踪；
5.要想确保看到真实的执行计划，不能用explain plan for和set autotrace on；
6.要想获取表被访问的次数，只能使用statistics_level=all的方法；

二. 如何辨别低效的SQL：

有7个可以注意的地方

1.真实返回值与产生逻辑读的比例
一般而言，每获取一行开销5个以下的逻辑读是ok的。
如果用statitics_level=all获取执行计划，查看BUFFERS（逻辑读）/A-ROWS（获取行数）的比值，如果用autotrace，则查看consistent gets（逻辑读）/rows processed（获取行数）的比值

2.执行计划中评估准确的重要性
这很重要，错误的评估往往意味着低效的执行计划，此种必须要用statistics_level=all查看。
因此要查看预估执行行数E-Rows与实际执行行数A-Rows的比值，偏差较大时，很可能是收集直方图不准确导致的，需要重新再收集一次直方图。

3.发生类型转换需要进行关注查看
关注执行计划中的Predicate Information (identified by operation id)部分其中是否出现类型转换。
因为往往出现类型转换后，无法调用到索引，造成效率低下，在不清楚字段类型就给予取值时容易出现这种问题。

4.递归调用次数的查看
6种方法里只有autotrace可以查看递归调用次数，如果某一语句的递归调用次数非常大，比如一个几万行的表就出现了几万次的递归调用，那一定是有问题的。
详细问题就要进一步通过10046 trace来继续跟踪查看具体原因，个人经验来说，一般是表连接出现问题导致。

5.表访问次数的查看
6种方法里只有 statisitcs_level=all 的方式可以看出表访问次数（STARTS)
如果一个表被访问次数很多，很可能有问题，这时要看表是否存在连接，以及连接的类型，如果表访问这么多次一般是哈希或者排序连接，如果是NL连接那么一定是有问题的。

6.注意表真实访问的行数
这个往往是由于查询条件上未能优化所导致的，真实访问次数太大的时候可以仔细查看一下sql语句是否可以有优化的空间。
这点和第一点有异曲同工之处，查看下rows processed，如果计划中的A-Rows在开始阶段太高，很可能有优化的空间。
看下面的例子非常好：
select
from (select t1., rownum as rn from t1, t2 where t1.object_id = t2.id1) a
where a.rn >= 1
and a.rn <= 10;
与
select
from (select t1., rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=10) a
where a.rn >= 1;
让我对于取范围值的优化上有了一个新的思路，适当情况下可以在子查询中放入范围查询的部分条件，这样访问的真实行数会大幅度下降。
即执行计划中的（COUNT STOPKEY）这个关键字，体现了局部访问的算法。

7.谨慎观察是否发生排序
查看执行计划里是否存在SORT ORDER BY，以及统计信息中的sorts，特别是sorts（disk）
如果用statistics_level=all的方法查看，要看Used-Mem这项，xxxxK后的（0）与（1）分别代表没交换到磁盘与已交换到磁盘。
如存在是否必须，是否可以用走索引来避免。

三. 如何读懂输出执行计划，下面用联合型+单独型的方式，画出执行计划的访问草图供参考：

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 15 | | | |
| 1 | CONNECT BY WITH FILTERING | | 1 | | 14 |00:00:00.01 | 15 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 3 | NESTED LOOPS | | 4 | 2 | 13 |00:00:00.01 | 8 | | | |
| 4 | CONNECT BY PUMP | | 4 | | 14 |00:00:00.01 | 0 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 2 | 13 |00:00:00.01 | 8 | | | |
|* 6 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 2 | 13 |00:00:00.01 | 5 | | | |

访问草图如下：

     ---[5]---[6]