上篇（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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

从处理的结果看，执行计划和对应的操作计数如下：

 

 

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

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

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

从分析过的结果片段中，我们可以发现在使用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生成更好的执行计划。