【听海日志】之DATA BLOCK物理结构初识

显示全部楼层 · 2012-6-20 17:38:14

本帖最后由听海★蓝心梦于 2012-3-10 08:33 编辑
之前看到论坛里很多大师都在研究ORACLE BLOCK的物理结构，对他们那些理论和研究方法很是佩服。
总觉得，作为一个DBA，对ORACLE的内部原理如果不了解，怎么都不能算是精通ORACLE。
于是，站在巨人的肩上，希望能从巨人的智慧中获取一些东西，除了知识，还有大师们孜孜不倦的求知精神和长时间如一日的坚定信念，我想这才是我们与他们之间的差别。
好了，废话少说，开贴：PDF已上传，欢迎大家下载。
一、Data Block 物理结构图

11.jpg (18.58 KB, 下载次数: 455)
下载附件
2012-3-8 08:16 上传

二、对block的dump过程1、dump blocktest@ORCL> create table test (a varchar(10));
Table created.
test@ORCL> insert into test values('a');
1 row created.
test@ORCL> commit;
Commit complete.
test@ORCL> set serveroutput on
test@ORCL> exec show_space('test');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................5
Last Used Ext BlockId................... 141344
Last Used Block.........................8
PL/SQL procedure successfully completed.
test@ORCL> alter system dump datafile 5 block 141347;
System altered.
test@ORCL> select * from v$tablespace;
TS# NAME
INC BIG FLA ENC
---------- ---------------------------------------- --- --- --- ---
0 SYSTEM
YES NOYES
1 SYSAUX
YES NOYES
2 UNDOTBS1
YES NOYES
4 USERS
YES NOYES
3 TEMP
NO NOYES
2、Trace 文件[oracle@localhost trace]$ more orcl_ora_3981.trc
Trace file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3981.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/db_1
System name:Linux
Node name:localhost.localdomain
Release: 2.6.18-128.el5
Version: #1 SMP Wed Dec 17 11:41:38 EST 2008
Machine:x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 3981, image: [url=mailto

[email protected]][email protected][/url] (TNS V1-V3)

*** 2012-03-07 13:38:14.885
*** SESSION ID

43.120) 2012-03-07 13:38:14.885
*** CLIENT ID

) 2012-03-07 13:38:14.885
*** SERVICE NAME

SYS$USERS) 2012-03-07 13:38:14.885
*** MODULE NAME

