- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=10059777&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
본장의 목적은 블록에 대한 이해를 기반으로 오라클의 구조를 심도 깊게 이해하는 것이다. 본장에서는 테이블 및 인덱스 세그먼트를 구성하는 블록을 중점적으로 다룬다.
- 오라클은 프리블록 관리를 위해서 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
10진수 16진수 블록유형 1 0x01 Undo sement header 11 0x0b Data file header 12 0x0c Data segment header with FLG blocks 14 0x0e Unlimited undo segment header 15 0x0f Unlimited save undo segment header 16 0x10 Unlimited data segment header 17 0x11 Unlimited data segment header with FLS blocks 18 0x12 Extent map block 23 0x17 Bitmapped segment header 29 0x1d Bitmapped file space header 32 0x20 First level bitmap block 33 0x21 Second level bitmap block 34 0x22 Third level bitmap block 35 0x23 Pagetable segment header block 36 0x24 Pagetable extent map block 37 0x25 System Managed Undo Extent Map Blck
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;
| 데이터 블록 덤프 수행(헤더부분만 요약)
– flg : 블록이 프리리스트에 등록된 블록인지의 여부를 나타내는 flag값 ( - : 프리블록아님 , O : 프리블록) |
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;
| 데이터 블록 덤프 수행(헤더부분만 요약)
|
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=10059777&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.