본장의 목적은 블록에 대한 이해를 기반으로 오라클의 구조를 심도 깊게 이해하는 것이다. 본장에서는 테이블 및 인덱스 세그먼트를 구성하는 블록을 중점적으로 다룬다.

• 오라클은 프리블록 관리를 위해서 FLM(Free List Management)방식과 오라클 9i부터 소개된 ASSM(Automatic Segment Space Management) 방식을 사용
  • FLM 방식 : MFL(Master Free List),PFL(Process Free List), TFL(Transaction Free List)을 이용하여 프리블록을 관리하는 방식
  • ASSM방식 : 비트맵 블록을 이용하여 프리블록을 관리하는 방식
    FLM 방식에서 freelists를 기본설정 값인 1로 설정할 경우에는 PFL을 사용하지 않고 MFL과 TFL만을 사용하여 프리블록을 관리
    여러세션에서 동시에 프리블록에 대한 요청이 있을 경우에 MFL에 대한 경합이 발생
    MFL에 대한 경합을 줄이기 위해 테이블 생성 시나 생성 후 FREELISTS 값을 1보다 큰 수치로 변경하여 PFL의 개수를 증가
    PFL이 사용될 경우, 프리블록을 요청한 세션은 세션의 PID(V$PROCESS.PID)값을 이용하여 PFL을 할당 받음으로써 프리리스트에 대한 경합을 줄일 수 있음.

아래의6개의 테스트 케이스를 이용하여 FLM 방식의 동작원리를 알아보도록 하자.

– FLM 방식의 테이블 스페이스 생성

Create tablespace test_block
extent management local uniform size 1m
segment space management manual
datafile 'e:\oracle\product\10.2.0\oradata\hunny\test_block01.dbf' size 100m;

-- 해당 테이블스페이스에 테이블 생성

create table free_list_test(name char(1000))
tablespace test_block
pctfree 10
pctused 40;

– DBA_SEGMENTS 뷰를 조회하여 세그먼터 헤더 블록 주소 확인

select segment_name, header_file, header_block, blocks
from dba_segments
where segment_name = 'FREE_LIST_TEST';

SEGMENT_NAME     HEADER_FILE HEADER_BLOC BLOCKS     
---------------- ----------- ----------- -----------
FREE_LIST_TEST             5           9         128

1 rows selected.

Case 1. 데이터 입력 전의 테이블 세그먼트 헤더 블록 분석

*alter system dump datafile 5 block 9;*

• 프리리스트 분석
  FLM 방식을 사용할 경우, freelist groups와 freelists의 기본설정 값은 1
  • nfl : 프리리스트 개수
  • nfb : 프리리스트 블록의 개수
  • typ : 프리리스트 type
  • nxf : transaction free lists 개수
  • lhd : 시작 프리블록의 DBA
  • ltl : 마지막 프리블록의 DBA값

• Block Type

Case 2. 데이터 입력 후의 테이블 세그먼트 헤더 블록 분석

-- 데이터를 입력 한 후 세그먼트 헤더 블록에 대한 덤프를 수행

begin 
 for I in 1..15 loop
    insert into free_list_test values ('TEST' ||lpad(to_char(i),3,0));
    commit;
    end loop;
    end;
    /

alter system dump datafile 5 block 9;

-- 각각의 블록에 저장된 레코드 수 확인

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) 
    from free_list_test 
    group by dbms_rowid.rowid_block_number(rowid) 
    order by 1;
DBMS_ROWID. COUNT(*)   
----------- -----------
         10           7 
         11           7
         12           1     ------->1개의 레코드, 추후 발생되는 입력작업은 여기에..
3 rows selected.

Case 3. TFL 동작 방식 분석

• TFL(트랜잭션 수행, 일반적으로 DELETE 작업)
  • Uncommitted TFL - 트랜잭션 내부에서만 사용되는 리스트, 외부에서 관찰 불가능
  • Committed TFL - 본장에서 설명되는 TFL

– TFL 확인하기 위해 DELETE 수행 후 TFL 정보확인
(테이블 생성 시 PCTUSED를 40으로 지정해서 생성했으므로 4건의 레코드를 첫 번째 블록에서 삭제하여 해당 TFL 변화 확인)
– 4건 삭제

delete free_list_test where name in ('TEST007','TEST006','TEST05','TEST04');
commit;

– DBMS_ROWID 패키지를 이용하여 블록 별 데이터 건수 확인

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) 
    from free_list_test 
    group by dbms_rowid.rowid_block_number(rowid) 
    order by 1;
