Oracle 9i부터 지원된 프리블록 관리방법을 말한다.
FLM(Free List Management)방식과 달리 비트맵을 이용하여 프리블록을 관리한다.
PCTUSED, FREELISTS, FREELIST GROUP항목들이 자동관리된다.
Balanced트리구조와 유사한 방식으로 관리된다.
<그림작성 트리구조> : 데이터입력을 위해 프리블록이 필요할 경우 L3 -> L2 -> L1 순서로 할당
L1 BMB가 실제적인 데이터 블록의 프리상태를 관리하게 된다.
하나의 Extent안에서 연속된 block을 관리하게됨
| Segment Size | Block Mapped(Range) |
| 1M 이하 | 16 Data Block |
| 32M 이하 | 64 Data Block |
| 1G 이하 | 256 Data Block |
| 1G 보다 큼 | 1024 Data Block |
<표5-2>
ASSM도인스턴스그룹과 세션 PID값을 이용하여 프리블록에 대한 요청을 처리하여
세션간의 경합을 줄여준다.
<그림5-1>
===============================================================
테스트 진행 : 비트맵블록 확인
===============================================================
-- Version확인
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
-- ASSM방식의 테이블스페이스 생성
create tablespace assmtbs datafile 'C:\oracle\product\10.2.0\oradata\orcl\test_blc02.dbf' size 50M
SEGMENT SPACE MANAGEMENT AUTO;
-- FLM방식의 테이블스페이스 생성
create tablespace flmtbs datafile 'C:\oracle\product\10.2.0\oradata\orcl\test_blc03.dbf' size 50M
SEGMENT SPACE MANAGEMENT MANUAL;
-- 테스트를 위한 테이블 생성
create table assm_test (name char(1000))
tablespace assmtbs
pctfree 10
pctused 40;
create table assm_test2 (name char(1000))
tablespace assmtbs
storage(initial 1m);
create table assm_test3 (name char(1000))
tablespace assmtbs
storage(initial 32m);
-- 익스텐트 정보 및 세그먼트 정보확인
select segment_name, header_file, header_block, blocks
from dba_segments
where segment_name='ASSM_TEST';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
-------------------- ----------- ------------ ----------
ASSM_TEST 9 11 8
select segment_name, extent_id, file_id, block_id, blocks
from dba_extents
where segment_name='ASSM_TEST';
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------------- ---------- ---------- ---------- ----------
ASSM_TEST 0 9 9 8
===============================================================
세그먼트 헤더블록위치와 익스텐트 첫 번째 블록의 차이(2블록)가 존재하며
이 블록은 ASSM방식을 위한 비트맵 블록을 위해 사용한 것이다.
세그먼트헤더의 첫번째 익스텐트의 처음 세개의 블록은 비트맵블록이다.
세그먼트의 크기가 크면 첫번째 익스텐트의 비트맵블록의 개수가 3개 이상이 될 수 있다.
첫번째 익스텐트를 제외한 각각의 익스텐트의 첫 번째 블록은 세그먼트 크기에 따라 L1 BMB나 데이터블록이 된다.
===============================================================
테스트 진행 : L1 BMB 확인
===============================================================
-- Block Dump
alter system dump datafile 9 block min 9 block max 16;
-- Dump 내용
buffer tsn: 8 rdba: 0x02400009 (9/9)
scn: 0x0000.06812e39 seq: 0x01 flg: 0x04 tail: 0x2e392001
frmt: 0x02 chkval: 0xcc9d type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07E8BC00 to 0x07E8DC00
7E8BC00 0000A220 02400009 06812E39 04010000 [ .....@.9.......]
... 중략
7E8DBF0 00000000 00000000 00000000 2E392001 [............. 9.]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x0240000a poffset: 0
unformatted: 5 total: 8 first useful block: 3
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 : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 154658
HWM Flag: HWM Set
Highwater:: 0x0240000c ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02400009 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
--------------------------------------------------------
===============================================================
type (0x20) : First Level Bitmap Block(L1 BMB)
parent dba (0x0240000a) : L1 BMB를 관리하는 L2 BMB주소값
unformatted(5), total(8), first useful block(3)
L1 BMB에서 관리하는 블록의 정보요약
(nf1 0 nf2 0 nf3 0 nf4 0) Freeness Status분석
Freeness Status Block of Free Space
F1 0 ~ 25%
F2 25 ~ 50%
F3 50 ~ 75%
F4 75 ~ 100%
<표 5-3>Freeness Status
DBA Ragnes (0x02400009 Length:8 Offset:0)
L1 BMB가 관리하는 익스텐트정보를 관리함
현재의 L1 BMB는 0x02400009에서 8블록을 관리를 뜻함.
===============================================================
테스트 진행 : L2 BMB 확인
===============================================================
-- L2 BMB의 위치확인
select to_char(to_number('024','XXXXXXXX')) / 4 as fno,
to_char(to_number('0000a','XXXXXXXX')) as bno
from dual;
FNO BNO
---------- -------------
9 10
-- Block Dump
alter system dump datafile 9 block 10 ;
-- Dump 내용
buffer tsn: 8 rdba: 0x0240000a (9/10)
scn: 0x0000.06812e38 seq: 0x01 flg: 0x04 tail: 0x2e382101
frmt: 0x02 chkval: 0xdfcd type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07E8BC00 to 0x07E8DC00
7E8BC00 0000A221 0240000A 06812E38 04010000 [!.....@.8.......]
...중략
7E8DBF0 00000000 00000000 00000000 2E382101 [.............!8.]
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x0240000b
Inc #: 0 Objd: 154658
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x02400009 Free: 5 Inst: 1
--------------------------------------------------------
===============================================================
type (0x21) : Second Level Bitmap Block(L2 BMB)
number(1) nfree(1) ffree(0) pdba(0x048000b)
L1 Ranges (0x02400009 Free:5 Inst:1)
===============================================================
테스트 진행 : L3 BMB 확인
===============================================================
-- L3 BMB의 위치확인
select to_char(to_number('024','XXXXXXXX')) / 4 as fno,
to_char(to_number('0000b','XXXXXXXX')) as bno
from dual;
FNO BNO
---------- -------------
9 11
-- Block Dump
alter system dump datafile 9 block 11 ;
-- Dump 내용
buffer tsn: 8 rdba: 0x0240000b (9/11)
scn: 0x0000.06812e39 seq: 0x01 flg: 0x04 tail: 0x2e392301
frmt: 0x02 chkval: 0xf12e type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07E8BC00 to 0x07E8DC00
7E8BC00 0000A223 0240000B 06812E39 04010000 [#.....@.9.......]
...중략
7E8DBF0 00000000 00000000 00000000 2E392301 [.............#9.]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0240000c ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0240000c ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x02400009
Level 1 BMB for Low HWM block: 0x02400009
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0240000a
Last Level 1 BMB: 0x02400009
Last Level II BMB: 0x0240000a
Last Level III BMB: 0x00000000 => ?? 0x0240000c
Map Header:: next 0x00000000 #extents: 1 obj#: 154658 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x02400009 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02400009 Data dba: 0x0240000c
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x0240000a
===============================================================
type (0x23) : PAGETABLE SEGMENT HEADER (L3 BMB)
L2 Hint for Inserts(0x0480000a)
Extent Map
Auxillary Map
동시 Insert 세션들의 프리리스트 경합을 줄이기 위한 처리방식 분석
===============================================================
테스트 진행 : ASSM의 Insert block분산
===============================================================
-- A session
insert into assm_test values('a');
select rowid, name from assm_test;
ROWID NAME
------------------ ---------------------
AAAlwiAAJAAAAANAAA a
c
-- B session
insert into assm_test values('b');
select rowid, name from assm_test;
ROWID NAME
------------------ ---------------------
AAAlwiAAJAAAAAOAAA b
-- C session
insert into assm_test values('c');
select rowid, name from assm_test;
ROWID NAME
------------------ ---------------------
AAAlwiAAJAAAAAPAAA c
===============================================================
===============================================================
덤프관련 샘플
===============================================================
– treedump (object no)
alter session set events 'immediate trace name treedump level 82224';
– block dump(block no)
execute immediate 'alter system dump datafile 11 block 1816316' ;
– block dump
alter system dump datafile 12 block 75304 ;
alter system dump datafile 11 block min 9 block max 11;
– DBA file/block 찾기
select dbms_utility.data_block_address_file(50406952) fno,
dbms_utility.data_block_address_block(50406952) bno
from dual;
– DBA file/block 찾기
select to_char(to_number('048','XXXXXXXX')) / 4 as fno,
to_char(to_number('0000a','XXXXXXXX')) as bno
from dual;
===============================================================