ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【听海日志】之data block物理结构

【听海日志】之data block物理结构

原创 Linux操作系统 作者:听海★蓝心梦 时间:2012-03-08 08:29:39 0 删除 编辑

一、Data Block 物理结构图

二、blockdump过程

1dump 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

2Trace 文件

[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

三、先介绍数据块中包括的3种头信息

       首先,数据块是通过data block buffer cache完成读和写操作的,所以它提供了20bytesCache Header4bytes TailCache,用来读取和管理。

       2部分是为Transaction层提供的Header信息。它一共占据了48bytes,包括24bytes的控制信息,和一系列的Interested Transaction Slot (ITS)

       剩下的部分都叫Data Area,用来存储用户数据。Data Area也包括data header,和row data及剩余空间。但是Cluster blocks, table blocks, index blockdata headerrow data结构是不相同的,这里主要介绍table blocks.

四、结合trace文件中的信息详细介绍

1The Cache Header and Tail

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:包括了SCNSCN base的低位(low-order)2bytes,然后是block type,还有Sequence number。每当block 被读的时候,都要检查Tail block header 是否一致,保证了这个block不是损坏的(corrupted)

2The Transaction Header

       一共占据48bytes,包括24bytes的控制信息,和一系列的Interested Transaction Slot (ITS)。这些ITS组合在一起称为Interested Transaction List (ITL)。初始的ITL slot 数量由 INITRANS 决定(index branch block 只有1slot)。如果有足够的剩余空间,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): 占用1byteITL slot数量。

Flag: 占用2bytesO表示这个blockfreelist 上。否则flag"-"

Block type: 占用1byte1=data; 2=index

ITL freelist slot(fsl): 占用1byteIndex to the first slot on the ITL freelist

Next freelist block(fnx): 占用4byteSegment freelist中下一个blockRDBA

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 blockDBAsequence 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中的空间数。

3Data Area

       包括14bytesdata header4bytes/tabletable dictionary2bytes/rowrow dictionary,即每增加一条rowrow 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

4、关于Minimum row length

       Oracle确实有Minimun row length的要求,即5 bytes的实际数据。如果加上3 bytesrow header, 和在block hader 中占据的2 bytesrow directory entry ,还有1bytecolumn长度,应该一共是11 bytes。所以,一个block中最多包括 db_block_size/11 rows

5、初始block大小

       对于一个没有插入任何rowblock来说,并且假设使用了默认的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结构解析

1segment header介绍

       segment header结构一半位于第3block(12个分别为L1,L2位图块)

可以通过以下视图查询到位置。

SQL> 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: 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

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: 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

4segment header dump数据差异

对于20字节的头部来说,有变动的只有scn,seqtail字段:

       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 block141347,那么高水线在1413470x01422823),而当插入几行数据后高水线推到了1413520x01422828)。

1.)  建立表后的高水线

Highwater::  0x01422823  ext#: 0      blk#: 3      ext size: 8

2.) 建立表后L1块的dba range

-------------------------------------------------------- 

DBA Ranges : 

-------------------------------------------------------- 

 0x01422820  Length: 8      Offset: 0 

 

 0:Metadata   1:Metadata   2:Metadata   3:unformatted 

 4:unformatted   5:unformatted   6:unformatted   7:unformatted 

--------------------------------------------------------

3.) 插入数据后高水线

Highwater::  0x01422828  ext#: 0      blk#: 8      ext size: 8

4.) 插入数据后L1块的dba range

一同变化的还有低高水线的位置

  --------------------------------------------------------

  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 id16进制的dba转化为10进制是:SELECT dbms_utility.data_block_address_block(to_number('01422823','xxxxxxxx')) FROM dual;

六、bitmap blocksegment 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_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 显示的是141626segment header),而extent_id=0也就是说第一个extent是从第141624block开始的,那么141624141625 block到底做什么用了?dump下看看:

SQL> alter system dump datafile 5 block min 141624 block max 141626;

 

System altered.

2dump主要内容摘录

=============================================

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

===========================================================

3FIRST LEVEL BITMAP BLOCK

       其中在第141624blockdump信息中我们发现frmt: 0x02 chkval: 0x9e9a type: 0x20=FIRST LEVEL BITMAP BLOCK,说明这个block是第一级bitmap blockoracle用第一级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 141624dump)发现141624block总共管理了16block,也就是2extent。由于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 141624141632正是我们上面查询dba_extents是显示的extent_id = 0 , 1两个extent。另外141624 block dump信息中的:

       0:Metadata   1:Metadata   2:Metadata

       表示141624#~141626block显示的是Metadata(元数据:数据仓库中非常重要的概念,管理数据的数据,oracle中的数据字典据都可以理解成元数据),其中0表示是141624block1 表示141625block2 表示141626blocksegment header),其他显示的都是full,表示这些block上都已经没有可用的空闲空间。同时也发现我们真真使用数据是从141627block开始的。

       141624block dump内容里还有一个重要的信息就是parent dba: 0x01422939表示141624blockparent block的地址,而这个block的地址正好是141625block的地址:rdba: 0x01422939 (5/141625)。至此141624block暂时分析完毕。

4SECOND LEVEL BITMAP BLOCK

再来141625block141625block的类型是:type: 0x21=SECOND LEVEL BITMAP BLOCK。说明该block是二级bitmap block,他是用来管理一级bitmap block的。141625block的主要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 blockblock_id,也正是dba_extents中显示的,同时也看到每一个一级bitmap block管理了16data block。不过奇怪的是为什么146177block怎么也成了一级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.

       再来看看141625block pdba: 0x0142293a,这个地址正是141626blockdbardba: 0x0142293a。接下来简单看看141626block的类型是:

       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可能非常的难。

七、Oracle ASSM三级位图块结构

1ASSM的结构猜想

       也就是说我认为BMB的结构应该是均衡的,同时段头的PAGETABLE SEGMENT HEADER同时充当了第03级位图块的角色。

       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 BMBLast 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、使第03级位图块也即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结构解析:

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.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

 

1.jpg

2.jpg

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

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