实战经验：关于Oracle Delete数据后空间重用问题的测试

概述

近期一个客户的一张单表，每天delete7天前的数据，每天的数据增量没什么变化，理论上来说，delete释放的空间是可重用的，但发现该表段最近一直在增长，现在大小为300G，170G的75% – 100% free space blocks.通常通过 APPEND，直接路径加载的方式会直接扩展SEGMENT，不会重用DELETE的空间，但客户的系统排除了APPEND INSERT。

测试目的

我们知道ASSM的表INSERT查找可用空间机制，会在L2 Hint for inserts指上的L2块，再通过算法确定L1块，查找存在可用空间的数据块，完成INSERT。

客户的系统里，发现一个现象，DUMP SEGMENT HEADER，发现L2 Hint for inserts一直指向最后一个L2块。

 Segment Type: 1 nl2: 37     blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x1fd54208    <<<<
  Last Level 1 BMB:  0x1fd78207
  Last Level II BMB:  0x1fd54208      <<<<
  Last Level III BMB:  0x00000000
   Second Level Bitmap block DBAs 
   --------------------------------------------------------
   DBA 1:   0x15c00099
省略
   DBA 36:   0x1fc82208
   DBA 37:   0x1fd54208  <<<<

如果ORACLE只是通过L2块查找可用空间，L2 Hint for inserts总是指向最后的L2的话，之前L2块所管理的数据块上数据被DELETE掉，那也没有重用的机会。正常的系统不应该是这样的，下面在我的机器上做一个测试，验证存在多个L2块的系统上，如果前面的L2块管理的块上数据DELETE掉，在表扩展之前，会修改 L2 Hint for inserts的指向，到前面有可用空间的L2，从而重用DELETE释放的空间。

测试步骤如下：
1、创建测试表

SQL> create table TEST.tab1(id number,c varchar2(1000)) tablespace users;


Table created.

2、INSERT数据，直接产生1个以上L2块为止。我这里有两个L2

insert 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万数据
SQL> @seg test.tab1


    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
      4608 TEST                 TAB1                                                          TABLE                USERS                              589824         23        130


SQL> 


SQL> alter system dump datafile 23 block 130;


System altered.


  Segment Type: 1 nl2: 2      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x05c82088 <<<<
  Last Level 1 BMB:  0x05c8e087
  Last Level II BMB:  0x05c82088 <<<<
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 255  obj#: 73129  flag: 0x10000000
     
   Seco