oracle ASSM内部存储研究大揭密(4)

回复： ASSM内部存储研究大揭密	Copy to clipboard
Posted by: wanghai Posted on: 2003-08-22 23:57 piner辛苦了，写的不错，我再补充一下当使用assm的情况下，修改表的pctfree设置后需要使用dbms_repair.segment_fix_status来重建bitmap

回复： ASSM内部存储研究大揭密	Copy to clipboard
Posted by: parrotao Posted on: 2003-08-24 22:36 少有的好文章呀

两个问题

Copy to clipboard

Posted by: overmars
Posted on: 2003-08-26 16:27

1、有点不懂你的“大家该知道高水点的位置是多少了吧，应该是56+8（文件头）+1=65”中的“1”是怎么得来的呢？其实通过dba_extents可以知道，表是从第9个块开始写数据的。HWM=56+9=65

2、你说的这个情况只是适用于0-15个extents（每个extents大小是64K，8个8K的数据块）,从第16个extents开始没有extents的大小是128个8K的数据块,这时它的管理是每128个块中包含2个块来做空间管理。

回复： ASSM内部存储研究大揭密

Copy to clipboard

Posted by: overmars
Posted on: 2003-08-26 16:37

FS1 Blocks = 1
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 17
Full Blocks = 352

PL/SQL 过程已成功完成。
在这里
FS1表示0-25%空闲空间的块
FS2表示25-50%空闲空间的块
FS3表示50-75%空闲空间的块
IXDBA.NET技术社区
FS4表示 75-100%空闲空间的块
SQL> exec show_space('ASSM','AUTO');
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................5
Last Used Ext BlockId...................264
Last Used Block.........................128

PL/SQL 过程已成功完成。

SQL> select extent_id,block_id,blocks from dba_extents where segment_name='ASSM'

EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
         0 9 8
         1 17 8
         2 25 8
         3 33 8
         4 41 8
         5 49 8
         6 57 8
         7 65 8
         8 73 8
         9 81 8
        10 89 8

EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
        11 97 8
        12 105 8
        13 113 8
        14 121 8
        15 129 8
        16 137 128
        17 265 128

已选择18行。

*** SESSION ID:(7.3) 2003-08-26 15:22:23.428
Start dump data blocks tsn: 8 file#: 5 minblk 137 maxblk 137
buffer tsn: 8 rdba: 0x01400089 (5/137)
scn: 0x0000.0016c1d5 seq: 0x0d flg: 0x04 tail: 0xc1d5200d
frmt: 0x02 chkval: 0x2732 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 1 parent dba: 0x0140000a poffset: 8
   unformatted: 0 total: 64 first useful block: 2
   owning instance : 1
   instance ownership changed at 08/26/2003 10:27:09
   Last successful Search 08/26/2003 10:27:09
   Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 64
   Bitmap block lock opcode 0
   Locker xid: : 0x0000.000.00000000
      Highwater:: 0x00000000 ext#: 0 blk#: 0 ext size: 0
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk 0x00000000 offset: 0
  HWM Flag: Not Set
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01400089 Length: 64 Offset: 0

   0:Metadata 1:Metadata 2:FULL 3:FULL
   4:FULL 5:FULL 6:FULL 7:FULL
   8:FULL 9:FULL 10:FULL 11:FULL
   12:FULL 13:FULL 14:FULL 15:FULL
   16:FULL 17:FULL 18:FULL 19:FULL
   20:FULL 21:FULL 22:FULL 23:FULL
   24:FULL 25:FULL 26:FULL 27:FULL
   28:FULL 29:FULL 30:FULL 31:FULL
   32:FULL 33:FULL 34:FULL 35:FULL
   36:FULL 37:FULL 38:FULL 39:FULL
   40:FULL 41:FULL 42:FULL 43:FULL
   44:FULL 45:FULL 46:FULL 47:FULL
   48:FULL 49:FULL 50:FULL 51:FULL
   52:FULL 53:FULL 54:FULL 55:FULL
   56:FULL 57:FULL 58:FULL 59:FULL
   60:FULL 61:FULL 62:FULL 63:FULL
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 137 maxblk 137

*** 2003-08-26 15:28:34.351
Start dump data blocks tsn: 8 file#: 5 minblk 138 maxblk 138
buffer tsn: 8 rdba: 0x0140008a (5/138)
scn: 0x0000.0016c1db seq: 0x02 flg: 0x04 tail: 0xc1db2002
frmt: 0x02 chkval: 0x2772 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 1 parent dba: 0x0140000a poffset: 9
   unformatted: 0 total: 64 first useful block: 0
   owning instance : 1
   instance ownership changed at 08/26/2003 10:27:12
   Last successful Search 08/26/2003 10:27:12
   Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 64
   Bitmap block lock opcode 0
   Locker xid: : 0x0000.000.00000000
      Highwater:: 0x00000000 ext#: 0 blk#: 0 ext size: 0
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk 0x00000000 offset: 0
  HWM Flag: Not Set
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x014000c9 Length: 64 Offset: 0

   0:FULL 1:FULL 2:FULL 3:FULL
   4:FULL 5:FULL 6:FULL 7:FULL
   8:FULL 9:FULL 10:FULL 11:FULL
   12:FULL 13:FULL 14:FULL 15:FULL
   16:FULL 17:FULL 18:FULL 19:FULL
   20:FULL 21:FULL 22:FULL 23:FULL
   24:FULL 25:FULL 26:FULL 27:FULL
   28:FULL 29:FULL 30:FULL 31:FULL
   32:FULL 33:FULL 34:FULL 35:FULL
   36:FULL 37:FULL 38:FULL 39:FULL
   40:FULL 41:FULL 42:FULL 43:FULL
   44:FULL 45:FULL 46:FULL 47:FULL
   48:FULL 49:FULL 50:FULL 51:FULL
   52:FULL 53:FULL 54:FULL 55:FULL
   56:FULL 57:FULL 58:FULL 59:FULL
   60:FULL 61:FULL 62:FULL 63:FULL
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 138 maxblk 138

