internal block structure(1)

来源：互联网发布：苏联解体后经济数据编辑：程序博客网时间：2024/05/16 13:50

参考自:

http://www.diybl.com/course/7_databases/oracle/Oracleshl/2008926/145870.html

以前讲课的时候提到块的结构,总提到block header,table directory,row directory,row header etc,总是有点虚.眼见为实,现在自己来具体看看这些结构.

自己的试验:

建立表空间:
create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 50m

extent management local uniform size 64k segment space management manual;

select pa.value || '/' || i.instance_name || '_ora_'

|| pr.spid || '.trc' as trace_file

from v$session s, v$process pr, v$parameter pa, v$instance i

where s.username = user and s.paddr = pr.addr

and pa.name='user_dump_dest';

--这个在10g中不对,应该...

create table hr.emp tablespace test

as select * from scott.emp;

查询file,block
select file_id,block_id,bytes,blocks from dba_extents

where owner='HR' and segment_name='EMP'

File_id block_id bytes blocks

7 9 65535 8

File7 ,开始bock是9号,block号码一直到9+8.

1,dump data block

Alter system dump datafile 7 block 10;(还是10)

More orcl_ora_3309.trc:

Start dump data blocks tsn: 7 file#: 7 minblk 10 maxblk 10 --->dump几个块

buffer tsn: 7 rdba: 0x01c0000a (7/10) ---->relative data block address(rdba) ---可以转换,这里已经给出来了

scn: 0x0000.000bdc6a seq: 0x02 flg: 0x04 tail: 0xdc6a0602 ---->scn

frmt: 0x02 chkval: 0x242b type: 0x06=trans data ---->object

Hex dump of block: st=0, typ_found=1

…. --->中间省略了一堆cache中的东西

Block header dump: 0x01c0000a

Object id on Block? Y

seg/obj: 0xcf6f csc: 0x00.bdc69 itc: 3 flg: - typ: 1 - DATA -->flg:- not on freelist, 0 on freelist; csc:block cleanout was performed on this block.

fsl: 0 fnx: 0x0 ver: 0x01

cache

Type Meaning

0x02 undo block

0x06 table or index data block

0x0e undo segment header

0x10 data segment header block

0x17 bitmapped data segment header

--进制转换
http://www.eygle.com/archives/2004/06/oracle_howto_convert.html
Select * from To_number('19f','xxx') from dual;

select to_number('cf6f','xxxx') from dual;

53103;

select * from dba_objects where object_id=53103;

No transactions , so skil ITL info...

data_block_dump,data header at 0xcc19a74

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

tsiz: 0x1f88 -->available space on this block,8072??

hsiz: 0x2e

pbl: 0x0cc19a74

bdba: 0x01c0000a

76543210

flag=--------

ntab=1 --->table numbers,

nrow=14 --->14 rows

frre=-1

fsbo=0x2e

fseo=0x1d51

avsp=0x1d23

tosp=0x1d23

0xe:pti[0] nrow=14 offs=0 --->directory of rows,,,,

0x12:pri[0] offs=0x1f62 -->1f62 is first row's offset address...

0x14:pri[1] offs=0x1f37

0x16:pri[2] offs=0x1f0c

0x18:pri[3] offs=0x1ee3

0x1a:pri[4] offs=0x1eb6

0x1c:pri[5] offs=0x1e8d

0x1e:pri[6] offs=0x1e64

0x20:pri[7] offs=0x1e3c

0x22:pri[8] offs=0x1e16

0x24:pri[9] offs=0x1deb

0x26:pri[10] offs=0x1dc5

0x28:pri[11] offs=0x1d9f

0x2a:pri[12] offs=0x1d78

0x2c:pri[13] offs=0x1d51

review一下ROWID的结构: O.F.B.R(objectno,relativeno,blockno,rowno),rowno points to a slot in the directory.-->locate row on this block.

block_row_dump:

tab 0, row 0, @0x1f62

tl: 38 fb: --H-FL-- lb: 0x0 cc: 8 --->t1(total bytes this row,including any overhead), cc-column count. Fb:row flag. H:包含row head, F:该块包括第一个片, L :该块包括该row的第二个片段,都包含说明该行在该块中.没有chain. 因为这也是row head,所以没有migrated.

col 0: [ 3] c2 4a 46

col 1: [ 5] 53 4d 49 54 48

col 2: [ 5] 43 4c 45 52 4b

col 3: [ 3] c2 50 03

col 4: [ 7] 77 b4 0c 11 01 01 01

col 5: [ 2] c2 09

col 6: *NULL*

col 7: [ 2] c1 15

SQL> desc hr.emp

Name Null? Type

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

EMPNO NUMBER(4) ----->1

ENAME VARCHAR2(10) ----->2

JOB VARCHAR2(9) ----->3

MGR NUMBER(4) ----->4

HIREDATE DATE ------>5

SAL NUMBER(7,2) ------>6

