上篇（http://space.itpub.net/17203031/viewspace-751979）中，我們看到了index full scan和index fast full scan在執行計劃生成過程中的差異，以及使用場景的不同。本篇中我們繼續討論兩者的差異。

3、Index Full Scan分析

Access Path實際上影響到Oracle查詢資料的動作方式。我們使用10046診斷事件，可以發現實際的情況。

--定位跟蹤檔案

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

VALUE

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

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

Executed in 0.015 seconds

SQL> alter system flush shared_pool;

System altered

Executed in 0.156 seconds

SQL> alter system flush buffer_cache;

System altered

Executed in 0.078 seconds

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

Session altered

Executed in 0.047 seconds

SQL> select /*+index(t_big)*/count(*) from t_big;

COUNT(*)

----------

72689

Executed in 0.063 seconds

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

Session altered

Executed in 0 seconds

對生成的跟蹤檔案進行處理。

E:\Test>tkprof wilson_ora_3613.trc

output = res_index.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期一 12月 31 13:05:37 2012

從處理的結果看，執行計劃和對應的操作計數如下：

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

SQL ID: 3wy08j5ctmca6

Plan Hash: 2587926039

select /*+index(t_big)*/count(*)

from

t_big

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.02 0.02 162 162 0 1

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

total 3 0.03 0.02 162 162 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=162 pr=162 pw=0 time=0 us)

72689 INDEX FULL SCAN IDX_T_BIG_ID (cr=162 pr=162 pw=0 time=133218 us cost=162 size=0 card=72689)(object id 76985)

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

SQL*Net message from client 2 0.02 0.02

db file sequential read 162 0.00 0.01

發現，index full scan的核心事件“db file sequential read”，是典型的“單塊讀”21:09。在全部過程中一共執行了162次。詳細我們參考未處理的trace檔案片段。

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

PARSING IN CURSOR #15 len=45 dep=0 uid=0 ct=3 lid=0 tim=1356958610920076 hv=1503244614 ad='4d03d42c' sqlid='3wy08j5ctmca6'

select /*+index(t_big)*/count(*) from t_big

END OF STMT

PARSE #15:c=15998,e=15329,p=7,cr=28,cu=4,mis=1,r=0,dep=0,og=1,plh=2587926039,tim=1356958610920073

EXEC #15:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2587926039,tim=1356958610920201

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

WAIT #15: nam='SQL*Net message from client' ela= 497 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1356958610920797

WAIT #15: nam='db file sequential read' ela= 109 file#=1 block#=90649 blocks=1 obj#=76985 tim=1356958610920983

WAIT #15: nam='db file sequential read' ela= 95 file#=1 block#=90650 blocks=1 obj#=76985 tim=1356958610921152

（篇幅原因，省略部分…….）

WAIT #15: nam='db file sequential read' ela= 56 file#=1 block#=90781 blocks=1 obj#=76985 tim=1356958610944439

WAIT #15: nam='db file sequential read' ela= 80 file#=1 block#=90782 blocks=1 obj#=76985 tim=1356958610944598

WAIT #15: nam='db file sequential read' ela= 75 file#=1 block#=90783 blocks=1 obj#=76985 tim=1356958610944751

WAIT #15: nam='db file sequential read' ela= 71 file#=1 block#=90784 blocks=1 obj#=76985 tim=1356958610944897

WAIT #15: nam='db file sequential read' ela= 78 file#=1 block#=90785 blocks=1 obj#=76985 tim=1356958610945050

WAIT #15: nam='db file sequential read' ela= 72 file#=1 block#=90786 blocks=1 obj#=76985 tim=1356958610945197

WAIT #15: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=76985 tim=1356958610945258

FETCH #15:c=23996,e=24438,p=162,cr=162,cu=0,mis=0,r=1,dep=0,og=1,plh=2587926039,tim=1356958610945288

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

