概述

近期一個客戶的一張單表，每天delete7天前的數(shù)據(jù)，每天的數(shù)據(jù)增量沒什么變化，理論上來說，delete釋放的空間是可重用的，但發(fā)現(xiàn)該表段最近一直在增長，現(xiàn)在大小為300G，170G的75% – 100% free space blocks.通常通過 APPEND，直接路徑加載的方式會直接擴展SEGMENT，不會重用DELETE的空間，但客戶的系統(tǒng)排除了APPEND INSERT。

測試目的

我們知道ASSM的表INSERT查找可用空間機制，會在L2 Hint for inserts指上的L2塊，再通過算法確定L1塊，查找存在可用空間的數(shù)據(jù)塊，完成INSERT。

客戶的系統(tǒng)里，發(fā)現(xiàn)一個現(xiàn)象，DUMP SEGMENT HEADER，發(fā)現(xiàn)L2 Hint for inserts一直指向最后一個L2塊。Segment Type: 1 nl2: 37 blksz: 8192 fbsz: 0L2 Array start offset: 0x00001434First Level 3 BMB: 0x00000000L2 Hint for inserts: 0x1fd54208 <<<

如果ORACLE只是通過L2塊查找可用空間，L2 Hint for inserts總是指向最后的L2的話，之前L2塊所管理的數(shù)據(jù)塊上數(shù)據(jù)被DELETE掉，那也沒有重用的機會。正常的系統(tǒng)不應(yīng)該是這樣的，下面在我的機器上做一個測試，驗證存在多個L2塊的系統(tǒng)上，如果前面的L2塊管理的塊上數(shù)據(jù)DELETE掉，在表擴展之前，會修改 L2 Hint for inserts的指向，到前面有可用空間的L2，從而重用DELETE釋放的空間。

測試步驟如下：

1、創(chuàng)建測試表SQL> create table TEST.tab1(id number,c varchar2(1000)) tablespace users;Table created.

2、INSERT數(shù)據(jù)，直接產(chǎn)生1個以上L2塊為止。我這里有兩個L2insert into TEST.tab1 select 0*100000+rownum,rpad('a',1000,'a') from dual connect by rownum<100000;insert into TEST.tab1 select 1*100000+rownum,rpad('a',1000,'a') from dual connect by rownum<100000;insert into TEST.tab1 select 40*100000+rownum,rpad('a',1000,'a') from dual connect by rownum<100000;這里INSERT了大400萬數(shù)據(jù)SQL> @seg test.tab1SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------4608 TEST TAB1 TABLE USERS 589824 23 130SQL>SQL> alter system dump datafile 23 block 130;System altered.Segment Type: 1 nl2: 2 blksz: 8192 fbsz: 0L2 Array start offset: 0x00001434First Level 3 BMB: 0x00000000L2 Hint for inserts: 0x05c82088 <<<

3、當(dāng)前的空間實用情況set serverout onexec show_space('TAB1','TEST');Total Blocks............................589824Total Bytes.............................4831838208Unused Blocks...........................0Unused Bytes............................0Last Used Ext FileId....................23Last Used Ext BlockId...................581760Last Used Block.........................8192*************************************************The segment is analyzed0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............125% -- 50% free space bytes.............819250% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........23775% -- 100% free space bytes............1941504Unused Blocks...........................2816Unused Bytes............................23068672Total Blocks............................585709Total bytes.............................4798128128PL/SQL procedure successfully completed.

4、 DELETE 50萬數(shù)據(jù)，因為我INSERT是順序的，DELETE id<500000的數(shù)據(jù)，應(yīng)該釋放的是段的前面的一批BLOCKSSQL> delete TEST.tab1 where id<500000;499995 rows deleted.commit;

5、再查看空間使用情況

75% – 100% free space blocks從DELETE前的237 增長到 71665。set serverout onexec show_space('TAB1','TEST');SQL>SQL> Total Blocks............................589824Total Bytes.............................4831838208Unused Blocks...........................0Unused Bytes............................0Last Used Ext FileId....................23Last Used Ext BlockId...................581760Last Used Block.........................8192*************************************************The segment is analyzed0% -- 25% free space blocks.............00% -- 25% free space bytes..............025% -- 50% free space blocks............125% -- 50% free space bytes.............819250% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........71665 <<<<75% -- 100% free space bytes............587079680Unused Blocks...........................2816Unused Bytes............................23068672Total Blocks............................514281Total bytes.............................4212989952PL/SQL procedure successfully completed.SQL>

6、INSERT 999行數(shù)據(jù)進行測試

