溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--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)
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
