下面我們去分析一下索引路徑的執行操作情況。

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

首先，我們檢查一下處理過的跟蹤檔案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

我們先檢查處理過的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行為方式和核心原理，進而可以更好的進行最佳化調優工作。

利用10046事件研究三種資料表訪問方式（下）

相關文章