通过案例学调优之--RECORDS_PER_BLOCK参数
RECORDS_PER_BLOCK参数用于设定每个BLOCK中记录数的最大值，其先找到当前表所有BLOCK中容纳的最大行数，并会把这个数字记录到数据字典，以后任何导致BLOCK行数超过这个数字的插入都会被拒绝。
RECORDS_PER_BLOCK参数是为位图索引而生的，能够改善位图索引的存储，减小位图索引的长度。这样，利用该位图索引的时候，就能获得比较好的效率了。
测试案例：
1、表默认的存储分析
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参数分散数据块的存储
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访问分析
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)
