溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
通過案例學(xué)調(diào)優(yōu)之--RECORDS_PER_BLOCK參數(shù)
RECORDS_PER_BLOCK參數(shù)用于設(shè)定每個(gè)BLOCK中記錄數(shù)的最大值，其先找到當(dāng)前表所有BLOCK中容納的最大行數(shù)，并會(huì)把這個(gè)數(shù)字記錄到數(shù)據(jù)字典，以后任何導(dǎo)致BLOCK行數(shù)超過這個(gè)數(shù)字的插入都會(huì)被拒絕。
RECORDS_PER_BLOCK參數(shù)是為位圖索引而生的，能夠改善位圖索引的存儲(chǔ)，減小位圖索引的長(zhǎng)度。這樣，利用該位圖索引的時(shí)候，就能獲得比較好的效率了。
測(cè)試案例：
1、表默認(rèn)的存儲(chǔ)分析
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 默認(rèn)值，T3表中的數(shù)據(jù)存儲(chǔ)在一個(gè)數(shù)據(jù)塊上。
2、通過RECORDS_PER_BLOCK參數(shù)分散數(shù)據(jù)塊的存儲(chǔ)
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個(gè)數(shù)據(jù)塊！
測(cè)試發(fā)現(xiàn)：執(zhí)行alter table test minimize records_per_block;之后，目前BLOCK中的記錄數(shù)（的最大值）會(huì)應(yīng)用到以后的新增數(shù)據(jù)中，也就是，當(dāng)以后再往表中INSERT數(shù)據(jù)時(shí)，每個(gè)BLOCK中可以包含的記錄數(shù)將與設(shè)定records_per_block之前的最大值保持一致。
需要注意的是：
不能對(duì)空表設(shè)定此參數(shù)。
每個(gè)BLOCK中可以包含的記錄數(shù)的最低下限是2。
不能在已經(jīng)有 bitmap 的表中使用records_per_block參數(shù)，也就是說，如果要使用records_per_block參數(shù)，必須先alter table xxx minimize records_per_block，然后才能在表上建立索引。
如果字段的類型、大小、個(gè)數(shù)發(fā)生了改變，那么就會(huì)導(dǎo)致一個(gè)比較差的結(jié)果，這就說明了，這項(xiàng)功能只在于使用在靜態(tài)的環(huán)境中，比如數(shù)據(jù)倉庫。
主要用途：
通過減少同一個(gè)block中的記錄數(shù)，使記錄分布于更多的數(shù)據(jù)塊中，可以優(yōu)化等待塊類型為data block的Buffer Busy Wait事件。
其主要用途是提高BITMAP INDEX的存儲(chǔ)性能
3、對(duì)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. 將數(shù)據(jù)塊以間隔的方式讀入內(nèi)存 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多個(gè)數(shù)據(jù)塊讀取的工作機(jī)制，當(dāng)內(nèi)存中已經(jīng)有了某個(gè)數(shù)據(jù)塊時(shí)，Oracle將不再從磁盤中讀取它。這里使用一個(gè)循環(huán)來通過索引塊訪問的方式（每次讀取一個(gè)數(shù)據(jù)塊），將間隔的數(shù)據(jù)塊讀入到內(nèi)存中。這樣，當(dāng)我們對(duì)T3表執(zhí)行全表掃描時(shí)，盡管設(shè)置了參數(shù)：
16:22:19 SCOTT@ prod >alter session set db_file_multiblock_read_count=64;
但是由于沒有連續(xù)的數(shù)據(jù)塊可以讀取了，所以O(shè)racle每次也只能將一個(gè)數(shù)據(jù)塊讀取到內(nèi)存。在等待事件中每一個(gè)WAIT#中 blocks=1說明每次I/O讀取的數(shù)據(jù)塊都為1，而且數(shù)據(jù)塊的序號(hào)正好間隔為1，說明她們之間的那個(gè)數(shù)據(jù)塊已經(jīng)讀取到內(nèi)存中了。因?yàn)樾枰x取的數(shù)據(jù)塊不再連續(xù)，所以此時(shí)不能一次讀取多個(gè)數(shù)據(jù)塊。
多數(shù)據(jù)塊讀取一般發(fā)生在：
FTS(FULL TABLE SCAN)
INDEX_FFS(INDEX FAST FULL SCAN)
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
