• 博客访问: 4971237
  • 博文数量: 710
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-07 15:08
  • 认证徽章:
个人简介

了解并联系warehouse: http://blog.itpub.net/19602/viewspace-1059211/

文章分类

全部博文(710)

文章存档

2018年(7)

2017年(7)

2016年(20)

2015年(20)

2014年(45)

2013年(68)

2012年(69)

2011年(87)

2010年(68)

2009年(103)

2008年(140)

2007年(65)

2006年(7)

2005年(3)

2004年(1)

分类: IT职场

2009-02-03 09:34:55

困扰自己很久的问题终于解决了,以前有个误区,以为这个值是指hwm以下blocks的avg space!

[@more@]

其实没啥用途,只是以前一直是我心中的疑问,突然解惑,总结一下,分享!
--=================================
有关这个字段先看看doc上的解释:
AVG_SPACE* NUMBER Average amount of free space, in bytes, [b][color=Red]in a data block allocated to the table [/color][/b]
--=================================
另外我心中有一个疑问:
一个block是被使用过还是从未被使用过oracle是根据block上的啥标记来判断的?itl?该疑问与本贴内容暂时无关!
SQL> select *from t;

ID NAME
---------- ----------
1 abc
2 bcd
3 cde
123 test
321 testabc

SQL> analyze table t compute statistics;

表已分析。
SQL> select blocks,empty_blocks,avg_space from dba_tables where table_name='T' a
nd owner='SYS';

BLOCKS EMPTY_BLOCKS AVG_SPACE
---------- ------------ ----------
5 3 [b][color=Red]8060[/color][/b] --这里的这个值是如何被计算出来的,我一度都很困惑!
SQL> select header_file,header_block,blocks from dba_segments where segment_name
='T' and owner='SYS';

HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
4 67 8 --段头block是67,说明从68开始可以存放用户数据,这个段一共有8个block
SQL> select file_id,block_id,blocks from dba_extents where segment_name='T' and
owner='SYS';

FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 65 8
SQL> alter system dump datafile 4 block min 65 block max 72;

系统已更改。

SQL> select id,name,rowid from t;

ID NAME ROWID
---------- ---------- ------------------
1 abc AAADJNAAEAAAABFAAA
2 bcd AAADJNAAEAAAABFAAB
3 cde AAADJNAAEAAAABFAAC
123 test AAADJNAAEAAAABFAAD
321 testabc AAADJNAAEAAAABFAAE

SQL> select dbms_rowid.rowid_block_number(rowid) from t; --数据使用的block是69

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
69
69
69
69
69
--dump这8个block之后我们发现从68~72这5个block中都有avsp这个值,其中69#block的avsp=0x1f43,其它几个block是
avsp=0x1f8a
转化成10进制数之后计算一下这5个block的avg space就是dba_tables中字段avg_space的值
SQL> select to_number('1f8a','xxxx') from dual;

TO_NUMBER('1F8A','XXXX')
------------------------
8074

SQL> select to_number('1f43','xxxx') from dual;

TO_NUMBER('1F43','XXXX')
------------------------
8003

SQL> select (8074*4+8003)/5 from dual;

(8074*4+8003)/5
---------------
8059.8

SQL> select round((8074*4+8003)/5,0) from dual;

ROUND((8074*4+8003)/5,0)
------------------------
8060

SQL>
dump的信息如下,供参考!
--=============================
Start dump data blocks tsn: 4 file#: 4 minblk 65 maxblk 72
buffer tsn: 4 rdba: 0x01000041 (4/65)
scn: 0x0000.004b555a seq: 0x02 flg: 0x04 tail: 0x555a2002
frmt: 0x02 chkval: 0xf46c type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A220 01000041 004B555A 04020000 [ ...A...ZUK.....]
7422210 0000F46C 00000000 00000000 00000000 [l...............]
7422220 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7422240 00000000 00000000 00000000 00000004 [................]
7422250 FFFFFFFF 00000000 00000003 00000008 [................]
7422260 00010001 00000000 00000000 00000000 [................]
7422270 00000005 00000003 49870B50 49870B50 [........P..IP..I]
7422280 00000000 00000000 00000000 00000000 [................]
7422290 01000042 00000000 00000000 00000008 [B...............]
74222A0 00000008 01000049 00000000 00000000 [....I...........]
74222B0 00000000 00000005 00000000 00000001 [................]
74222C0 0000324D 00000000 00000000 01000041 [M2..........A...]
74222D0 00000008 00000000 00000000 00000000 [................]
74222E0 00000000 00000000 00000000 00000000 [................]
Repeat 9 times
7422380 00000000 00000000 00000000 55551511 [..............UU]
7422390 00000000 00000000 00000000 00000000 [................]
Repeat 485 times
74241F0 00000000 00000000 00000000 555A2002 [............. ZU]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x01000042 poffset: 0
unformatted: 0 total: 8 first useful block: 3
owning instance : 1
instance ownership changed at 02/02/2009 23:03:44
Last successful Search 02/02/2009 23:03:44
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 5

Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 12877
HWM Flag: HWM Set
Highwater:: 0x01000049 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01000041 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
--------------------------------------------------------
buffer tsn: 4 rdba: 0x01000042 (4/66)
scn: 0x0000.004b5548 seq: 0x02 flg: 0x04 tail: 0x55482102
frmt: 0x02 chkval: 0xb462 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A221 01000042 004B5548 04020000 [!...B...HUK.....]
7422210 0000B462 00000000 00000000 00000000 [b...............]
7422220 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7422240 00000000 00000000 00000000 01000043 [............C...]
7422250 00000001 00000001 00000000 00000000 [................]
7422260 00000000 00000000 0000324D 00000001 [........M2......]
7422270 00000000 01000041 00010005 00000000 [....A...........]
7422280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
74241F0 00000000 00000000 00000000 55482102 [.............!HU]
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x01000043
Inc #: 0 Objd: 12877
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x01000041 Free: 5 Inst: 1

