通过案例学调优之--RECORDS_PER_BLOCK参数
发表于：2025-02-03 作者：千家信息网编辑
千家信息网最后更新 2025年02月03日，通过案例学调优之--RECORDS_PER_BLOCK参数RECORDS_PER_BLOCK参数用于设定每个BLOCK中记录数的最大值，其先找到当前表所有BLOCK中容纳的最大行数，并会把这个数字记录
千家信息网最后更新 2025年02月03日通过案例学调优之--RECORDS_PER_BLOCK参数
通过案例学调优之--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))--------------------------------------------------- 115:53:09 SCOTT@ prod >col segment_name for a2015: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))--------------------------------------------------- 12816: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 multNAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------db_file_multiblock_read_count integer 28parallel_adaptive_multi_user boolean TRUE17:32:42 SCOTT@ prod >col object_name for a2017:32:49 SCOTT@ prod >select object_name,object_id from user_objects where object_name='T3';OBJECT_NAME OBJECT_ID-------------------- ----------T3 7650516:22:19 SCOTT@ prod >alter session set db_file_multiblock_read_count=64;Session altered.将数据块以间隔的方式读入内存16:09:03 SCOTT@ prod >declare16:09:20 2 num number;16:09:25 3 begin16:09:29 4 for i in 1..6416:09:34 5 loop16: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 76506188 rows selected.16:14:20 SYS@ prod >grant alter session to scott;Grant succeeded.16:14:39 SYS@ prod >conn scott/tigerConnected.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 trace16:15:37 SCOTT@ prod >select * from t3 ;256 rows selected.Elapsed: 00:00:00.02Execution 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/|moretotal 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.trcWAIT #10: nam='db file sequential read' ela= 13 file#=4 block#=130 blocks=1 obj#=76505 tim=1416389324098217WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=135 blocks=1 obj#=76505 tim=1416389324098716WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=193 blocks=1 obj#=76505 tim=1416389324098758WAIT #10: nam='db file sequential read' ela= 0 file#=6 block#=195 blocks=1 obj#=76505 tim=1416389324098837WAIT #10: nam='db file sequential read' ela= 0 file#=6 block#=197 blocks=1 obj#=76505 tim=1416389324098837WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=199 blocks=1 obj#=76505 tim=1416389324098874WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=137 blocks=1 obj#=76505 tim=1416389324098917WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=140 blocks=1 obj#=76505 tim=1416389324099100WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=142 blocks=1 obj#=76505 tim=1416389324099144WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=200 blocks=1 obj#=76505 tim=1416389324099188WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=202 blocks=1 obj#=76505 tim=1416389324099230WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=204 blocks=1 obj#=76505 tim=1416389324099395WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=206 blocks=1 obj#=76505 tim=1416389324099439WAIT #10: nam='db file sequential read' ela= 223 file#=4 block#=149 blocks=1 obj#=76505 tim=1416389324100699WAIT #10: nam='db file sequential read' ela= 13 file#=4 block#=151 blocks=1 obj#=76505 tim=1416389324100962WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=209 blocks=1 obj#=76505 tim=1416389324101019WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=211 blocks=1 obj#=76505 tim=1416389324101319WAIT #10: nam='db file sequential read' ela= 7 file#=6 block#=213 blocks=1 obj#=76505 tim=1416389324101384WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=215 blocks=1 obj#=76505 tim=1416389324101418WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=153 blocks=1 obj#=76505 tim=1416389324101459WAIT #10: nam='db file sequential read' ela= 10 file#=4 block#=156 blocks=1 obj#=76505 tim=1416389324101664WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=158 blocks=1 obj#=76505 tim=1416389324101716WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=216 blocks=1 obj#=76505 tim=1416389324101770WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=218 blocks=1 obj#=76505 tim=1416389324101813WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=220 blocks=1 obj#=76505 tim=1416389324101992WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=222 blocks=1 obj#=76505 tim=1416389324102036WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=165 blocks=1 obj#=76505 tim=1416389324102276WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=167 blocks=1 obj#=76505 tim=1416389324102309WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=233 blocks=1 obj#=76505 tim=1416389324102355WAIT #10: nam='db file sequential read' ela= 32 file#=6 block#=235 blocks=1 obj#=76505 tim=1416389324102705WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=237 blocks=1 obj#=76505 tim=1416389324102931WAIT #10: nam='db file sequential read' ela= 27 file#=6 block#=239 blocks=1 obj#=76505 tim=1416389324103182WAIT #10: nam='db file sequential read' ela= 10 file#=6 block#=256 blocks=1 obj#=76505 tim=1416389324103344WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=129 blocks=1 obj#=76505 tim=1416389324103389WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=257 blocks=1 obj#=76505 tim=1416389324103423WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=521 blocks=1 obj#=76505 tim=1416389324103466WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=524 blocks=1 obj#=76505 tim=1416389324103678WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=526 blocks=1 obj#=76505 tim=1416389324103722WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=240 blocks=1 obj#=76505 tim=1416389324103766WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=242 blocks=1 obj#=76505 tim=1416389324103808WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=244 blocks=1 obj#=76505 tim=1416389324103872WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=246 blocks=1 obj#=76505 tim=1416389324103918WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=533 blocks=1 obj#=76505 tim=1416389324104170WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=535 blocks=1 obj#=76505 tim=1416389324104206WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=249 blocks=1 obj#=76505 tim=1416389324104250WAIT #10: nam='db file sequential read' ela= 9 file#=6 block#=251 blocks=1 obj#=76505 tim=1416389324104449WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=253 blocks=1 obj#=76505 tim=1416389324104512WAIT #10: nam='db file sequential read' ela= 8 file#=6 block#=255 blocks=1 obj#=76505 tim=1416389324104544WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=537 blocks=1 obj#=76505 tim=1416389324104584WAIT #10: nam='db file sequential read' ela= 9 file#=4 block#=540 blocks=1 obj#=76505 tim=1416389324104759WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=542 blocks=1 obj#=76505 tim=1416389324104802WAIT #10: nam='db file sequential read' ela= 8 file#=4 block#=544 blocks=1 obj#=76505 tim=1416389324104845WAIT #10: nam='db file sequential read' ela= 76 file#=4 block#=546 blocks=1 obj#=76505 tim=1416389324105604WAIT #10: nam='db file sequential read' ela= 7 file#=4 block#=548 blocks=1 obj#=76505 tim=1416389324105805WAIT #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)
数据
参数
索引
内存
存储
最大
位图
最大值
案例
也就是
事件
多个
数字
方式
用途
类型
分析
测试
最低
一致
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
矿机服务器配置要求
眉山软件开发费用是多少
数据库好难学啊
邮件smtp服务器
电信免流服务器
java 于数据库连接
网络安全敏感的国家
musql数据库是什么型数据库
方象erp为什么连不上数据库
方舟服务器实用模组
vue初始化数据库
国家网络安全应急服务支撑
阿里云rpa 数据库
恕瑞玛的服务器
腾讯网络安全生态舰队
app软件开发专利申请
文正教务数据库
RF查询数据库返回str型
数据库分表后修改数据
软件开发格式更换复杂么
绵阳工业软件开发
苹果关闭手机定位服务器
江苏省软件开发有限公司
海典软件开发
有网络技术 该往什么方向
网络安全电子商务领域
杭州学乐云网络技术有限公司
软件开发是零税率
中央网络安全和信息化职责
服务器组装是什么行业