STAT #15 id=2 cnt=72689 pid=1 pos=1 bj=76985 p='INDEX FULL SCAN IDX_T_BIG_ID (cr=162 pr=162 pw=0 time=133218 us cost=162 size=0 card=72689)'

WAIT #15: nam='SQL*Net message from client' ela= 28512 driver id=1413697536 #bytes=1 p3=0 obj#=76985 tim=1356958610973915

CLOSE #15:c=0,e=29,dep=0,type=0,tim=1356958610974056

Obj#為76985的物件為idx_t_big_id，一系列（162次）的sequential read動作都是對這個物件進行的。我們首先分析下第一個sequential read動作。

WAIT #15: nam='db file sequential read' ela= 109 file#=1 block#=90649 blocks=1 obj#=76985 tim=1356958610920983

這個動作讀的是1號檔案的90649號資料塊。這個資料塊是什麼呢？我們檢視索引段的基本資訊。

SQL> select HEADER_FILE, HEADER_BLOCK from dba_segments where wner='SYS' and segment_name='IDX_T_BIG_ID';

HEADER_FILE HEADER_BLOCK

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

1 90648

Executed in 0.187 seconds

Sequential read所讀的90649塊，正好是索引段頭塊後面的第一個塊。對Oracle索引來說，這個塊就是根節點儲存的資料塊。

在之後的的161個讀動作中，每次只讀了一個資料塊，雖然很多資料塊都是連續的，而且161正好是索引葉子節點的個數。所以，這個161動作其實就是Oracle利用葉子節點之間的前後連結關係，水平掃描的過程。

附加extent分配關係。

SQL> select extent_id, block_id, blocks from dba_extents where wner='SYS' and segment_name='IDX_T_BIG_ID';

EXTENT_ID BLOCK_ID BLOCKS

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

0 90648 8

1 90656 8

2 90664 8

3 90672 8

4 90680 8

5 90688 8

6 90696 8

7 90704 8

8 90712 8

9 90720 8

10 90728 8

11 90736 8

12 90744 8

13 91008 8

14 91016 8

15 91024 8

16 90752 128

17 rows selected

Executed in 0.312 seconds

綜合上述的情況，我們可以推測出Oracle進行Index Full Scan的全過程：首先透過資料字典定位索引段頭塊位置。之後從後面一個塊（也就是根節點）開始，向下遍歷到最左邊的葉子節點，利用葉子節點之間的連結掃描所有的葉子塊。這個過程同傳統的index操作方式是一致的，進行的都是精確定位的單塊讀操作。

Index Fast Full Scan有什麼差別呢？我們用相同的方法進行分析。

4、Index Fast Full Scan分析

下面我們來分析一下Index Fast Full Scan操作。依然是使用10046診斷事件進行分析。

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

Session altered

Executed in 0 seconds

SQL> select count(*) from t_big;

COUNT(*)

----------

72689

Executed in 0.047 seconds

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

Session altered

Executed in 0 seconds

使用tkprof進行分析。

E:\Test>tkprof wilson_ora_3613.trc

output = res_index.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期一 12月 31 13:05:37 2012

從分析過的結果片段中，我們可以發現在使用index fast full scan的過程中，Oracle選擇了另一種操作方式。

SQL ID: 7x3ug7kus9zvs

Plan Hash: 2892922722

select count(*)

from

t_big

call count cpu elapsed disk query current rows

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

Parse 2 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 2 0.02 0.02 164 338 0 2

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

total 6 0.02 0.02 164 338 0 2

Misses in library cache during parse: 2

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

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

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

72689 INDEX FAST FULL SCAN IDX_T_BIG_ID (cr=169 pr=1 pw=0 time=72432 us cost=43 size=0 card=72689)(object id 76985)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 4 0.00 0.00

SQL*Net message from client 4 0.02 0.04

db file sequential read 2 0.00 0.00

db file scattered read 17 0.00 0.00

原有的162次點讀消失了，取代的是17次的db file scattered read操作。通常我們認為這個操作是典型的多塊讀操作，也就是每次都有多於一個資料塊被讀取。在trace檔案中，我們分析到了原始的資訊。