DBMS_ROWID. COUNT(*)   
----------- -----------
         10           3
         11           7
         12           1
3 rows selected.

alter system dump datafile 5 block 9;

Case 4. FREELISTS절 사용 시의 Freelist 관리 방법 분석

위의 테스트에선 FREELISTS절을 지정하지 않았으므로 기본적으로 MFL,TFL만이 사용

-- 테스트 테이블 생성

create table free_list_test2(name char(1000))
storage(freelists 2)
tablespace test_block
pctfree 10
pctused 40;

– DBA_SEGMENTS 뷰를 조회하여 세그먼트 헤더블록 주소 확인

SQL> select segment_name, header_file, header_block, blocks 
from dba_segments 
where segment_name = 'FREE_LIST_TEST2';
SEGMENT_NAME                  HEADER_FILE  HEADER_BLOC BLOCKS     
--------------------------------- ------------------ ------------------- -----------
FREE_LIST_TEST2                                  5                   137         128
1 rows selected.

alter system dump datafile 5 block 137;

– PFL은 PID값을 이용하여 사용할 프리리스트를 정하게 됨

-- PID값 확인
SQL> select pid from v$process 
where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum=1));
PID        
-----------
         22
1 rows selected.

-- 테스트 데이터 입력

begin 
 for I in 1..15 loop
    insert into free_list_test2 values ('TEST' ||lpad(to_char(i),3,0));
    commit;
    end loop;
    end;
    /

alter system dump datafile 5 block 137;

– PFL할당 공식

SQL> select mod(22,2) +1 as PFL_Number FROM dual;                                                     
PFL_NUMBER
----------
         1

-- 다른 세션에서 데이터를 입력 할 경우 프리리스트의 사용변화 확인
PID = 19

-- 테스트 데이터 입력

begin 
 for I in 16..20 loop
    insert into free_list_test2 values ('TEST' ||lpad(to_char(i),3,0));
    commit;
    end loop;
    end;
    /

alter system dump datafile 5 block 137;

SQL> select mod(19,2) +1 as PFL_Number FROM dual;
PFL_NUMBER 
-----------
          2
1 rows selected.

• PID값에 따라서 서로다른 PFL을 사용함으로 프리블록을 얻기 위한 경합을 줄이는 것이 가능
• FREELISTS의 최대값은 블록 크기에 의존적

Case 5. 블록 덤프를 이용한 프리블록 여부 확인 방법

데이터 블록의 프리블록 여부는 블록 덤프를 통해서 확인이 가능

– DBA_SEGMENTS 뷰를 조회하여 세그먼트 헤더블록 주소 확인

SQL> select segment_name, header_file, header_block, blocks 
from dba_segments 
where segment_name = 'FREE_LIST_TEST2';
SEGMENT_NAME                  HEADER_FILE  HEADER_BLOC BLOCKS     
--------------------------------- ------------------ ------------------- -----------
FREE_LIST_TEST2                                  5                   137         128


alter system dump datafile 5 block 138;
alter system dump datafile 5 block 139;
alter system dump datafile 5 block 140;
alter system dump datafile 5 block 141;

Case 6. 프리리스트를 이용한 프리블록 관리 방식

• 추가입력 작업과 삭제 작업 후 세그먼트 헤더 블록과 각각의 데이터 블록 덤프를 확인

-- 데이터 입력

begin
 for I in 1..50 loop
    insert into free_list_test2 values ('TEST' ||lpad(to_char(i),3,0));
    commit;
    end loop;
    end;
    /

SQL> select dbms_rowid.rowid_block_number(rowid), count(*) 
    from free_list_test2 
    group by dbms_rowid.rowid_block_number(rowid) 
    order by 1;
DBMS_ROWID. COUNT(*)   
----------- -----------
        138           7
        139           7
        140           7
        141           7
        142           7
        143           7
        144           7
        145           1
8 rows selected.

-- 데이터 삭제

delete free_list_test2 where name in ('TEST003','TEST004','TEST005','TEST006','TEST007','TEST008','TEST009','TEST010','TEST011');

alter system dump datafile 5 block 137;

alter system dump datafile 5 block 138;
alter system dump datafile 5 block 139;
alter system dump datafile 5 block 140;
alter system dump datafile 5 block 141;
alter system dump datafile 5 block 142;
alter system dump datafile 5 block 143;
alter system dump datafile 5 block 144;
alter system dump datafile 5 block 145;