SQL*Plus) 2012-03-07 13:38:14.885
*** ACTION NAME

) 2012-03-07 13:38:14.885
Start dump data blocks tsn: 4 file#:5 minblk 141345 maxblk 141345
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=21112865
BH (0x67bd9dc8) file#: 5 rdba: 0x01422821 (5/141345) class: 9 ba: 0x67834000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 81699 objn: 81699 tsn: 4 afn: 5 hint: f
hash: [0x6e55dc30,0x6e55dc30] lru: [0x677f6f00,0x67bf1ab0]
obj-flags: object_ckpt_list
ckptq: [0x673faf68,0x677eb608] fileq: [0x6a824028,0x677eb618] objq: [0x673fc708,0x67bf1ad8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xe.ebc2.0] LSCN: [0x0.148e704] HSCN: [0x0.148e704] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x01422821 (5/141345)
scn: 0x0000.01371db0 seq: 0x02 flg: 0x04 tail: 0x1db02102
frmt: 0x02 chkval: 0x9311 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002AE0A5C58200 to 0x00002AE0A5C5A200
2AE0A5C58200 0000A221 01422821 01371DB0 04020000[!...!(B...7.....]
2AE0A5C58210 00009311 00000000 00000000 00000000[................]
2AE0A5C58220 00000000 00000000 00000000 00000000[................]
Repeat 1 times
2AE0A5C58240 00000000 00000000 00000000 01422822[............"(B.]
2AE0A5C58250 00000001 00000001 00000000 00000000[................]
2AE0A5C58260 00000000 00000000 00013C62 00000001[........b select header_file,header_block from dba_segments where segment_name='TEST';
HEADER_FILE HEADER_BLOCK
----------- ------------
5 141346
2、空表segment header dump数据Start dump data blocks tsn: 4 file#:5 minblk 141346 maxblk 141346
*** 2012-03-07 18:57:14.768
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=21112866
BH (0x66ff7078) file#: 5 rdba: 0x01422822 (5/141346) class: 4 ba: 0x66f46000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 81700 objn: 81699 tsn: 4 afn: 5 hint: f
hash: [0x673e5da8,0x6e54e140] lru: [0x6a81cce0,0x67bf5020]
obj-flags: object_ckpt_list
ckptq: [0x673e2fd8,0x6a81cbf8] fileq: [0x6e5c6540,0x6e5c6540] objq: [0x6bbe99f0,0x6bbe99f0]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.21ee.0] LSCN: [0x0.149208d] HSCN: [0x0.149208d] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x673e5cf8) file#: 5 rdba: 0x01422822 (5/141346) class: 4 ba: 0x67176000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 81700 objn: -1 tsn: 4 afn: 5 hint: f
hash: [0x6a81a7e8,0x66ff7128] lru: [0x66ff9170,0x67bf8de0]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.1492089],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1492089],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6a81a738) file#: 5 rdba: 0x01422822 (5/141346) class: 4 ba: 0x6aaea000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 81699 objn: -1 tsn: 4 afn: 5 hint: f
hash: [0x6e54e140,0x673e5da8] lru: [0x66ff79b0,0x6a809400]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.1492079],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1492079],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x01422822 (5/141346)
scn: 0x0000.01492080 seq: 0x01 flg: 0x04 tail: 0x20802301
frmt: 0x02 chkval: 0xdd36 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002AFF1E617200 to 0x00002AFF1E619200
......................................................................................................................................................
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0spare2: 0#extents: 1#blocks: 8

last map0x00000000#maps: 0offset: 2716
Highwater::0x01422823ext#: 0blk#: 3ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk0x00000000offset: 0
Disk Lock:: Locked by xid:0x0001.016.000048c7
--------------------------------------------------------
Low HighWater Mark :
Highwater::0x01422823ext#: 0blk#: 3ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk0x00000000offset: 0
Level 1 BMB for High HWM block: 0x01422820
Level 1 BMB for Low HWM block: 0x01422820
--------------------------------------------------------
Segment Type: 1 nl2: 1blksz: 8192 fbsz: 0
L2 Array start offset:0x00001434
First Level 3 BMB:0x00000000
L2 Hint for inserts:0x01422821
Last Level 1 BMB:0x01422820
Last Level II BMB:0x01422821
Last Level III BMB:0x00000000
Map Header:: next0x00000000#extents: 1obj#: 81700flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01422820length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 :L1 dba:0x01422820 Data dba:0x01422823
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01422821
End dump data blocks tsn: 4 file#: 5 minblk 141346 maxblk 141346
3、插入数据后segment header dump数据Start dump data blocks tsn: 4 file#:5 minblk 141346 maxblk 141346
*** 2012-03-07 19:07:53.269
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=21112866
BH (0x673db328) file#: 5 rdba: 0x01422822 (5/141346) class: 4 ba: 0x67058000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 81700 objn: 81699 tsn: 4 afn: 5 hint: f
hash: [0x6e54e140,0x6e54e140] lru: [0x6a80fc50,0x673dbd90]
lru-flags: hot_buffer
ckptq: [NULL] fileq: [NULL] objq: [0x66fe2768,0x66ff92c8]
st: XCURRENT md: NULL tch: 3
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x01422822 (5/141346)
scn: 0x0000.0149228b seq: 0x03 flg: 0x04 tail: 0x228b2303
frmt: 0x02 chkval: 0x95e7 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00002AFF1E617200 to 0x00002AFF1E619200
………………………………………………………………………………
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0spare2: 0#extents: 1#blocks: 8

