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的介绍：

db_file_multiblock_read_count

二、实验说明：

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

db_file_multiblock_read_count

----创建一张表jack并插入一下数据----

1 SQL> create table jack(x int,y int);

3 Table created.

5 SQL> insert into jack values(1,1);

7 1 row created.

9 SQL> insert into jack values(2,1);

11 1 row created.

----将表jack中的每个数据块存放在记录数收缩到最小，以便于记录分布在尽可能多的数据块上----

13 SQL> alter table jack minimize records_per_block;

15 Table altered.

----再次插入一下数据，并做一下数据分析----

17 SQL> insert into jack select rownum+2,1 from all_objects where rownum<=254;

19 254 rows created.

21 SQL> create index jack_ind on jack(x);

23 Index created.

25 SQL> exec dbms_stats.gather_table_stats(user,'jack');

27 PL/SQL procedure successfully completed.

----下面的查询说明jack表占用的数据块数为128个。

29 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from jack;

31 COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

32 ---------------------------------------------------

33 128

----清楚缓存，并将数据块间隔着读入内存中----

35 SQL> alter system flush buffer_cache;

37 System altered.

39 SQL> alter session set db_file_multiblock_read_count = 64;

41 Session altered.

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 /

52 PL/SQL procedure successfully completed.

----查看一下trace文件的路径----

54 SQL> @showtrace

56 trace_file_name

57 --------------------------------------------------------------------------------

58 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4556.trc

60 [[email protected] ~]$ 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;

----打开10046事件，并进行一次全表扫描----

78 SQL> alter session set events '10046 trace name context forever,level 12';

80 Session altered.

82 SQL> set autotrace traceonly statistics;

83 SQL> select * from jack;

85 256 rows selected.

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 [[email protected] ~]$ 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，说明它们之间的那个数据块已经读取到内存中了。

db_file_multiblock_read_count

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

db_file_multiblock_read_count

1 SQL> create table echo (x int,y int);

3 Table created.

5 SQL> insert into echo values(1,1);

7 1 row created.

9 SQL> insert into echo values(2,1);

11 1 row created.

13 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from echo;

15 COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

16 ---------------------------------------------------

17 1

19 SQL> insert into echo select rownum+2,1 from all_objects where rownum<=254;

21 254 rows created.

23 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from echo;

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

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

db_file_multiblock_read_count

----创建一张echo表----
1 SQL> create table echo as select * from dba_objects;

3 Table created.

5 SQL> set autotrace trace exp;

----将参数设置成16，此时没有索引，只能进行全表扫描----
6 SQL> alter session set db_file_multiblock_read_count=16;

8 Session altered.

10 SQL> select * from echo;

12 Execution Plan

13 ----------------------------------------------------------

14 Plan hash value: 642657756

16 --------------------------------------------------------------------------

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 --------------------------------------------------------------------------

23 Note

24 -----

25 - dynamic sampling used for this statement (level=2)

27 SQL> alter session set db_file_multiblock_read_count=60;

29 Session altered.

31 SQL> select * from echo;

33 Execution Plan

34 ----------------------------------------------------------

35 Plan hash value: 642657756

37 --------------------------------------------------------------------------

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 --------------------------------------------------------------------------

44 Note

45 -----

46 - dynamic sampling used for this statement (level=2)

48 SQL> alter session set db_file_multiblock_read_count=128;

50 Session altered.

52 SQL> select * from echo;

54 Execution Plan

55 ----------------------------------------------------------

56 Plan hash value: 642657756

58 --------------------------------------------------------------------------

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 --------------------------------------------------------------------------

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);

71 Table altered.

73 SQL> alter session set db_file_multiblock_read_count=16;

75 Session altered.

77 SQL> select count(*) from echo;

79 Execution Plan

80 ----------------------------------------------------------

81 Plan hash value: 1123611804

83 -------------------------------------------------------------------------

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 -------------------------------------------------------------------------

91 Note

92 -----

93 - dynamic sampling used for this statement (level=2)

95 SQL> alter session set db_file_multiblock_read_count=60;

97 Session altered.

99 SQL> select count(*) from echo;

100

101 Execution Plan

102 ----------------------------------------------------------

103 Plan hash value: 1123611804

104

105 -------------------------------------------------------------------------

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 -------------------------------------------------------------------------

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 ---------------------------------------------------------------------------------------

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 ---------------------------------------------------------------------------------------

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 ---------------------------------------------------------------------------------------

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 ----------------------------------------------------------------------------

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 ----------------------------------------------------------------------------

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 ----------------------------------------------------------------------------

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的成本就会越低，执行计划就越向这边倾斜。

db_file_multiblock_read_count

四、总结

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

FTS(FULL TABLE SCAN)

INDEX_FFS(INDEX FAST FULL SCAN)

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

db_file_multiblock_read_count

相关推荐