對Oracle資料庫而言，資料表的訪問一共有三種方式，分別為：table access by rowid、full table scan(FTS)和table access by index。

三種訪問方式是出現在Oracle SQL的執行計劃中，作為最佳化器產物並且最後進行執行的動作操作。

那麼，從微觀角度看，三種訪問方式的具體形態如何呢？本文使用10046這個基本工具，跟蹤執行過程，歸納操作特點。首先，我們還是進行環境準備。

1、環境準備

我們使用Oracle 11gR2進行試驗。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

構建實驗資料表T，同時在object_id列構建索引。為了保證實驗效果，我們構建一張百條記錄資料表即可。

SQL> create table t as select * from dba_objects where wner='SCOTT';

Table created

SQL> create index idx_t_id on t(object_id);

Index created

SQL> insert into t select * from dba_objects where wner='XDB';

844 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

--資料表和索引段的object_id編號，注意，後面會用到！！

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 OBJECT_ID, data_object_id from dba_objects where wner='SYS' and object_name='T';

OBJECT_ID DATA_OBJECT_ID

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

75535 75535

此時，從segment段空間角度，看資料表和其索引的形態如下：

--資料表T；

SQL> select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where wner='SYS' and segment_name='T';

HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS

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

1 90632 131072 16 2

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where wner='SYS' and segment_name='T';

EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS

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

0 1 90632 65536 8

1 1 90648 65536 8

--索引段

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

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where wner='SYS' and segment_name='IDX_T_ID';

EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS

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

0 1 90640 65536 8

上面結果中有一些需要關注，在下面實驗中反覆使用：

對資料表T而言，Oracle為了容納資料，一共分配了2個分割槽，16個資料塊。頭塊位置為（file=1, block=90632）。兩個分割槽的頭塊分別為（file=1, block=90632）和（file=1, block=90648），每個分割槽連續8個資料塊。

對索引段而言，頭塊為（file=1,block=90640）。分配了一個分割槽，8個資料塊。

做好上面的資訊準備之後，我們就開始實驗。

2、FTS全表掃描動作實驗

FTS是最基本的oracle資料表訪問動作之一。進行FTS的時候，Oracle會檢索所有高水位線HWM以下的資料塊內容。那麼，具體是如何呢？

注意：本篇所有select動作前，都要保證shared pool和buffer cache清空。這樣做是為了保證實驗效果。

SQL> alter system flush buffer_cache;

系統已更改。

SQL> alter system flush shared_pool;

系統已更改。

使用10046事件對select進行跟蹤，獲取跟蹤檔案。

--11g中檢視，可以方便獲取到跟蹤檔案資訊；

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

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

/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_5969.trc

--跟蹤會話

SQL> alter session set events '10046 trace name context forever, level 12';

會話已更改。

SQL> select count(*) from t;

COUNT(*)

----------

858

SQL> alter session set events '10046 trace name context off';

會話已更改。

到指定目錄中，確定跟蹤檔案。

--在伺服器本地；

[root@oracle11g trace]# pwd

/u01/diag/rdbms/wilson/wilson/trace

[root@oracle11g trace]# ls -l | grep 5969

-rw-r----- 1 oracle oinstall 2705 Jan 15 2011 wilson_j004_5969.trc

-rw-r----- 1 oracle oinstall 195 Jan 15 2011 wilson_j004_5969.trm

-rw-r----- 1 oracle oinstall 16068 May 26 20:42 wilson_ora_5969.trc

-rw-r----- 1 oracle oinstall 117 May 26 20:42 wilson_ora_5969.trm

--使用tkprof命令處理raw trace file；

[root@oracle11g trace]# tkprof wilson_ora_5969.trc res1.txt

TKPROF: Release 11.2.0.1.0 - Development on Sat May 26 20:35:25 2012

[root@oracle11g trace]# ls -l | grep res1

-rw-r--r-- 1 root root 5220 May 26 20:35 res1.txt

首先，我們檢查res1.txt中處理過的資料檔案。注意，由於我們事先清理過shared pool和buffer cache，在select過程中有很多資料字典相關的select操作，如對obj$，這些recursive call是內部生成的語句。我們只需要關注目標select的資訊。

*************************************************************************

SQL ID: cyzznbykb509s

Plan Hash: 2966233522

select count(*)

from

t

call count cpu elapsed disk query current rows

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

Parse 1 0.02 0.02 4 27 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 16 18 0 1

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

total 4 0.02 0.02 20 45 0 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

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

1 SORT AGGREGATE (cr=18 pr=16 pw=0 time=0 us)

858 TABLE ACCESS FULL T (cr=18 pr=16 pw=0 time=2815 us cost=6 size=0 card=858)

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

db file scattered read 2 0.00 0.00

SQL*Net message from client 2 0.00 0.00

********************************************************************************

