一、参数说明：

    初始化参数db_file_multiblock_read_count是用来约束Oracle进行多数据块读取时的行为，所谓多数据块读取，就是Oracle在一次I/O时，可以读取多个数据块，从而用最小的I/O完成数据的读取。

    db_file_multiblock_read_count的设置要受OS最大IO能力影响，也就是说，如果你系统的硬件IO能力有限，即使设置再大的db_file_multiblock_read_count也是没用的。

    理论上，最大db_file_multiblock_read_count和系统IO能力应该有如下关系：

          Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size

    当然这个Max(db_file_multiblock_read_count)还要受Oracle的限制。

    在这里引用一下Oracle 11g r2的Reference中关于db_file_multiblock_read_count的介绍：

二、实验说明：

      在这里引用一下谭大师的《让Oracle跑得更快2》中的一个例子：

----创建一张表jack并插入一下数据----   1 SQL> create table jack(x int,y int);  2   3 Table created.  4   5 SQL> insert into jack values(1,1);  6   7 1 row created.  8   9 SQL> insert into jack values(2,1); 10  11 1 row created. 12   ----将表jack中的每个数据块存放在记录数收缩到最小，以便于记录分布在尽可能多的数据块上----  13 SQL> alter table jack minimize records_per_block; 14  15 Table altered. 16   ----再次插入一下数据，并做一下数据分析----  17 SQL> insert into jack select rownum+2,1 from all_objects where rownum<=254; 18  19 254 rows created. 20  21 SQL> create index jack_ind on jack(x); 22  23 Index created. 24  25 SQL> exec dbms_stats.gather_table_stats(user,'jack'); 26  27 PL/SQL procedure successfully completed. 28   ----下面的查询说明jack表占用的数据块数为128个。  29 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from jack; 30  31 COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) 32 --------------------------------------------------- 33                         128 34   ----清楚缓存，并将数据块间隔着读入内存中----  35 SQL> alter system flush buffer_cache; 36  37 System altered. 38  39 SQL> alter session set db_file_multiblock_read_count = 64; 40  41 Session altered. 42  43 SQL> declare 44   2      l_y number; 45   3  begin 46   4       for i in 1..64 loop 47   5          select y into l_y from jack where x = i*4; 48   6       end loop; 49   7  end; 50   8  / 51  52 PL/SQL procedure successfully completed. 53   ----查看一下trace文件的路径----  54 SQL> @showtrace 55  56 trace_file_name 57 -------------------------------------------------------------------------------- 58 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4556.trc 59  60 [oracle@yft ~]$ cat showtrace.sql  61 SELECT  d.VALUE 62       || '/' 63       || LOWER (RTRIM(i.INSTANCE,CHR(0))) 64       || '_ora_' 65       || p.spid 66       || '.trc' as "trace_file_name" 67  FROM (SELECT p.spid 68            FROM v$mystat m,v$session s,v$process p 69          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p, 70        (SELECT t.INSTANCE 71            FROM v$thread t,v$parameter v 72          WHERE v.NAME = 'thread' 73            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, 74        (SELECT VALUE 75            FROM v$parameter 76          WHERE NAME = 'user_dump_dest') d; 77   ----打开10046事件，并进行一次全表扫描----  78 SQL> alter session set events '10046 trace name context forever,level 12'; 79  80 Session altered. 81  82 SQL> set autotrace traceonly statistics; 83 SQL> select * from jack; 84  85 256 rows selected. 86  87  88 Statistics 89 ---------------------------------------------------------- 90       1  recursive calls 91       0  db block gets 92     199  consistent gets 93     123  physical reads 94       0  redo size 95    4829  bytes sent via SQL*Net to client 96     606  bytes received via SQL*Net from client 97      19  SQL*Net roundtrips to/from client 98       0  sorts (memory) 99       0  sorts (disk)100     256  rows processed101 102 SQL> alter session set events '10046 trace name context off';103 104 Session altered. ---查看10046事件中的信息---- 105 [oracle@yft ~]$ cat /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4556.trc106 WAIT #14: nam='db file sequential read' ela= 41 file#=6 block#=157 blocks=1 obj#=75062 tim=1358893053963775107 WAIT #14: nam='db file sequential read' ela= 83 file#=6 block#=159 blocks=1 obj#=75062 tim=1358893053963907108 WAIT #14: nam='db file sequential read' ela= 104 file#=6 block#=161 blocks=1 obj#=75062 tim=1358893053964108109 WAIT #14: nam='db file scattered read' ela= 124 file#=6 block#=163 blocks=2 obj#=75062 tim=1358893053964318110 FETCH #14:c=1000,e=698,p=5,cr=8,cu=0,mis=0,r=15,dep=0,og=1,plh=949574992,tim=1358893053964360111 WAIT #14: nam='SQL*Net message from client' ela= 129 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053964536112 WAIT #14: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053964625113 WAIT #14: nam='db file sequential read' ela= 92 file#=6 block#=166 blocks=1 obj#=75062 tim=1358893053964774114 WAIT #14: nam='db file sequential read' ela= 91 file#=6 block#=168 blocks=1 obj#=75062 tim=1358893053964930115 FETCH #14:c=0,e=393,p=2,cr=8,cu=0,mis=0,r=15,dep=0,og=1,plh=949574992,tim=1358893053964999116 WAIT #14: nam='SQL*Net message from client' ela= 129 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053965174117 WAIT #14: nam='db file scattered read' ela= 221 file#=6 block#=171 blocks=2 obj#=75062 tim=1358893053965485118 WAIT #14: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053965536119 WAIT #14: nam='db file sequential read' ela= 64 file#=6 block#=174 blocks=1 obj#=75062 tim=1358893053965695120 WAIT #14: nam='db file sequential read' ela= 85 file#=6 block#=178 blocks=1 obj#=75062 tim=1358893053965849     这个例子展示了Oracle多个数据块读取的工作机制，当内存中已经有了某个数据块时，Oracle将不再从磁盘中读取它。这里使用一个循环来通过索引块访问的方式(每次读取一个数据块)， 将间隔的数据块读入到内存中，这样即使db_file_multiblock_read_count设置为64时，执行jack表的全表扫描时，由于已经没有连续的数据块可供读取了，所以Oracle每次也只能将一个数据块读取到内存。在等待时间中每一个WAIT#中blocks=1说明每一次I/O读取的数据块都为1，而且数据块的序号正好间隔为1，说明它们之间的那个数据块已经读取到内存中了。

在这里要说明一下SQL> alter table jack minimize records_per_block;该语句的作用：

1 SQL> create table echo (x int,y int); 2  3 Table created. 4  5 SQL> insert into echo values(1,1); 6  7 1 row created. 8  9 SQL> insert into echo values(2,1);10 11 1 row created.12 13 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from echo;14 15 COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))16 ---------------------------------------------------17                           118 19 SQL> insert into echo select rownum+2,1 from all_objects where rownum<=254;20 21 254 rows created.22 23 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from echo;24 25 COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))26 ---------------------------------------------------27  在这里很明显可以看到没有使用alter table jack minimize records_per_block语句时，echo表占用的数据块数为1.                         1

 

 三、设置db_file_multiblock_read_count为不同的值，观察SQL的性能变化，写出几种类型的SQL会从这个参数中受益

