avg_space of dba_tables是如何被計算出來的！
困擾自己很久的問題終於解決了,以前有個誤區，以為這個值是指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
--========================================
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1016605/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Janky frames 是如何計算出來的
- dba_tables中的avg_row_len是如何被計算的？
- 欄位avg_row_len of dba_tables是如何被計算的！
- Linux 中 CPU 利用率是如何算出來的？Linux
- One Order行專案裡Item Category是怎麼計算出來的Go
- mysql expire_logs_days是怎麼生效和計算出來的MySql
- 你來寫，“Calca”來快速算出計算結果
- 剖析雲端計算出來後的“共享型資料庫”資料庫
- 如何在 1 到 2000 中計算出子集和能被 5 整除的子集有多少個？
- WinMain是如何被呼叫的AI
- Java Main 如何是如何被執行的？JavaAI
- 密碼是如何被竊取的密碼
- Java Spring Beans.xml裡的Bean定義是如何被解析出來的JavaSpringBeanXML
- setContentView是如何一步一步被顯示出來的？View
- 你是如何離程式設計越來越遠的？程式設計
- 交易是如何被建立和打包的4
- Windows Phone是如何被微軟ZUO死的？Windows微軟
- 開源專案是如何被搞砸的？
- 為什麼milvus 計算出來的歐式距離和實際不符合？
- 用程式計算出鋼琴88個鍵的音高
- 讀《程式是如何跑起來的》
- 我是一個請求，我是如何被髮送的？
- 爬蟲是如何被網站識別的？爬蟲網站
- Go timer 是如何被排程的？Go
- PHP計算出記憶體最高佔用.PHP記憶體
- 網站主頁被篡改了,該如何改回來_主頁被篡改改不回來網站
- 計算出用字串表示的數學表示式的值 (轉)字串
- 一句話計算出 TopN 的增長情況
- 計算出你和另一個人的關係(超準)
- 如何根據網站訪問量計算出所需要的頻寬或流量大小網站
- 記介面當中使用 session 是如何被排斥的Session
- 股票收益率的協方差矩陣算出來有什麼用矩陣
- RPG遊戲的地圖和場景是如何設計出來的？遊戲地圖
- 如何防止在網路上被“人肉”出來？
- 計算的未來是什麼？
- 準的邪門!計算出你和另一個人的關係
- milvus 使用 l2 歐式距離計算向量的距離，計算出來的距離的最大值是多少？
- Activity生命週期回撥是如何被回撥的？