COMM NUMBER(7,2) ------>7

DEPTNO NUMBER(2) ------>8

-->试验一下row migrate, row chain

-->试验assm

-->试验update

--->corrupt

--->试验一下编码类型,char类型

2 Dumping extent header
Alter system dump datafile 7 block 9;

Start dump data blocks tsn: 7 file#: 7 minblk 9 maxblk 9

buffer tsn: 7 rdba: 0x01c00009 (7/9)

scn: 0x0000.000bdc6e seq: 0x01 flg: 0x04 tail: 0xdc6e1001

frmt: 0x02 chkval: 0x289a type: 0x10=DATA SEGMENT HEADER - UNLIMITED --->type is segment header

Hex dump of block: st=0, typ_found=1

Extent Control Header

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

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7 --->1 extent,7 data blocks(1 is header, totally is 8)

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x01c0000b ext#: 0 blk#: 1 ext size: 7 -->HWM,0 extent 1 block (2nd block)

#blocks in seg. hdr's freelists: 0

#blocks below: 1 -->below HWM block nubmers

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 1 obj#: 53103 flag: 0x40000000

Extent Map

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

0x01c0000a length: 7

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0

SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

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

HWM,high water mark. 记录的是FTS的时候,读到(包含<=)哪个块为止,所以,这里是第二个块,地址为…0b.
HWM当然不需要这么麻烦来查询,dbms_space()包来看,还有高人xzh写了个宇宙无敌终极版的show_space:)-以前测试过,这里不看了,去查查,结果一样.

3,dumping index.

只看B-tree索引,暂时不考虑bitmap index和IOT.

索引包含branch块和leaf块,dump的方法也需要先知道object_id号.

create index hr.emp_id_idx on hr.emp(empno) tablespace users;
select * from dba_objects where owner='HR' and object_name='EMP_ID_IDX';
53113

alter session set events 'immediate trace name treedump level 53113';

more *11352*:

Oracle process number: 21

Unix process pid: 11352, image: oracleorcl@rh1

*** SERVICE NAME:(orcl) 2009-07-07 08:06:35.588

*** SESSION ID:(138.1426) 2009-07-07 08:06:35.588

----- begin tree dump

leaf: 0x100019c 16777628 (0: nrow: 14 rrow: 14)-->no branch, only level 0,

----- end tree dump

如果有branch,从root节点的address开始会帮助我们遍历(traverse)整个树.

-->需试验测试增加level的时候情况.

Data block address:

http://www.orafaq.com/wiki/Data_block_address

SELECT dbms_utility.data_block_address_block(16777628) "BLOCK",

dbms_utility.data_block_address_file(16777628) "FILE"

FROM dual;

File :4, block:412

select * from dba_extents where file_id=4 and 412 between block_id and (block_id+blocks);

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

1 HR EMP_ID_IDX INDEX USERS 0 4 409 65536 8 4

验证通过

Alter system dump datafile 4 block 412;

Block header dump: 0x0100019c

Object id on Block? Y

seg/obj: 0xcf79 csc: 0x00.c1733 itc: 2 flg: E typ: 2 - INDEX --->type, index

brn: 0 bdba: 0x1000199 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.000c1733

Leaf block dump

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

header address 214014564=0xcc19a64

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 14

kdxcofbo 64=0x40

kdxcofeo 7851=0x1eab

kdxcoavs 7787

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0 --->next leaf block address pointer

