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

最新推荐文章于 2021-12-23 17:03:48 发布

cnqt2015

最新推荐文章于 2021-12-23 17:03:48 发布

阅读量716

点赞数

文章标签：数据库操作系统 php

一、Data Block 物理结构图

二、对block的dump过程

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

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: 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完成读和写操作的，所以它提供了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.

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

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

2、The Transaction Header

一共占据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中的空间数。

3、Data Area

包括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

4、关于Minimum row length

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。

5、初始block大小

对于一个没有插入任何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结构解析

1、segment header介绍

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

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

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

六、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_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.

2、dump主要内容摘录

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

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

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

3、FIRST LEVEL BITMAP BLOCK

其中在第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暂时分析完毕。

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 :

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

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

七、Oracle ASSM三级位图块结构

1、ASSM的结构猜想

也就是说我认为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级位图块的地址。

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 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/751371/viewspace-718011/

cnqt2015

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【听海日志】之data block物理结构

一、Data Block 物理结构图二、对block的dump过程1、dump blocktest@ORCL> create table test (a varchar(10));Tabl...
复制链接

扫一扫