一、参数说明：
初始化参数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将使用这个新的值。
http://www.cnblogs.com/Richardzhu/archive/2013/01/23/2872587.html
