1.段头里包含了哪些信息?
============================================================
SEGMENT HEADER
============================================================
Start dump data blocks tsn: 3 file#: 3 minblk 30 maxblk 30
buffer tsn: 3 rdba: 0x00c0001e (3/30)
scn: 0x0000.00067e48 seq: 0x01 flg: 0x00 tail: 0x7e482301
frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 1280
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x00c000c9 (--高高水位线) ext#: 0 blk#: 192 ext size: 1280
#blocks in seg. hdr's freelists: 0
#blocks below: 170
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x00c00089(--低高水位线) ext#: 0 blk#: 128 ext size: 1280
#blocks in seg. hdr's freelists: 0
#blocks below: 128
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x00c0000b ---高高水位线所在的一级位图块
Level 1 BMB for Low HWM block: 0x00c0000a ---低高水位线所在的一级位图块
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x00c0001d
Last Level 1 BMB: 0x00c0001c --最后一个一级位图块的地址
Last Level II BMB: 0x00c0001d --最后一个二级位图块的地址
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 5987 flag: 0x20000000
Extent Map
SEGMENT HEADER
============================================================
Start dump data blocks tsn: 3 file#: 3 minblk 30 maxblk 30
buffer tsn: 3 rdba: 0x00c0001e (3/30)
scn: 0x0000.00067e48 seq: 0x01 flg: 0x00 tail: 0x7e482301
frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 1280
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x00c000c9 (--高高水位线) ext#: 0 blk#: 192 ext size: 1280
#blocks in seg. hdr's freelists: 0
#blocks below: 170
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x00c00089(--低高水位线) ext#: 0 blk#: 128 ext size: 1280
#blocks in seg. hdr's freelists: 0
#blocks below: 128
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x00c0000b ---高高水位线所在的一级位图块
Level 1 BMB for Low HWM block: 0x00c0000a ---低高水位线所在的一级位图块
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x00c0001d
Last Level 1 BMB: 0x00c0001c --最后一个一级位图块的地址
Last Level II BMB: 0x00c0001d --最后一个二级位图块的地址
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 5987 flag: 0x20000000
Extent Map
2.高高水位线所在的一级位图块包含了哪些信息?
============================================================
First Level Bitmap Block
============================================================
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x00c0001d poffset: 2
unformatted: 48 total: 64 first useful block: 0
owning instance : 1
instance ownership changed at 12/06/2010 16:20:36
Last successful Search 12/06/2010 16:20:36
Freeness Status: nf1 0 nf2 1 nf3 0 nf4 15
First Level Bitmap Block
============================================================
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x00c0001d poffset: 2
unformatted: 48 total: 64 first useful block: 0
owning instance : 1
instance ownership changed at 12/06/2010 16:20:36
Last successful Search 12/06/2010 16:20:36
Freeness Status: nf1 0 nf2 1 nf3 0 nf4 15
Extent Map Block Offset: 4294967295
First free datablock : 0
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Highwater:: 0x00c000c9(--高高水位线地址) ext#: 0 blk#: 192 ext size: 1280
#blocks in seg. hdr's freelists: 0
#blocks below: 170
mapblk 0x00000000 offset: 0
HWM Flag: HWM Set
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x00c00089(--注意,此地址即是低高水位线) Length: 64 Offset: 0
First free datablock : 0
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Highwater:: 0x00c000c9(--高高水位线地址) ext#: 0 blk#: 192 ext size: 1280
#blocks in seg. hdr's freelists: 0
#blocks below: 170
mapblk 0x00000000 offset: 0
HWM Flag: HWM Set
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x00c00089(--注意,此地址即是低高水位线) Length: 64 Offset: 0
0:75-100% free 1:75-100% free 2:75-100% free 3:75-100% free
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
8:75-100% free 9:75-100% free 10:75-100% free 11:25-50% free
12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted ----此位图块共包含了64个块,最后一个块是高高水位线所在位置。
--------------------------------------------------------
End dump data blocks tsn: 3 file#: 3 minblk 11 maxblk 11
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
8:75-100% free 9:75-100% free 10:75-100% free 11:25-50% free
12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted ----此位图块共包含了64个块,最后一个块是高高水位线所在位置。
--------------------------------------------------------
End dump data blocks tsn: 3 file#: 3 minblk 11 maxblk 11
换算一下低高水位线和高高水位线的地址:
sys@TEST>/
Enter value for dba: 00c000c9
old 1: select dbms_utility.data_block_address_block(to_number('&dba','xxxxxxxx')) from dual
new 1: select dbms_utility.data_block_address_block(to_number('00c000c9','xxxxxxxx')) from dual
sys@TEST>/
Enter value for dba: 00c000c9
old 1: select dbms_utility.data_block_address_block(to_number('&dba','xxxxxxxx')) from dual
new 1: select dbms_utility.data_block_address_block(to_number('00c000c9','xxxxxxxx')) from dual
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('00C000C9','XXXXXXXX'))
-----------------------------------------------------------------------
201
-----------------------------------------------------------------------
201
sys@TEST>/
Enter value for dba: 00c00089
old 1: select dbms_utility.data_block_address_block(to_number('&dba','xxxxxxxx')) from dual
new 1: select dbms_utility.data_block_address_block(to_number('00c00089','xxxxxxxx')) from dual
Enter value for dba: 00c00089
old 1: select dbms_utility.data_block_address_block(to_number('&dba','xxxxxxxx')) from dual
new 1: select dbms_utility.data_block_address_block(to_number('00c00089','xxxxxxxx')) from dual
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('00C00089','XXXXXXXX'))
-----------------------------------------------------------------------
137 ----137+64=201 也就是说块137是低高水位线,块201是高高水位线
-----------------------------------------------------------------------
137 ----137+64=201 也就是说块137是低高水位线,块201是高高水位线
在低高水位线块137与高高水位线块201之间,存在着格式化和未格式化的块,按照concepts里所说,oracle可以安全的读取低高水位线之前的数据,而在读取低高水位线与高高水位线之间的数据时,就需要谨慎的判断哪些块是格式化的,哪些块是未格式化的。
3.全表扫描时读取了哪些块?
============================================================
全表扫描时的10046 trace内容
============================================================
全表扫描时的10046 trace内容
============================================================
PARSING IN CURSOR #1 len=24 dep=0 uid=0 ct=3 lid=0 tim=1261397892906801 hv=2877299937 ad='5526c5f0'
select count(*) from ttt
END OF STMT
PARSE #1:c=20998,e=41879,p=1,cr=14,cu=0,mis=1,r=0,dep=0,og=4,tim=1261397892906794
BINDS #1:
EXEC #1:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1261397892906905
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 38 p1=3 p2=30 p3=1 ----顺序读,读取段头(块号30)
WAIT #1: nam='db file sequential read' ela= 31 p1=3 p2=11 p3=1 ----顺序读,读取高高水位线所在的位图块(地址0x00c000c9也就是块11)
WAIT #1: nam='db file sequential read' ela= 23 p1=3 p2=29 p3=1 ----顺序读,读取二级位图块
WAIT #1: nam='db file scattered read' ela= 463 p1=3 p2=31 p3=8 ----多块读,从段头之后的第一个块(块号31)开始读,一次读8个块
WAIT #1: nam='db file scattered read' ela= 527 p1=3 p2=39 p3=8
WAIT #1: nam='db file scattered read' ela= 848 p1=3 p2=47 p3=8
WAIT #1: nam='db file scattered read' ela= 409 p1=3 p2=55 p3=8
WAIT #1: nam='db file scattered read' ela= 488 p1=3 p2=63 p3=8
WAIT #1: nam='db file scattered read' ela= 472 p1=3 p2=71 p3=8
WAIT #1: nam='db file scattered read' ela= 413 p1=3 p2=79 p3=8
WAIT #1: nam='db file scattered read' ela= 123 p1=3 p2=87 p3=8
WAIT #1: nam='db file scattered read' ela= 522 p1=3 p2=95 p3=8
WAIT #1: nam='db file scattered read' ela= 566 p1=3 p2=103 p3=8
WAIT #1: nam='db file scattered read' ela= 124 p1=3 p2=111 p3=8
WAIT #1: nam='db file scattered read' ela= 117 p1=3 p2=119 p3=8
WAIT #1: nam='db file scattered read' ela= 422 p1=3 p2=127 p3=8
WAIT #1: nam='db file scattered read' ela= 479 p1=3 p2=135 p3=8 -----注意,此处读取了低高水位线也就是块号137,之后开始读取高高水位线之下的块
WAIT #1: nam='db file scattered read' ela= 542 p1=3 p2=143 p3=8
WAIT #1: nam='db file scattered read' ela= 102 p1=3 p2=151 p3=2 ------最后读到块152,停止读取.
FETCH #1:c=545916,e=533242,p=125,cr=127,cu=0,mis=0,r=1,dep=0,og=4,tim=1261397893440198
WAIT #1: nam='SQL*Net message from client' ela= 570 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1261397893440886
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
select count(*) from ttt
END OF STMT
PARSE #1:c=20998,e=41879,p=1,cr=14,cu=0,mis=1,r=0,dep=0,og=4,tim=1261397892906794
BINDS #1:
EXEC #1:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1261397892906905
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 38 p1=3 p2=30 p3=1 ----顺序读,读取段头(块号30)
WAIT #1: nam='db file sequential read' ela= 31 p1=3 p2=11 p3=1 ----顺序读,读取高高水位线所在的位图块(地址0x00c000c9也就是块11)
WAIT #1: nam='db file sequential read' ela= 23 p1=3 p2=29 p3=1 ----顺序读,读取二级位图块
WAIT #1: nam='db file scattered read' ela= 463 p1=3 p2=31 p3=8 ----多块读,从段头之后的第一个块(块号31)开始读,一次读8个块
WAIT #1: nam='db file scattered read' ela= 527 p1=3 p2=39 p3=8
WAIT #1: nam='db file scattered read' ela= 848 p1=3 p2=47 p3=8
WAIT #1: nam='db file scattered read' ela= 409 p1=3 p2=55 p3=8
WAIT #1: nam='db file scattered read' ela= 488 p1=3 p2=63 p3=8
WAIT #1: nam='db file scattered read' ela= 472 p1=3 p2=71 p3=8
WAIT #1: nam='db file scattered read' ela= 413 p1=3 p2=79 p3=8
WAIT #1: nam='db file scattered read' ela= 123 p1=3 p2=87 p3=8
WAIT #1: nam='db file scattered read' ela= 522 p1=3 p2=95 p3=8
WAIT #1: nam='db file scattered read' ela= 566 p1=3 p2=103 p3=8
WAIT #1: nam='db file scattered read' ela= 124 p1=3 p2=111 p3=8
WAIT #1: nam='db file scattered read' ela= 117 p1=3 p2=119 p3=8
WAIT #1: nam='db file scattered read' ela= 422 p1=3 p2=127 p3=8
WAIT #1: nam='db file scattered read' ela= 479 p1=3 p2=135 p3=8 -----注意,此处读取了低高水位线也就是块号137,之后开始读取高高水位线之下的块
WAIT #1: nam='db file scattered read' ela= 542 p1=3 p2=143 p3=8
WAIT #1: nam='db file scattered read' ela= 102 p1=3 p2=151 p3=2 ------最后读到块152,停止读取.
FETCH #1:c=545916,e=533242,p=125,cr=127,cu=0,mis=0,r=1,dep=0,og=4,tim=1261397893440198
WAIT #1: nam='SQL*Net message from client' ela= 570 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1261397893440886
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
============================================================
高高水位线所在的位图块里指示的各个块状态
============================================================
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x00c00089 Length: 64 Offset: 0
0:75-100% free 1:75-100% free 2:75-100% free 3:75-100% free
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
8:75-100% free 9:75-100% free 10:75-100% free 11:25-50% free
12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free ---多块读读到此处为止,不读取未格式化的块
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
高高水位线所在的位图块里指示的各个块状态
============================================================
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x00c00089 Length: 64 Offset: 0
0:75-100% free 1:75-100% free 2:75-100% free 3:75-100% free
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
8:75-100% free 9:75-100% free 10:75-100% free 11:25-50% free
12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free ---多块读读到此处为止,不读取未格式化的块
16:unformatted 17:unformatted 18:unformatted 19:unformatted
20:unformatted 21:unformatted 22:unformatted 23:unformatted
24:unformatted 25:unformatted 26:unformatted 27:unformatted
28:unformatted 29:unformatted 30:unformatted 31:unformatted
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted
60:unformatted 61:unformatted 62:unformatted 63:unformatted
结论:全表扫描读取到高高水位线以下的最后一个格式化的数据块,不读取未格式化的数据块。
4.二级位图块里包含了什么信息?
============================================================
SECOND LEVEL BITMAP BLOCK
============================================================
Start dump data blocks tsn: 3 file#: 3 minblk 29 maxblk 29
buffer tsn: 3 rdba: 0x00c0001d (3/29)
scn: 0x0000.00067e48 seq: 0x03 flg: 0x00 tail: 0x7e482103
frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK
Dump of Second Level Bitmap Block
number: 20 nfree: 18 ffree: 2 pdba: 0x00c0001e
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x00c00009 Free: 1 Inst: 1 --第一个一级位图块的地址
0x00c0000a Free: 1 Inst: 1
0x00c0000b Free: 5 Inst: 1
0x00c0000c Free: 5 Inst: 1
0x00c0000d Free: 5 Inst: 1
0x00c0000e Free: 5 Inst: 1
0x00c0000f Free: 5 Inst: 1
0x00c00010 Free: 5 Inst: 1
0x00c00011 Free: 5 Inst: 1
0x00c00012 Free: 5 Inst: 1
0x00c00013 Free: 5 Inst: 1
0x00c00014 Free: 5 Inst: 1
0x00c00015 Free: 5 Inst: 1
0x00c00016 Free: 5 Inst: 1
0x00c00017 Free: 5 Inst: 1
0x00c00018 Free: 5 Inst: 1
0x00c00019 Free: 5 Inst: 1
0x00c0001a Free: 5 Inst: 1
0x00c0001b Free: 5 Inst: 1
0x00c0001c Free: 5 Inst: 1 --最后一个一级位图块的地址
--------------------------------------------------------
End dump data blocks tsn: 3 file#: 3 minblk 29 maxblk 29
SECOND LEVEL BITMAP BLOCK
============================================================
Start dump data blocks tsn: 3 file#: 3 minblk 29 maxblk 29
buffer tsn: 3 rdba: 0x00c0001d (3/29)
scn: 0x0000.00067e48 seq: 0x03 flg: 0x00 tail: 0x7e482103
frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK
Dump of Second Level Bitmap Block
number: 20 nfree: 18 ffree: 2 pdba: 0x00c0001e
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x00c00009 Free: 1 Inst: 1 --第一个一级位图块的地址
0x00c0000a Free: 1 Inst: 1
0x00c0000b Free: 5 Inst: 1
0x00c0000c Free: 5 Inst: 1
0x00c0000d Free: 5 Inst: 1
0x00c0000e Free: 5 Inst: 1
0x00c0000f Free: 5 Inst: 1
0x00c00010 Free: 5 Inst: 1
0x00c00011 Free: 5 Inst: 1
0x00c00012 Free: 5 Inst: 1
0x00c00013 Free: 5 Inst: 1
0x00c00014 Free: 5 Inst: 1
0x00c00015 Free: 5 Inst: 1
0x00c00016 Free: 5 Inst: 1
0x00c00017 Free: 5 Inst: 1
0x00c00018 Free: 5 Inst: 1
0x00c00019 Free: 5 Inst: 1
0x00c0001a Free: 5 Inst: 1
0x00c0001b Free: 5 Inst: 1
0x00c0001c Free: 5 Inst: 1 --最后一个一级位图块的地址
--------------------------------------------------------
End dump data blocks tsn: 3 file#: 3 minblk 29 maxblk 29
可见,二级位图块里包含了指向一级位图块的地址。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-681154/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-681154/