PARSING IN CURSOR #14 len=28 dep=0 uid=0 ct=3 lid=0 tim=1356958954691351 hv=3045392248 ad='52631ad4' sqlid='7x3ug7kus9zvs'

select count(*) from t_big

END OF STMT

PARSE #14:c=14997,e=14989,p=6,cr=28,cu=0,mis=1,r=0,dep=0,og=1,plh=2892922722,tim=1356958954691348

EXEC #14:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2892922722,tim=1356958954691489

WAIT #14: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=76985 tim=1356958954691525

WAIT #14: nam='SQL*Net message from client' ela= 269 driver id=1413697536 #bytes=1 p3=0 obj#=76985 tim=1356958954691840

WAIT #14: nam='db file sequential read' ela= 102 file#=1 block#=90648 blocks=1 obj#=76985 tim=1356958954692024

WAIT #14: nam='db file scattered read' ela= 150 file#=1 block#=90649 blocks=7 obj#=76985 tim=1356958954692287

WAIT #14: nam='db file scattered read' ela= 106 file#=1 block#=90656 blocks=8 obj#=76985 tim=1356958954693057

WAIT #14: nam='db file scattered read' ela= 112 file#=1 block#=90664 blocks=8 obj#=76985 tim=1356958954693864

WAIT #14: nam='db file scattered read' ela= 140 file#=1 block#=90672 blocks=8 obj#=76985 tim=1356958954694721

WAIT #14: nam='db file scattered read' ela= 106 file#=1 block#=90680 blocks=8 obj#=76985 tim=1356958954695689

WAIT #14: nam='db file scattered read' ela= 143 file#=1 block#=90688 blocks=8 obj#=76985 tim=1356958954696447

WAIT #14: nam='db file scattered read' ela= 142 file#=1 block#=90696 blocks=8 obj#=76985 tim=1356958954697276

WAIT #14: nam='db file scattered read' ela= 113 file#=1 block#=90704 blocks=8 obj#=76985 tim=1356958954698091

WAIT #14: nam='db file scattered read' ela= 101 file#=1 block#=90712 blocks=8 obj#=76985 tim=1356958954698873

WAIT #14: nam='db file scattered read' ela= 120 file#=1 block#=90720 blocks=8 obj#=76985 tim=1356958954699695

WAIT #14: nam='db file scattered read' ela= 137 file#=1 block#=90728 blocks=8 obj#=76985 tim=1356958954700451

WAIT #14: nam='db file scattered read' ela= 83 file#=1 block#=90736 blocks=8 obj#=76985 tim=1356958954701222

WAIT #14: nam='db file scattered read' ela= 102 file#=1 block#=90744 blocks=8 obj#=76985 tim=1356958954701972

WAIT #14: nam='db file scattered read' ela= 117 file#=1 block#=91008 blocks=8 obj#=76985 tim=1356958954702739

WAIT #14: nam='db file scattered read' ela= 114 file#=1 block#=91016 blocks=8 obj#=76985 tim=1356958954703597

WAIT #14: nam='db file scattered read' ela= 115 file#=1 block#=91024 blocks=8 obj#=76985 tim=1356958954704380

WAIT #14: nam='db file scattered read' ela= 543 file#=1 block#=90752 blocks=35 obj#=76985 tim=1356958954705618

WAIT #14: nam='SQL*Net message to client' ela= 15 driver id=1413697536 #bytes=1 p3=0 obj#=76985 tim=1356958954708425

FETCH #14:c=15998,e=16578,p=163,cr=169,cu=0,mis=0,r=1,dep=0,og=1,plh=2892922722,tim=1356958954708462

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

STAT #14 id=2 cnt=72689 pid=1 pos=1 bj=76985 p='INDEX FAST FULL SCAN IDX_T_BIG_ID (cr=169 pr=163 pw=0 time=74479 us cost=43 size=0 card=72689)'

