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

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

Posted by: piner
Posted on: 2003-08-22 16:38

好，到这里了，我们开始插入数据，进一步说明情况。
[color=navy]SQL>insert into demotab
select rownum from dba_objects;
28985 rows inserted; [/color]

然后我们运行
SQL> exec show_space('demotab');
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................72
Unused Bytes............................589824
Last Used Ext FileId....................26
Last Used Ext BlockId...................56
Last Used Block.........................8

这里有一个新的过程，用于专门分析自动段管理的段中的块的组成
[color=navy]declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('SYS', 'DEMOTAB', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;/ [/color]

Unformatted Blocks = 0
FS1 Blocks = 1
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 6
Full Blocks = 43
PL/SQL procedure successfully completed

在这里
FS1表示0-25%空闲空间的块
FS2表示25-50%空闲空间的块
FS3表示50-75%空闲空间的块
FS4表示 75-100%空闲空间的块

这里我们能看到什么呢？
可以看到的是，
Total Blocks............................128
Unused Blocks...........................72

就是说，我们这次插入用了[color=red]128-72=56[/color]个块。
但是看下面的数据，也就是数据块只有[color=red]1+6+43=50[/color]个，还有6个的差别在哪里呢？我们已知的就是段头有3个，那还有3个应当就是分布在表中的位图块了（假定）。我们进一步分析。
IXDBA.NET技术社区
用了56个块，其实就是7个区间，7个区间，每个位图块只能管理16个块（2个区间），这里应当用到了4个位图块才能够管理过来，4个中有一个属于段头的3个，那正好是[color=red]50+3+3=56=128-72[/color]了。我们进一步分析。

再看那段头变成什么样了？
Start dump data blocks tsn: 6 file#: 7 minblk 9 maxblk 9
buffer tsn: 6 rdba: 0x06800009 (7/9)
scn: 0x0000.0018b7ca seq: 0x2d flg: 0x00 tail: 0xb7ca202d
frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x0680000a poffset: 0
[color=red]unformatted: 0 total: 16 first useful block: 3 [/color]
owning instance : 1
instance ownership changed at 08/19/2003 10:41:42
Last successful Search 08/19/2003 10:41:42
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295
First free datablock : 16
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 :
--------------------------------------------------------
0x06800009 Length: 8 Offset: 0
0x06800011 Length: 8 Offset: 8

[color=red]0:Metadata 1:Metadata 2:Metadata 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 [/color]
--------------------------------------------------------
End dump data blocks tsn: 6 file#: 7 minblk 9 maxblk 9

*** 2003-08-19 11:10:10.000
Start dump data blocks tsn: 6 file#: 7 minblk 10 maxblk 10
buffer tsn: 6 rdba: 0x0680000a (7/10)
scn: 0x0000.0018b7cb seq: 0x07 flg: 0x00 tail: 0xb7cb2107
frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK
Dump of Second Level Bitmap Block
number: 8 nfree: 5 ffree: 3 pdba: 0x0680000b
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
[color=red]0x06800009 Free: 1 Inst: 1
0x06800019 Free: 1 Inst: 1
0x06800029 Free: 1 Inst: 1 [/color]
0x06800039 Free: 5 Inst: 1
0x06800049 Free: 5 Inst: 1
0x06800059 Free: 5 Inst: 1
0x06800069 Free: 5 Inst: 1
0x06800079 Free: 5 Inst: 1

--------------------------------------------------------
End dump data blocks tsn: 6 file#: 7 minblk 10 maxblk 10

Start dump data blocks tsn: 6 file#: 7 minblk 11 maxblk 11
buffer tsn: 6 rdba: 0x0680000b (7/11)
scn: 0x0000.0018b7cb seq: 0x03 flg: 0x00 tail: 0xb7cb2303
frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 16 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
[color=red]Highwater:: 0x06800041 [/color] ext#: 6 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 50
mapblk 0x00000000 offset: 6
Unlocked
--------------------------------------------------------
Low HighWater Mark :
[color=red]Highwater:: 0x0680000c [/color] ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x06800039
Level 1 BMB for Low HWM block: 0x06800009
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0680000a
Last Level 1 BMB: 0x06800079
Last Level II BMB: 0x0680000a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 16 obj#: 29929 flag: 0x20000000
Extent Map
-----------------------------------------------------------------
0x06800009 length: 8
0x06800011 length: 8
0x06800019 length: 8
0x06800021 length: 8
0x06800029 length: 8
0x06800031 length: 8
0x06800039 length: 8
0x06800041 length: 8
0x06800049 length: 8
0x06800051 length: 8
0x06800059 length: 8
0x06800061 length: 8
0x06800069 length: 8
0x06800071 length: 8
0x06800079 length: 8
0x06800081 length: 8

Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x06800009 Data dba: 0x0680000c
Extent 1 : L1 dba: 0x06800009 Data dba: 0x06800011
Extent 2 : L1 dba: 0x06800019 Data dba: 0x0680001a
Extent 3 : L1 dba: 0x06800019 Data dba: 0x06800021
Extent 4 : L1 dba: 0x06800029 Data dba: 0x0680002a
Extent 5 : L1 dba: 0x06800029 Data dba: 0x06800031
Extent 6 : L1 dba: 0x06800039 Data dba: 0x0680003a
Extent 7 : L1 dba: 0x06800039 Data dba: 0x06800041
Extent 8 : L1 dba: 0x06800049 Data dba: 0x0680004a
Extent 9 : L1 dba: 0x06800049 Data dba: 0x06800051
Extent 10 : L1 dba: 0x06800059 Data dba: 0x0680005a
Extent 11 : L1 dba: 0x06800059 Data dba: 0x06800061
Extent 12 : L1 dba: 0x06800069 Data dba: 0x0680006a
Extent 13 : L1 dba: 0x06800069 Data dba: 0x06800071
Extent 14 : L1 dba: 0x06800079 Data dba: 0x0680007a
Extent 15 : L1 dba: 0x06800079 Data dba: 0x06800081
--------------------------------------------------------

Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x0680000a

End dump data blocks tsn: 6 file#: 7 minblk 11 maxblk 11

看块9，可以看到，段头的三个块没有动。另外的13个块都是FULL（100%充满）状态。
看块10，可以看到，充分利用的位图块的位置是：
0x06800009 Free: 1 Inst: 1 --对应9
0x06800019 Free: 1 Inst: 1 --对应25
0x06800029 Free: 1 Inst: 1 --对应41
应当还有一个不完全利用的
0x06800039 Free: 5 Inst: 1 --对应57
下面我们证实一下

块25的信息：
Start dump data blocks tsn: 6 file#: 7 minblk 25 maxblk 25
buffer tsn: 6 rdba: 0x06800019 (7/25)
scn: 0x0000.0018b7cb seq: 0x0e flg: 0x04 tail: 0xb7cb200e
frmt: 0x02 chkval: 0x261c type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x0680000a poffset: 1
unformatted: 0 total: 16 first useful block: 1
owning instance : 1
instance ownership changed at 08/19/2003 10:41:42
Last successful Search 08/19/2003 10:41:42
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295
First free datablock : 16
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 :
--------------------------------------------------------
0x06800019 Length: 8 Offset: 0
0x06800021 Length: 8 Offset: 8

[color=red]0:Metadata 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 [/color]
--------------------------------------------------------
End dump data blocks tsn: 6 file#: 7 minblk 25 maxblk 25

块41的信息
Start dump data blocks tsn: 6 file#: 7 minblk 41 maxblk 41
buffer tsn: 6 rdba: 0x06800029 (7/41)
scn: 0x0000.0018b7cb seq: 0x35 flg: 0x04 tail: 0xb7cb2035
frmt: 0x02 chkval: 0x260f type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x0680000a poffset: 2
unformatted: 0 total: 16 first useful block: 1
owning instance : 1
instance ownership changed at 08/19/2003 10:41:42
Last successful Search 08/19/2003 10:41:42
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295
First free datablock : 16
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 :
--------------------------------------------------------
0x06800029 Length: 8 Offset: 0
0x06800031 Length: 8 Offset: 8

[color=red]0:Metadata 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 [/color]
--------------------------------------------------------
End dump data blocks tsn: 6 file#: 7 minblk 41 maxblk 41

块57的信息
Start dump data blocks tsn: 6 file#: 7 minblk 57 maxblk 57
buffer tsn: 6 rdba: 0x06800039 (7/57)
scn: 0x0000.0018b7cb seq: 0x04 flg: 0x04 tail: 0xb7cb2004
frmt: 0x02 chkval: 0x27d2 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2 parent dba: 0x0680000a poffset: 3
unformatted: 8 total: 16 first useful block: 1
owning instance : 1
instance ownership changed at 08/19/2003 10:41:42
Last successful Search 08/19/2003 10:41:42
Freeness Status: nf1 1 nf2 0 nf3 0 nf4 6

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

[color=red]0:Metadata 1:75-100% free 2:75-100% free 3:75-100% free
4:75-100% free 5:75-100% free 6:75-100% free 7:0-25% free
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted [/color]
--------------------------------------------------------
End dump data blocks tsn: 6 file#: 7 minblk 57 maxblk 57

块9：2个段头+1个位图+13个FULL
块25：1个位图+15个FULL
块41：1个位图+15个FULL
块57：1个位图+6个75-100% free+1个0-25% free

大家可以看到正好满足上面的：
FS1 Blocks = 1
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 6
Full Blocks = 43

到这里，大家也明白了
[color=red]3*16+8=56=1+6+43+3（段头，其中有一个是BMB）+3（BMB） [/color]

最后，我们讨论高水点的确定。
到这里，大家该知道高水点的位置是多少了吧，应该是[color=red]56+8（文件头）+1=65[/color]（这个1表示在数据块之上，因为块从1开始而不是从0开始）。但是看我们的块11，怎么解释这个呢？
[color=red]Highwater:: 0x06800041 [/color] ext#: 6 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 50
mapblk 0x00000000 offset: 6
Unlocked
--------------------------------------------------------
Low HighWater Mark :
[color=red]Highwater:: 0x0680000c [/color] ext#: 0 blk#: 3 ext size: 8

其中有两个Highwater，第一个记录了真正的高水点，H41，就是十进制65
第二个表示底的高水点，从哪里开始，很明显，从12（十六进制的c）
个块开始（前面是8个文件头+3个段头）
其实，我们从最后一个位图块（57）的dump中也可以看到这样的信息
Locker xid: : 0x0000.000.00000000
[color=red]Highwater:: 0x06800041 [/color] ext#: 6 blk#: 8 ext size: 8