ITPub博客

首页 > 数据库 > Oracle > ASSM的初步研究

ASSM的初步研究

原创 Oracle 作者:jolly10 时间:2008-10-24 13:42:43 0 删除 编辑
一直以来以ASSM的概念很模糊,今天做了个实验,总算有点收获。[@more@]

SQL> create tablespace eric
2 datafile '/u01/app/oradata/orcl/eric01.dbf' size 10m
3 extent management local uniform size 128k
4 segment space management auto;

Tablespace created.

SQL> select tablespace_name,extent_management,allocation_type,segment_space_management
2 from dba_tablespaces
3 where tablespace_name='ERIC';

TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ------
ERIC LOCAL UNIFORM AUTO


SQL> create table test tablespace eric
2 as select * from dba_objects;

Table created.

SQL> col segment_name for a30
SQL> select segment_name,file_id,block_id,to_char(block_id,'xxx') hex_block_id,blocks
2 from dba_extents where tablespace_name='ERIC';

SEGMENT_NAME FILE_ID BLOCK_ID HEX_B BLOCKS
------------------------------ ---------- ---------- ----- ----------
TEST 9 9 9 16
TEST 9 25 19 16
TEST 9 41 29 16
TEST 9 57 39 16
TEST 9 73 49 16
TEST 9 89 59 16
TEST 9 105 69 16
TEST 9 121 79 16
TEST 9 137 89 16
TEST 9 153 99 16
TEST 9 169 a9 16
TEST 9 185 b9 16
TEST 9 201 c9 16
TEST 9 217 d9 16
TEST 9 233 e9 16
TEST 9 249 f9 16
TEST 9 265 109 16
TEST 9 281 119 16
TEST 9 297 129 16
TEST 9 313 139 16
TEST 9 329 149 16
TEST 9 345 159 16
TEST 9 361 169 16
TEST 9 377 179 16
TEST 9 393 189 16
TEST 9 409 199 16
TEST 9 425 1a9 16
TEST 9 441 1b9 16
TEST 9 457 1c9 16
TEST 9 473 1d9 16
TEST 9 489 1e9 16
TEST 9 505 1f9 16
TEST 9 521 209 16
TEST 9 537 219 16
TEST 9 553 229 16
TEST 9 569 239 16
TEST 9 585 249 16
TEST 9 601 259 16
TEST 9 617 269 16
TEST 9 633 279 16
TEST 9 649 289 16
TEST 9 665 299 16
TEST 9 681 2a9 16
TEST 9 697 2b9 16
TEST 9 713 2c9 16
TEST 9 729 2d9 16

46 rows selected.


前2个数据块为数据文头,3~8为区位图块.接下来的第9个块是这个segment的第一个块,下面来dump一下这个块的内容.

Start dump data blocks tsn: 9 file#: 9 minblk 9 maxblk 9
buffer tsn: 9 rdba: 0x02400009 (9/9)
scn: 0x0000.0013f32e seq: 0x03 flg: 0x04 tail: 0xf32e2003
frmt: 0x02 chkval: 0x76d7 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
...

Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x0240000a poffset: 0
unformatted: 0 total: 16 first useful block: 3
owning instance : 1
instance ownership changed at
Last successful Search
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
Inc #: 0 Objd: 62137
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02400009 Length: 16 Offset: 0

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
--------------------------------------------------------
End dump data blocks tsn: 9 file#: 9 minblk 9 maxblk 9


看到这个位图管理了16个block,地址范围0x02400009开始,其中前三个(0~2)是储存的元数据,其它的块都已满了.First Level Bitmap Block表示是第一级位图.parent dba: 0x0240000a指出了这个块的父dba地址,也就是二级位图块的地址.
0a就是第10个block,转储一下第10个block的内容:

buffer tsn: 9 rdba: 0x0240000a (9/10)
scn: 0x0000.0013f32e seq: 0x10 flg: 0x04 tail: 0xf32e2110
frmt: 0x02 chkval: 0x7576 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
...
Dump of Second Level Bitmap Block
number: 17 nfree: 1 ffree: 16 pdba: 0x0240000b
Inc #: 0 Objd: 62137
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x02400009 Free: 1 Inst: 1
0x02400019 Free: 1 Inst: 1
0x02400029 Free: 1 Inst: 1
0x02400039 Free: 1 Inst: 1
0x02400049 Free: 1 Inst: 1
0x02400059 Free: 1 Inst: 1
0x02400069 Free: 1 Inst: 1
0x02400079 Free: 1 Inst: 1
0x024000b9 Free: 1 Inst: 1
0x024000f9 Free: 1 Inst: 1
0x02400139 Free: 1 Inst: 1
0x02400179 Free: 1 Inst: 1
0x024001b9 Free: 1 Inst: 1
0x024001f9 Free: 1 Inst: 1
0x02400239 Free: 1 Inst: 1
0x02400279 Free: 1 Inst: 1
0x024002b9 Free: 5 Inst: 1

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