*** 2003-08-26 10:40:27.307
Start dump data blocks tsn: 8 file#: 5 minblk 265 maxblk 265
buffer tsn: 8 rdba: 0x01400109 (5/265)
scn: 0x0000.0016c1df seq: 0x1c flg: 0x00 tail: 0xc1df201c
frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 1 parent dba: 0x0140000a poffset: 10
   unformatted: 0 total: 64 first useful block: 2
   owning instance : 1
   instance ownership changed at 08/26/2003 10:27:13
   Last successful Search 08/26/2003 10:27:13
   Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 64
   Bitmap block lock opcode 0
   Locker xid: : 0x0000.000.00000000
      Highwater:: 0x00000000 ext#: 0 blk#: 0 ext size: 0
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk 0x00000000 offset: 0
  HWM Flag: Not Set
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01400109 Length: 64 Offset: 0

   0:Metadata 1:Metadata 2:FULL 3:FULL---
   4:FULL 5:FULL 6:FULL 7:FULL
   8:FULL 9:FULL 10:FULL 11:FULL
   12:FULL 13:FULL 14:FULL 15:FULL
   16:FULL 17:FULL 18:FULL 19:FULL
   20:FULL 21:FULL 22:FULL 23:FULL
   24:FULL 25:FULL 26:FULL 27:FULL
   28:FULL 29:FULL 30:FULL 31:FULL
   32:FULL 33:FULL 34:FULL 35:FULL
   36:FULL 37:FULL 38:FULL 39:FULL
   40:FULL 41:FULL 42:FULL 43:FULL
   44:FULL 45:FULL 46:FULL 47:FULL
   48:FULL 49:FULL 50:FULL 51:FULL
   52:FULL 53:FULL 54:FULL 55:FULL
   56:FULL 57:FULL 58:FULL 59:FULL
   60:FULL 61:FULL 62:FULL 63:FULL
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 265 maxblk 265
*** 2003-08-26 10:41:05.642
Start dump data blocks tsn: 8 file#: 5 minblk 266 maxblk 266
buffer tsn: 8 rdba: 0x0140010a (5/266)
scn: 0x0000.0016c1e3 seq: 0x06 flg: 0x00 tail: 0xc1e32006
frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 1 parent dba: 0x0140000a poffset: 11
   unformatted: 0 total: 64 first useful block: 0
   owning instance : 1
   instance ownership changed at 08/26/2003 10:27:14
   Last successful Search 08/26/2003 10:27:14
   Freeness Status: nf1 1 nf2 0 nf3 0 nf4 17

   Extent Map Block Offset: 4294967295
   First free datablock : 0
   Bitmap block lock opcode 0
   Locker xid: : 0x0000.000.00000000
      Highwater:: 0x01400189 ext#: 17 blk#: 128 ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 370
  mapblk 0x00000000 offset: 17
  HWM Flag: HWM Set
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x01400149 Length: 64 Offset: 0

   0:75-100% free 1:FULL 2:FULL 3:0-25% free
   4:75-100% free 5:FULL 6:FULL 7:75-100% free
   8:FULL 9:FULL 10:FULL 11:75-100% free
   12:FULL 13:FULL 14:FULL 15:75-100% free
   16:FULL 17:FULL 18:FULL 19:75-100% free
   20:FULL 21:FULL 22:FULL 23:75-100% free
   24:FULL 25:FULL 26:FULL 27:75-100% free
   28:FULL 29:FULL 30:FULL 31:75-100% free
   32:FULL 33:FULL 34:FULL 35:75-100% free
   36:FULL 37:FULL 38:FULL 39:75-100% free
   40:FULL 41:FULL 42:FULL 43:75-100% free
   44:FULL 45:FULL 46:FULL 47:75-100% free
   48:FULL 49:FULL 50:FULL 51:75-100% free
   52:FULL 53:FULL 54:FULL 55:75-100% free
   56:FULL 57:FULL 58:FULL 59:75-100% free
   60:FULL 61:FULL 62:FULL 63:75-100% free
  --------------------------------------------------------
End dump data blocks tsn: 8 file#: 5 minblk 266 maxblk 266

可以看到从第16号extents开始每128个块中有2个块来管理位图的信息，节省了很多的空间。