倾力大奉献——ASSM内部存储研究大揭密

foxmile · 发表于 2007-12-13 15:28

陈总的帖子，我一定见一次顶一次。

jinshen1979 · 发表于 2007-12-13 20:54

看完后才觉得我的学习还很漫长顶一下

foxmile · 发表于 2007-12-17 11:35

陈总的钻研精神我一直佩服。

xiaodong_1567 · 发表于 2008-3-1 14:01

看了《循序渐进Oracle数据库管理、优化与备份恢复》
再看本文
最后试验
ASSM基本清楚

chen_7733 · 发表于 2008-3-2 09:30

几大高手都出现了,顶顶吧

lottery009 · 发表于 2008-3-5 17:27

陈总真是太牛了

daimin1983 · 发表于 2008-3-30 16:55

对 grassbell ：
对于segment header的第一个（或多个，如本例）block，记录了后面多少个block的使用情况，
应该取决于第一个extent的大小。后面同理。。。

此例中，表assmtab只有2个extents，每个包括128个blocks
可以看到block 9和10共同来记录后面128个blocks地使用情况，每个分别记录64个。
对PINER：
你的这个结果是很有代表性的，因为开始extent中block太多
一个1级块管理不过来，所以出现了2个1级块。

然后才是1个2级块，最后是段头
所以可以看到4个Metadata的块

请教一个问题：
是不是说如果初始分配的的extents越大，那么就会在 1级块的个数就越多，一般初始分配的的extents是由什么参数来决定的呢？1级块的个数越多会不会对性能有影响？

daimin1983 · 发表于 2008-3-30 17:04

我做了这样一个测试：
create tablespace demo1
datafile  '/opt/ora9/oradata/ora9i/demo05.dbf'
size  2000M
EXTENT MANAGEMENT LOCAL -- Turn on LMT
SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;

create table assmtab1(x int) tablespace demo1
storage(initial 1024M);

begin
  show_space('ASSMTAB1','AUTO');
  end;

Total Blocks............................131200
Total Bytes.............................1074790400
Unused Blocks...........................131182
Unused Bytes............................1074642944
Last Used Ext FileId....................13
Last Used Ext BlockId...................8
Last Used Block.........................18
on FREELIST,17used by table

然后查询
SQL> select extent_id, file_id, block_id, bytes
  2       from dba_extents
  3    where segment_name = 'ASSMTAB1';

EXTENT_ID FILE_ID BLOCK_ID    BYTES
---------- ---------- ---------- ----------
      0       13       9 8388608
      1       13    1033 1048576
      2       13    1161 1048576
      3       13    1289 1048576
      4       13    1417 1048576
      5       13    1545 1048576
      6       13    1673 1048576
      7       13    1801 1048576
      8       13    1929 1048576
      9       13    2057 1048576
      10       13    2185 1048576
      11       13    2313 1048576
      12       13    2441 1048576
      13       13    2569 1048576
      14       13    2697 1048576
      15       13    2825 1048576
      16       13    2953 1048576
      17       13    3081 1048576
      18       13    3209 1048576
      19       13    3337 1048576

EXTENT_ID FILE_ID BLOCK_ID    BYTES
---------- ---------- ---------- ----------
      20       13    3465 1048576
      21       13    3593 1048576
      22       13    3721 1048576

发现
EXTENT_ID FILE_ID BLOCK_ID    BYTES
---------- ---------- ---------- ----------
      0       13       9 8388608
这个数据块上的bytes很大，这个是初始的extents，然后
alter system dump datafile 13 block 9;

出来的信息是：
Start dump data blocks tsn: 15 file#: 13 minblk 9 maxblk 9
buffer tsn: 15 rdba: 0x03400009 (13/9)
scn: 0x0000.001935e7 seq: 0x02 flg: 0x04 tail: 0x35e72002
frmt: 0x02 chkval: 0x2213 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1       parent dba:  0x03400019 poffset: 0
unformatted: 46    total: 64       first useful block: 18
owning instance : 1
instance ownership changed at
Last successful Search
Freeness Status:  nf1 0    nf2 0    nf3 0    nf4 0

