通过案例学调优之--RECORDS_PER_BLOCK参数

来源：互联网发布：idea新建java项目编辑：程序博客网时间：2024/06/16 20:37

通过案例学调优之--RECORDS_PER_BLOCK参数

RECORDS_PER_BLOCK参数用于设定每个BLOCK中记录数的最大值，其先找到当前表所有BLOCK中容纳的最大行数，并会把这个数字记录到数据字典，以后任何导致BLOCK行数超过这个数字的插入都会被拒绝。

RECORDS_PER_BLOCK参数是为位图索引而生的，能够改善位图索引的存储，减小位图索引的长度。这样，利用该位图索引的时候，就能获得比较好的效率了。

测试案例：

1、表默认的存储分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
15:45:46 SCOTT@ prod >create table t3 (x int,y int);
Table created.
 
15:46:03 SCOTT@ prod >insert into t3 values (1,1);
1 row created.
 
15:46:12 SCOTT@ prod >insert into t3 values (2,1);
1 row created.
 
15:46:27 SCOTT@ prod >commit;
Commit complete.
 
15:48:01 SCOTT@ prod >insert into t3 select rownum+2,1 from all_objects where rownum <=254;
254 rows created.
 
15:48:37 SCOTT@ prod >create index t3_indx on t3(x);
Index created.
 
15:48:57 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T3',cascade=>true);
PL/SQL procedure successfully completed.
 
15:49:54 SCOTT@ prod >select count(distinct dbms_rowid.rowid_block_number(rowid)) from t3;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                  1
 
15:53:09 SCOTT@ prod >col segment_name for a20
15:53:21 SCOTT@ prod >select segment_name,EXTENTS,BLOCKS,BYTES from user_segments where segment_name='T3';
SEGMENT_NAME            EXTENTS     BLOCKS      BYTES
-------------------- ---------- ---------- ----------
T3                            1          8      65536
 
默认值，T3表中的数据存储在一个数据块上。

2、通过RECORDS_PER_BLOCK参数分散数据块的存储

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
15:57:47 SCOTT@ prod >drop table t3 purge;
Table dropped.
15:59:59 SCOTT@ prod >create table t3 (x int,y int);
Table created.
 
16:00:08 SCOTT@ prod >insert into t3 values (1,1);
1 row created.
 
16:00:16 SCOTT@ prod >insert into t3 values (2,1);
1 row created.
 
16:00:25 SCOTT@ prod >commit;
Commit complete.
 
16:00:37 SCOTT@ prod >alter table t3 minimize records_per_block;
Table altered.
 
16:00:54 SCOTT@ prod >insert into t3 select rownum+2,1 from all_objects where rownum <=254;
254 rows created.
 
16:01:09 SCOTT@ prod >commit;
Commit complete.
 
17:15:14 SCOTT@ prod >create index t3_indx on t3(x);
 
Index created.
 
16:01:12 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'T3');
PL/SQL procedure successfully completed.
 
16:01:58 SCOTT@ prod >select count(distinct dbms_rowid.rowid_block_number(rowid)) from t3;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                128
 
16:21:29 SCOTT@ prod >select dbms_rowid.rowid_block_number(rowid),count(0)   from t3
   group by dbms_rowid.rowid_block_number(rowid);
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(0)
------------------------------------ ----------
                                 198          2
                                 138          2
                                 151          2
                                 153          2
                                 167          2
                                 244          2
                                 245          2
                                 247          2
                                 537          2
                                 544          2
                                 134          2
                                 194          2
                                 207          2
                                 147          2
                                 209          2
                                 213          2
                                 155          2
......
                               
 
128 rows selected.
 
可以看出，T3表占用了128个数据块！

测试发现：执行alter table test minimize records_per_block;之后，目前BLOCK中的记录数（的最大值）会应用到以后的新增数据中，也就是，当以后再往表中INSERT数据时，每个BLOCK中可以包含的记录数将与设定records_per_block之前的最大值保持一致。

需要注意的是：

不能对空表设定此参数。
每个BLOCK中可以包含的记录数的最低下限是2。
不能在已经有 bitmap 的表中使用records_per_block参数，也就是说，如果要使用records_per_block参数，必须先alter table xxx minimize records_per_block，然后才能在表上建立索引。

如果字段的类型、大小、个数发生了改变，那么就会导致一个比较差的结果，这就说明了，这项功能只在于使用在静态的环境中，比如数据仓库。