----创建一张echo表----   1 SQL> create table echo as select * from dba_objects;  2   3 Table created.  4   5 SQL> set autotrace trace exp;  ----将参数设置成16，此时没有索引，只能进行全表扫描----   6 SQL> alter session set db_file_multiblock_read_count=16;  7   8 Session altered.  9  10 SQL> select * from echo; 11  12 Execution Plan 13 ---------------------------------------------------------- 14 Plan hash value: 642657756 15  16 -------------------------------------------------------------------------- 17 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     | 18 -------------------------------------------------------------------------- 19 |   0 | SELECT STATEMENT  |     | 63977 |    12M|   234   (1)| 00:00:03 | 20 |   1 |  TABLE ACCESS FULL| ECHO | 63977 |    12M|   234   (1)| 00:00:03 | 21 -------------------------------------------------------------------------- 22  23 Note 24 ----- 25    - dynamic sampling used for this statement (level=2) 26  27 SQL> alter session set db_file_multiblock_read_count=60; 28  29 Session altered. 30  31 SQL> select * from echo; 32  33 Execution Plan 34 ---------------------------------------------------------- 35 Plan hash value: 642657756 36  37 -------------------------------------------------------------------------- 38 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     | 39 -------------------------------------------------------------------------- 40 |   0 | SELECT STATEMENT  |     | 63977 |    12M|   194   (1)| 00:00:03 | 41 |   1 |  TABLE ACCESS FULL| ECHO | 63977 |    12M|   194   (1)| 00:00:03 | 42 -------------------------------------------------------------------------- 43  44 Note 45 ----- 46    - dynamic sampling used for this statement (level=2) 47  48 SQL> alter session set db_file_multiblock_read_count=128; 49  50 Session altered. 51  52 SQL> select * from echo; 53  54 Execution Plan 55 ---------------------------------------------------------- 56 Plan hash value: 642657756 57  58 -------------------------------------------------------------------------- 59 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     | 60 -------------------------------------------------------------------------- 61 |   0 | SELECT STATEMENT  |     | 63977 |    12M|   186   (1)| 00:00:03 | 62 |   1 |  TABLE ACCESS FULL| ECHO | 63977 |    12M|   186   (1)| 00:00:03 | 63 -------------------------------------------------------------------------- 64  65 Note 66 ----- 67    - dynamic sampling used for this statement (level=2)  -----当参数设定为16,60,128时，CBO计算出的FTS成本分别是234,194,186，很显然当参数的值越高CBO更倾向于全表扫描。  68   ----给表创建一个主键----  69 SQL> alter table echo add constraint pk_echo primary key (object_id); 70  71 Table altered. 72  73 SQL> alter session set db_file_multiblock_read_count=16; 74  75 Session altered. 76  77 SQL> select count(*) from echo; 78  79 Execution Plan 80 ---------------------------------------------------------- 81 Plan hash value: 1123611804 82  83 ------------------------------------------------------------------------- 84 | Id  | Operation          | Name    | Rows    | Cost (%CPU)| Time    | 85 ------------------------------------------------------------------------- 86 |   0 | SELECT STATEMENT      |     |     1 |    38   (0)| 00:00:01 | 87 |   1 |  SORT AGGREGATE       |     |     1 |         |        | 88 |   2 |   INDEX FAST FULL SCAN| PK_ECHO | 63977 |    38   (0)| 00:00:01 | 89 ------------------------------------------------------------------------- 90  91 Note 92 ----- 93    - dynamic sampling used for this statement (level=2) 94  95 SQL> alter session set db_file_multiblock_read_count=60; 96  97 Session altered. 98  99 SQL> select count(*) from echo;100 101 Execution Plan102 ----------------------------------------------------------103 Plan hash value: 1123611804104 105 -------------------------------------------------------------------------106 | Id  | Operation          | Name    | Rows    | Cost (%CPU)| Time    |107 -------------------------------------------------------------------------108 |   0 | SELECT STATEMENT      |     |     1 |    31   (0)| 00:00:01 |109 |   1 |  SORT AGGREGATE       |     |     1 |         |        |110 |   2 |   INDEX FAST FULL SCAN| PK_ECHO | 63977 |    31   (0)| 00:00:01 |111 -------------------------------------------------------------------------112 113 Note114 -----115    - dynamic sampling used for this statement (level=2)116 117 SQL> alter session set db_file_multiblock_read_count=128;118 119 Session altered.120 121 SQL> select count(*) from echo;122 123 Execution Plan124 ----------------------------------------------------------125 Plan hash value: 1123611804126 127 -------------------------------------------------------------------------128 | Id  | Operation          | Name    | Rows    | Cost (%CPU)| Time    |129 -------------------------------------------------------------------------130 |   0 | SELECT STATEMENT      |     |     1 |    30   (0)| 00:00:01 |131 |   1 |  SORT AGGREGATE       |     |     1 |         |        |132 |   2 |   INDEX FAST FULL SCAN| PK_ECHO | 63977 |    30   (0)| 00:00:01 |133 -------------------------------------------------------------------------134 135 Note136 -----137    - dynamic sampling used for this statement (level=2)  ----进行INDEX_FFS查询时，参数的值越大CBO计算的成本越低 138   ----执行INDEX RANGE SCAN查询---- 139 SQL> alter session set db_file_multiblock_read_count=16;140 141 Session altered.142 143 SQL> select * from echo where object_id<1000;144 145 Execution Plan146 ----------------------------------------------------------147 Plan hash value: 3487819792148 149 ---------------------------------------------------------------------------------------150 | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |151 ---------------------------------------------------------------------------------------152 |   0 | SELECT STATEMENT        |          |   942 |   190K|    29    (0)| 00:00:01 |153 |   1 |  TABLE ACCESS BY INDEX ROWID| ECHO    |   942 |   190K|    29    (0)| 00:00:01 |154 |*  2 |   INDEX RANGE SCAN        | PK_ECHO |   942 |       |     4    (0)| 00:00:01 |155 ---------------------------------------------------------------------------------------156 157 Predicate Information (identified by operation id):158 ---------------------------------------------------159 160    2 - access("OBJECT_ID"<1000)161 162 Note163 -----164    - dynamic sampling used for this statement (level=2)165 166 SQL> alter session set db_file_multiblock_read_count=60;167 168 Session altered.169 170 SQL> select * from echo where object_id<1000;171 172 Execution Plan173 ----------------------------------------------------------174 Plan hash value: 3487819792175 176 ---------------------------------------------------------------------------------------177 | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |178 ---------------------------------------------------------------------------------------179 |   0 | SELECT STATEMENT        |          |   942 |   190K|    29    (0)| 00:00:01 |180 |   1 |  TABLE ACCESS BY INDEX ROWID| ECHO    |   942 |   190K|    29    (0)| 00:00:01 |181 |*  2 |   INDEX RANGE SCAN        | PK_ECHO |   942 |       |     4    (0)| 00:00:01 |182 ---------------------------------------------------------------------------------------183 184 Predicate Information (identified by operation id):185 ---------------------------------------------------186 187    2 - access("OBJECT_ID"<1000)188 189 Note190 -----191    - dynamic sampling used for this statement (level=2)192 193 SQL> alter session set db_file_multiblock_read_count=128;194 195 Session altered.196 197 SQL> select * from echo where object_id<1000;198 199 Execution Plan200 ----------------------------------------------------------201 Plan hash value: 3487819792202 203 ---------------------------------------------------------------------------------------204 | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |205 ---------------------------------------------------------------------------------------206 |   0 | SELECT STATEMENT        |          |   942 |   190K|    29    (0)| 00:00:01 |207 |   1 |  TABLE ACCESS BY INDEX ROWID| ECHO    |   942 |   190K|    29    (0)| 00:00:01 |208 |*  2 |   INDEX RANGE SCAN        | PK_ECHO |   942 |       |     4    (0)| 00:00:01 |209 ---------------------------------------------------------------------------------------210 211 Predicate Information (identified by operation id):212 ---------------------------------------------------213 214    2 - access("OBJECT_ID"<1000)215 216 Note217 -----218    - dynamic sampling used for this statement (level=2)  ----当参数的值改变时，CBO计算的成本没有发生变化 219   ----执行INDEX FULL SCAN查询---- 220 SQL> alter session set db_file_multiblock_read_count=16;221 222 Session altered.223 224 SQL> select object_id from echo order by object_id;225 226 Execution Plan227 ----------------------------------------------------------228 Plan hash value: 1544245908229 230 ----------------------------------------------------------------------------231 | Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time       |232 ----------------------------------------------------------------------------233 |   0 | SELECT STATEMENT |       | 63977 |   812K|   165   (0)| 00:00:02 |234 |   1 |  INDEX FULL SCAN | PK_ECHO | 63977 |   812K|   165   (0)| 00:00:02 |235 ----------------------------------------------------------------------------236 237 Note238 -----239    - dynamic sampling used for this statement (level=2)240 241 SQL> alter session set db_file_multiblock_read_count=60;242 243 Session altered.244 245 SQL> select object_id from echo order by object_id;246 247 Execution Plan248 ----------------------------------------------------------249 Plan hash value: 1544245908250 251 ----------------------------------------------------------------------------252 | Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time       |253 ----------------------------------------------------------------------------254 |   0 | SELECT STATEMENT |       | 63977 |   812K|   165   (0)| 00:00:02 |255 |   1 |  INDEX FULL SCAN | PK_ECHO | 63977 |   812K|   165   (0)| 00:00:02 |256 ----------------------------------------------------------------------------257 258 Note259 -----260    - dynamic sampling used for this statement (level=2)261 262 SQL> alter session set db_file_multiblock_read_count=128;263 264 Session altered.265 266 SQL> select object_id from echo order by object_id;267 268 Execution Plan269 ----------------------------------------------------------270 Plan hash value: 1544245908271 272 ----------------------------------------------------------------------------273 | Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time       |274 ----------------------------------------------------------------------------275 |   0 | SELECT STATEMENT |       | 63977 |   812K|   165   (0)| 00:00:02 |276 |   1 |  INDEX FULL SCAN | PK_ECHO | 63977 |   812K|   165   (0)| 00:00:02 |277 ----------------------------------------------------------------------------278 279 Note280 -----281    - dynamic sampling used for this statement (level=2)   ----当参数的值改变时，CBO计算的成本没有发生变化 总结：在这里只列举是4种查询方式，可以看出当参数的值越大时，FTS和INDEX_FFS的成本就会越低，执行计划就越向这边倾斜。

四、总结

    对于OLTP数据库，每次用户读取的记录数非常少，这个值可以考虑设置小一点；而对于OLAP数据库，因为查询的量非常大，索引可以考虑设置大一些，但是需要注意多数据块读取只发生在以下两种情况下：

    FTS(FULL TABLE SCAN)

    INDEX_FFS(INDEX FAST FULL SCAN)

    关于这个参数，在Oracle 10G r2及以后的版本里，Oracle不建议修改它的默认值，当设置这个参数为默认值时，Oracle会通过收集SQL的I/O情况，来动态设置这个参数的值；如果手工修改了它的默认值，Oracle将使用这个新的值。