oracle 执行计划耗时,oracle各种执行计划优缺点

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

explain plan for 方式

步骤1：explain plan for 后跟着SQL语句

步骤2：select * from table(dbms_xplan.display());

优点： 1.不需要真正的去执行语句，快捷方便

缺点： 1.虽然快捷但是因为没有真正去运行，所以没有输出运行时的相关统计信息(逻辑读，递归调用，物理读)

2.不知道被处理的行数

3.不知道表被访问的次数

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.无法看到表被访问的次数。

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.看不出递归调用的次数，看不出物理读的多少

带入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.无法判断表被访问的次数。

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.执行计划中的条件语句不能清晰的展现出来。

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 | | | |

访问草图如下：

[1]---[2]

---[3]---[4]

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

oracle各种执行计划优缺点

标签：输入   sys   时间   isp   多次   ddr   参数   _id   最简

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://blog.51cto.com/1937519/2309730