主要用途：

通过减少同一个block中的记录数，使记录分布于更多的数据块中，可以优化等待块类型为data block的Buffer Busy Wait事件。
其主要用途是提高BITMAP INDEX的存储性能

3、对table访问分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
15:44:39 SYS@ prod >alter system flush buffer_cache;
System altered.
 
16:07:01 SYS@ prod >show parameter mult
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_file_multiblock_read_count        integer                          28
parallel_adaptive_multi_user         boolean                          TRUE
 
17:32:42 SCOTT@ prod >col object_name for a20
17:32:49 SCOTT@ prod >select object_name,object_id from user_objects where object_name='T3';
 
OBJECT_NAME           OBJECT_ID
-------------------- ----------
T3                        76505
 
16:22:19 SCOTT@ prod >alter session set db_file_multiblock_read_count=64;
 
Session altered.
将数据块以间隔的方式读入内存
16:09:03 SCOTT@ prod >declare
16:09:20   2  num number;
16:09:25   3  begin
16:09:29   4  for i in 1..64
16:09:34   5  loop
16:09:37   6  select y into num from t3 where x=i*4;
16:09:42   7  end loop;
16:09:48   8  end;
16:09:50   9  /
PL/SQL procedure successfully completed.
 
17:25:29 SYS@ prod >select file#,block#,status,objd from v$bh where file#=4;
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        521 free            76505
         4        521 free            76505
         4        521 free            76505
         4        165 free            76505
         4        165 free            76505
         4        165 free            76505
         4        542 free            76505
         4        542 free            76505
         4        542 free            76505
         4        131 free            76505
         4        131 free            76505
         4        131 free            76505
         4        131 xcur            76505
         4        529 free            76505
         4        529 free            76505
         4        529 free            76505
         4        529 xcur            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        550 free            76505
         4        550 free            76505
         4        550 free            76505
         4        139 free            76505
         4        139 free            76505
         4        139 free            76505
         4        139 xcur            76505
         4        537 free            76505
         4        537 free            76505
         4        537 free            76505
         4          3 free       4294967295
         4          3 free       4294967295
         4        147 free            76505
         4        147 free            76505
         4        147 free            76505
         4        524 free            76505
         4        524 free            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        524 free            76505
         4        545 free            76505
         4        545 free            76505
         4        545 free            76505
         4        545 xcur            76505
         4        134 free            76505
         4        134 free            76505
         4        134 free            76505
         4        134 xcur            76505
         4        155 free            76505
         4        155 free            76505
         4        155 free            76505
         4        155 xcur            76505
         4        532 free            76505
         4        532 free            76505
         4        532 free            76505
         4        532 xcur            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        553 free            76506
         4        142 free            76505
         4        142 free            76505
         4        142 free            76505
         4        163 free            76505
         4        163 free            76505
         4        163 free            76505
         4        540 free            76505
         4        540 free            76505
         4        540 free            76505
         4        129 free            76505
         4        129 free            76505
         4        129 free            76505
         4        150 free            76505
         4        150 free            76505
         4        150 free            76505
         4        150 xcur            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        527 free            76505
         4        527 free            76505
         4        527 free            76505
         4        527 xcur            76505
         4        548 free            76505
         4        548 free            76505
         4        548 free            76505
         4        137 free            76505
         4        137 free            76505
         4        158 free            76505
         4        158 free            76505
         4        535 free            76505
         4        535 free            76505
         4        145 free            76505
         4        145 free            76505
         4        145 xcur            76505
         4        522 free            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        522 free            76505
         4        522 xcur            76505
         4        166 free            76505
         4        166 free            76505
         4        166 xcur            76505
         4        543 free            76505
         4        543 free            76505
         4        543 xcur            76505
         4        132 free            76505
         4        132 free            76505
         4        153 free            76505
         4        153 free            76505
         4        530 free            76505
         4        530 free            76505
         4        551 free            76505
         4        551 free            76505
         4        551 xcur            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        140 free            76505
         4        140 free            76505
         4        161 free            76505
         4        161 free            76505
         4        161 xcur            76505
         4        538 free            76505
         4        538 free            76505
         4        538 xcur            76505
         4        148 free            76505
         4        148 free            76505
         4        148 xcur            76505
         4        525 free            76505
         4        525 free            76505
         4        525 xcur            76505
         4        546 free            76505
         4        546 free            76505
         4        135 free            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        135 free            76505
         4        156 free            76505
         4        156 free            76505
         4        533 free            76505
         4        533 free            76505
         4        554 free            76506
         4        143 free            76505
         4        143 free            76505
         4        143 xcur            76505
         4        164 free            76505
         4        164 free            76505
         4        164 xcur            76505
         4        541 free            76505
         4        541 free            76505
         4        541 xcur            76505
         4        130 free            76505
         4        130 free            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        151 free            76505
         4        151 free            76505
         4        549 free            76505
         4        549 free            76505
         4        549 xcur            76505
         4        138 free            76505
         4        138 free            76505
         4        138 xcur            76505
         4        159 free            76505
         4        159 free            76505
         4        159 xcur            76505
         4          2 free       4294967295
         4        146 free            76505
         4        146 free            76505
         4        523 free            76505
         4        523 free            76505
         4        523 xcur            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        167 free            76505
         4        167 free            76505
         4        544 free            76505
         4        544 free            76505
         4        133 free            76505
         4        133 free            76505
         4        154 free            76505
         4        154 free            76505
         4        154 xcur            76505
         4        531 free            76505
         4        531 free            76505
         4        552 free            76506
         4        141 free            76505
         4        141 free            76505
         4        141 xcur            76505
         4        162 free            76505
         4        162 free            76505
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        539 free            76505
         4        539 free            76505
         4        539 xcur            76505
         4        149 free            76505
         4        149 free            76505
         4        526 free            76505
         4        526 free            76505
         4        547 free            76505
         4        547 free            76505
         4        547 xcur            76505
         4        157 free            76505
         4        157 free            76505
         4        157 xcur            76505
         4        534 free            76505
         4        534 free            76505
         4        534 xcur            76505
         4        555 free            76506
 
     FILE#     BLOCK# STATUS           OBJD