SECOND LEVEL BITMAP BLOCK表示这是个二级位图块,记录了17个一级位图块的地址.

##这里为何只有17个一级位图块?不是有46个extent吗?难道有的一级位图位所管理的块不至16个block?
是的,经测试从第8个一级位图块起,也就是0x02400079管理了64个block.最后有测试.


接着看pdba: 0x0240000b表示这个块同样有一个上级位图块,地址是0x0240000b,也就是块11.
再来看看块11的结构
Start dump data blocks tsn: 9 file#: 9 minblk 11 maxblk 11
buffer tsn: 9 rdba: 0x0240000b (9/11)
scn: 0x0000.0013f332 seq: 0x01 flg: 0x04 tail: 0xf3322301
frmt: 0x02 chkval: 0x59fc type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1

...

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 46 #blocks: 736
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x024002db ext#: 45 blk#: 2 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 722
mapblk 0x00000000 offset: 45
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x024002db ext#: 45 blk#: 2 ext size: 16
#blocks in seg. hdr's freelists: 0
#blocks below: 722
mapblk 0x00000000 offset: 45
Level 1 BMB for High HWM block: 0x024002b9
Level 1 BMB for Low HWM block: 0x024002b9
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0240000a
Last Level 1 BMB: 0x024002b9
Last Level II BMB: 0x0240000a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 46 obj#: 62137 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x02400009 length: 16
0x02400019 length: 16
0x02400029 length: 16
0x02400039 length: 16
0x02400049 length: 16
0x02400059 length: 16
0x02400069 length: 16
0x02400079 length: 16
0x02400089 length: 16
0x02400099 length: 16
0x024000a9 length: 16
0x024000b9 length: 16
0x024000c9 length: 16
0x024000d9 length: 16
0x024000e9 length: 16
0x024000f9 length: 16
0x02400109 length: 16
0x02400119 length: 16
0x02400129 length: 16
0x02400139 length: 16
0x02400149 length: 16
0x02400159 length: 16
0x02400169 length: 16
0x02400179 length: 16
0x02400189 length: 16
0x02400199 length: 16
0x024001a9 length: 16
0x024001b9 length: 16
0x024001c9 length: 16
0x024001d9 length: 16
0x024001e9 length: 16
0x024001f9 length: 16
0x02400209 length: 16
0x02400219 length: 16
0x02400229 length: 16
0x02400239 length: 16
0x02400249 length: 16
0x02400259 length: 16
0x02400269 length: 16
0x02400279 length: 16
0x02400289 length: 16
0x02400299 length: 16
0x024002a9 length: 16
0x024002b9 length: 16
0x024002c9 length: 16
0x024002d9 length: 16

Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02400009 Data dba: 0x0240000c
Extent 1 : L1 dba: 0x02400019 Data dba: 0x0240001a
Extent 2 : L1 dba: 0x02400029 Data dba: 0x0240002a
Extent 3 : L1 dba: 0x02400039 Data dba: 0x0240003a
Extent 4 : L1 dba: 0x02400049 Data dba: 0x0240004a
Extent 5 : L1 dba: 0x02400059 Data dba: 0x0240005a
Extent 6 : L1 dba: 0x02400069 Data dba: 0x0240006a
Extent 7 : L1 dba: 0x02400079 Data dba: 0x0240007a
Extent 8 : L1 dba: 0x02400079 Data dba: 0x02400089
Extent 9 : L1 dba: 0x02400079 Data dba: 0x02400099
Extent 10 : L1 dba: 0x02400079 Data dba: 0x024000a9
Extent 11 : L1 dba: 0x024000b9 Data dba: 0x024000ba
Extent 12 : L1 dba: 0x024000b9 Data dba: 0x024000c9
Extent 13 : L1 dba: 0x024000b9 Data dba: 0x024000d9
Extent 14 : L1 dba: 0x024000b9 Data dba: 0x024000e9
Extent 15 : L1 dba: 0x024000f9 Data dba: 0x024000fa
Extent 16 : L1 dba: 0x024000f9 Data dba: 0x02400109
Extent 17 : L1 dba: 0x024000f9 Data dba: 0x02400119
Extent 18 : L1 dba: 0x024000f9 Data dba: 0x02400129
Extent 19 : L1 dba: 0x02400139 Data dba: 0x0240013a
Extent 20 : L1 dba: 0x02400139 Data dba: 0x02400149
Extent 21 : L1 dba: 0x02400139 Data dba: 0x02400159
Extent 22 : L1 dba: 0x02400139 Data dba: 0x02400169
Extent 23 : L1 dba: 0x02400179 Data dba: 0x0240017a
Extent 24 : L1 dba: 0x02400179 Data dba: 0x02400189
Extent 25 : L1 dba: 0x02400179 Data dba: 0x02400199
Extent 26 : L1 dba: 0x02400179 Data dba: 0x024001a9
Extent 27 : L1 dba: 0x024001b9 Data dba: 0x024001ba
Extent 28 : L1 dba: 0x024001b9 Data dba: 0x024001c9
Extent 29 : L1 dba: 0x024001b9 Data dba: 0x024001d9
Extent 30 : L1 dba: 0x024001b9 Data dba: 0x024001e9
Extent 31 : L1 dba: 0x024001f9 Data dba: 0x024001fa
Extent 32 : L1 dba: 0x024001f9 Data dba: 0x02400209
Extent 33 : L1 dba: 0x024001f9 Data dba: 0x02400219
Extent 34 : L1 dba: 0x024001f9 Data dba: 0x02400229
Extent 35 : L1 dba: 0x02400239 Data dba: 0x0240023a
Extent 36 : L1 dba: 0x02400239 Data dba: 0x02400249
Extent 37 : L1 dba: 0x02400239 Data dba: 0x02400259
Extent 38 : L1 dba: 0x02400239 Data dba: 0x02400269
Extent 39 : L1 dba: 0x02400279 Data dba: 0x0240027a
Extent 40 : L1 dba: 0x02400279 Data dba: 0x02400289
Extent 41 : L1 dba: 0x02400279 Data dba: 0x02400299
Extent 42 : L1 dba: 0x02400279 Data dba: 0x024002a9
Extent 43 : L1 dba: 0x024002b9 Data dba: 0x024002ba
Extent 44 : L1 dba: 0x024002b9 Data dba: 0x024002c9
Extent 45 : L1 dba: 0x024002b9 Data dba: 0x024002d9
--------------------------------------------------------

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