last map0x00000000#maps: 0offset: 2716
Highwater::0x01422828ext#: 0blk#: 8ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk0x00000000offset: 0

Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater::0x01422828ext#: 0blk#: 8ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk0x00000000offset: 0
Level 1 BMB for High HWM block: 0x01422820
Level 1 BMB for Low HWM block: 0x01422820
--------------------------------------------------------
Segment Type: 1 nl2: 1blksz: 8192 fbsz: 0
L2 Array start offset:0x00001434
First Level 3 BMB:0x00000000
L2 Hint for inserts:0x01422821
Last Level 1 BMB:0x01422820
Last Level II BMB:0x01422821
Last Level III BMB:0x00000000
Map Header:: next0x00000000#extents: 1obj#: 81700flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01422820length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 :L1 dba:0x01422820 Data dba:0x01422823
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01422821
End dump data blocks tsn: 4 file#: 5 minblk 141346 maxblk 141346
4、segment header dump数据差异对于20字节的头部来说，有变动的只有scn,seq和tail字段：
scn: 0x0000.01492080 seq: 0x01 flg: 0x04 tail: 0x20802301
scn: 0x0000.0149228b seq: 0x03 flg: 0x04 tail: 0x228b2303
而对于Extent Control Header部分，变动的部分是高水线,空表情况下数据块都是unformat，高水线在segment header block后面的一个block,也就是首个extent block为141347，那么高水线在141347（0x01422823），而当插入几行数据后高水线推到了141352（0x01422828）。
1.)建立表后的高水线Highwater::0x01422823ext#: 0blk#: 3ext size: 8
2.) 建立表后L1块的dba range --------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01422820Length: 8Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
--------------------------------------------------------
3.) 插入数据后高水线Highwater::0x01422828ext#: 0blk#: 8ext size: 8
4.) 插入数据后L1块的dba range 一同变化的还有低高水线的位置
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01422820Length: 8Offset: 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
----------------------------------------------------------
注：高水位线Highwater::0x01422828中，前两位：0x是十六进制标识，最后八位才是高水位线的block id。16进制的dba转化为10进制是：SELECT dbms_utility.data_block_address_block(to_number('01422823','xxxxxxxx')) FROM dual;

千问 · 2012-6-20 17:38:14