Extent Map Block Offset: 4294967295
First free datablock : 18
Bitmap block lock opcode 0
Locker xid:    :  0x0000.000.00000000
   Highwater::  0x0340001b  ext#: 0    blk#: 18    ext size: 1024
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 0
  HWM Flag: HWM Set
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
0x03400009  Length: 64    Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:Metadata 5:Metadata 6:Metadata 7:Metadata
8:Metadata 9:Metadata 10:Metadata 11:Metadata
12:Metadata 13:Metadata 14:Metadata 15:Metadata
16:Metadata 17:Metadata 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
  --------------------------------------------------------
End dump data blocks tsn: 15 file#: 13 minblk 9 maxblk 9
这里的Metadata有好多，
请问这个时候1级块是多少个？是怎么看的？你们讲的这个我可能好象还没有完全理解？
谢谢

jboracle1981 · 发表于 2008-11-30 16:11

对 grassbell ：
对于segment header的第一个（或多个，如本例）block，记录了后面多少个block的使用情况，
应该取决于第一个extent的大小。后面同理。。。

有理......

jboracle1981 · 发表于 2008-11-30 22:25

测试环境Oracle Database 10g Enterprise Edition Release 10.1.0.2.0  windows下
表空间extent mangement local unifrom size 128k
*** 2008-11-30 22:09:42.500
Start dump data blocks tsn: 23 file#: 12 minblk 9 maxblk 9
buffer tsn: 23 rdba: 0x03000009 (12/9)
scn: 0x0000.0a2860c2 seq: 0x02 flg: 0x04 tail: 0x60c22002
frmt: 0x02 chkval: 0x6f31 type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1       parent dba:  0x0300000a poffset: 0
unformatted: 0    total: 16       first useful block: 3
owning instance : 1
instance ownership changed at 11/30/2008 19:02:33
Last successful Search 11/30/2008 19:02:33
Freeness Status:  nf1 0    nf2 0    nf3 0    nf4 0

Extent Map Block Offset: 4294967295
First free datablock : 16
Bitmap block lock opcode 0
Locker xid:    :  0x0000.000.00000000
Inc #: 0 Objd: 57380
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
0x03000009  Length: 16    Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
  --------------------------------------------------------
End dump data blocks tsn: 23 file#: 12 minblk 9 maxblk 9
Start dump data blocks tsn: 23 file#: 12 minblk 10 maxblk 10
buffer tsn: 23 rdba: 0x0300000a (12/10)
scn: 0x0000.0a286546 seq: 0x03 flg: 0x04 tail: 0x65462103
frmt: 0x02 chkval: 0x6d27 type: 0x21=SECOND LEVEL BITMAP BLOCK
Dump of Second Level Bitmap Block
number: 8    nfree: 1    ffree: 7    pdba:    0x0300000b
Inc #: 0 Objd: 57380
  opcode:0
xid:
  L1 Ranges :
  --------------------------------------------------------
0x03000009  Free: 1 Inst: 1
0x03000019  Free: 1 Inst: 1
0x03000029  Free: 1 Inst: 1
0x03000039  Free: 1 Inst: 1
0x03000049  Free: 1 Inst: 1
0x03000059  Free: 1 Inst: 1
0x03000069  Free: 1 Inst: 1
0x03000079  Free: 5 Inst: 1

  --------------------------------------------------------
