下面我们去分析一下索引路径的执行操作情况。
3、索引路径执行信息
我们借助在object_id列上添加的索引，构造一个索引路径执行计划。我们同样适用10046事件进行跟踪。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
------------------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_6063.trc
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select * from t where object_id=75381;
OWNER
------------------------------------------------------------
OBJECT_NAME
------------------------------------------------------------------------------
（结果集合省略……）
SQL> alter session set events '10046 trace name context off';
会话已更改。
我们同样进行跟踪文件处理。
[root@oracle11g trace]# ls -l | grep 6063
-rw-r----- 1 oracle oinstall 30869 May 26 20:56 wilson_ora_6063.trc
-rw-r----- 1 oracle oinstall 161 May 26 20:56 wilson_ora_6063.trm
[root@oracle11g trace]# tkprof wilson_ora_6063.trc res2.txt
TKPROF: Release 11.2.0.1.0 - Development on Sat May 26 20:57:50 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
首先，我们检查一下处理过的跟踪文件res2.txt。其中，我们发现了如下片段。
***************************************************************
SQL ID: 1dsg0anukjddx
Plan Hash: 514881935
select *
from t where object_id=75381
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 9 72 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 3 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.05 12 76 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=3 pw=0 time=0 us cost=2 size=90 card=1)
1 INDEX RANGE SCAN IDX_T_ID (cr=3 pr=2 pw=0 time=0 us cost=1 size=0 card=1)(object id 75536)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 3 0.00 0.00
SQL*Net message from client 2 0.01 0.02
**************************************************************************
从执行计划上，我们看到了索引路径，并且存在三次的db file sequential real。下面是相应的trace raw file片段。
=====================
PARSING IN CURSOR #3 len=37 dep=0 uid=0 ct=3 lid=0 tim=1338036989520646 hv=891860413 ad='31c9bdc4' sqlid='1dsg0anukjddx'
select * from t where object_id=75381
END OF STMT
PARSE #3:c=48992,e=49128,p=9,cr=72,cu=0,mis=1,r=0,dep=0,og=1,plh=514881935,tim=1338036989520644
EXEC #3:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=514881935,tim=1338036989521534
WAIT #3: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1338036989521590
WAIT #3: nam='db file sequential read' ela= 282 file#=1 block#=90641 blocks=1 obj#=75536 tim=1338036989521930
WAIT #3: nam='db file sequential read' ela= 458 file#=1 block#=90644 blocks=1 obj#=75536 tim=1338036989522485
WAIT #3: nam='db file sequential read' ela= 60 file#=1 block#=90633 blocks=1 obj#=75535 tim=1338036989522629
FETCH #3:c=1000,e=997,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=514881935,tim=1338036989522644
WAIT #3: nam='SQL*Net message from client' ela= 11873 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036989534519
FETCH #3:c=0,e=29,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=514881935,tim=1338036989534638
STAT #3 id=1 cnt=1 pid=0 pos=1 bj=75535 p='TABLE ACCESS BY INDEX ROWID T (cr=4 pr=3 pw=0 time=0 us cost=2 size=90 card=1)'
STAT #3 id=2 cnt=1 pid=1 pos=1 bj=75536 p='INDEX RANGE SCAN IDX_T_ID (cr=3 pr=2 pw=0 time=0 us cost=1 size=0 card=1)'
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036989534653
WAIT #3: nam='SQL*Net message from client' ela= 8467 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036989543122
CLOSE #3:c=0,e=42,dep=0,type=0,tim=1338036989543528
WAIT #0: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036989543603
*** 2012-05-26 20:56:53.216
WAIT #0: nam='SQL*Net message from client' ela= 23673169 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338037013216802
=====================
我们的确看到了三次的“db file sequential read”，索引路径中出现该事件的比例还是较多的。下面我们逐句进行分析：
首先一次的db file sequential read，对数据块（file=1,block=90641）进行检索。对象为75536。
WAIT #3: nam='db file sequential read' ela= 282 file#=1 block#=90641 blocks=1 obj#=75536 tim=1338036989521930
SQL> select OBJECT_ID, data_object_id from dba_objects where wner='SYS' and object_name='IDX_T_ID';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
75536 75536
--索引段
SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SYS' and segment_name='IDX_T_ID';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ ---------- ---------- ----------
1 90640 65536 8 1
从上面的数据字典信息，可以看到第一个read的动作是索引段的段头块的后一个块。对索引结构了解的朋友们可以知道，索引段头块后一个数据块就是索引的根节点所在数据块。这样，通过定位就可以一下找到索引的根节点，进行遍历。
接下来，Server Process同样会去访问索引段的90644数据块。我们猜测也可以知道，这个块就是目标叶子节点所在的数据块。这样就可以定位到结果所在数据行的rowid信息。
WAIT #3: nam='db file sequential read' ela= 458 file#=1 block#=90644 blocks=1 obj#=75536 tim=1338036989522485
最后就好理解了，第三次db file sequential read就直接获取数据行好了。
WAIT #3: nam='db file sequential read' ela= 60 file#=1 block#=90633 blocks=1 obj#=75535 tim=1338036989522629
经过上面的分析，我们可以知道Server Process在处理索引路径时的行为。
ü 首先从数据字典中，获取到索引的头块位置信息。但是，Server Process并不去读取头块，而是读取头块的后一个数据块，这就是索引树的根节点；
ü 定位根节点之后，就按照分支节点、叶子节点的顺序找到符合条件结果叶子节点。进而获取到rowid列表。
ü 最后根据rowid列表，可以直接定位到数据块位置；
注意，在索引路径中，都是db file sequential read动作。
最后我们分析一下号称最快定位数据的rowid方式。
4、rowid访问方式
Rowid是Oracle内部的物理地址。对堆表（heap table）而言，rowid是基本不会发生变化的。我们依然是采用10046事件方式，对rowid动作进行检验。
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
-----------------------------------------------------------------------------
/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_6196.trc
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> alter session set events '10046 trace name context forever, level 12';
会话已更改。
SQL> select * from t where rowid='AAAScPAABAAAWIJAAM';
OWNER
------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
（篇幅原因，省略部分……）
SQL> alter session set events '10046 trace name context off';
会话已更改。
处理trace raw file。
[root@oracle11g trace]# ls -l | grep 6196
-rw-r----- 1 oracle oinstall 31312 May 26 21:10 wilson_ora_6196.trc
-rw-r----- 1 oracle oinstall 151 May 26 21:10 wilson_ora_6196.trm
[root@oracle11g trace]# tkprof wilson_ora_6196.trc res3.txt
TKPROF: Release 11.2.0.1.0 - Development on Sat May 26 21:11:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
我们先检查处理过的res3.txt文件。
*********************************************************
SQL ID: 1zwq3yahqajra
Plan Hash: 3207308387
select *
from
t where rowid='AAAScPAABAAAWIJAAM'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 6 74 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.04 7 75 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID T (cr=1 pr=1 pw=0 time=0 us cost=1 size=90 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.00 0.00
SQL*Net message from client 2 0.00 0.00
************************************************************************
执行计划是table access by rowid，说明执行路径的确是rowid。在其中，我们只看到了一次的db file sequential read。在trace raw file中，我们确定如此：
=====================
PARSING IN CURSOR #2 len=48 dep=0 uid=0 ct=3 lid=0 tim=1338037828092715 hv=2707769066 ad='3857b96c' sqlid='1zwq3yahqajra'
select * from t where rowid='AAAScPAABAAAWIJAAM'
END OF STMT
PARSE #2:c=39993,e=40057,p=6,cr=74,cu=0,mis=1,r=0,dep=0,og=1,plh=3207308387,tim=1338037828092711
EXEC #2:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3207308387,tim=1338037828092840
WAIT #2: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1338037828092892
WAIT #2: nam='db file sequential read' ela= 493 file#=1 block#=90633 blocks=1 obj#=75535 tim=1338037828093519
FETCH #2:c=1000,e=652,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3207308387,tim=1338037828093602
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=75535 p='TABLE ACCESS BY USER ROWID T (cr=1 pr=1 pw=0 time=0 us cost=1 size=90 card=1)'
WAIT #2: nam='SQL*Net message from client' ela= 859 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338037828094613
FETCH #2:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3207308387,tim=1338037828094668
WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338037828094713
WAIT #2: nam='SQL*Net message from client' ela= 7853 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338037828102594
CLOSE #2:c=0,e=23,dep=0,type=0,tim=1338037828102717
WAIT #0: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338037828102788
*** 2012-05-26 21:10:36.532
WAIT #0: nam='SQL*Net message from client' ela= 8429696 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338037836532514
=====================
从参数上看，我们看到db file sequential read动作只去访问了（file=1, block=90633）一个数据块。解析我们条件语句中的rowid取值，可以发现对应。
SQL> select dbms_rowid.rowid_relative_fno('AAAScPAABAAAWIJAAM') fno, dbms_rowid.rowid_block_number('AAAScPAABAAAWIJAAM') blocknum from dual;
FNO BLOCKNUM
---------- ----------
1 90633
由此，如果直接是rowid方式，Oracle Server Process会直接根据rowid的取值将结果数据块获取到。
5、总结
Oracle数据表访问的几种方式，是我们最常用的执行计划动作。深刻了解细节，有助于我们进一步分析Oracle行为方式和内核原理，进而可以更好的进行优化调优工作。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-731403/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-731403/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
