ITPub博客

首页 > 数据库 > Oracle > Heap Block Compress现象分析

Heap Block Compress现象分析

原创 Oracle 作者:oliseh 时间:2014-10-08 23:48:41 0 删除 编辑

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 directorydata之间的可用空间是否足够容纳这一行,如果够则直接插入,如果不够则检查整个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条进行1commità插入第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$mystatheap 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地址。若继续制造第45heap 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事件的统计


 

                            

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1292304/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616850