Heap Block Compress定義：

When a session inserts a row into a block (or updates a row in a way that increases its size) it has to check whether the row will fit into the available space. It’s possibe that there is enough space, but not at the top of the free space heap in the block. If this is the case, Oracle packs the block downwards to coalesce all the free space into a single chunk between the rows (stored at the end of the block) and the row directory. Since this is an expensive operation, Oracle only does it when absolutely necessary.大致的意思是一行新的資料插入某個block時會檢查row directory和data之間的可用空間是否足夠容納這一行，如果夠則直接插入，如果不夠則檢查整個data block裡是否有足夠的可用空間(這個時候可用空間可能是不連續的，與已被使用的部分互相間隔)，如果有的話對塊裡的行往block底部進行壓縮，以儘可能的在上部留出較多的連續空間存放新插入的資料。

有必要先介紹一下data block header的幾個欄位含義：

tsiz: 0x1f98 =>total data area size

hsiz: 0x32 =>data header size

pbl: 0x11085fa64

76543210

flag=--------

ntab=1 =>number of tables

nrow=16 =>number of rows

frre=-1 =>The first free row entry in the row directory,if=-1,no free entry, you have to add one

fsbo=0x32 =>free space begin offset

fseo=0x176 =>free space end offset

avsp=0x18f6 =>available space in the block

tosp=0x1aa4 =>total available space when all transactions commit

實驗思路：

建立t1表，依次插入19條記錄並插入記錄à刪除這19條記錄，commità再依次插入3條記錄，每插入1條進行1次commità插入第3條時發生了首次heap block compress現象à繼續模擬第二、三次的heap block compress現象

實驗過程：

一、 製造首次heap block compress

##建表、灌入19條資料

create table t1 (col1 varchar2(500)) tablespace ts_zwcs01_dat;

declare

begin

for i in 1..19 loop

insert into t1 values (lpad(i,400));

end loop;

commit;

end;

/

##檢視19條資料分佈在哪些塊裡

SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.rowid_block_number(rowid) blkno,count(1) from t1 group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) ;

RFILE# BLKNO COUNT(1)

---------- ---------- ----------

230 145371 2

230 145375 17

##刪除19條資料，刪除後的block dump內容詳見

delete t1;

commit;

塊內容摘錄，可以發現row entry依然指向行的offset地址，只不過地址裡的內容已經變為了--HDFL--，表名這行原先存有記錄，目前刪除了：

tsiz: 0x1f98

hsiz: 0x34

pbl: 0x11085fa64

76543210

flag=--------

ntab=1

nrow=17

frre=-1

fsbo=0x34

fseo=0x4a2

avsp=0x46e

tosp=0x1f64

0xe:pti[0] nrow=17 offs=0

0x12:pri[0] offs=0x1e02

0x14:pri[1] offs=0x1c6c

0x16:pri[2] offs=0x1ad6

0x18:pri[3] offs=0x1940

0x1a:pri[4] offs=0x17aa

0x1c:pri[5] offs=0x1614

0x1e:pri[6] offs=0x147e

0x20:pri[7] offs=0x12e8

0x22:pri[8] offs=0x1152

0x24:pri[9] offs=0xfbc

0x26:pri[10] offs=0xe26

0x28:pri[11] offs=0xc90

0x2a:pri[12] offs=0xafa

0x2c:pri[13] offs=0x964

0x2e:pri[14] offs=0x7ce

0x30:pri[15] offs=0x638

0x32:pri[16] offs=0x4a2

block_row_dump:

tab 0, row 0, @0x1e02

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 1, @0x1c6c

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 2, @0x1ad6

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 3, @0x1940

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 4, @0x17aa

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 5, @0x1614

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 6, @0x147e

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 7, @0x12e8

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 8, @0x1152

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 9, @0xfbc

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 10, @0xe26

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 11, @0xc90

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 12, @0xafa

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 13, @0x964

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 14, @0x7ce

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 15, @0x638

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 16, @0x4a2

tl: 2 fb: --HDFL-- lb: 0x2

end_of_block_dump

##新插入第一條記錄，插入後的塊使用情況

insert into t1 values (lpad(20,400));

commit;

alter system checkpoint;

SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.rowid_block_number(rowid) blkno,count(1) from t1 group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)

2 ;

RFILE# BLKNO COUNT(1)

---------- ---------- ----------

230 145375 1

##dump block 230/145375

alter system checkpoint;

alter system dump datafile 230 block 145375; => dump 結果詳見145375.ist20.txt

下面是dump結果部分摘錄，可以看出插入的這條記錄目前在0x30c這個地址，是比較靠近block頂部的一個地址，其它row entry對應的都是sfll=X，這個應該和delete以後及時commit有關。在第二、三次模擬heap block compress的過程中都是delete後沒有commit的情況，屆時可以看出差別

tsiz: 0x1f98

hsiz: 0x34

pbl: 0x11085fa64

76543210

flag=--------

ntab=1

nrow=17

frre=1

fsbo=0x34

fseo=0x30c

avsp=0x1dce

tosp=0x1dce

0xe:pti[0] nrow=17 offs=0

0x12:pri[0] offs=0x30c

0x14:pri[1] sfll=2

0x16:pri[2] sfll=3

0x18:pri[3] sfll=4

0x1a:pri[4] sfll=5

0x1c:pri[5] sfll=6

0x1e:pri[6] sfll=7

0x20:pri[7] sfll=8

0x22:pri[8] sfll=9

0x24:pri[9] sfll=10

0x26:pri[10] sfll=11

0x28:pri[11] sfll=12

0x2a:pri[12] sfll=13

0x2c:pri[13] sfll=14

0x2e:pri[14] sfll=15

0x30:pri[15] sfll=16

0x32:pri[16] sfll=-1

##新插入第二條資料，查詢v$mystat未有heap block compress事件發生

insert into t1 values (lpad(21,400));

commit;

select value from v$mystat ms, v$statname sn

where sn.name = 'heap block compress'

and ms.statistic# = sn.statistic#;

##dump block 230/145375

alter system checkpoint;

alter system dump datafile 230 block 145375; => dump 結果詳見145375.ist21.txt

下面是dump結果部分摘錄，可以看出插入的這條記錄目前在0x176這個地址，0x30c-0x176=0x196，換算成十進位制是406bytes，即是一條記錄的長度，0x176這個地址就是fseo=0x176中定義的free space end offset位置，可以推算下一次fseo將會變成0x176-0x196

<0，由於值會小於0所以下一次插入時必會引發heap block compress

tsiz: 0x1f98

hsiz: 0x34

pbl: 0x11085fa64

76543210

flag=--------

ntab=1

nrow=17

frre=2

fsbo=0x34

fseo=0x176

avsp=0x1c38

tosp=0x1c38

0xe:pti[0] nrow=17 offs=0

0x12:pri[0] offs=0x30c

0x14:pri[1] offs=0x176

0x16:pri[2] sfll=3

0x18:pri[3] sfll=4

0x1a:pri[4] sfll=5

0x1c:pri[5] sfll=6

0x1e:pri[6] sfll=7

0x20:pri[7] sfll=8

0x22:pri[8] sfll=9

0x24:pri[9] sfll=10

0x26:pri[10] sfll=11

0x28:pri[11] sfll=12

0x2a:pri[12] sfll=13

0x2c:pri[13] sfll=14

0x2e:pri[14] sfll=15

0x30:pri[15] sfll=16

0x32:pri[16] sfll=-1

##新插入第三條資料，查詢v$mystat有heap block compress事件發生

insert into t1 values (lpad(22,400));

commit;

select value from v$mystat ms, v$statname sn

where sn.name = 'heap block compress'

and ms.statistic# = sn.statistic#;

##dump block 230/145375

alter system checkpoint;

alter system dump datafile 230 block 145375; => dump 結果詳見145375.ist22.txt

下面是dump結果部分摘錄，可以看出插入的這條記錄目前在0x1ad6這個地址，是比較靠近block尾部的地址，前兩次插入記錄其也被向下挪到了靠近block底部的大地址上

tsiz: 0x1f98

hsiz: 0x18

pbl: 0x11085fa64

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1ad6

avsp=0x1abe

tosp=0x1abe

0xe:pti[0] nrow=3 offs=0

