| 常见索引扫描浅析（二）：FFS | DBA之路：OoNiceDream |

常见索引扫描浅析（二）：FFS

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/06/index_fast_full_scan.html

查看执行计划：

SQL> set autot trace exp
SQL> select id from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 3121486721

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   115K|   565K|    35   (3)| 00:00:05 |
|   1 |  INDEX FAST FULL SCAN| IND_TEST_ID |   115K|   565K|    35   (3)| 00:00:05 |
------------------------------------------------------------------------------------

通过10046观察BLOCK读取顺序：

SQL> alter system flush BUFFER_CACHE;
 
System altered.
 
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
$ cat emrep_ora_28975.trc | grep "db file s"
WAIT #3: nam='db file sequential read' ela= 959 file#=1 block#=68985 blocks=1 obj#=93996 tim=26724537354277
WAIT #3: nam='db file scattered read' ela= 217 file#=1 block#=68986 blocks=7 obj#=93996 tim=26724537354762
WAIT #3: nam='db file scattered read' ela= 180 file#=1 block#=69297 blocks=8 obj#=93996 tim=26724537474568
WAIT #3: nam='db file scattered read' ela= 138 file#=1 block#=69337 blocks=8 obj#=93996 tim=26724537611369
WAIT #3: nam='db file scattered read' ela= 207 file#=1 block#=69345 blocks=8 obj#=93996 tim=26724537902917
WAIT #3: nam='db file scattered read' ela= 141 file#=1 block#=69353 blocks=8 obj#=93996 tim=26724538276147
WAIT #3: nam='db file scattered read' ela= 144 file#=1 block#=69361 blocks=8 obj#=93996 tim=26724538746730
WAIT #3: nam='db file scattered read' ela= 142 file#=1 block#=69369 blocks=8 obj#=93996 tim=26724539040363
WAIT #3: nam='db file scattered read' ela= 144 file#=1 block#=69377 blocks=8 obj#=93996 tim=26724539440460
WAIT #3: nam='db file scattered read' ela= 126 file#=1 block#=69513 blocks=8 obj#=93996 tim=26724539722311
WAIT #3: nam='db file scattered read' ela= 187 file#=1 block#=69521 blocks=8 obj#=93996 tim=26724540193140
WAIT #3: nam='db file scattered read' ela= 140 file#=1 block#=69529 blocks=8 obj#=93996 tim=26724540475610
WAIT #3: nam='db file scattered read' ela= 199 file#=1 block#=69537 blocks=8 obj#=93996 tim=26724540732011
WAIT #3: nam='db file scattered read' ela= 203 file#=1 block#=69545 blocks=8 obj#=93996 tim=26724540877239
WAIT #3: nam='db file scattered read' ela= 197 file#=1 block#=69553 blocks=8 obj#=93996 tim=26724541018629
WAIT #3: nam='db file scattered read' ela= 153 file#=1 block#=69561 blocks=8 obj#=93996 tim=26724541160195
WAIT #3: nam='db file scattered read' ela= 154 file#=1 block#=72497 blocks=8 obj#=93996 tim=26724541302888
WAIT #3: nam='db file scattered read' ela= 245 file#=1 block#=69641 blocks=16 obj#=93996 tim=26724541444418
WAIT #3: nam='db file scattered read' ela= 540 file#=1 block#=69657 blocks=16 obj#=93996 tim=26724541728877
WAIT #3: nam='db file scattered read' ela= 317 file#=1 block#=69673 blocks=16 obj#=93996 tim=26724542014736
WAIT #3: nam='db file scattered read' ela= 297 file#=1 block#=69689 blocks=16 obj#=93996 tim=26724542301024
WAIT #3: nam='db file scattered read' ela= 268 file#=1 block#=69705 blocks=16 obj#=93996 tim=26724542582520
WAIT #3: nam='db file scattered read' ela= 334 file#=1 block#=69721 blocks=16 obj#=93996 tim=26724542863001
WAIT #3: nam='db file scattered read' ela= 311 file#=1 block#=69737 blocks=16 obj#=93996 tim=26724543131641
WAIT #3: nam='db file scattered read' ela= 601 file#=1 block#=69753 blocks=16 obj#=93996 tim=26724543707590
WAIT #3: nam='db file scattered read' ela= 266 file#=1 block#=69769 blocks=2 obj#=93996 tim=26724544375720

