ANBOB™

看老白日记中有一个案例提到有一个select from where rownum<10的查询要很长时间，后来做了block dump断定是碎片，但是dump出来的内容是如何判断都是空块的呢？

附上一断trace
data_block_dump,data header at 0x6000000000208e64
===============
tsiz: 0x1f98
hsiz: 0x4c
pbl: 0x6000000000208e64
bdba: 0x24816ae4
76543210
flag=——–
ntab=1
nrow=29
frre=0
fsbo=0x4c
fseo=0xf7
avsp=0x1f4c
tosp=0x1f4c
0xe:pti[0] nrow=29 offs=0
0x12:pri[0] sfll=1
0x14:pri[1] sfll=2
0x16:pri[2] sfll=3
0x18:pri[3] sfll=4
0x1a:pri[4] sfll=5
0x1c:pri[5] sfll=6
0x1e:pri[6] sfll=7
0x20:pri[7] sfll=8
0x22:pri[8] sfll=9
0x24:pri[9] sfll=10
0x26:pri[10] sfll=11
0x28:pri[11] sfll=12
0x2a:pri[12] sfll=13
0x2c:pri[13] sfll=14
0x2e:pri[14] sfll=15
0x30:pri[15] sfll=16
0x32:pri[16] sfll=17
0x34:pri[17] sfll=18
0x36:pri[18] sfll=19
0x38:pri[19] sfll=20
0x3a:pri[20] sfll=21
0x3c:pri[21] sfll=22
0x3e:pri[22] sfll=23
0x40:pri[23] sfll=24
0x42:pri[24] sfll=25
0x44:pri[25] sfll=26
0x46:pri[26] sfll=27
0x48:pri[27] sfll=28
0x4a:pri[28] sfll=-1
block_row_dump:
end_of_block_dump
里面全部是空块。建议客户做一个ALTER TABLE

MOVE;表重组后，发现原来12G的表只剩下800M了。

附转载的一篇文章网友转发不明来历，所以抱歉不能声明出处

Sometimes we find that the number of rows in row dictionary (nrow) doesn’t match the number of row entries in block.

For example, like this data block dump file:

data_block_dump,data header at 0×10384a05c
===============
tsiz: 0×1fa0
hsiz: 0×2a
pbl: 0×10384a05c
bdba: 0×0900058a
76543210
flag=——–
ntab=1
nrow=12
frre=0
fsbo=0×2a
fseo=0×1f4d
avsp=0×1f37
tosp=0×1f37
0xe:pti[0] nrow=12 offs=0
0×12:pri[0] sfll=1
0×14:pri[1] sfll=2
0×16:pri[2] sfll=3
0×18:pri[3] sfll=4
0×1a:pri[4] sfll=-1
0×1c:pri[5] offs=0×1f7d
0×1e:pri[6] offs=0×1f84
0×20:pri[7] offs=0×1f8b
0×22:pri[8] offs=0×1f92
0×24:pri[9] offs=0×1f99
0×26:pri[10] offs=0×1f53
0×28:pri[11] offs=0×1f4d
block_row_dump:
tab 0, row 5, @0×1f7d
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 2c 41
tab 0, row 6, @0×1f84
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 13 0e
tab 0, row 7, @0×1f8b
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 39 62
tab 0, row 8, @0×1f92
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 41 54
tab 0, row 9, @0×1f99
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 41 55
tab 0, row 10, @0×1f53
tl: 6 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 2] c1 02
tab 0, row 11, @0×1f4d
tl: 6 fb: –H-FL– lb: 0×2 cc: 1
col 0: [ 2] c1 02
end_of_block_dump
End dump data blocks tsn: 31 file#: 36 minblk 1418 maxblk 1418

You can only find row5~row11. Where are row0~row4?

The answer is they have been purged. The sfll flag in row dictionary indicates that.

Everybody knows block cleanout which is oracle’s feature to speed up the commit process.

Before block cleanout, the block is like the following. The deleted rows still have the offset flag in row dictionary, only the -D- flag in row entry indicates that the rows have already been deleted.

data_block_dump,data header at 0×10384a05c
===============
tsiz: 0×1fa0
hsiz: 0×28
pbl: 0×10384a05c
bdba: 0×0900058a
76543210
flag=——–
ntab=1
nrow=11
frre=-1
fsbo=0×28
fseo=0×1f53
avsp=0×1f15
tosp=0×1f42
0xe:pti[0] nrow=11 offs=0
0×12:pri[0] offs=0×1f59
0×14:pri[1] offs=0×1f60
0×16:pri[2] offs=0×1f68
0×18:pri[3] offs=0×1f70
0×1a:pri[4] offs=0×1f76
0×1c:pri[5] offs=0×1f7d
0×1e:pri[6] offs=0×1f84
0×20:pri[7] offs=0×1f8b
0×22:pri[8] offs=0×1f92
0×24:pri[9] offs=0×1f99
0×26:pri[10] offs=0×1f53
block_row_dump:
tab 0, row 0, @0×1f59
tl: 2 fb: –HDFL– lb: 0×2
tab 0, row 1, @0×1f60
tl: 2 fb: –HDFL– lb: 0×2
tab 0, row 2, @0×1f68
tl: 2 fb: –HDFL– lb: 0×2
tab 0, row 3, @0×1f70
tl: 2 fb: –HDFL– lb: 0×2
tab 0, row 4, @0×1f76
tl: 2 fb: –HDFL– lb: 0×2
tab 0, row 5, @0×1f7d
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 2c 41
tab 0, row 6, @0×1f84
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 13 0e
tab 0, row 7, @0×1f8b
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 39 62
tab 0, row 8, @0×1f92
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 41 54
tab 0, row 9, @0×1f99
tl: 7 fb: –H-FL– lb: 0×0 cc: 1
col 0: [ 3] c2 41 55
tab 0, row 10, @0×1f53
tl: 6 fb: –H-FL– lb: 0×1 cc: 1
col 0: [ 2] c1 02
end_of_block_dump

So duing block clean out, the deleted rows have been purged and all the offset flags have been replaced with sfll flag.

And you may also notice that the avsp(avaliable space) was also updated during block cleanout.

打赏

微信扫一扫，打赏作者吧～