WAIT #14: nam='SQL*Net message from client' ela= 11633 driver id=1413697536 #bytes=1 p3=0 obj#=76985 tim=1356958954720219

CLOSE #14:c=0,e=17,dep=0,type=0,tim=1356958954720335

從系列的db file xxx read動作中，我們分析操作的規律。首先，Oracle進行了一次單塊讀操作“db file sequential read”操作。

WAIT #14: nam='db file sequential read' ela= 102 file#=1 block#=90648 blocks=1 obj#=76985 tim=1356958954692024

這個操作實際上是讀取了索引段段頭資訊。注意，這裡是段頭！段頭塊（segment header block）中包括了索引段所有的對應extents資訊和HWM資訊。

之後，Oracle進行了一系列的多塊讀操作，每次至少都是將對應extents所有的塊讀取到。最後一個讀取動作讀取了35個塊，我們可以認為是HWM的極限情況。

對這樣的方式，我們並不陌生。Oracle在進行FTS（Full Table Scan）的時候，也是這樣的方式，從段頭塊開始，依次讀取HWM下的所有分割槽。所以說，Index Fast Full Scan實際上就是對索引進行了一次“Full Table Scan”。

但是，效果是一樣的。無論是Index Full Scan還是Index Fast Full Scan，進行的都是對葉子塊的掃描。核心區別就在於動作上，Index Full Scan依據的是Oracle經典索引操作方式，進行“單塊”操作。而Index Fast Full Scan，則是“一股腦”的將索引段全部吞掉，進行“多塊讀”操作。

5、討論和分析

發現了兩個的差別，我們還有兩個問題要進行思考，一個是兩種操作的差異在單塊讀和多塊讀上，為什麼Oracle要引入index fast full scan？另一個是為什麼大小表差異會影響CBO的抉擇？

首先，我們需要討論下“Single Block Read”和“Multi-Block Read”的差異。Oracle CBO的成本一共經歷了兩個時代，IO-Cost和CPU-Cost。

原始的IO-Cost成本模型，只考慮SQL語句返回結果集合的資料量大小，轉化為成本估算值。這樣做是有一定的道理，因為訪問資料的消耗在整個SQL執行過程中佔到了絕對的比例。

但是這樣會引起一些問題，作業系統在進行單塊讀和多塊讀的時間完全不成比例。例如，Oracle進行10次單塊讀的總時間消耗要遠遠大於進行一次讀10個塊的多塊讀操作。所以，IO-Cost模型下的CBO會無意識的低估單塊讀操作的成本。所以，在IO-Cost模型體系下，索引路徑等訪問連線方式更容易被選擇到。

IO-Cost模型相對應的就是現在普遍使用的CPU-Cost模型。CPU-Cost模型就是在原有的IO-Cost模型基礎上新增入了系統統計量（System Statistical Data）因素，形成更加貼近實際的成本估算引數。

回到Index Full Scan和Index Fast Full Scan的問題上，我們似乎可以得到這樣的推論。健康的索引標準，我們普遍認為是層次Level較低、無效葉子節點數量較少。所以，分支節點實際上很少的。

在索引段很大的情況下，如果進行頻繁的單塊讀操作來訪問葉子塊，這樣的成本消耗顯然不如多次的多塊讀“吞掉”整個索引。

至於第二個問題，為什麼很小表的情況下，Index Full Scan還會出現。我們說，當資料表很小的時候，索引段會更小。如果索引段只有幾個塊的情況，幾次單塊讀操作合計成本是可能小於一次的多塊讀的。在這樣的場景下，CBO會認為Index Full Scan的成本較低。

6、結論

本系列詳細分析了Oracle Index Full Scan和Index Fast Full Scan兩個典型操作的特點和內部執行細節。這樣的分析有助於我們更好的理解最佳化器的工作原理機制，從而更好地讓我們控制CBO生成更好的執行計劃。

Index Full Scan和Index Fast Full Scan行為差異分析（下）

相關文章