oracle数据块pctfree变化查看

这篇文章对文章ORACLE空间管理实验6：块管理之ASSM下插入操作--高水位的影响及大并发插入的性能问题中第实验二：查看pctfree变化，做个实验补充。

思路：

1、创建一个表t1

2、通过t1表的segment的segment_header找到L2 -> L1从而查看L1中的pctfree变化

3、让数据块pctfree变化的方法是插入数据再删除数据

SQL> create table t1(name varchar2(1000)) tablespace test1;

Table created.

SQL> insert into t1 select rpad('A', 999, 'A') from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select OWNER, SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK from dba_segments where SEGMENT_NAME='T1' and owner='TEST';

OWNER  SEGMENT_NAME  HEADER_FILE HEADER_BLOCK
------ ------------- ----------- ------------
TEST   T1                      9          146

SQL> alter system dump datafile 9 block 146;

System altered.

通过dump文件信息找到L2块号

  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x02400091
  Last Level 1 BMB:  0x02400090
  Last Level II BMB:  0x02400091
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 17370  flag: 0x10000000
  Inc # 0 
从上面可以看到L2块号是 0x91 -> 145(十进制)

再dump 9号文件145号块

SQL> alter system dump datafile 9 block 146;

System altered.

下面是对我有用的信息

Dump of Second Level Bitmap Block
   number: 1       nfree: 1       ffree: 0      pdba:     0x02400092 
   Inc #: 0 Objd: 17370
  opcode:0 
 xid: 
  L1 Ranges :
  --------------------------------------------------------
   0x02400090  Free: 5 Inst: 1 
  
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 9 minblk 145 maxblk 145
从上面我们看到L1号块时 0x90 -> 144(十进制)

再dump 9号文件144号块

SQL> alter system dump datafile 9 block 144;

System altered.

下面是对我们有用的信息

Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x02400091   poffset: 0     
   unformatted: 0       total: 8         first useful block: 3      
   owning instance : 1
   instance ownership changed at 08/24/2014 18:42:49
   Last successful Search 08/24/2014 18:42:49
   Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 5       --nf4 5 ：代表 <75-100% free>类型的快有5个
 
   Extent Map Block Offset: 4294967295 
   First free datablock : 3      
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Dealloc scn: 0.0 
   Flag: 0x00000001 (-/-/-/-/-/HWM)
   Inc #: 0 Objd: 17370 
  HWM Flag: HWM Set
      Highwater::  0x02400098  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x02400090  Length: 8      Offset: 0      
  
   0:Metadata   1:Metadata   2:Metadata   3:75-100% free
   4:75-100% free   5:75-100% free   6:75-100% free   7:75-100% free  --看出有五块的pctfree都是在 <75-100% free>类型中
  --------------------------------------------------------
这时我们向表t1中插入数据查看 以上两处的变化

SQL> insert into t1 select rpad('B', 999, 'B') from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 9 block 144;

System altered.

Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x02400091   poffset: 0     
   unformatted: 0       total: 8         first useful block: 3      
   owning instance : 1
   instance ownership changed at 08/24/2014 18:42:49
   Last successful Search 08/24/2014 18:42:49
   Freeness Status:  nf1 0      nf2 0      nf3 1      nf4 4      --看到有一个块变化了在nf3类型中<50-75% free>
 
   Extent Map Block Offset: 4294967295 
   First free datablock : 3      
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Dealloc scn: 0.0 
   Flag: 0x00000001 (-/-/-/-/-/HWM)
   Inc #: 0 Objd: 17370 
  HWM Flag: HWM Set
      Highwater::  0x02400098  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x02400090  Length: 8      Offset: 0      
  
   0:Metadata   1:Metadata   2:Metadata   3:75-100% free
   4:75-100% free   5:75-100% free   6:75-100% free   7:50-75% free   --看到一个块pctfree已经发生变化

下面我们再删除一行看看块的pctfree变化

SQL> delete from t1 where name like '%B%';

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 9 block 144;

System altered.

下面是对我们有用的信息

Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x02400091   poffset: 0     
   unformatted: 0       total: 8         first useful block: 3      
   owning instance : 1
   instance ownership changed at 08/24/2014 18:42:49
   Last successful Search 08/24/2014 18:42:49
   Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 5      --变回原来的样子
 
   Extent Map Block Offset: 4294967295 
   First free datablock : 3      
   Bitmap block lock opcode 0
   Locker xid:     :  0x0000.000.00000000
   Dealloc scn: 0.0 
   Flag: 0x00000001 (-/-/-/-/-/HWM)
   Inc #: 0 Objd: 17370 
  HWM Flag: HWM Set
      Highwater::  0x02400098  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x02400090  Length: 8      Offset: 0      
  
   0:Metadata   1:Metadata   2:Metadata   3:75-100% free
   4:75-100% free   5:75-100% free   6:75-100% free   7:75-100% free --pctfree变回原来的样子