0x12:pri[0] offs=0x1e02

0x14:pri[1] offs=0x1c6c

0x16:pri[2] offs=0x1ad6

二、 製造第二次heap block compress，距離上次發生相隔14條記錄

##接著上面的場景繼續插入記錄，與上面場景不同的是，插入1條刪除1條，不commit

insert into t1 values (lpad(23,400));

delete t1 where col1=lpad(23,400);

insert into t1 values (lpad(24,400));

delete t1 where col1=lpad(24,400);

。。。。此處省略重複操作，直到下面的記錄插入後再次觀察到了heap block compress現象

insert into t1 values (lpad(36,400));

delete t1 where col1=lpad(36,400);

alter system checkpoint;

三、 製造第三次heap block compress，距離上次發生相隔16條記錄

insert into t1 values (lpad(37,400));

delete t1 where col1=lpad(37,400);

insert into t1 values (lpad(38,400));

delete t1 where col1=lpad(38,400);

。。。。此處省略重複操作，直到下面的記錄插入後再次觀察到了heap block compress現象

insert into t1 values (lpad(52,400));

delete t1 where col1=lpad(52,400);

四、 第二次和第三次heap block compress現象發生前後的block dump比較

每一次compress之後fseo就會變大，fsbo隨著row entry數量的增加每次遞增2；

當fseo-行位元組數時，就會觸發heap block compress，第二次距離第一次heap block compress間隔14條記錄，第三次距離第二次heap block compress間隔16條記錄，14<16的原因在於insert into t1 values (lpad(20,400))記錄插入後並沒有使用offs=0x1940(0x1ad6-0x196)這個地址，而是使用了0x12e8這個地址，0x12e8是在表中存有19條記錄的時候，第8條記錄pri[7]所處的offset地址。若繼續製造第4、5次heap block compress事件，可以推算出每一次發生heap block compress事件間隔的記錄數都會小於等於前一次

第二次compress前第二次compress 第三次compress之前第三次compress