---------- ---------- ---------- ----------
         4        555 xcur            76506
188 rows selected.
16:14:20 SYS@ prod >grant alter session to scott;
Grant succeeded.
 
16:14:39 SYS@ prod >conn scott/tiger
Connected.
16:14:42 SCOTT@ prod >alter session set events '10046 trace name context forever,level 12';
Session altered.
 
16:15:31 SCOTT@ prod >set autotrace trace
16:15:37 SCOTT@ prod >select * from t3 ;
256 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 |  1792 |    68   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T3   |   256 |  1792 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        196  consistent gets
          0  physical reads
          0  redo size
       4829  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed
         
[oracle@RH6 ~]$ ls -lt /u01/app/oracle/diag/rdbms/prod/prod/trace/|more
total 12056
-rw-r----- 1 oracle oinstall  51244 Nov 19 17:28 prod_ora_3681.trc
-rw-r----- 1 oracle oinstall    199 Nov 19 17:28 prod_ora_3681.trm
-rw-r--r-- 1 oracle oinstall 430401 Nov 19 17:22 alert_prod.log
-rw-r----- 1 oracle oinstall   8230 Nov 19 17:18 prod_ora_3629.trc
 
[oracle@RH6 ~]$ grep sequen /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_3681.trc
WAIT #10: nam='db file sequential read' ela= 13 file#=4 block#=130 blocks=1 obj#=76505 tim=1416389324098217
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=135 blocks=1 obj#=76505 tim=1416389324098716
WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=193 blocks=1 obj#=76505 tim=1416389324098758
WAIT #10: nam='db file sequential read' ela= 0 file#=6 block#=195 blocks=1 obj#=76505 tim=1416389324098837
WAIT #10: nam='db file sequential read' ela= 0 file#=6 block#=197 blocks=1 obj#=76505 tim=1416389324098837
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=199 blocks=1 obj#=76505 tim=1416389324098874
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=137 blocks=1 obj#=76505 tim=1416389324098917
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=140 blocks=1 obj#=76505 tim=1416389324099100
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=142 blocks=1 obj#=76505 tim=1416389324099144
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=200 blocks=1 obj#=76505 tim=1416389324099188
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=202 blocks=1 obj#=76505 tim=1416389324099230
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=204 blocks=1 obj#=76505 tim=1416389324099395
WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=206 blocks=1 obj#=76505 tim=1416389324099439
WAIT #10: nam='db file sequential read' ela= 223 file#=4 block#=149 blocks=1 obj#=76505 tim=1416389324100699
WAIT #10: nam='db file sequential read' ela= 13 file#=4 block#=151 blocks=1 obj#=76505 tim=1416389324100962
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=209 blocks=1 obj#=76505 tim=1416389324101019
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=211 blocks=1 obj#=76505 tim=1416389324101319
WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=213 blocks=1 obj#=76505 tim=1416389324101384
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=215 blocks=1 obj#=76505 tim=1416389324101418
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=153 blocks=1 obj#=76505 tim=1416389324101459
WAIT #10: nam='db file sequential read' ela= 10 file#=4 block#=156 blocks=1 obj#=76505 tim=1416389324101664
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=158 blocks=1 obj#=76505 tim=1416389324101716
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=216 blocks=1 obj#=76505 tim=1416389324101770
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=218 blocks=1 obj#=76505 tim=1416389324101813
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=220 blocks=1 obj#=76505 tim=1416389324101992
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=222 blocks=1 obj#=76505 tim=1416389324102036
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=165 blocks=1 obj#=76505 tim=1416389324102276
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=167 blocks=1 obj#=76505 tim=1416389324102309
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=233 blocks=1 obj#=76505 tim=1416389324102355
WAIT #10: nam='db file sequential read' ela= 32 file#=6 block#=235 blocks=1 obj#=76505 tim=1416389324102705
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=237 blocks=1 obj#=76505 tim=1416389324102931
WAIT #10: nam='db file sequential read' ela= 27 file#=6 block#=239 blocks=1 obj#=76505 tim=1416389324103182
WAIT #10: nam='db file sequential read' ela= 10 file#=6 block#=256 blocks=1 obj#=76505 tim=1416389324103344
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=129 blocks=1 obj#=76505 tim=1416389324103389
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=257 blocks=1 obj#=76505 tim=1416389324103423
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=521 blocks=1 obj#=76505 tim=1416389324103466
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=524 blocks=1 obj#=76505 tim=1416389324103678
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=526 blocks=1 obj#=76505 tim=1416389324103722
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=240 blocks=1 obj#=76505 tim=1416389324103766
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=242 blocks=1 obj#=76505 tim=1416389324103808
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=244 blocks=1 obj#=76505 tim=1416389324103872
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=246 blocks=1 obj#=76505 tim=1416389324103918
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=533 blocks=1 obj#=76505 tim=1416389324104170
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=535 blocks=1 obj#=76505 tim=1416389324104206
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=249 blocks=1 obj#=76505 tim=1416389324104250
WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=251 blocks=1 obj#=76505 tim=1416389324104449
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=253 blocks=1 obj#=76505 tim=1416389324104512
WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=255 blocks=1 obj#=76505 tim=1416389324104544
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=537 blocks=1 obj#=76505 tim=1416389324104584
WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=540 blocks=1 obj#=76505 tim=1416389324104759
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=542 blocks=1 obj#=76505 tim=1416389324104802
WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=544 blocks=1 obj#=76505 tim=1416389324104845
WAIT #10: nam='db file sequential read' ela= 76 file#=4 block#=546 blocks=1 obj#=76505 tim=1416389324105604
WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=548 blocks=1 obj#=76505 tim=1416389324105805
WAIT #10: nam='db file sequential read' ela= 6 file#=4 block#=550 blocks=1 obj#=76505 tim=1416389324105834
......

以上向我们展示了Oracle多个数据块读取的工作机制，当内存中已经有了某个数据块时，Oracle将不再从磁盘中读取它。这里使用一个循环来通过索引块访问的方式（每次读取一个数据块），将间隔的数据块读入到内存中。这样，当我们对T3表执行全表扫描时，尽管设置了参数：

16:22:19 SCOTT@ prod >alter session set db_file_multiblock_read_count=64;

但是由于没有连续的数据块可以读取了，所以Oracle每次也只能将一个数据块读取到内存。在等待事件中每一个WAIT#中 blocks=1说明每次I/O读取的数据块都为1，而且数据块的序号正好间隔为1，说明她们之间的那个数据块已经读取到内存中了。因为需要读取的数据块不再连续，所以此时不能一次读取多个数据块。

多数据块读取一般发生在：

FTS(FULL TABLE SCAN)

INDEX_FFS(INDEX FAST FULL SCAN)

0 0