Oracle ASSM管理

文中dump信息基于11.2.0.4版本。
在ASSM被oracle使用之前,也就是MSSM模式下,Oracle的高水位High Water Mark(HWM)代表所有的相关数据块,高水位以下的数据块是必须被格式化的,高水位以上是未被格式化的数据块,在INSERT数据时不能直接被使用。
在ASSM中引入了LOW HWM和High HWM。在Low HWM下的块是要被格式化的,High HWM以上的块是没有被格式化的,Low和High之间是被格式化也可以不被格式化的。

ASSM具有几个特点:
1.ASSM结构类似于B树索引的结构;
2.Tree traversing用于获得数据块的使用信息;
3.ASSM树是三层 L3 L2 L1
4.查找空间块的时候速度比较快,但是比MSSM浪费了一些空间

实验过程

create tablespace assm_blue datafile '+DGIDT1GC' size 1m extent management local uniform size 64k segment space management auto;

create table assm.assm_blue(id number) tablespace assm_blue;

SQL> insert into assm.assm_blue values (1);

1 row created.

SQL> select * From assm.assm_blue;

        ID
----------
         1

SQL> insert into assm.assm_blue select 1 from dba_objects where rownum <= 2000; 
2000 rows created. 
SQL> select file_id,block_id,blocks from dba_extents where segment_name = 'ASSM_BLUE';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
        31          8          8

SQL> select segment_name,header_file,header_block,blocks,extents from DBA_segments where segment_name = 'ASSM_BLUE';

SEGMENT_NAME    HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
--------------- ----------- ------------ ---------- ----------
ASSM_BLUE                31           10          8          1

SQL> alter system dump datafile 31 block min 8 block max 16;

get trace filename --- ora_2847.trc

LInux下执行
grep -E ‘First Level |Last Level’ ora_2847.trc
Dump of First Level Bitmap Block
First Level 3 BMB: 0x00000000
Last Level 1 BMB: 0x07c00008
Last Level II BMB: 0x07c00009
Last Level III BMB: 0x00000000

dump文件中的信息

Start dump data blocks tsn: 27 file#:31 minblk 8 maxblk 16
Block dump from cache:
Dump of buffer cache at level 4 for tsn=27 rdba=130023432
BH (0x181d93918) file#: 31 rdba: 0x07c00008 (31/8) class: 8 ba: 0x1805d2000
  set: 80 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 3 obj: 129384 objn: 129384 tsn: 27 afn: 31 hint: f
  hash: [0x2dfa7c118,0x2dfa7c118] lru: [0x1ede23be8,0x1ebebdea8]
  ckptq: [NULL] fileq: [NULL] objq: [0x2a88d5a78,0x2a88d5a78] objaq: [0x2a88d5a68,0x2a88d5a68]
  st: XCURRENT md: NULL fpin: 'ktspfwh6: ktspffbmb' tch: 1 le: 0x1b3f16508
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 27 rdba: 0x07c00008 (31/8)  --RDBA
scn: 0x000b.4bb6a8a3 seq: 0x01 flg: 0x04 tail: 0xa8a32001
frmt: 0x02 chkval: 0xd654 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of First Level Bitmap Block
 --------------------------------
   nbits : 4 nranges: 1         parent dba:  0x07c00009   poffset: 0     
   unformatted: 0       total: 8         first useful block: 3      
   owning instance : 1
   instance ownership changed at 08/08/2016 14:07:16
   Last successful Search 08/08/2016 14:07:16
   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
   Dealloc scn: 1199498637.11 
   Flag: 0x00000001 (-/-/-/-/-/HWM)
   Inc #: 0 Objd: 129384 
  HWM Flag: HWM Set
      Highwater::  0x07c00010  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
  --------------------------------------------------------
  DBA Ranges :
  --------------------------------------------------------
   0x07c00008  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
  --------------------------------------------------------

0x07c00008对应FIRST LEVEL BITMAP BLOCK