本帖最后由听海★蓝心梦于 2012-3-8 08:24 编辑
六、bitmap block及segment header块
1、测试如下
SQL> create table tt as select * from dba_objects;Table created.SQL> select EXTENT_ID , FILE_ID , BLOCK_ID , BLOCKS from dba_extents where segment_name='TT';EXTENT_IDFILE_IDBLOCK_ID BLOCKS---------- ---------- ---------- ----------0 5141624 81 5141632 82 5141640 83 5144216 84 5144224 85 5144232 86 5144240 87 5144248 88 5146432 89 5146440 8 10 5146448 8EXTENT_IDFILE_IDBLOCK_ID BLOCKS---------- ---------- ---------- ---------- 11 5146456 8 12 5146464 8 13 5146472 8 14 5146480 8 15 5146488 8 16 5145280 128 17 5145408 128 18 5145536 128 19 5145664 128 20 5145792 128 21 5145920 128EXTENT_IDFILE_IDBLOCK_ID BLOCKS---------- ---------- ---------- ---------- 22 5146048 128 23 5146176 12824 rows selected.SQL> select header_file,header_block,blocks,extents from dba_segments where segment_name='TT';HEADER_FILE HEADER_BLOCK BLOCKSEXTENTS----------- ------------ ---------- ---------- 5 141626 115224 HEADER_BLOCK 显示的是141626（segment header），而extent_id=0也就是说第一个extent是从第141624个block开始的，那么141624，141625 block到底做什么用了？dump下看看：SQL> alter system dump datafile 5 block min 141624 block max 141626;System altered.2、dump主要内容摘录 =============================================Start dump data blocks tsn: 4 file#:5 minblk 141624 maxblk 141626*** 2012-03-07 22:02:58.619Block dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=21113144Block dump from disk:buffer tsn: 4 rdba: 0x01422938 (5/141624)scn: 0x0000.01494105 seq: 0x02 flg: 0x04 tail: 0x41052002frmt: 0x02 chkval: 0x9e9a type: 0x20=FIRST LEVEL BITMAP BLOCKHex dump of block: st=0, typ_found=1Dump of memory from 0x00002AFF1E617200 to 0x00002AFF1E619200…………………………………………………………………….Dump of First Level Bitmap Block-------------------------------- nbits : 4 nranges: 2 parent dba:0x01422939 poffset: 0 unformatted: 0 total: 16first useful block: 3 owning instance : 1 instance ownership changed at Last successful Search Freeness Status:nf1 0nf2 0nf3 0nf4 0 Extent Map Block Offset: 4294967295 First free datablock : 16 Bitmap block lock opcode 0 Locker xid: :0x0000.000.00000000 Inc #: 0 Objd: 81701--------------------------------------------------------DBA Ranges :-------------------------------------------------------- 0x01422938Length: 8Offset: 0 0x01422940Length: 8Offset: 8 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--------------------------------------------------------Block dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=21113145Block dump from disk:buffer tsn: 4 rdba: 0x01422939 (5/141625)scn: 0x0000.01494103 seq: 0x11 flg: 0x04 tail: 0x41032111frmt: 0x02 chkval: 0xb922 type: 0x21=SECOND LEVEL BITMAP BLOCKHex dump of block: st=0, typ_found=1Dump of memory from 0x00002AFF1E617200 to 0x00002AFF1E6192002AFF1E617200 0000A221 01422939 01494103 04110000[!...9)B..AI.....]2AFF1E617210 0000B922 00000000 00000000 00000000["...............]2AFF1E617220 00000000 00000000 00000000 00000000[................]Repeat 1 times2AFF1E617240 00000000 00000000 00000000 0142293A[............

