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-行字节数
第二次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事件的统计
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/53956/viewspace-1292304/ ，如需转载，请注明出处，否则将追究法律责任。