--------------------------------------------------------
buffer tsn: 4 rdba: 0x01000043 (4/67)
scn: 0x0000.004b555a seq: 0x02 flg: 0x04 tail: 0x555a2302
frmt: 0x02 chkval: 0x9989 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A223 01000043 004B555A 04020000 [#...C...ZUK.....]
7422210 00009989 00000000 00000000 00000000 [................]
7422220 00000000 00000001 00000008 00000A9C [................]
7422230 00000000 00000008 00000008 01000049 [............I...]
7422240 00000000 00000000 00000000 00000005 [................]
7422250 00000000 00000000 00000000 00000000 [................]
7422260 00000008 00000008 01000049 00000000 [........I.......]
7422270 00000000 00000000 00000005 01000041 [............A...]
7422280 01000041 00000000 00000000 00000000 [A...............]
7422290 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
74222D0 00000001 00002000 00000000 00001434 [..... ......4...]
74222E0 00000000 01000042 00000001 01000041 [....B.......A...]
74222F0 01000042 00000000 00000000 00000000 [B...............]
7422300 00000000 00000000 00000001 00000000 [................]
7422310 0000324D 10000000 01000041 00000008 [M2......A.......]
7422320 00000000 00000000 00000000 00000000 [................]
Repeat 152 times
7422CB0 01000041 01000044 00000000 00000000 [A...D...........]
7422CC0 00000000 00000000 00000000 00000000 [................]
Repeat 151 times
7423640 00000000 00000000 01000042 00000000 [........B.......]
7423650 00000000 00000000 00000000 00000000 [................]
Repeat 185 times
74241F0 00000000 00000000 00000000 555A2302 [.............#ZU]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01000049 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:: 0x01000049 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: 0x01000041
Level 1 BMB for Low HWM block: 0x01000041
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000042
Last Level 1 BMB: 0x01000041
Last Level II BMB: 0x01000042
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 12877 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000041 length: 8

Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000041 Data dba: 0x01000044
--------------------------------------------------------

Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000042

buffer tsn: 4 rdba: 0x01000044 (4/68)
scn: 0x0000.004b555a seq: 0x02 flg: 0x04 tail: 0x555a0602
frmt: 0x02 chkval: 0x274c type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A206 01000044 004B555A 04020000 [....D...ZUK.....]
7422210 0000274C 00000001 0000324D 004B555A [L'......M2..ZUK.]
7422220 00000000 00320002 01000041 00000000 [......2.A.......]
7422230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
7422260 00000000 00000000 000EFFFF 1F8A1F98 [................]
7422270 00001F8A 00000000 00000000 00000000 [................]
7422280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
74241F0 00000000 00000000 00000000 555A0602 [..............ZU]
Block header dump: 0x01000044
Object id on Block? Y
seg/obj: 0x324d csc: 0x00.4b555a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000041 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 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x7422264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x07422264
bdba: 0x01000044
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
[b][color=Blue]avsp=0x1f8a[/color][/b]
tosp=0x1f8a
block_row_dump:
end_of_block_dump
buffer tsn: 4 rdba: 0x01000045 (4/69)
scn: 0x0000.004b5567 seq: 0x01 flg: 0x06 tail: 0x55670601
frmt: 0x02 chkval: 0xab1f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A206 01000045 004B5567 06010000 [....E...gUK.....]
7422210 0000AB1F 00000001 0000324D 004B555A [........M2..ZUK.]
7422220 00000000 00320002 01000041 00110007 [......2.A.......]
7422230 00000233 008009F3 004600F7 00002005 [3.........F.. ..]
7422240 004B5567 00000000 00000000 00000000 [gUK.............]
7422250 00000000 00000000 00000000 00000000 [................]
7422260 00000000 00050100 001CFFFF 1F431F5F [............_.C.]
7422270 00001F43 1F8E0005 1F7A1F84 1F5F1F6E [C.........z.n._.]
7422280 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
74241C0 2C000000 C2030201 74071604 61747365 [...,.......testa]
74241D0 012C6362 02C20302 65740418 012C7473 [bc,.......test,.]
74241E0 04C10202 65646303 0202012C 620303C1 [.....cde,......b]
74241F0 012C6463 02C10202 63626103 55670601 [cd,......abc..gU]
Block header dump: 0x01000045
Object id on Block? Y
seg/obj: 0x324d csc: 0x00.4b555a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000041 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.011.00000233 0x008009f3.00f7.46 --U- 5 fsc 0x0000.004b5567
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x7422264
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x07422264
bdba: 0x01000045
76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f5f
[b][color=Blue]avsp=0x1f43[/color][/b]
tosp=0x1f43
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0x1f8e
0x14:pri[1] offs=0x1f84
0x16:pri[2] offs=0x1f7a
0x18:pri[3] offs=0x1f6e
0x1a:pri[4] offs=0x1f5f
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 61 62 63
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 62 63 64
tab 0, row 2, @0x1f7a
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 63 64 65
tab 0, row 3, @0x1f6e
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 02 18
col 1: [ 4] 74 65 73 74
tab 0, row 4, @0x1f5f
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 04 16
col 1: [ 7] 74 65 73 74 61 62 63
end_of_block_dump
buffer tsn: 4 rdba: 0x01000046 (4/70)
scn: 0x0000.004b555a seq: 0x02 flg: 0x04 tail: 0x555a0602
frmt: 0x02 chkval: 0x274e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A206 01000046 004B555A 04020000 [....F...ZUK.....]
7422210 0000274E 00000001 0000324D 004B555A [N'......M2..ZUK.]
7422220 00000000 00320002 01000041 00000000 [......2.A.......]
7422230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
7422260 00000000 00000000 000EFFFF 1F8A1F98 [................]
7422270 00001F8A 00000000 00000000 00000000 [................]
7422280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
74241F0 00000000 00000000 00000000 555A0602 [..............ZU]
Block header dump: 0x01000046
Object id on Block? Y
seg/obj: 0x324d csc: 0x00.4b555a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000041 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 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x7422264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x07422264
bdba: 0x01000046
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
[b][color=Blue]avsp=0x1f8a[/color][/b]
tosp=0x1f8a
block_row_dump:
end_of_block_dump
buffer tsn: 4 rdba: 0x01000047 (4/71)
scn: 0x0000.004b555a seq: 0x02 flg: 0x04 tail: 0x555a0602
frmt: 0x02 chkval: 0x274f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A206 01000047 004B555A 04020000 [....G...ZUK.....]
7422210 0000274F 00000001 0000324D 004B555A [O'......M2..ZUK.]
7422220 00000000 00320002 01000041 00000000 [......2.A.......]
7422230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
7422260 00000000 00000000 000EFFFF 1F8A1F98 [................]
7422270 00001F8A 00000000 00000000 00000000 [................]
7422280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
74241F0 00000000 00000000 00000000 555A0602 [..............ZU]
Block header dump: 0x01000047
Object id on Block? Y
seg/obj: 0x324d csc: 0x00.4b555a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000041 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 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x7422264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x07422264
bdba: 0x01000047
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
[b][color=Blue]avsp=0x1f8a[/color][/b]
tosp=0x1f8a
block_row_dump:
end_of_block_dump
buffer tsn: 4 rdba: 0x01000048 (4/72)
scn: 0x0000.004b555a seq: 0x02 flg: 0x04 tail: 0x555a0602
frmt: 0x02 chkval: 0x2740 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07422200 to 0x07424200
7422200 0000A206 01000048 004B555A 04020000 [....H...ZUK.....]
7422210 00002740 00000001 0000324D 004B555A [@'......M2..ZUK.]
7422220 00000000 00320002 01000041 00000000 [......2.A.......]
7422230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
7422260 00000000 00000000 000EFFFF 1F8A1F98 [................]
7422270 00001F8A 00000000 00000000 00000000 [................]
7422280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
74241F0 00000000 00000000 00000000 555A0602 [..............ZU]
Block header dump: 0x01000048
Object id on Block? Y
seg/obj: 0x324d csc: 0x00.4b555a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000041 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 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x7422264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x07422264
bdba: 0x01000048
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
[b][color=Blue]avsp=0x1f8a[/color][/b]
tosp=0x1f8a
block_row_dump:
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 65 maxblk 72
--========================================

阅读(151071) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册