B.]2AFF1E617250 00000018 00000002 00000016 00000000[................]2AFF1E617260 00000000 00000000 00013F25 00000001[........%?......]2AFF1E617270 00000000 01422938 00010001 01422948[....8)B.....H)B.]2AFF1E617280 00010001 01423360 00010001 01423370[....`3B.....p3B.]2AFF1E617290 00010001 01423C00 00010001 01423C10[.....[B] select dbms_utility.data_block_address_block(to_number('01422938 ','xxxxxxxx'))block_id from dual;BLOCK_ID-----------------------------------------------------------------------141624SQL> select dbms_utility.data_block_address_block(to_number('01422940','xxxxxxxxx')) from dual;BLOCK_ID-------------------------------------------------------------------------141632 而block 141624，141632正是我们上面查询dba_extents是显示的extent_id = 0 , 1两个extent。另外141624＃ block dump信息中的： 0:Metadata 1:Metadata 2:Metadata 表示141624＃～141626＃block显示的是Metadata(元数据:数据仓库中非常重要的概念，管理数据的数据，oracle中的数据字典据都可以理解成元数据)，其中0表示是141624＃block，1 表示141625＃block，2 表示141626＃block（segment header），其他显示的都是full，表示这些block上都已经没有可用的空闲空间。同时也发现我们真真使用数据是从141627＃block开始的。 141624＃block dump内容里还有一个重要的信息就是parent dba: 0x01422939表示141624＃block的parent block的地址，而这个block的地址正好是141625＃block的地址：rdba: 0x01422939 (5/141625)。至此141624＃block暂时分析完毕。4、SECOND LEVEL BITMAP BLOCK 再来141625＃block，141625＃block的类型是：type: 0x21=SECOND LEVEL BITMAP BLOCK。说明该block是二级bitmap block，他是用来管理一级bitmap block的。141625＃block的主要dump信息：L1 Ranges :-------------------------------------------------------- 0x01422938Free: 1 Inst: 1 0x01422948Free: 1 Inst: 1 0x01423360Free: 1 Inst: 1 0x01423370Free: 1 Inst: 1 0x01423c00Free: 1 Inst: 1 0x01423c10Free: 1 Inst: 1 0x01423c20Free: 1 Inst: 1 0x01423c30Free: 1 Inst: 1 0x01423780Free: 1 Inst: 1 0x01423781Free: 1 Inst: 1 0x01423800Free: 1 Inst: 1 0x01423801Free: 1 Inst: 1 0x01423880Free: 1 Inst: 1 0x01423881Free: 1 Inst: 1 0x01423900Free: 1 Inst: 1 0x01423901Free: 1 Inst: 1 0x01423980Free: 1 Inst: 1 0x01423981Free: 1 Inst: 1 0x01423a00Free: 1 Inst: 1 0x01423a01Free: 1 Inst: 1 0x01423a80Free: 1 Inst: 1 0x01423a81Free: 1 Inst: 1 0x01423b00Free: 5 Inst: 1 0x01423b01Free: 5 Inst: 1--------------------------------------------------------其中L1 Ranges 中的哪些dba转为10进制如下：141624141640144224144240146432146448146464146480145280145281145408145409145536145537145664145665145792145793145920145921146048146049146176 146177 而这些值都是一级bitmap block的block_id,也正是dba_extents中显示的，同时也看到每一个一级bitmap block管理了16个data block。不过奇怪的是为什么146177＃block怎么也成了一级bitmap block了？暂时解释不清。SQL> select block_id from dba_extents where segment_name='TT' AND mod(extent_id,2)=0;BLOCK_ID----------141624141640144224144240146432146448146464146480145280145536145792BLOCK_ID----------14604812 rows selected. 再来看看141625＃block的 pdba: 0x0142293a，这个地址正是141626＃block的dba：rdba: 0x0142293a。接下来简单看看141626＃block的类型是： type: 0x23=PAGETABLE SEGMENT HEADER 也就是我们熟悉的segment header block，这里它也同时是一个特殊的三级bitmap block。同时最下面一条非常有用的信息是：Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x01422939 记录了二级btimap block的地址，因为segment小，只有1个二级bitmap block，大家也可以创建一个大一点的表，主要需要保证要有足够多的block才可能看到第二个二级block出现，要看到出现一个三级bitmapblock可能非常的难。

千问 · 2012-6-20 17:38:14

本帖最后由听海★蓝心梦于 2012-3-8 08:22 编辑
七、Oracle ASSM三级位图块结构
1、ASSM的结构猜想

22.jpg (40.54 KB, 下载次数: 438)
下载附件
2012-3-8 08:22 上传

也就是说我认为BMB的结构应该是均衡的，同时段头的PAGETABLE SEGMENT HEADER同时充当了第0个3级位图块的角色。在PAGETABLE SEGMENT HEADER中实际上我们可以很容易的看到这样的输出：--------------------------------------------------------Segment Type: 1 nl2: 103 blksz: 2048 fbsz: 0 L2 Array start offset: 0x00000434First Level 3 BMB: 0x00000000L2 Hint for inserts: 0x0355cfadLast Level 1 BMB: 0x03560c9cLast Level II BMB: 0x0355cfadLast Level III BMB: 0x00000000Map Header:: next 0x034000bf #extents: 51 obj#: 33141 flag: 0x20000000Extent Map----------------------------------------------------------------- 也就是说，这里记录了First Level 3 BMB和Last Level III BMB的地址，那么这就足够了，这里的双向指针完全可以进行Level 3级位图块的导航，而这第0个三级位图块也即段头，并无需记录所有3级位图块的地址。2、第二个3级位图块
由于产生另外一个3级位图块并不容易，所以有时候会需要你创建一个几百G的表，但这也并不能够保证一定能够产生另外的3级位图块：SQL> select bytes/1024/1024/1024 "SIZE(G)" from user_segments where segment_name='TEST'; SIZE(G) ---------- 873.25
为了寻找3级位图块，我着手做了以下实验，实验要能够： 1、实现更快快速的区间分配与扩展。 2、使第0个3级位图块也即segment header尽量小，以便进一步扩展。为此我创建了一个2k block_size的表空间，设置uniform size区间大小为10K，这样可以尽量所见空间耗用：SQL> create tablespace testdatafile '/opt/oracle/oradata/orcl/test01.dbf' size 1024M reuse blocksize 2048extent management local uniform size 10ksegment space management auto;Tablespace created. SQL> set timing onSQL> alter tablespace test add datafile '/opt/oracle/oradata/orcl/test02.dbf ' size 8191M reuse;Tablespace created. 然后创建一个数据表，设置高pctfree值，使得每个Block只存储一行数据，然后插入1千万记录：SQL> create table test( ID NUMBER(8),UNAME CHAR(1000))tablespace eyglepctfree 50initrans 1maxtrans 255;Table created.SQL> beginfor i in 1 .. 100 loopfor i in 1 .. 100000 loopinsert into test values(i,'test');end loop;commit;end loop;end;/完成这些操作之后，这个表用了大约9G空间：SQL> select bytes/1024/1024/1024 sizegb from dba_segments where segment_name='TEST';SIZEGB----------8.9988327 此时第一个3级位图块出现了，这是多么珍贵的一个3级位图块啊：Start dump data blocks tsn: 12 file#: 13 minblk 4032222 maxblk 4032222buffer tsn: 12 rdba: 0x037d86de (13/4032222)scn: 0x0000.00bdc864 seq: 0x01 flg: 0x04 tail: 0xc8642201frmt: 0x02 chkval: 0xf597 type: 0x22=THIRD LEVEL BITMAP BLOCKDump of Third Level Bitmap Blocknumber: 9 , next : 0x00000000 L2 Ranges :--------------------------------------------------------0x037d86dd0x037dd22d 0x037e1d7d 0x037e68cd 0x037eb41d0x037eff6d 0x037f4abd 0x037f960d 0x037fe15d--------------------------------------------------------End dump data blocks tsn: 12 file#: 13 minblk 4032222 maxblk 4032222 这个位图上上存在一个向下的指针：next : 0x00000000 ，当然现在还没有数值，我们可以再产生下一个3级位图块来观察。实际上到这里已经足够了，我的图示已经得到了足够的说明。参考资料偷窥Data block 的物理结构：http://www.itpub.net/thread-247459-1-1.htmldumping_oracle_blocks翻译（二）：http://www.itpub.net/thread-1516907-1-1.htmloracle存储结构－segment header结构解析：http://74.207.254.129/blog_oracle/index.php/2011/09/19/oracle%E5%AD%98%E5%82%A8%E7%BB%93%E6%9E%84%EF%BC%8Dsegment-header%E7%BB%93%E6%9E%84%E8%A7%A3%E6%9E%90/bitmap block以及segment header块学习：http://space.itpub.net/22295535/viewspace-710229关于ASSM的位图管理，证实了一些想法:http://www.itpub.net/thread-734505-1-3.htmlOracle ASSM三级位图块结构:http://www.eygle.com/archives/2007/07/oracle_assm_level3_bmb.html关于block中数据的存储和重组的探究:http://www.itpub.net/thread-112239-1-9.html

千问 · 2012-6-20 17:38:14

我要顶你

千问 · 2012-6-20 17:38:14

，很好

千问 · 2012-6-20 17:38:14

支持听海兄的原创

千问 · 2012-6-20 17:38:14

你要红啊~~

千问 · 2012-6-20 17:38:14

大家都说：出来混迟早是要还的！
我说：我还，现在就还...
我在这种混沌状态惆怅了很久，知道现在也没有找到好的解决办法。眼看着时间一秒一秒的失去，我怕最后我连“还账”的机会都没有...
事已至此，不再追求回报，只求还上以前的债，落个心安，省的以后躺倒棺材里揪心。
哈哈，上PDF吧：

对data block物理结构的认识.pdf(465.25 KB, 下载次数: 611)2012-3-8 10:04 上传点击文件名下载附件
里面有很多不足和遗漏，希望大家指正。一起找点乐子。