End dump data blocks tsn: 23 file#: 12 minblk 10 maxblk 10
Start dump data blocks tsn: 23 file#: 12 minblk 11 maxblk 11
buffer tsn: 23 rdba: 0x0300000b (12/11)
scn: 0x0000.0a286549 seq: 0x03 flg: 0x04 tail: 0x65492303
frmt: 0x02 chkval: 0x4100 type: 0x23=PAGETABLE SEGMENT HEADER
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0    spare2: 0    #extents: 9    #blocks: 144
               last map  0x00000000  #maps: 0    offset: 2716
   Highwater::  0x03000099  ext#: 8    blk#: 16    ext size: 16
  #blocks in seg. hdr's freelists: 0
  #blocks below: 134
  mapblk  0x00000000  offset: 8
               Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
   Highwater::  0x03000099  ext#: 8    blk#: 16    ext size: 16
  #blocks in seg. hdr's freelists: 0
  #blocks below: 134
  mapblk  0x00000000  offset: 8
  Level 1 BMB for High HWM block: 0x03000079
  Level 1 BMB for Low HWM block: 0x03000079
  --------------------------------------------------------
  Segment Type: 1 nl2: 1    blksz: 8192 fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x0300000a
  Last Level 1 BMB:  0x03000079
  Last Level II BMB:  0x0300000a
  Last Level III BMB:  0x00000000
   Map Header:: next  0x00000000  #extents: 9 obj#: 57380  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
0x03000009  length: 16
0x03000019  length: 16
0x03000029  length: 16
0x03000039  length: 16
0x03000049  length: 16
0x03000059  length: 16
0x03000069  length: 16
0x03000079  length: 16
0x03000089  length: 16

  Auxillary Map
  --------------------------------------------------------
Extent 0    :  L1 dba:  0x03000009 Data dba:  0x0300000c
Extent 1    :  L1 dba:  0x03000019 Data dba:  0x0300001a
Extent 2    :  L1 dba:  0x03000029 Data dba:  0x0300002a
Extent 3    :  L1 dba:  0x03000039 Data dba:  0x0300003a
Extent 4    :  L1 dba:  0x03000049 Data dba:  0x0300004a
Extent 5    :  L1 dba:  0x03000059 Data dba:  0x0300005a
Extent 6    :  L1 dba:  0x03000069 Data dba:  0x0300006a
Extent 7    :  L1 dba:  0x03000079 Data dba:  0x0300007a
Extent 8    :  L1 dba:  0x03000079 Data dba:  0x03000089
  --------------------------------------------------------

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

End dump data blocks tsn: 23 file#: 12 minblk 11 maxblk 11
*** 2008-11-30 22:15:06.687
Start dump data blocks tsn: 23 file#: 12 minblk 121 maxblk 121
buffer tsn: 23 rdba: 0x03000079 (12/121)
scn: 0x0000.0a286557 seq: 0x01 flg: 0x04 tail: 0x65572001
frmt: 0x02 chkval: 0x3b9e type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 2       parent dba:  0x0300000a poffset: 7
unformatted: 0    total: 32       first useful block: 1
owning instance : 1
instance ownership changed at 11/30/2008 22:07:19
Last successful Search 11/30/2008 22:07:19
Freeness Status:  nf1 0    nf2 0    nf3 1    nf4 10

Extent Map Block Offset: 4294967295
First free datablock : 17
Bitmap block lock opcode 0
Locker xid:    :  0x0000.000.00000000
Inc #: 0 Objd: 57380
  HWM Flag: HWM Set
   Highwater::  0x03000099  ext#: 8    blk#: 16    ext size: 16
  #blocks in seg. hdr's freelists: 0
  #blocks below: 134
  mapblk  0x00000000  offset: 8
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
0x03000079  Length: 16    Offset: 0
0x03000089  Length: 16    Offset: 16

0:Metadata 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
16:FULL 17:75-100% free 18:FULL 19:FULL
20:FULL 21:FULL 22:50-75% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
28:75-100% free 29:75-100% free 30:75-100% free 31:75-100% free
  --------------------------------------------------------
End dump data blocks tsn: 23 file#: 12 minblk 121 maxblk 121

一共9个extent ,length为16
0x03000079  Free: 5 Inst: 1
最后一个L1管理了32个块,前面的都是16个块.

[精华] 倾力大奉献——ASSM内部存储研究大揭密