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

通过案例学调优之--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)