对比TREEDUMP(参考前文），我们发现，并不是按索引结构来顺序读取的，事实上，INDEX FAST FULL SCAN是按照根据dba_extents作为MAP，一次读取多个块：

SQL> select SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,BLOCK_ID,BLOCKS
  2  from dba_extents
  3  where SEGMENT_NAME='IND_TEST_ID';

SEGMENT_NAME         TABLESPACE_NAME       EXTENT_ID   BLOCK_ID     BLOCKS
-------------------- -------------------- ---------- ---------- ----------
IND_TEST_ID          SYSTEM                        0      68985          8
IND_TEST_ID          SYSTEM                        1      69297          8
IND_TEST_ID          SYSTEM                        2      69337          8
IND_TEST_ID          SYSTEM                        3      69345          8
IND_TEST_ID          SYSTEM                        4      69353          8
IND_TEST_ID          SYSTEM                        5      69361          8
IND_TEST_ID          SYSTEM                        6      69369          8
IND_TEST_ID          SYSTEM                        7      69377          8
IND_TEST_ID          SYSTEM                        8      69513          8
IND_TEST_ID          SYSTEM                        9      69521          8
IND_TEST_ID          SYSTEM                       10      69529          8
IND_TEST_ID          SYSTEM                       11      69537          8
IND_TEST_ID          SYSTEM                       12      69545          8
IND_TEST_ID          SYSTEM                       13      69553          8
IND_TEST_ID          SYSTEM                       14      69561          8
IND_TEST_ID          SYSTEM                       15      72497          8
IND_TEST_ID          SYSTEM                       16      69641        128
IND_TEST_ID          SYSTEM                       17      69769        128

18 rows selected.

在10046中，我们观察到，第一次读取的只有一个BLOCK：68985，这其实是段头信息，因为我们知道ASSM方式下，段头一般占用3个block，MSSM下占用的是1个block，而这里我们SYSTEM表空间采用的是MSSM方式：

SQL> select tablespace_name,segment_space_management
  2  from dba_tablespaces
  3  where tablespace_name='SYSTEM';

TABLESPACE_NAME                SEGMEN
------------------------------ ------
SYSTEM                         MANUAL

SQL> select SEGMENT_NAME,SEGMENT_TYPE,HEADER_BLOCK
  2  from dba_segments
  3  where SEGMENT_NAME='IND_TEST_ID';

SEGMENT_NAME               SEGMENT_TYPE       HEADER_BLOCK
-------------------------- ------------------ ------------
IND_TEST_ID                INDEX                     68985

接着我们观察到，对于分配的EXTENT中，如果BLOCK数为8的，是整个EXTENT一次读取的，而对于extent大于8个BLOCK的，也就是128个BLOCK的EXTNT，读取的BLOCK数则为16。这其实就是受到参数db_file_multiblock_read_count影响：

SQL> show parameter db_file_m

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
db_file_multiblock_read_count        integer     16

如果我们将db_file_multiblock_read_count修改为32,此时再来观察10046 TRACE结果，我们会发现，读取BLOCK数变成32了：

$ cat emrep_ora_1781.trc | grep "db file s"
WAIT #2: nam='db file sequential read' ela= 44 file#=1 block#=68985 blocks=1 obj#=93996 tim=26725484141482
WAIT #2: nam='db file scattered read' ela= 247 file#=1 block#=68986 blocks=7 obj#=93996 tim=26725484141979
WAIT #2: nam='db file scattered read' ela= 172 file#=1 block#=69297 blocks=8 obj#=93996 tim=26725484258091
WAIT #2: nam='db file scattered read' ela= 142 file#=1 block#=69337 blocks=8 obj#=93996 tim=26725484383969
WAIT #2: nam='db file scattered read' ela= 229 file#=1 block#=69345 blocks=8 obj#=93996 tim=26725484703873
WAIT #2: nam='db file scattered read' ela= 142 file#=1 block#=69353 blocks=8 obj#=93996 tim=26725485113987
WAIT #2: nam='db file scattered read' ela= 187 file#=1 block#=69361 blocks=8 obj#=93996 tim=26725485308374
WAIT #2: nam='db file scattered read' ela= 137 file#=1 block#=69369 blocks=8 obj#=93996 tim=26725485453982
WAIT #2: nam='db file scattered read' ela= 179 file#=1 block#=69377 blocks=8 obj#=93996 tim=26725485598684
WAIT #2: nam='db file scattered read' ela= 137 file#=1 block#=69513 blocks=8 obj#=93996 tim=26725485743036
WAIT #2: nam='db file scattered read' ela= 179 file#=1 block#=69521 blocks=8 obj#=93996 tim=26725485888046
WAIT #2: nam='db file scattered read' ela= 133 file#=1 block#=69529 blocks=8 obj#=93996 tim=26725486032852
WAIT #2: nam='db file scattered read' ela= 138 file#=1 block#=69537 blocks=8 obj#=93996 tim=26725486177705
WAIT #2: nam='db file scattered read' ela= 129 file#=1 block#=69545 blocks=8 obj#=93996 tim=26725486318771
WAIT #2: nam='db file scattered read' ela= 140 file#=1 block#=69553 blocks=8 obj#=93996 tim=26725486461797
WAIT #2: nam='db file scattered read' ela= 130 file#=1 block#=69561 blocks=8 obj#=93996 tim=26725486603898
WAIT #2: nam='db file scattered read' ela= 136 file#=1 block#=72497 blocks=8 obj#=93996 tim=26725486745736
WAIT #2: nam='db file scattered read' ela= 1306 file#=1 block#=69641 blocks=32 obj#=93996 tim=26725486888560
WAIT #2: nam='db file scattered read' ela= 1214 file#=1 block#=69673 blocks=32 obj#=93996 tim=26725487467634
WAIT #2: nam='db file scattered read' ela= 1158 file#=1 block#=69705 blocks=32 obj#=93996 tim=26725488005724
WAIT #2: nam='db file scattered read' ela= 1184 file#=1 block#=69737 blocks=32 obj#=93996 tim=26725488584206
WAIT #2: nam='db file scattered read' ela= 86 file#=1 block#=69769 blocks=2 obj#=93996 tim=26725489170444

这里128刚好能被16、32除尽，如果我们将db_file_multiblock_read_count设为40,那对于EXTENT 16第四次读取时，是只读8个BLOCK，还是会到下一个EXTENT中去读取别的BLOCK呢？设置db_file_multiblock_read_count为40后，我们再观察TRACE结果：

$ cat emrep_ora_4338.trc | grep "db file s"
WAIT #4: nam='db file sequential read' ela= 42 file#=1 block#=68985 blocks=1 obj#=93996 tim=26725710727981
WAIT #4: nam='db file scattered read' ela= 239 file#=1 block#=68986 blocks=7 obj#=93996 tim=26725710728457
WAIT #4: nam='db file scattered read' ela= 128 file#=1 block#=69297 blocks=8 obj#=93996 tim=26725710841901
WAIT #4: nam='db file scattered read' ela= 171 file#=1 block#=69337 blocks=8 obj#=93996 tim=26725710967809
WAIT #4: nam='db file scattered read' ela= 229 file#=1 block#=69345 blocks=8 obj#=93996 tim=26725711278786
WAIT #4: nam='db file scattered read' ela= 167 file#=1 block#=69353 blocks=8 obj#=93996 tim=26725711702748
WAIT #4: nam='db file scattered read' ela= 138 file#=1 block#=69361 blocks=8 obj#=93996 tim=26725711963359
WAIT #4: nam='db file scattered read' ela= 129 file#=1 block#=69369 blocks=8 obj#=93996 tim=26725712135436
WAIT #4: nam='db file scattered read' ela= 201 file#=1 block#=69377 blocks=8 obj#=93996 tim=26725712283187
WAIT #4: nam='db file scattered read' ela= 184 file#=1 block#=69513 blocks=8 obj#=93996 tim=26725712431846
WAIT #4: nam='db file scattered read' ela= 159 file#=1 block#=69521 blocks=8 obj#=93996 tim=26725712579860
WAIT #4: nam='db file scattered read' ela= 197 file#=1 block#=69529 blocks=8 obj#=93996 tim=26725712727707
WAIT #4: nam='db file scattered read' ela= 151 file#=1 block#=69537 blocks=8 obj#=93996 tim=26725712873260
WAIT #4: nam='db file scattered read' ela= 140 file#=1 block#=69545 blocks=8 obj#=93996 tim=26725713018535
WAIT #4: nam='db file scattered read' ela= 146 file#=1 block#=69553 blocks=8 obj#=93996 tim=26725713164457
WAIT #4: nam='db file scattered read' ela= 147 file#=1 block#=69561 blocks=8 obj#=93996 tim=26725713312794
WAIT #4: nam='db file scattered read' ela= 144 file#=1 block#=72497 blocks=8 obj#=93996 tim=26725713462257
WAIT #4: nam='db file scattered read' ela= 1616 file#=1 block#=69641 blocks=40 obj#=93996 tim=26725713613928
WAIT #4: nam='db file scattered read' ela= 1854 file#=1 block#=69681 blocks=40 obj#=93996 tim=26725714329323
WAIT #4: nam='db file scattered read' ela= 1405 file#=1 block#=69721 blocks=40 obj#=93996 tim=26725715040796
WAIT #4: nam='db file scattered read' ela= 148 file#=1 block#=69761 blocks=8 obj#=93996 tim=26725715708440
WAIT #4: nam='db file scattered read' ela= 41 file#=1 block#=69769 blocks=2 obj#=93996 tim=26725715838327

结果我们发现，即使一次能读更多的BLOK，Oracle也不会跨EXTENT去获取了。

通过以上实验，我们大致能看出，INDEX FAST FULL SCAN的扫描方式是根据分配的EXTENT，以DBA_EXTENTS作为MAP进行扫描的，扫描过程中，可以一次读取多个块，读取块的个数受到参数db_file_multiblock_read_count及分配的EXTENT的BLOCK个数的影响。由于INDEX FAST FULL SCAN并没有根据索引的结构去扫描，因而获取的数据并不是有序的，不能达到消除ORDER BY的效果。
另外，要走INDEX FAST FULL SCAN，还要求索引中至少有一列是带有not null约束的：

SQL> desc test
 Name      Null?    Type
 --------- -------- ---------------
 ID        NOT NULL NUMBER
 NAME               VARCHAR2(30)

SQL> set autot trace exp
SQL> select id from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 3121486721

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   115K|   565K|    35   (3)| 00:00:05 |
|   1 |  INDEX FAST FULL SCAN| IND_TEST_ID |   115K|   565K|    35   (3)| 00:00:05 |
------------------------------------------------------------------------------------

SQL> alter table test modify (id null);

Table altered.

SQL> select id from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   115K|   565K|    73   (2)| 00:00:11 |
|   1 |  TABLE ACCESS FULL| TEST |   115K|   565K|    73   (2)| 00:00:11 |
--------------------------------------------------------------------------

SQL> alter table test modify (id not null);

Table altered.

SQL> select id from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 3121486721

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   115K|   565K|    35   (3)| 00:00:05 |
|   1 |  INDEX FAST FULL SCAN| IND_TEST_ID |   115K|   565K|    35   (3)| 00:00:05 |
------------------------------------------------------------------------------------

SQL> set autotrace off

— The End —

关键字: SQL优化 | 性能优化