End dump data blocks tsn: 9 file#: 9 minblk 11 maxblk 11

type: 0x23=PAGETABLE SEGMENT HEADER表示是真正的segment header,这里可以看作是第一个3级位图块.如果这个块不足以存储二级位图块的指针,那么新的Level 3级的位图块将被创建.
DBA 1: 0x0240000a表示这个三级位图块下面只有一个二级位图块.

Extent 0 : L1 dba: 0x02400009 Data dba: 0x0240000c
Extent 1 : L1 dba: 0x02400019 Data dba: 0x0240001a
说明:这里例出了每个extent的分配情况,指出L1级的地址,及存放实际数据的第一个地址dba地址.这里可以看到Extent 0的存放数据的dba是0c,因为0a被L2级块占用,0b被L3级块占用,所以实际存放数据的是从0c开始.而Extent 1则不同,在这个区间没有分配L2和L3的地址块,所以实际存放数据是从L1后的第一个块,也就是19后的1a开始存放.


下面导出第二个Level 1的块看看:

SQL> alter system dump datafile 9 block 25;


Start dump data blocks tsn: 9 file#: 9 minblk 25 maxblk 25
buffer tsn: 9 rdba: 0x02400019 (9/25)
scn: 0x0000.0013f32e seq: 0x01 flg: 0x04 tail: 0xf32e2001
frmt: 0x02 chkval: 0x76d4 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800

...
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x0240000a poffset: 1
unformatted: 0 total: 16 first useful block: 1
owning instance : 1
instance ownership changed at
Last successful Search
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
Inc #: 0 Objd: 62137
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02400019 Length: 16 Offset: 0

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
--------------------------------------------------------
End dump data blocks tsn: 9 file#: 9 minblk 25 maxblk 25

没错,里只有第一个块是元数据,就是本身的块0x02400019.


现在已基本搞清楚了这三个级别位图块的情况,下面看看刚最先提出的一个问题,来dump一下121这个块,因为发现从这个块开始管理的块就多了起来.
Start dump data blocks tsn: 9 file#: 9 minblk 121 maxblk 121
buffer tsn: 9 rdba: 0x02400079 (9/121)
scn: 0x0000.0013f32e seq: 0x01 flg: 0x04 tail: 0xf32e2001
frmt: 0x02 chkval: 0x743e type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
...
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 4 parent dba: 0x0240000a poffset: 7
unformatted: 0 total: 64 first useful block: 1
owning instance : 1
instance ownership changed at
Last successful Search
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
Inc #: 0 Objd: 62137
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02400079 Length: 16 Offset: 0
0x02400089 Length: 16 Offset: 16
0x02400099 Length: 16 Offset: 32
0x024000a9 Length: 16 Offset: 48

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
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: 9 file#: 9 minblk 121 maxblk 121

所以说并不是每个一级位图位所管理的block数据都是一样的.但是是有规则可循的.

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

请登录后发表评论 登录
全部评论

注册时间:2008-02-20

  • 博文量
    263
  • 访问量
    769326