db_file_multiblock_read_count
一、参数说明：
初始化参数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 processed 101 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.trc 106 WAIT #14: nam='db file sequential read' ela= 41 file#=6 block#=157 blocks=1 obj#=75062 tim=1358893053963775 107 WAIT #14: nam='db file sequential read' ela= 83 file#=6 block#=159 blocks=1 obj#=75062 tim=1358893053963907 108 WAIT #14: nam='db file sequential read' ela= 104 file#=6 block#=161 blocks=1 obj#=75062 tim=1358893053964108 109 WAIT #14: nam='db file scattered read' ela= 124 file#=6 block#=163 blocks=2 obj#=75062 tim=1358893053964318 110 FETCH #14:c=1000,e=698,p=5,cr=8,cu=0,mis=0,r=15,dep=0,og=1,plh=949574992,tim=1358893053964360 111 WAIT #14: nam='SQL*Net message from client' ela= 129 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053964536 112 WAIT #14: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053964625 113 WAIT #14: nam='db file sequential read' ela= 92 file#=6 block#=166 blocks=1 obj#=75062 tim=1358893053964774 114 WAIT #14: nam='db file sequential read' ela= 91 file#=6 block#=168 blocks=1 obj#=75062 tim=1358893053964930 115 FETCH #14:c=0,e=393,p=2,cr=8,cu=0,mis=0,r=15,dep=0,og=1,plh=949574992,tim=1358893053964999 116 WAIT #14: nam='SQL*Net message from client' ela= 129 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053965174 117 WAIT #14: nam='db file scattered read' ela= 221 file#=6 block#=171 blocks=2 obj#=75062 tim=1358893053965485 118 WAIT #14: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=75062 tim=1358893053965536 119 WAIT #14: nam='db file sequential read' ela= 64 file#=6 block#=174 blocks=1 obj#=75062 tim=1358893053965695 120 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 1 18 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 Plan 102 ---------------------------------------------------------- 103 Plan hash value: 1123611804 104 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 Note 114 ----- 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 Plan 124 ---------------------------------------------------------- 125 Plan hash value: 1123611804 126 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 Note 136 ----- 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 Plan 146 ---------------------------------------------------------- 147 Plan hash value: 3487819792 148 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 Note 163 ----- 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 Plan 173 ---------------------------------------------------------- 174 Plan hash value: 3487819792 175 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 Note 190 ----- 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 Plan 200 ---------------------------------------------------------- 201 Plan hash value: 3487819792 202 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 Note 217 ----- 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 Plan 227 ---------------------------------------------------------- 228 Plan hash value: 1544245908 229 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 Note 238 ----- 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 Plan 248 ---------------------------------------------------------- 249 Plan hash value: 1544245908 250 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 Note 259 ----- 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 Plan 269 ---------------------------------------------------------- 270 Plan hash value: 1544245908 271 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 Note 280 ----- 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将使用这个新的值。