tsiz: 0x1f98		tsiz: 0x1f98		tsiz: 0x1f98		tsiz: 0x1f98
hsiz: 0x32		hsiz: 0x34		hsiz: 0x52		hsiz: 0x54
pbl: 0x11085fa64		pbl: 0x11085fa64		pbl: 0x11085fa64		pbl: 0x11085fa64
76543210		76543210		76543210		76543210
flag=--------		flag=--------		flag=--------		flag=--------
ntab=1		ntab=1		ntab=1		ntab=1
nrow=16		nrow=17		nrow=32		nrow=33
frre=-1		frre=-1		frre=-1		frre=-1
fsbo=0x32		fsbo=0x34		fsbo=0x52		fsbo=0x54
fseo=0x176		fseo=0x1926		fseo=0x15c		fseo=0x1906
avsp=0x18f6		avsp=0x18f2		avsp=0x18b6		avsp=0x18b2
tosp=0x1aa4		tosp=0x1aa2		tosp=0x1a84		tosp=0x1a82
0xe:pti[0]	nrow=16 offs=0	0xe:pti[0]	nrow=17 offs=0	0xe:pti[0]	nrow=32 offs=0	0xe:pti[0]	nrow=33 offs=0
0x12:pri[0]	offs=0x1e02	0x12:pri[0]	offs=0x1e02	0x12:pri[0]	offs=0x1e02	0x12:pri[0]	offs=0x1e02
0x14:pri[1]	offs=0x1c6c	0x14:pri[1]	offs=0x1c6c	0x14:pri[1]	offs=0x1c6c	0x14:pri[1]	offs=0x1c6c
0x16:pri[2]	offs=0x1ad6	0x16:pri[2]	offs=0x1ad6	0x16:pri[2]	offs=0x1ad6	0x16:pri[2]	offs=0x1ad6
0x18:pri[3]	offs=0x12e8	0x18:pri[3]	offs=0x1ad4	0x18:pri[3]	offs=0x1ad4	0x18:pri[3]	offs=0x1ad4
0x1a:pri[4]	offs=0x1152	0x1a:pri[4]	offs=0x1ad2	0x1a:pri[4]	offs=0x1ad2	0x1a:pri[4]	offs=0x1ad2
0x1c:pri[5]	offs=0xfbc	0x1c:pri[5]	offs=0x1ad0	0x1c:pri[5]	offs=0x1ad0	0x1c:pri[5]	offs=0x1ad0
0x1e:pri[6]	offs=0x147e	0x1e:pri[6]	offs=0x1ace	0x1e:pri[6]	offs=0x1ace	0x1e:pri[6]	offs=0x1ace
0x20:pri[7]	offs=0xe26	0x20:pri[7]	offs=0x1acc	0x20:pri[7]	offs=0x1acc	0x20:pri[7]	offs=0x1acc
0x22:pri[8]	offs=0xc90	0x22:pri[8]	offs=0x1aca	0x22:pri[8]	offs=0x1aca	0x22:pri[8]	offs=0x1aca
0x24:pri[9]	offs=0xafa	0x24:pri[9]	offs=0x1ac8	0x24:pri[9]	offs=0x1ac8	0x24:pri[9]	offs=0x1ac8
0x26:pri[10]	offs=0x964	0x26:pri[10]	offs=0x1ac6	0x26:pri[10]	offs=0x1ac6	0x26:pri[10]	offs=0x1ac6
0x28:pri[11]	offs=0x7ce	0x28:pri[11]	offs=0x1ac4	0x28:pri[11]	offs=0x1ac4	0x28:pri[11]	offs=0x1ac4
0x2a:pri[12]	offs=0x638	0x2a:pri[12]	offs=0x1ac2	0x2a:pri[12]	offs=0x1ac2	0x2a:pri[12]	offs=0x1ac2
0x2c:pri[13]	offs=0x4a2	0x2c:pri[13]	offs=0x1ac0	0x2c:pri[13]	offs=0x1ac0	0x2c:pri[13]	offs=0x1ac0
0x2e:pri[14]	offs=0x30c	0x2e:pri[14]	offs=0x1abe	0x2e:pri[14]	offs=0x1abe	0x2e:pri[14]	offs=0x1abe
0x30:pri[15]	offs=0x176	0x30:pri[15]	offs=0x1abc	0x30:pri[15]	offs=0x1abc	0x30:pri[15]	offs=0x1abc
		0x32:pri[16]	offs=0x1926	0x32:pri[16]	offs=0x1926	0x32:pri[16]	offs=0x1aba
				0x34:pri[17]	offs=0x1790	0x34:pri[17]	offs=0x1ab8
				0x36:pri[18]	offs=0x15fa	0x36:pri[18]	offs=0x1ab6
				0x38:pri[19]	offs=0x1464	0x38:pri[19]	offs=0x1ab4
				0x3a:pri[20]	offs=0x12ce	0x3a:pri[20]	offs=0x1ab2
				0x3c:pri[21]	offs=0x1138	0x3c:pri[21]	offs=0x1ab0
				0x3e:pri[22]	offs=0xfa2	0x3e:pri[22]	offs=0x1aae
				0x40:pri[23]	offs=0xe0c	0x40:pri[23]	offs=0x1aac
				0x42:pri[24]	offs=0xc76	0x42:pri[24]	offs=0x1aaa
				0x44:pri[25]	offs=0xae0	0x44:pri[25]	offs=0x1aa8
				0x46:pri[26]	offs=0x94a	0x46:pri[26]	offs=0x1aa6
				0x48:pri[27]	offs=0x7b4	0x48:pri[27]	offs=0x1aa4
				0x4a:pri[28]	offs=0x61e	0x4a:pri[28]	offs=0x1aa2
				0x4c:pri[29]	offs=0x488	0x4c:pri[29]	offs=0x1aa0
				0x4e:pri[30]	offs=0x2f2	0x4e:pri[30]	offs=0x1a9e
				0x50:pri[31]	offs=0x15c	0x50:pri[31]	offs=0x1a9c
						0x52:pri[32]	offs=0x1906

結論:heap block compress主要發生在同一個塊上insert記錄後再delete，之後又insert的情況，可以透過v$systat中的heap block compress事件來了解系統中heap block compress的發生率，在awr裡也有關於heap block compress事件的統計

Heap Block Compress現象分析

相關文章