一、Data Block 物理结构图
test@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 NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES [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: oracle@localhost.localdomain (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<......] 2AE0A5C58270 00000000 01422820 00010005 00000000 [.... (B.........] 2AE0A5C58280 00000000 00000000 00000000 00000000 [................] Repeat 502 times 2AE0A5C5A1F0 00000000 00000000 00000000 1DB02102 [.............!..] Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x01422822 Inc #: 0 Objd: 80994 opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x01422820 Free: 5 Inst: 1 -------------------------------------------------------- End dump data blocks tsn: 4 file#: 5 minblk 141345 maxblk 141345 首先，数据块是通过data block buffer cache完成读和写操作的，所以它提供了20bytes的Cache Header和4bytes的 Tail给Cache，用来读取和管理。 第2部分是为Transaction层提供的Header信息。它一共占据了48bytes，包括24bytes的控制信息，和一系列的Interested Transaction Slot (ITS)。 剩下的部分都叫Data Area，用来存储用户数据。Data Area也包括data header，和row data及剩余空间。但是Cluster blocks, table blocks, index block的data header，row data结构是不相同的，这里主要介绍table blocks. buffer tsn: 4 rdba: 0x01423359 (5/144217) scn: 0x0000.01461c06 seq: 0x02 flg: 0x04 tail: 0x1c062102 frmt: 0x02 chkval: 0x8ab3 type: 0x21=SECOND LEVEL BITMAP BLOCK l Database block address:占用4 bytes。 l Tablespace relative database block address(RDBA)：包括Tablespace 所在数据文件的相对文件号file#=5, 和数据块的block_id=144217。 l SCN:占用6bytes，最后变化的scn。包括2bytes的高位字节(SCN wrap)，和4bytes的低位字节(SCN base)。 l Sequence:占用1byte，用途不明确，可能是辅助SCN的变化。 l Flag:占用1byte。 l Format:占用1byte，应该是用来区分版本。Oracle 8之前值为1，之后为2。 l Checksum:占用2byte，跟db_block_checksum 参数有关系。 引用oracle document 的解释："DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read-only if this parameter is true and the last write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log. If this parameter is set to false, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces." l Block type:占用1byte，经常用到的有，1=undo segment header block; 2=undo data block; 5= data segment header block; 6=data block。 l Unused:占用4bytes，用来前后兼容。 l Tail：包括了SCN中SCN base的低位(low-order)2bytes，然后是block type，还有Sequence number。每当block 被读的时候，都要检查Tail与 block header 是否一致，保证了这个block不是损坏的(corrupted)。 一共占据48bytes，包括24bytes的控制信息，和一系列的Interested Transaction Slot (ITS)。这些ITS组合在一起称为Interested Transaction List (ITL)。初始的ITL slot 数量由 INITRANS 决定(index branch block 只有1个slot)。如果有足够的剩余空间，oracle会根据需要动态的分配这些slot，直到受到空间限制或者达到了MAXTRANS。 Block header dump: 0x01422823 Object id on Block? Y seg/obj: 0x13f23 csc: 0x00.148e70b itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1422820 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0001.014.000048b8 0x00c005e8.02c7.2b --U- 1 fsc 0x0000.0148e70d 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 24bytes的控制信息包括： Object number(seg/obj):占用4bytes，指在OBJ$中记录的segment 的 object number(0x13f23=81699)。 Cleanout SCN(csc):占用6bytes，最后一次 full cleanout 的scn。 ITL count(itc): 占用1byte，ITL 的slot数量。 Flag: 占用2bytes。O表示这个block在freelist 上。否则flag为"-"。 Block type: 占用1byte。1=data; 2=index。 ITL freelist slot(fsl): 占用1byte。Index to the first slot on the ITL freelist。 Next freelist block(fnx): 占用4byte。Segment freelist中下一个block的RDBA。 Version: 1 byte。 Unused: 4bytes，用来前后兼容。 每个ITL entry包括以下的内容： Transaction id(Xid): 8bytes。其中包括rollback segment number, transaction table中的slot number等。 Undo block address(Uba): 8bytes。其中包括rollback segment block的DBA，sequence number等。 Flags: 1nibble。 ---- = transaction is active, or committed pending cleanout C--- = transaction has been committed and locks cleaned out -B-- = this undo record contains the undo for this ITL entry --U- = transaction committed (maybe long ago); SCN is an upper bound ---T = transaction was still active at block cleanout SCN Locks: 3nibbles. 也就是所谓的行级锁(row-level locks)。 SCN or free space credit: 6bytes. 如果这个事务已经clean out，这个值就是SCN；否则，前两个字节表示由这个事务释放的此block中的空间数。 包括14bytes的data header，4bytes/table的table dictionary，2bytes/row的row dictionary，即每增加一条row，row dictionary就多出2bytes用来记载该row，可以通过下面的hsiz反映出来。table dictionary主要用于cluster block中，只不过table block中的table dictionary只有一个table。 data_block_dump,data header at 0x2aff1e617264 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x2aff1e617264 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f93 avsp=0x1f7b tosp=0x1f7b 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f93 block_row_dump: tab 0, row 0, @0x1f93 tl: 5 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 1] 61 end_of_block_dump 关于fb Flag Byte: K = Cluster Key (Flags may change meaning if this is set to show HASH cluster) C = Cluster table member H = Head piece of row D = Deleted row F = First data piece L = Last data piece P = First column continues from previous piece N = Last column continues in next piece Oracle确实有Minimun row length的要求，即5 bytes的实际数据。如果加上3 bytes的row header, 和在block hader 中占据的2 bytes的row directory entry ,还有1byte的column长度，应该一共是11 bytes。所以，一个block中最多包括 db_block_size/11 条rows。 对于一个没有插入任何row的block来说，并且假设使用了默认的INITRANS，它的大小应该是: db_block_size- (cache header+ transaction header +data header + Tail + table dictionary*ntab + row dictionary*row )=8192 - ( 20+48+14+4+4 +0)=8192 - 90 =8102 bytes segment header结构一半位于第3个block(第1，2个分别为L1,L2位图块)， 可以通过以下视图查询到位置。 SQL> select header_file,header_block from dba_segments where segment_name='TEST'; HEADER_FILE HEADER_BLOCK ----------- ------------ 5 141346 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: 0 spare2: 0 #extents: 1 #blocks: 8 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x01422823 ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Disk Lock:: Locked by xid: 0x0001.016.000048c7 -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x01422823 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: 0x01422820 Level 1 BMB for Low HWM block: 0x01422820 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 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:: next 0x00000000 #extents: 1 obj#: 81700 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x01422820 length: 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 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: 0 spare2: 0 #extents: 1 #blocks: 8 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x01422828 ext#: 0 blk#: 8 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x01422828 ext#: 0 blk#: 8 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x01422820 Level 1 BMB for Low HWM block: 0x01422820 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 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:: next 0x00000000 #extents: 1 obj#: 81700 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x01422820 length: 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 对于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）。 Highwater:: 0x01422823 ext#: 0 blk#: 3 ext size: 8 -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x01422820 Length: 8 Offset: 0 0:Metadata 1:Metadata 2:Metadata 3:unformatted 4:unformatted 5:unformatted 6:unformatted 7:unformatted -------------------------------------------------------- Highwater:: 0x01422828 ext#: 0 blk#: 8 ext size: 8 一同变化的还有低高水线的位置 -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x01422820 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 ---------------------------------------------------------- 注：高水位线Highwater:: 0x01422828中，前两位：0x是十六进制标识，最后八位才是高水位线的block id。16进制的dba转化为10进制是：SELECT dbms_utility.data_block_address_block(to_number('01422823','xxxxxxxx')) FROM dual; 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_ID FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- ---------- 0 5 141624 8 1 5 141632 8 2 5 141640 8 3 5 144216 8 4 5 144224 8 5 5 144232 8 6 5 144240 8 7 5 144248 8 8 5 146432 8 9 5 146440 8 10 5 146448 8 EXTENT_ID FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- ---------- 11 5 146456 8 12 5 146464 8 13 5 146472 8 14 5 146480 8 15 5 146488 8 16 5 145280 128 17 5 145408 128 18 5 145536 128 19 5 145664 128 20 5 145792 128 21 5 145920 128 EXTENT_ID FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- ---------- 22 5 146048 128 23 5 146176 128 24 rows selected. SQL> select header_file,header_block,blocks,extents from dba_segments where segment_name='TT'; HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS ----------- ------------ ---------- ---------- 5 141626 1152 24 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. ============================================= Start dump data blocks tsn: 4 file#:5 minblk 141624 maxblk 141626 *** 2012-03-07 22:02:58.619 Block dump from cache: Dump of buffer cache at level 4 for tsn=4, rdba=21113144 Block dump from disk: buffer tsn: 4 rdba: 0x01422938 (5/141624) scn: 0x0000.01494105 seq: 0x02 flg: 0x04 tail: 0x41052002 frmt: 0x02 chkval: 0x9e9a type: 0x20=FIRST LEVEL BITMAP BLOCK Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00002AFF1E617200 to 0x00002AFF1E619200 ……………………………………………………………………. Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x01422939 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: 81701 -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x01422938 Length: 8 Offset: 0 0x01422940 Length: 8 Offset: 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=21113145 Block dump from disk: buffer tsn: 4 rdba: 0x01422939 (5/141625) scn: 0x0000.01494103 seq: 0x11 flg: 0x04 tail: 0x41032111 frmt: 0x02 chkval: 0xb922 type: 0x21=SECOND LEVEL BITMAP BLOCK Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00002AFF1E617200 to 0x00002AFF1E619200 2AFF1E617200 0000A221 01422939 01494103 04110000 [!...9)B..AI.....] 2AFF1E617210 0000B922 00000000 00000000 00000000 ["...............] 2AFF1E617220 00000000 00000000 00000000 00000000 [................] Repeat 1 times 2AFF1E617240 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 [..... 2AFF1E6172A0 00010001 01423C20 00010001 01423C30 [.... 2AFF1E6172B0 00010001 01423780 00010001 01423781 [.....7B......7B.] 2AFF1E6172C0 00010001 01423800 00010001 01423801 [.....8B......8B.] 2AFF1E6172D0 00010001 01423880 00010001 01423881 [.....8B......8B.] 2AFF1E6172E0 00010001 01423900 00010001 01423901 [.....9B......9B.] 2AFF1E6172F0 00010001 01423980 00010001 01423981 [.....9B......9B.] 2AFF1E617300 00010001 01423A00 00010001 01423A01 [.....:B......:B.] 2AFF1E617310 00010001 01423A80 00010001 01423A81 [.....:B......:B.] 2AFF1E617320 00010001 01423B00 00010005 01423B01 [.....;B......;B.] 2AFF1E617330 00010005 00000000 00000000 00000000 [................] 2AFF1E617340 00000000 00000000 00000000 00000000 [................] Repeat 490 times 2AFF1E6191F0 00000000 00000000 00000000 41032111 [.............!.A] Dump of Second Level Bitmap Block number: 24 nfree: 2 ffree: 22 pdba: 0x0142293a Inc #: 0 Objd: 81701 opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x01422938 Free: 1 Inst: 1 0x01422948 Free: 1 Inst: 1 0x01423360 Free: 1 Inst: 1 0x01423370 Free: 1 Inst: 1 0x01423c00 Free: 1 Inst: 1 0x01423c10 Free: 1 Inst: 1 0x01423c20 Free: 1 Inst: 1 0x01423c30 Free: 1 Inst: 1 0x01423780 Free: 1 Inst: 1 0x01423781 Free: 1 Inst: 1 0x01423800 Free: 1 Inst: 1 0x01423801 Free: 1 Inst: 1 0x01423880 Free: 1 Inst: 1 0x01423881 Free: 1 Inst: 1 0x01423900 Free: 1 Inst: 1 0x01423901 Free: 1 Inst: 1 0x01423980 Free: 1 Inst: 1 0x01423981 Free: 1 Inst: 1 0x01423a00 Free: 1 Inst: 1 0x01423a01 Free: 1 Inst: 1 0x01423a80 Free: 1 Inst: 1 0x01423a81 Free: 1 Inst: 1 0x01423b00 Free: 5 Inst: 1 0x01423b01 Free: 5 Inst: 1 -------------------------------------------------------- Block dump from cache: Dump of buffer cache at level 4 for tsn=4, rdba=21113146 Block dump from disk: buffer tsn: 4 rdba: 0x0142293a (5/141626) scn: 0x0000.01494108 seq: 0x01 flg: 0x04 tail: 0x41082301 frmt: 0x02 chkval: 0xa724 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: 0 spare2: 0 #extents: 24 #blocks: 1152 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x01423b32 ext#: 23 blk#: 50 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1074 mapblk 0x00000000 offset: 23 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x01423b32 ext#: 23 blk#: 50 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1074 mapblk 0x00000000 offset: 23 Level 1 BMB for High HWM block: 0x01423b00 Level 1 BMB for Low HWM block: 0x01423b00 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x01422939 Last Level 1 BMB: 0x01423b01 Last Level II BMB: 0x01422939 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 24 obj#: 81701 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x01422938 length: 8 0x01422940 length: 8 0x01422948 length: 8 0x01423358 length: 8 0x01423360 length: 8 0x01423368 length: 8 0x01423370 length: 8 0x01423378 length: 8 0x01423c00 length: 8 0x01423c08 length: 8 0x01423c10 length: 8 0x01423c18 length: 8 0x01423c20 length: 8 0x01423c28 length: 8 0x01423c30 length: 8 0x01423c38 length: 8 0x01423780 length: 128 0x01423800 length: 128 0x01423880 length: 128 0x01423900 length: 128 0x01423980 length: 128 0x01423a00 length: 128 0x01423a80 length: 128 0x01423b00 length: 128 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x01422938 Data dba: 0x0142293b Extent 1 : L1 dba: 0x01422938 Data dba: 0x01422940 Extent 2 : L1 dba: 0x01422948 Data dba: 0x01422949 Extent 3 : L1 dba: 0x01422948 Data dba: 0x01423358 Extent 4 : L1 dba: 0x01423360 Data dba: 0x01423361 Extent 5 : L1 dba: 0x01423360 Data dba: 0x01423368 Extent 6 : L1 dba: 0x01423370 Data dba: 0x01423371 Extent 7 : L1 dba: 0x01423370 Data dba: 0x01423378 Extent 8 : L1 dba: 0x01423c00 Data dba: 0x01423c01 Extent 9 : L1 dba: 0x01423c00 Data dba: 0x01423c08 Extent 10 : L1 dba: 0x01423c10 Data dba: 0x01423c11 Extent 11 : L1 dba: 0x01423c10 Data dba: 0x01423c18 Extent 12 : L1 dba: 0x01423c20 Data dba: 0x01423c21 Extent 13 : L1 dba: 0x01423c20 Data dba: 0x01423c28 Extent 14 : L1 dba: 0x01423c30 Data dba: 0x01423c31 Extent 15 : L1 dba: 0x01423c30 Data dba: 0x01423c38 Extent 16 : L1 dba: 0x01423780 Data dba: 0x01423782 Extent 17 : L1 dba: 0x01423800 Data dba: 0x01423802 Extent 18 : L1 dba: 0x01423880 Data dba: 0x01423882 Extent 19 : L1 dba: 0x01423900 Data dba: 0x01423902 Extent 20 : L1 dba: 0x01423980 Data dba: 0x01423982 Extent 21 : L1 dba: 0x01423a00 Data dba: 0x01423a02 Extent 22 : L1 dba: 0x01423a80 Data dba: 0x01423a82 Extent 23 : L1 dba: 0x01423b00 Data dba: 0x01423b02 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x01422939 End dump data blocks tsn: 4 file#: 5 minblk 141624 maxblk 141626 =========================================================== 其中在第141624个block的dump信息中我们发现frmt: 0x02 chkval: 0x9e9a type: 0x20=FIRST LEVEL BITMAP BLOCK，说明这个block是第一级bitmap block，oracle用第一级bitmap block来管理data block的使用情况： -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x01422938 Length: 8 Offset: 0 0x01422940 Length: 8 Offset: 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 141624的dump）发现141624＃block总共管理了16个block，也就是2个extent。由于extent是由连续的block组成的，因此只需要记录每个extent的起始block地址(0x01422938 )和extent的数量即可。两个16进制的dba转化为10进制的分别是： SQL> select dbms_utility.data_block_address_block(to_number('01422938 ','xxxxxxxx')) block_id from dual; BLOCK_ID ----------------------------------------------------------------------- 141624 SQL> 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暂时分析完毕。 再来141625＃block，141625＃block的类型是：type: 0x21=SECOND LEVEL BITMAP BLOCK。说明该block是二级bitmap block，他是用来管理一级bitmap block的。141625＃block的主要dump信息： L1 Ranges : -------------------------------------------------------- 0x01422938 Free: 1 Inst: 1 0x01422948 Free: 1 Inst: 1 0x01423360 Free: 1 Inst: 1 0x01423370 Free: 1 Inst: 1 0x01423c00 Free: 1 Inst: 1 0x01423c10 Free: 1 Inst: 1 0x01423c20 Free: 1 Inst: 1 0x01423c30 Free: 1 Inst: 1 0x01423780 Free: 1 Inst: 1 0x01423781 Free: 1 Inst: 1 0x01423800 Free: 1 Inst: 1 0x01423801 Free: 1 Inst: 1 0x01423880 Free: 1 Inst: 1 0x01423881 Free: 1 Inst: 1 0x01423900 Free: 1 Inst: 1 0x01423901 Free: 1 Inst: 1 0x01423980 Free: 1 Inst: 1 0x01423981 Free: 1 Inst: 1 0x01423a00 Free: 1 Inst: 1 0x01423a01 Free: 1 Inst: 1 0x01423a80 Free: 1 Inst: 1 0x01423a81 Free: 1 Inst: 1 0x01423b00 Free: 5 Inst: 1 0x01423b01 Free: 5 Inst: 1 -------------------------------------------------------- 其中L1 Ranges 中的哪些dba转为10进制如下： 141624 141640 144224 144240 146432 146448 146464 146480 145280 145281 145408 145409 145536 145537 145664 145665 145792 145793 145920 145921 146048 146049 146176 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 ---------- 141624 141640 144224 144240 146432 146448 146464 146480 145280 145536 145792 BLOCK_ID ---------- 146048 12 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可能非常的难。
也就是说我认为BMB的结构应该是均衡的，同时段头的PAGETABLE SEGMENT HEADER同时充当了第0个3级位图块的角色。 在PAGETABLE SEGMENT HEADER中实际上我们可以很容易的看到这样的输出： -------------------------------------------------------- Segment Type: 1 nl2: 103 blksz: 2048 fbsz: 0 L2 Array start offset: 0x00000434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x0355cfad Last Level 1 BMB: 0x03560c9c Last Level II BMB: 0x0355cfad Last Level III BMB: 0x00000000 Map Header:: next 0x034000bf #extents: 51 obj#: 33141 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 也就是说，这里记录了First Level 3 BMB和Last Level III BMB的地址，那么这就足够了，这里的双向指针完全可以进行Level 3级位图块的导航，而这第0个三级位图块也即段头，并无需记录所有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 test datafile '/opt/oracle/oradata/orcl/test01.dbf' size 1024M reuse blocksize 2048 extent management local uniform. size 10k segment space management auto; Tablespace created. SQL> set timing on SQL> 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 eygle pctfree 50 initrans 1 maxtrans 255; Table created. SQL> begin for i in 1 .. 100 loop for i in 1 .. 100000 loop insert 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 4032222 buffer tsn: 12 rdba: 0x037d86de (13/4032222) scn: 0x0000.00bdc864 seq: 0x01 flg: 0x04 tail: 0xc8642201 frmt: 0x02 chkval: 0xf597 type: 0x22=THIRD LEVEL BITMAP BLOCK Dump of Third Level Bitmap Block number: 9 , next : 0x00000000 L2 Ranges : -------------------------------------------------------- 0x037d86dd 0x037dd22d 0x037e1d7d 0x037e68cd 0x037eb41d 0x037eff6d 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.html dumping_oracle_blocks翻译（二）： http://www.itpub.net/thread-1516907-1-1.html oracle存储结构－segment header结构解析： bitmap block以及segment header块学习： http://space.itpub.net/22295535/viewspace-710229 关于ASSM的位图管理，证实了一些想法: http://www.itpub.net/thread-734505-1-3.html Oracle ASSM三级位图块结构: http://www.eygle.com/archives/2007/07/oracle_assm_level3_bmb.html 关于block中数据的存储和重组的探究: http://www.itpub.net/thread-112239-1-9.html 
二、对block的dump过程
1、dump block
2、Trace 文件
三、先介绍数据块中包括的3种头信息
四、结合trace文件中的信息详细介绍
1、The Cache Header and Tail
2、The Transaction Header
3、Data Area
4、关于Minimum row length
5、初始block大小
五、segment header结构解析
1、segment header介绍
2、空表segment header dump数据
3、插入数据后segment header dump数据
4、segment header dump数据差异
1.) 建立表后的高水线
2.) 建立表后L1块的dba range
3.) 插入数据后高水线
4.) 插入数据后L1块的dba range
六、bitmap block及segment header块
1、测试如下
2、dump主要内容摘录
3、FIRST LEVEL BITMAP BLOCK
4、SECOND LEVEL BITMAP BLOCK
七、Oracle ASSM三级位图块结构
1、ASSM的结构猜想
2、第二个3级位图块
参考资料
1.jpg来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/751371/viewspace-718011/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- #奋斗故事#我在魔都的IT经历
- ASH内存强制Flush日志解决一例
- Linux 6.2 安装Oracle 10g ORA-27125:unable to create shared memory segment
- HP UNIX 11.23 Itanium 64 for oracle 9i
- 2014年第1季度Oracle PSU更新(For 10.2.0.4+版本)
- Oracle 11g opiodr aborting process unknown ospid (***) as a result of ORA-28
- ORACLE 11.2 RAC EXPDP ORA-31693: 表数据对象 ORA-31617: 无法打开要写入的转储文件
- 常用的Linux可插拔认证模块（PAM）应用举例
- ORA-04030: out of process memory when trying to allocate **bytes (sort subheap)
- Oracle OS Watcher 工具 使用详解