注意上面程式碼片段中標紅的部分，首先執行計劃中反映該語句的執行計劃是進行FTS。其次，在跟蹤語句的wait events上，我們看到了可能與FTS實際動作有關的兩個wait events，分別為“db file sequential read”和“db file scattered read”。在標紅部分，我們只能看到分別發生了1次和2次。

至於說具體兩個等待的動作引數取值，我們只能靠分析trace raw file的片段來獲取得知。

=====================

PARSING IN CURSOR #1 len=22 dep=0 uid=0 ct=3 lid=0 tim=1338036170287069 hv=2763161912 ad='2fab988c' sqlid='cyzznbykb509s'

select count(*) from t

END OF STMT

PARSE #1:c=23997,e=23800,p=4,cr=27,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=1338036170287043

EXEC #1:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1338036170287120

WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1338036170287123

WAIT #1: nam='db file sequential read' ela= 341 file#=1 block#=90632 blocks=1 obj#=75535 tim=1338036170287464

WAIT #1: nam='db file scattered read' ela= 400 file#=1 block#=90633 blocks=7 obj#=75535 tim=1338036170288322

WAIT #1: nam='db file scattered read' ela= 63 file#=1 block#=90648 blocks=8 obj#=75535 tim=1338036170289789

FETCH #1:c=3999,e=3658,p=16,cr=18,cu=0,mis=0,r=1,dep=0,og=1,plh=2966233522,tim=1338036170290781

STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=18 pr=16 pw=0 time=0 us)'

STAT #1 id=2 cnt=858 pid=1 pos=1 bj=75535 p='TABLE ACCESS FULL T (cr=18 pr=16 pw=0 time=2815 us cost=6 size=0 card=858)'

WAIT #1: nam='SQL*Net message from client' ela= 866 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170291781

FETCH #1:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2966233522,tim=1338036170291841

WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170291887

WAIT #1: nam='SQL*Net message from client' ela= 1373 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170293290

CLOSE #1:c=0,e=20,dep=0,type=0,tim=1338036170293362

WAIT #0: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036170293750

*** 2012-05-26 20:42:55.857

WAIT #0: nam='SQL*Net message from client' ela= 5563757 driver id=1413697536 #bytes=1 p3=0 obj#=75535 tim=1338036175857537

=====================

上面的sql_id，表明了我們擷取的片段正是我們的目標SQL。標紅的三句wait event，也正是我們希望研究的FTS動作模式。下面我們逐句進行分析：

首先是一次的“db file sequential read”，這個事件對於熟悉OWI和經常檢視AWR的朋友並不陌生。一些教條的說法是：該事件經常伴隨在索引路徑訪問方式。但是，在當前我們的FTS中，同樣看到了他的身影。這個事件的三個引數分別為檔案編號、起始頭編號和資料塊數量。下面片段為：

WAIT #1: nam='db file sequential read' ela= 341 file#=1 block#=90632 blocks=1 obj#=75535 tim=1338036170287464

其中，ela表示elapse time，obj#為物件編號，tim為全域性時間序號。物件75535就是資料表T。對應的讀資料塊為（file#=1, block#=90632,blocknum=1）。注意，這個資料塊就是資料表的頭塊。資料頭塊中間包括該資料段分割槽的資訊、高水位線位置。讀頭塊的意義在於，server process據此可以獲取所有的檢索路徑。

下面出現了兩次的db file scatted read動作。具體我們觀察細節。

WAIT #1: nam='db file scattered read' ela= 400 file#=1 block#=90633 blocks=7 obj#=75535 tim=1338036170288322

WAIT #1: nam='db file scattered read' ela= 63 file#=1 block#=90648 blocks=8 obj#=75535 tim=1338036170289789

兩次的scatted read，分別從（file=1, block=90633, blocks=7）和（file=1,block=90648, blocks=8）入手，進行了兩次讀動作。兩次讀動作涉及的物件同樣是資料表T。我們注意，第一個讀動作是從extent=0頭塊的第二個資料塊開始讀的，按照一個分割槽8個資料塊的分佈，該動作相當於將第一個分割槽除了頭塊之外所有塊讀了一遍。第二個讀動作對應的就是extent=1的八個資料塊，注意雖然blocks=8，但是從ela=63的資訊上，我們猜測並沒有完全讀完八個資料塊，而是讀到了HWM位置。

如果資料表包括成千上萬個extents，那麼db scatted read動作也應該會出現成千上萬次。筆者竊以為這就是流言中“db scatted read”與FTS相對應的來源了。

從上面的分析，我們可以總結FTS（全表掃描）的動作特點：

ü Oracle首先會去從資料字典中，獲取到這個資料表物件的段頭資訊；

ü 無論資料表多大或者多小，Server Process會先去訪問段頭塊，從其中獲取到分割槽列表資訊和HWM資訊。此時Server Process對應的事件為“db sequential read”；

ü 之後，Server Process會按照一個分割槽的順序，以“db scatted read”的動作訪問HWM下所有的資料塊。

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

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

相關文章