kdxleprv 0=0x0 --->next leaf previous block address pointer ( what's meaning kdx ? )

kdxledsz 0

kdxlebksz 8032

row#0[8019] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4a 46 --->key column on index

col 1; len 6; (6): 01 c0 00 0a 00 00 --->this is rowid

row#1[8006] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4b 64

col 1; len 6; (6): 01 c0 00 0a 00 01

row#2[7993] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4c 16

col 1; len 6; (6): 01 c0 00 0a 00 02

row#3[7980] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4c 43

col 1; len 6; (6): 01 c0 00 0a 00 03

row#4[7967] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4d 37

col 1; len 6; (6): 01 c0 00 0a 00 04

row#5[7954] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4d 63

col 1; len 6; (6): 01 c0 00 0a 00 05

row#6[7941] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4e 53

col 1; len 6; (6): 01 c0 00 0a 00 06

row#7[7928] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4e 59

col 1; len 6; (6): 01 c0 00 0a 00 07

row#8[7915] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4f 28

col 1; len 6; (6): 01 c0 00 0a 00 08

row#9[7902] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4f 2d

col 1; len 6; (6): 01 c0 00 0a 00 09

row#10[7889] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4f 4d

col 1; len 6; (6): 01 c0 00 0a 00 0a

row#11[7877] flag: ------, lock: 0, len=12

col 0; len 2; (2): c2 50

col 1; len 6; (6): 01 c0 00 0a 00 0b

row#12[7864] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 50 03

col 1; len 6; (6): 01 c0 00 0a 00 0c

row#13[7851] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 50 23

col 1; len 6; (6): 01 c0 00 0a 00 0d

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 412 maxblk 412

该索引只有一个block,所以前后叶子节点的指针都指向空.

如果不是的话,则当前能找到的最后一个叶子节点之后,该节点显示:(类似…)

the end of the leaf block dump and the start of the next branch block.

----- end of leaf block dump -----

branch: 0xc01fef 12591087 (0: nrow: 279, level: 1) -->0第2个branch,第一个是-1(从左向右数),level是1. nrow是number of rows on block.

leaf: 0xc01ed6 12590806 (-1: nrow: 25 rrow: 25)

为什么不从0开始(象其他计数方法一样呢)

因为第一个key value是不存储的,总是假设小于第二个key值,而第二个key值是存在branch节点上是0,所以,-1,第一个branch小于0.

对于nrow,如果是leaf block,代表该block的rows数量. 如果是branch,该branch上的block数量.

rrow:当前会话commit之后的row数量; 如果没有active transaction的话, rrow= nrow (需要测一下). --->测试了一下不对,commit之前也已经变了.

--->测试索引的扩充试验

1* insert into hr.emp select * from hr.emp -->896 rows now.

448 rows created.

----- begin tree dump

branch: 0x100019c 16777628 (0: nrow: 2, level: 1)

leaf: 0x100019e 16777630 (-1: nrow: 448 rrow: 448)

leaf: 0x100019f 16777631 (0: nrow: 448 rrow: 448)

----- end tree dump

branch不变-仍然是原来第一个块, 4, 412 (file&block,一下略).

Leaf1:4,414
Leaf2:4,415

全dump出来看看:(409~417)

alter system dump datafile 4 block min 409 block max 417;

412--index,bdba:
Block header dump: 0x0100019c
kdxbrlmc 16777630=0x100019e
row#0[8047] dba: 16777631=0x100019f

col 0; len 3; (3): c2 4e 59

col 1; TERM

413-空

414-
rdba: 0x0100019e (4/414)

Leaf block dump

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

header address 214014564=0xcc19a64

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 1

kdxconro 448 -->rows number

kdxcofbo 932=0x3a4

kdxcofeo 2195=0x893

kdxcoavs 1276

kdxlespl 0

kdxlende 0

kdxlenxt 16777631=0x100019f --->next leaf address

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

Key values

First:
col 0; len 3; (3): c2 4a 46

col 1; len 6; (6): 01 c0 00 0a 00 00

…..

Last:

col 0; len 3; (3): c2 4e 53

col 1; len 6; (6): 01 c0 00 10 00 16

415-

header address 214014564=0xcc19a64

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 1

kdxconro 448

kdxcofbo 932=0x3a4

kdxcofeo 2272=0x8e0

kdxcoavs 1340

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0 ---->null

kdxleprv 16777630=0x100019e --->previous leaf node

kdxledsz 0

kdxlebksz 8032

row#0[4612] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 4e 59

col 1; len 6; (6): 01 c0 00 0a 00 07

416-空索引块

417,418-未用块

再次插入数据

branch: 0x100019c 16777628 (0: nrow: 5, level: 1)

leaf: 0x100019e 16777630 (-1: nrow: 422 rrow: 422)

leaf: 0x10001a0 16777632 (0: nrow: 474 rrow: 474)

leaf: 0x100019f 16777631 (1: nrow: 421 rrow: 421)

leaf: 0x10001a6 16777638 (2: nrow: 423 rrow: 423)

leaf: 0x100019d 16777629 (3: nrow: 52 rrow: 52)

Branch block dump

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

header address 214014540=0xcc19a4c

kdxcolev 1

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 1

kdxconro 4

kdxcofbo 36=0x24

kdxcofeo 8006=0x1f46

kdxcoavs 7970

kdxbrlmc 16777630=0x100019e

kdxbrsno 2

kdxbrbksz 8056

kdxbr2urrc 0

row#0[8034] dba: 16777632=0x10001a0

col 0; len 3; (3): c2 4c 43

col 1; len 4; (4): 01 c0 00 0e

row#1[8047] dba: 16777631=0x100019f

col 0; len 3; (3): c2 4e 59

col 1; TERM

row#2[8006] dba: 16777638=0x10001a6

col 0; len 3; (3): c2 4f 4d

col 1; len 4; (4): 01 c0 00 0e

row#3[8019] dba: 16777629=0x100019d

col 0; len 3; (3): c2 50 23

col 1; len 6; (6): 01 c0 00 11 00 28

小结:大致判断一下,一个块的时候,索引只有一个level 0,两个块的时候,level 1是branch节点(就是原来那个块,不过内容变了),该branch块记录了叶子节点的信息以及节点分割的key值(好利用各种排序二叉树等算法快速定位啊).也就是每个叶子节点在branch上记录key分割值和address).跟我们课堂上学到的原理一致.

(待续)