关于explain plan的使用！

上一篇 / 下一篇 2007-03-26 00:00:00 / 个人分类：oracle

如何生成explain plan?

　　解答:运行utlxplan.sql. 建立plan 表

　　针对特定SQL语句，使用 explain plan set statement_id = 'tst1' into plan_table

　　运行utlxplp.sql 或 utlxpls.sql察看explain plan

ITPUB个人空间A4T:N5vfQ
EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.

你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.

NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.

ITPUB个人空间SZ4[.rq-EHy JD
译者按:

通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便.

举例:

ITPUB个人空间"tWR3{[!GFjx
SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE emp.deptno = dept.deptno

SQL> set autotrace traceonly /*traceonly 可以不显示执行结果*/

SQL> /

14 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

-wMefh$N,F#ow%`0Statistics

----------------------------------------------------------

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

ITPUB个人空间L2D7o4cDSnb*R
通过以上分析,可以得出实际的执行步骤是:

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)

注: 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们. ITPUB个人空间pz#DCx
----------------------------------------------------------------------------

x6TW@CA(wC0对于sql执行的小量高低.我们可以通过执行计划的信息基本上可以进行分析查看该SQL语句执行的时间.连接顺序及浪费的数据库资源等信息,从而判断该SQL语句执行的效率如何,下面就简单的介绍一下执行计划的使用ITPUB个人空间KM1wr!bE4p1A*Qc
2. Explain使用ITPUB个人空间:j-{P8J|cs
Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。
"s_i5i,kJ FF02.1. 安装
g Wv\/d L2T ?0要使用执行计划首先需要执行相应的脚本。ITPUB个人空间+`X8`Y#u^R
使用Explain工具需要创建Explain_plan表，这必须先进入相关应用表、视图和索引的所有者的帐户内。Oracle的介质中包含有执行此项工作的SQL源程序，例如：ITPUB个人空间6b8_/Ea;L
ORA_RDBMS: XPLAINPL.SQL (VMS) ITPUB个人空间g!M T8B\M
$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)ITPUB个人空间7O I#Z9a2r
该脚本后会生成一个表这个程序会创建一个名为plan_table的表，表结构如下：
2IQ y,`%u;R~0我们简单的介绍一下主要的字段含义：
\ L4[(^csg3Y0字段名字段类型含义
7r_ol0c_0STATEMENT_ID VARCHAR2(30) explain PLAN 语句中所指定的最优STATEMENT_ID 参数值, 如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID，那么此值会被设为NULL。 ITPUB个人空间&n7T}A&]
REMARKS VARCHAR2(80) 与被解释规划的各步骤相关联的注释最长可达80 字节
Ci/S7mg_9p"Kh0OPERATION VARCHAR2(30) 各步骤所执行内部操作的名称在某条语句所产生的第一行中该列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT
0KlNYP6Q0OPTIONS VARCHAR2(30) 对OPERATION 列中所描述操作的变种ITPUB个人空间b"c N+T-ve
OBJECT_NODE VARCHAR2(128) 用于访问对象的数据库链接database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序
H^N#I?8K$N8x0OBJECT_OWNER VARCHAR2(30) 对于包含有表或索引的架构schema 给出其所有者的名称ITPUB个人空间i"i[)kV2V*k A-y
OBJECT_NAME VARCHAR2(30) 表或索引的名称ITPUB个人空间&t*j0qB]9b'P2_^
OBJECT_INSTANCE INTEGER 根据对象出现在原始original 语句中的次序所给出的相应次序编号就原始的语句文本而论其处理顺序为自左至右自外向内景象扩张viewITPUB个人空间4].z:K(wu+op6zL
OBJECT_TYPE VARCHAR2(30) 用于提供对象描述性信息的修饰符例如索引的NON-UNIQUE
4d8n|/P\7y7_f9d0OPTIMIZER VARCHAR2(255) 当前优化程序的模式ITPUB个人空间v(O]z`\
ID INTEGER 分配给执行规划各步骤的编号ITPUB个人空间-xz7X pvZ!C_9h
PARENT_ID INTEGER 对ID 步骤的输出进行操作的下一个执行步骤的IDITPUB个人空间V Z!@[4L5[({eq{k
POSITION INTEGER 对于具有相同PARENT_ID 的步骤其相应的处理次序ITPUB个人空间rLc CKsG
COST INTEGER 根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值ITPUB个人空间5N/z7o(JC.}+Mk*u(OP
CARDINALITY INTEGER 根据基于开销的方法对操作所访问行数的估计值ITPUB个人空间N`s4A Q"t
BYTES INTEGER 根据基于开销的方法对操作所访问字节的估计

2.2. 使用ITPUB个人空间2n/g/{)E%d r"?
2.2.1. 常规使用ITPUB个人空间 G'A/h+iD`P8H4A-J5l
常规使用语法：ITPUB个人空间jDkA\xCz
explain PLAN [ SET STATEMENT_ID [=] <string literal> ] ITPUB个人空间}-t#N1p'E(D%DJ\u
[ INTO <table> ] ITPUB个人空间u6`d ` m_%p
FOR <sql>
L4E6j9nI Ytpj'?0其中：
$vx7h K7B%G g*[bC0STATEMENT_ID是一个唯一的字符串，把当前执行计划与存储在同一PLAN表中的其它执行计划区别开来。
6}&io_)e7[0TABLE_NAME是plan表名，它结构如前所示，你可以任意设定这个名称。
Bf$z@ W1R)sT0SQL_STATEMENT是真正的SQL语句。ITPUB个人空间ID:wLu.Rp(B T f7v
如：
)c*ZX yu@i(kwm0SQL> explain plan set statement_id='test1' for
'F n^k"pv:O0 2 SELECT a.soctermbegin,ITPUB个人空间,W!P0T2d? R
3 a.soctermend,ITPUB个人空间P0Ec#I4a
4 a.dealserialno,ITPUB个人空间Ly5pu&TA&o3RJ/Z
5 a.levydataid,
)Z-Fu"F_2`0 6 a.dealtotal,ITPUB个人空间qh`uW6rV1d
7 e.categoryitemcode,ITPUB个人空间*B5r,x)q%g\xQ@_
8 row_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRowITPUB个人空间![!AR0kEQe*g
9 FROM tb_soc_packdealdata a,
x@7yA0K| k3h010 tb_Lvy_TaxDataBillMap c,
AG U`W] O011 Tb_lvy_BillData d,ITPUB个人空间,|%Ls^-V6@
12 tb_soc_levydetaildata e
6J of}7Y-Z013 WHERE a.levydataid = c.datafrompointer(+)ITPUB个人空间6C%o0H,\M
14 AND c.billdataid = d.billdataid(+)
15 AND a.levydataid = e.levydataid
v9b$CYyd"s{016 AND a.packdealstatuscode = '10'ITPUB个人空间(N,dvp4g'q{|,y#L G8z
17 AND (a.datastatus '9' OR a.datastatus is NULL)
X7Q2n/W?-uR3Ek018 AND (d.billstatus IS NULL OR
Q|lDm019 (d.billstatus '2' AND d.billstatus '8'))ITPUB个人空间8h:|#WAUaX6u
20 AND a.Insurcode = '6010952'ITPUB个人空间%JJ4hx;rGz5R
21 ;ITPUB个人空间(y*UC`hf X
ExplainedITPUB个人空间 N9{uP&\mwDF?
执行下面语句就可以查看该语句执行的执行计划:
Mne)H.y(JS@0SQL> SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
o4Y:K/g7q\3x"A+jm9b0 2 FROM PLAN_TABLE a
jl8[/J;f4Xf#v"u0 3 WHERE STATEMENT_ID='test1'ITPUB个人空间`2D$Jx#],N(f~
4 ORDER BY Id;
l%Z N v!Bc^B0OPERATION OPTIONS OBJECT_NAME OBJECT_TYPEID PARENT_ID
'I5z3otLRn.Rb s0---------------- --------------------------------------------- ------------- ----------ITPUB个人空间+a%tmw(n~C
SELECT STATEMENT 0
m%QB oS!}GgJ0WINDOW SORT 1 0ITPUB个人空间\*`1z4[X){
FILTER 2 1ITPUB个人空间 uU*dvObfC!lk
NESTED LOOPS OUTER 3 2ITPUB个人空间#DP+r+Rv,]c
NESTED LOOPS OUTER 4 3ITPUB个人空间P$\Y7?`[3w
NESTED LOOPS 5 4ITPUB个人空间Aw#^z5P|&?
TABLE ACCESS FULL TB_SOC_PACKDEALDATA 6 5ITPUB个人空间L#h7T_9}r
TABLE ACCESS BY INDEX ROWID TB_SOC_LEVYDETAILDATA 7 5ITPUB个人空间Lhoy{ntp|1u
INDEX RANGE SCAN IND_DATAID_LEVSOC NON-UNIQUE 8 7
't#Esf$a;d.R0TABLE ACCESS BY INDEX ROWID TB_LVY_TAXDATABILLMAP 9 4
L Fi6Fz7T&@0INDEX RANGE SCAN TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10 9ITPUB个人空间%|5t&\A1f
TABLE ACCESS BY INDEX ROWID TB_LVY_BILLDATA 11 3
qV$aRX*u&dz)N Y0INDEX UNIQUE SCAN TBLVYBILLDATA_BILLDATAID UNIQUE
rB@`a Qnw8Z*p!`02.2.2. 自动显示使用ITPUB个人空间@i2KV7j/F?[(_&S(s
在SQLPLUS中自动跟踪显示执行计划及相关信息ITPUB个人空间P7Her6WfI`
SQL>set timing on --显示执行时间
6ay6K4L9\0g6Z1X0SQL>set autorace on �C显示执行计划
4n{:Z*vB.y0SQL>set autorace on �C显示执行计划
n(i2o6v ez(C6T0SQL>set autotrace traceonly �C只显示执行计划即不显示查询出来的数据
KVha R He0设置完毕后执行SQL语句就会显示执行计划信息及相应的统计信息(需要设置显示该选项)ITPUB个人空间@{k2d+`/FSJk~
SQL> select nvl(sum(t.taxdue), 0)ITPUB个人空间b m9U4Z+q5{3q,[+I8FC
2 from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b
+d[-y(g!R0]W0 3 where a.dossiercode = 'SB02041108'ITPUB个人空间 i?MD(]
4 and a.pages = 123
[sGW%i0 5 and a.remarkid = b.remarkid
#k\,Ie [XP0 6 AND A.REMARKID IS NOT NULL
L.yL/?kWh0 7 and b.declaredocid = t.declaredocid;
tX M J.P@D5vwq0NVL(SUM(T.TAXDUE),0)ITPUB个人空间rQ[ZmV)|
--------------------ITPUB个人空间 ga$N)IPS4zno_
0ITPUB个人空间mf V;OH
已用时间: 00: 00: 04.07ITPUB个人空间+dLf7p%L.s^
Execution Plan
8i G UmF0----------------------------------------------------------ITPUB个人空间:h |aK-Z'm[
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=110)ITPUB个人空间3F8M!?hl#l5z
1 0 SORT (AGGREGATE)ITPUB个人空间7Z-DOKf
2 1 NESTED LOOPS (Cost=6 Card=1 Bytes=110)
^.X%i])I9Gx0Lk+X0 3 2 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=74)
,?Y {X+`2|&Y(O4g7D:W0 4 3 TABLE ACCESS (FULL) OF 'TB_LVY_SBZS100' (Cost=2 Card =1 Bytes=31)ITPUB个人空间o6~U,o4O3IAo
5 3 BUFFER (SORT) (Cost=2 Card=1 Bytes=43)ITPUB个人空间6_8WG)sWv
6 5 TABLE ACCESS (FULL) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=43)ITPUB个人空间 op?;TTKG+N[
7 2 TABLE ACCESS (BY INDEX ROWID) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=36)ITPUB个人空间*w!{D l7hH
8 7 INDEX (UNIQUE SCAN) OF 'TBLVYDECLAREDOC_DECLAREDOCID' (UNIQUE)
*M%y&xGd#s-pX0Statistics
b`tE X*aRc0----------------------------------------------------------ITPUB个人空间}#Ot qc
0 recursive calls --循环递归次数
_I)^ ?/w2O(}0 0 db block gets―请求的数据块在buffer能满足的个数
;w+B/_scNa0 6675 consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中ITPUB个人空间/[K]@+f!G
45 physical reads �C从磁盘读到Buffer Cache数据块数量
"`c(F"_Sq0w$ZO!K0 0 redo size �C产生的redo日志大小
LG'I kL0 217 bytes sent via SQL*Net to clientITPUB个人空间?+o^Ee5[(s
276 bytes received via SQL*Net from client
Uza_#D[_0 2 SQL*Net roundtrips to/from clientITPUB个人空间zd0F^5r
1 sorts (memory)ITPUB个人空间m8h ^y!Tb7{&iH2G
0 sorts (disk)ITPUB个人空间x:p5L ^,S_K5G
1 rows processed
.b']tWs0SQL>ITPUB个人空间W:I1g$aD8tqSY
如果6675 consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中
s,H-tS1S7f![;dP045 physical reads �C从磁盘读到Buffer Cache数据块数量ITPUB个人空间*_.KU-Xo
的数值比较小则该语句对对数据库的性能比较高。

2.2.3. PL/SQL和TOAD中使用
+Qe-z^"H!f2f9e0如果在PL/SQL中使用选择要查询语句显示执行计划，则只需要SQL WINDOWS 窗口里面输入要查询的SQL语句，然后选择按键F5或者在菜单TOOLS�D�D>Explain Plan 菜单按键就可以在执行计划窗口查看该语句的执行计划。
Z#ZJ \r0在TOAD语句中在执行当前的SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划信息。ITPUB个人空间t&XKd`4r
2.3. 限制
H7YoEkd"qxV0虽然任何SQL语句都可以用explain解释，但对于没有查询的INSERT，UPDATE，DELETE操作来说，这个工具并没有太大的用处。没有子查询的INSERT操作不会创建执行计划，但没有WHERE子句或子查询的UPDATE和DELETE操作会创建执行计划，因为这些操作必须先找出所要的记录。 ITPUB个人空间.EW%_^KY"t W
另外，如果你在SQL语句中使用其它类型如sequence等，explain也能揭示它的用法。ITPUB个人空间t#U#U5CLq
explain真正的唯一的限制是用户不能去解释其它用户的表，视图，索引或其它类型，用户必须是所有被解释事物的所有者，如果不是所有者而只有select权限，explain会返回一个错误。