Block dump from cache:
Dump of buffer cache at level 4 for tsn=27 rdba=130023433
BH (0x1f1e2d558) file#: 31 rdba: 0x07c00009 (31/9) class: 9 ba: 0x1f11a6000
  set: 79 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 2 obj: 129384 objn: 129384 tsn: 27 afn: 31 hint: f
  hash: [0x2df948018,0x2df948018] lru: [0x255ea0aa8,0x1e5df8e28]
  ckptq: [NULL] fileq: [NULL] objq: [0x2b1315990,0x2b1315990] objaq: [0x2b1315980,0x2b1315980]
  st: XCURRENT md: NULL fpin: 'ktspswh4: ktspfsbmb' tch: 1 le: 0x207ff9a58
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 27 rdba: 0x07c00009 (31/9)  --RDBA
scn: 0x000b.4bb6a898 seq: 0x01 flg: 0x04 tail: 0xa8982101
frmt: 0x02 chkval: 0x323b type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FAA0B47E000 to 0x00007FAA0B480000
7FAA0B47E000 0000A221 07C00009 4BB6A898 0401000B  [!..........K....]
7FAA0B47E010 0000323B 00000000 00000000 00000000  [;2..............]
7FAA0B47E020 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7FAA0B47E040 00000000 00000000 00000000 07C0000A  [................]
7FAA0B47E050 00000001 00000001 00000000 00000000  [................]
7FAA0B47E060 00000000 00000000 0001F968 00000001  [........h.......]
7FAA0B47E070 00000000 07C00008 00010005 00000000  [................]
7FAA0B47E080 00000000 00000000 00000000 00000000  [................]
        Repeat 502 times
7FAA0B47FFF0 00000000 00000000 00000000 A8982101  [.............!..]
Dump of Second Level Bitmap Block                ----------------------------
   number: 1       nfree: 1       ffree: 0      pdba:     0x07c0000a 
   Inc #: 0 Objd: 129384
  opcode:0 
 xid: 
  L1 Ranges :
  --------------------------------------------------------
   0x07c00008  Free: 5 Inst: 1 
  
  --------------------------------------------------------


Block dump from cache:
Dump of buffer cache at level 4 for tsn=27 rdba=130023434
BH (0x25beb5a18) file#: 31 rdba: 0x07c0000a (31/10) class: 4 ba: 0x25bc22000
  set: 78 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,15
  dbwrid: 1 obj: 129384 objn: 129384 tsn: 27 afn: 31 hint: f
  hash: [0x2cad60d48,0x2cad60d48] lru: [0x237dde608,0x223e5be88]
  ckptq: [NULL] fileq: [NULL] objq: [0x2a896cdc0,0x2a896cdc0] objaq: [0x2a896cdb0,0x2a896cdb0]
  st: XCURRENT md: NULL fpin: 'ktswh03: ktscts' tch: 3 le: 0x1fffd9238
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 27 rdba: 0x07c0000a (31/10)   --RDBA
scn: 0x000b.4bb6a8a3 seq: 0x01 flg: 0x04 tail: 0xa8a32301
frmt: 0x02 chkval: 0x195f type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FAA0B47E000 to 0x00007FAA0B480000

 

buffer tsn: 27 rdba: 0x07c0000a (31/10)
scn: 0x000b.4bb6a8a3 seq: 0x01 flg: 0x04 tail: 0xa8a32301
frmt: 0x02 chkval: 0x195f type: 0x23=PAGETABLE SEGMENT HEADER

转换成file_id和block_id

SQL> select dbms_utility.data_block_address_file(to_number('07c0000a','xxxxxxxx')) block_id,
  2         dbms_utility.data_block_address_block(to_number('07c0000a','xxxxxxxx')) file_id
  3  from dual;

  BLOCK_ID    FILE_ID
---------- ----------
        31         10

Block的类型是PAGETABLE SEGMENT HEADER,与dba_segment中查询的header_block一致。

Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x07c00010  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::  0x07c00010  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: 0x07c00008
  Level 1 BMB for Low HWM block: 0x07c00008
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x07c00009   --搜索L2 BMB起始位置,insert需要
  Last Level 1 BMB:  0x07c00008
  Last Level II BMB:  0x07c00009
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 129384 flag: 0x10000000
  Inc # 0 
  Extent Map
  -----------------------------------------------------------------
   0x07c00008  length: 8     --起始DBA + length
  
  Auxillary Map    auxillary map = extent no + L1 BMB of first DBA + first DBA in extent
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x07c00008 Data dba:  0x07c0000b
  --------------------------------------------------------
  
   Second Level Bitmap block DBAs 
   --------------------------------------------------------
   DBA 1:   0x07c00009
此条目发表在ASSM分类目录。将固定链接加入收藏夹。

One Response to Oracle ASSM管理

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s