因為當(dāng)前 L2 Hint for inserts指向的L2上仍然有可用空間，會發(fā)現(xiàn)下面的INSERT并沒有使用DELETE釋放的這部分空間。SQL> insert into TEST.tab1 select 0+rownum,rpad('a',1000,'a') from dual connect by rownum<1000;999 rows created.SQL> SQL> commit;Commit complete.SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where tablespace_name='USERS' and owner='TEST' and segment_name='TAB1' order by extent_id;EXTENT_ID FILE_ID BLOCK_ID BLOCKS---------- ---------- ---------- ----------0 23 128 81 23 136 82 23 144 83 23 152 84 23 160 85 23 168 86 23 176 87 23 184 88 23 192 89 23 200 810 23 208 811 23 216 812 23 224 813 23 232 814 23 240 815 23 248 816 23 256 12817 23 384 12818 23 512 12819 23 640 12820 23 768 12821 23 896 12822 23 1024 12823 23 1152 128....----該段的前部分塊上并沒有我們剛剛INSERT的數(shù)據(jù)，說明沒有重用DELETE的空間。SQL> select id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from TEST.tab1 where2 rowid >=dbms_rowid.rowid_create(1,73129,23,127,1) and rowid <=dbms_rowid.rowid_create(1,73129,23,1024,500)3 and id<100;no?rows?selected

7、繼續(xù)INSERT 10萬行數(shù)據(jù)

因為最后的L2塊上，高水位下面沒有這么多的空間存放這10萬行數(shù)據(jù)，可以看到這次重用了之前DELETE釋放的空間，同時看到L2 Hint for inserts現(xiàn)在指向到了第一個L2塊。SQL> insert into TEST.tab1 select 0+rownum,rpad('a',1000,'a') from dual connect by rownum<100000;99999 rows created.SQL> commit;Commit complete.----INSERT 10萬行后，Total Blocks 589824，表總BLOCKS沒有增長，75% -- 100% free space blocks從71665減少到60053SQL> set serverout onSQL> exec show_space('TAB1','TEST');Total Blocks............................589824Total Bytes.............................4831838208Unused Blocks...........................0Unused Bytes............................0Last Used Ext FileId....................23Last Used Ext BlockId...................581760Last Used Block.........................8192*************************************************The segment is analyzed0% -- 25% free space blocks.............10% -- 25% free space bytes..............819225% -- 50% free space blocks............025% -- 50% free space bytes.............050% -- 75% free space blocks............050% -- 75% free space bytes.............075% -- 100% free space blocks...........6005375% -- 100% free space bytes............491954176Unused Blocks...........................0Unused Bytes............................0Total Blocks............................528709Total bytes.............................4331184128PL/SQL procedure successfully completed.---可以看到前段的1000個塊上有我們剛剛INSERT的數(shù)據(jù)，說明重用了DELETE釋放的空間SQL> select id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from TEST.tab1 where2 rowid >=dbms_rowid.rowid_create(1,73129,23,127,1) and rowid <=dbms_rowid.rowid_create(1,73129,23,10240,500)3 and id<100000 and rownum<100;ID FILE_ID BLOCK_ID---------- ---------- ----------20445 23 17720446 23 17720447 23 17720448 23 17720449 23 17720450 23 17720451 23 177。。。。。。。。20418 23 18820419 23 18820420 23 18820421 23 18820422 23 18820423 23 18820424 23 18920425 23 18920426 23 18920427 23 18920428 23 18920429 23 18920430 23 18920431 23 19020432 23 19020433 23 19020434 23 19020435 23 19020436 23 19020437 23 19020438 23 19199 rows selected.SQL>SQL> alter system dump datafile 23 block 130;System altered.L2 Hint for inserts的指向也從第二個L2塊，換成了第一個L2塊。Extent Control Header-----------------------------------------------------------------Extent Header:: spare1: 0 spare2: 0 #extents: 255 #blocks: 589824last map 0x00000000 #maps: 0 offset: 2716Highwater:: 0x05c90080 ext#: 254 blk#: 8192 ext size: 8192#blocks in seg. hdr's freelists: 0#blocks below: 588763mapblk 0x00000000 offset: 254Unlocked--------------------------------------------------------Low HighWater Mark :Highwater:: 0x05c90080 ext#: 254 blk#: 8192 ext size: 8192#blocks in seg. hdr's freelists: 0#blocks below: 588763mapblk 0x00000000 offset: 254Level 1 BMB for High HWM block: 0x05c8e087Level 1 BMB for Low HWM block: 0x05c8e087--------------------------------------------------------Segment Type: 1 nl2: 2 blksz: 8192 fbsz: 0L2 Array start offset: 0x00001434First Level 3 BMB: 0x00000000L2 Hint for inserts: 0x05c00081 <<<<<

總結(jié)

猜測正常情況下，L2 Hint for inserts指向的L2塊找不到可用空間的時侯，在嘗試ALLOCATE新的EXTENT的前，會去判斷之前的L2塊有沒有可用空間。如果有，會修改L2 Hint for inserts，去使用之前的L2管理的塊上釋放出來的空間。

客戶環(huán)境中，大量75% – 100% free space blocks的情況下，INSERT不重用釋放的空間問題，還需要繼續(xù)分析！

作者

范計杰，云和恩墨技術(shù)顧問，5年大型ORACLE數(shù)據(jù)庫維護經(jīng)驗，擅長性能調(diào)優(yōu)、故障處理等。

墨天輪原文鏈接：https://www.modb.pro/db/43425(復(fù)制到瀏覽器或者點擊“閱讀原文”立即查看)

總結(jié)

以上是生活随笔為你收集整理的oracle delete 空间增加,实战经验：关于Oracle Delete数据后空间重用问题的测试的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。