오라클 성능 고도화 원리와 해법 I (2009년)
Transaction
사용자가 DML을 수행하게 되면(즉 트랜잭션을 수행하게 되면) 오라클은 내부적으로 다음과 같은 순서로 작업을 진행한다.
- 해당 트랜잭션에 대해 Undo Segment를 할당한다. 이 경우 현재 온라인 상태인 Undo Segment 중 하나를 우선적으로 사용하며 Undo Segment를 획득하는 시점에 적당한 온라인 상태의 Undo Segment가 없으면 Online 상태의 Undo Segment를 확보될 때까지 {*}enq:US - Contention{*} 이벤트를대기한다.
- Undo Segment를 할당 받으면, Undo Segment Header에 Transaction Table Slot을 생성한다.
- Transaction Table을 생성하고 나면 TXID(Transaction ID)를 생성하고, 현재 트랜잭션에 할당된다. TXID는 V$TRANSACTION뷰의 XIDUSN, XIDSLOT, XIDSQN으로 표현되는데, 이 값은 트랜잭션에 할당된 UNDO영여역의 Undo Segment Header에 존재하는 트랜잭션 테이블의 정확한 위치를 가리킨다.
- 트랜잭션의 대상이 되는 블록들을 버퍼 캐시로 적재하고 블록 헤더의 ITL(Interested Transaction List)에 Transaction Entry를 등록한다. 만일 ITL 엔트리를 등록할 공간이 없다면, {*}enq:TX - allocate ITL entry{*} 이벤트를 대기한다.
- 변경할 블록들의 변경정보는 PGA에 체인지 벡터라는 이름으로 저장된다. 보통 하나의 로우가 변경되는 경우 각각 언두 헤더 블록(체인지 벡터#1), 언두블록(체인지 벡터 #2), 데이터 블록(체인지 벡터 #3)에 해당하는 체인비 벡터들이 생긴다. 프로세스는 PGA의 체인지 벡터들을 리두 레코드(또는 리두 엔트리)라는 이름으로 리두 버퍼(Redo Buffer)로 복사한다.
- 이전 이미지(Before Image)에 대한 정보를 언두 블록에 기록하고, 데이터 블록을 변경한다. 변경된 데이터 블록은 더티(Dirty) 상태가 된다. 또한 변경된 데이터 블록에 대한 CR블록이 버퍼 캐시에 생성된다.
- 커밋이 수행되면, 트랜잭션에 SCN을 할당한다. 커밋 정보는 리두 버퍼에 저장된다.
- 언두 세그먼트 헤더의 트랜잭션 테이블에 커밋이 이루어졌음을 저장하고, 락을 포한한 모든 리소스에 대한 점유를 해제한다.
- 리두 버퍼의 내용을 리두 로그 파일에 기록한다.
Transaction에 따른 Undo Header Block, Undo Block, Data Block Dump
- 데이터 블록 덤프--기본 Table 생성
SCOTT@GHLEE > create table undo_test (c1 number, c2 varchar2(10), c3 char(20));
SCOTT@GHLEE > insert into undo_test values(1, 'A', 'a');
SCOTT@GHLEE > insert into undo_test values(2, 'B', 'b');
SCOTT@GHLEE >commit;
--SID 확인
SCOTT@GHLEE >select sid from v$mystat where rownum=1;
SID
----------
1051
--rowid확인
SCOTT@GHLEE >select rowid, substr(rowid, 1, 15), c1 from undo_test;
ROWID SUBSTR(ROWID,1,15) C1
----------------------------------------------------------
AAAOYKAAdAAAAkKAAA AAAOYKAAdAAAAkK 1
AAAOYKAAdAAAAkKAAB AAAOYKAAdAAAAkK 2
--Dump수행을 위한 File#, Block#추출
SCOTT@GHLEE >var v_rowid_type number;
SCOTT@GHLEE >var v_object_number number;
SCOTT@GHLEE >var v_relative_fno number;
SCOTT@GHLEE >var v_block_number number;
SCOTT@GHLEE >var v_row_number number;
SCOTT@GHLEE >set serveroutput on
SCOTT@GHLEE >exec dbms_rowid.rowid_info ('&1', :v_rowid_type, :v_object_number, :v_relative_fno, :v_block_number,:v_row_number);
1의 값을 입력하십시오: AAAOYKAAdAAAAkKAAA
SCOTT@GHLEE >print v_relative_fno
V_RELATIVE_FNO
--------------
29
SCOTT@GHLEE >print v_block_number
V_BLOCK_NUMBER
--------------
2314
SCOTT@GHLEE >delete from undo_test;
--테스트 1단계 커밋 수행 전 데이터 블록 덤프
SYS@GHLEE >alter system dump datafile 29 block 2314;
SYS@GHLEE >select xidusn, xidslot, xidsqn
2 from v$transaction
3 where addr = (select taddr from v$session where sid = 1051);
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 25 15206
--테스트 2단계 커밋 수행 전 언두 헤더 블록 덤프
SYS@GHLEE >alter system dump undo header '_SYSSMU3$';
--테스트 3단계 커밋 수행 전 언두 블록 덤프
alter system dump undo block '_SYSSMU3$' xid 3 25 15206;
--테스트 4단계 커밋 수행 후 데이터 블록 덤프
SCOTT@GHLEE >commit;
SYS@GHLEE >alter system dump datafile 29 block 2314;
--테스트 5단계 커밋 수행 전 언두 헤더 블록 덤프
SYS@GHLEE >alter system dump undo header '_SYSSMU3$';
--테스트 6단계 커밋 수행 전 언두 블록 덤프
alter system dump undo block '_SYSSMU3$' xid 3 25 15206;
- 커밋 전 데이터 블록 덤프 결과 분석
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.06343f28 seq: 0x06 flg: 0x02 tail: 0x3f280606
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Object id on Block? Y
seg/obj: 0xe60b csc: 0xa27.6343f5a itc: 2 flg: O typ: 1 - DATA
fsl: 0? fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.021.00002f0d 0x00802e49.14a2.33 C--- 0 scn 0x0a27.06343f28
0x02 0x0003.019.00003b66 0x0080002d.14ec.0a ---- 2 fsc 0x0036.00000000
- XID분석( 0x0003.019.00003b66)
Transaction을 시작하기 위해서는 Undo Segment를 할당 받아야 한다. Undo Segment를 할당 받은 후 XID (Transaction Identifier)를 할당 받게 된다. XID는 크게 3개의 필드(Usn#, Slot#, Wrap#)로 구성된다. 위 덤프 내용 중에 Itl 0x02번의 XID를 보면 Usn#=0x0003(3), Slot#=0x019(25), Wrap#=0x3b66(15206)인 것을 확인할 수 있다. 즉, 해당 트랜잭션은 3번 언두(_SYSSMU3$) 에 롤백을 위한 정보를 저장하고 있으며, 3번 언두 헤더 블록 내에 있는 25번째 슬롯에 트랜잭션과 관련된 정보를 저정하고 있으며, 해당 트랜잭션 슬롯은 15206번(0x3b66) 재사용 되었다는 것을 알 수 있다.(Wrap#는 재사용 될 때 마다 1씩 증가한다.) - Uba분석(0x0080002d.14ec.0a)
Uba는 언두 블록 내에 변경된 주소를 나타내고 있으며, 3개의 필드(UDBA, SEQ, SLOT)로 구성된다. UBA는 언두블록의 DBA를 나타내며, seq는 시쿼스 번소, slot은 언두 블록내의 레코드 번호를 나타낸다. 데이터 블록 내의 Itl에서 나타내고 있는 UBA는 트랜잭션에 의해 처음으로 사용된 언두 블록의 주소를 나타낸다. - Flag 분석(
)
트랜잭션이 완료되지 않았으므로, Flag에는 아무런 정보도 가지고 있지 않다. - Lck 분석(2)
트랜잭션과 관련된 레코드의 수 이다. 즉 해당 트랜잭션은 2개의 레코드에 대해서 로우 레벨 락을 설정한 상태이다. - Scn/Fsc 분석( fsc 0x0036.00000000)
Scn은 System Change Number를 나타내며, Fsc는 Free Space Credit를 나타낸다. Fsc는 해당 트랜잭션으로 인해, 데이터 블록 내에 발생되는 프리 공간을 의미힌다. 현재 트랜잭션이 진행중이므로 Scn이 할당되지 않았으며, 해당 트랜잭션으로 인해 54byte(0x0036)의 Free 공간이 발생한 것을 알 수 있다.
- XID분석( 0x0003.019.00003b66)
- 커밋 후 데이터 블록 덤프 결과 분석
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.06343f9c seq: 0x01 flg: 0x02 tail: 0x3f9c0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0740090a
Object id on Block? Y
seg/obj: 0xe60b csc: 0xa27.6343f5a itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.021.00002f0d 0x00802e49.14a2.33 C--- 0 scn 0x0a27.06343f28
0x02 0x0003.019.00003b66 0x0080002d.14ec.0a --U- 2 fsc 0x0036.06343f9c
- XID분석(0x0003.019.00003b66)
커밋 전과 동일하여, 향후 언두 정보를 이용한 읽기 일관성 및 블록 클린아웃 시에 사용된다. - UBA 분석(0x0080002d.14ec.0a)
커밋 전과 동일하다. - Flag 분석(--U-)
트랜잭션이 완료되어 Flag가 U로 변경되었다. U Flag가 의미하는 것은 Upper bound Commit이라는 의미이다. Upper bound Commit은 오라클에서 커밋 시에 사용하는 Fast Commit방식과 관련이 있다. Fast Commit방식이란, 트랜잭션이 종료하게 되면, 언두 헤더 블록내의 트랜잭션 슬롯의 state를 변경하고 트랜잭션과 관련된 데이터 블록 중에 버퍼 캐시에 상주해 있는 블록에 대해서만 Itl의 Flag 필드를 U로 변경하고 Scn/Fsc 필드(Scn Wrap#:2byte, Scn Base#:4Bytes) 중에서 Scn Base# 부분만을 설정한 후에 커밋을 완료하는 방식이다. Fast Commit 방식에 의해 데이터 블록의 내용이 변경되는 (Cleanout)것을 fast block cleanout이라고 하며, 이러한 방식을 사용하느 이유는 커밋 수행을 최대한 빨리 끝내기 위해서이다. 예를 들어, 트랜잭션과 관련된 블록이 10개이고 이중 9개가 버퍼캐시 내에서 age out 되었을 경우에, 10개의 블록 전체에 대한 커밋 처리를 하기 위해서는 9번의 single block i/o가 발생하게 되고, 이로 인해 커밋 처리가 지연되기 때문이다. U Flag는 해당 블록에 대한 delayed block cleanout이 발생하면 'C'로 변경된다. - Lck 분석(2)
트랜잭션과 관련된 레코드의 수이다. 커밋이 완료되어서 해당 트랜잭션에 대한 로우레벨 락은 해제되었지만, fast block cleanout시에는 해당 내용을 변경하지 않으므로, 여전히 커밋 전 데이터를 유지하고 있다. 해당 내용은 delayed block cleanout이 발생하는 시점에 0으로 초기화 된다. - Scn/Fsc분석(fsc 0x0036.06343f9c)
커밋이 수행되었지만 fast block cleanout 시에는 Scn Base#만을 Scn/Fsc필드에 변경하고, Scn Wrap#는 변경하지 않는다. Scn Wrap#는 delayed block cleanout이 발생하는 시점에 Itl의 XID정보를 이용하여 해당 트랜잭션과 관련되었던 언두 헤더 블록의 트랜잭션 슬롯의 정보를 이용하여 변경하게 된다.
- XID분석(0x0003.019.00003b66)
- 커밋 전 언두 헤더 블록 덤프 결과 분석
********************************************************************************
Undo Segment: _SYSSMU3$ (3)
********************************************************************************
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
...
0x18 9 0x00 0x3b8b 0x0011 0x0a27.06343daa 0x0080002b 0x0000.000.00000000 0x00000001 0x00000000 1256068612
0x19 10 0x80 0x3b66 0x0000 0x0a27.06343f28 0x0080002d 0x0000.000.00000000 0x00000001 0x00000000 0
0x1a 9 0x00 0x3b69 0x0002 0x0a27.06343e94 0x0080002b 0x0000.000.00000000 0x00000001 0x00000000 1256068912
....
- Index(0x19) 및 state(10)분석
TRN TBL(Transaction Table)에서 관리되는 트랜잭션 슬롯 정보 중에서 index칼럼은 슬롯 번호를 나타내며, state칼럼은 트랜잭션의 상태를 나타낸다. Active 한 트랜잭션에 대한 정보를 가지고 있는 슬롯의 state는 10이며, 트랜잭션이 종료한 경우에 9로 설정된다. 현재 0x19슬롯을 사용하고 있으며, 이 정보는 데이터 블록의 Itl에서 관리되는 XID(0x0003.019.00003b66)의 두 번째 필드 정보와 동일하다. - Wrap#분석(0x3b66)
트랜잭션 슬롯이 재사용될 때 마다 증가하게 된다. Itl에서 관리되는 XID(0x0003.019.00003b66)의 세 번째 필드 정보와 동일하다. - dba분석 (0x0080002d)
해당 되는 트랜잭션의 이전 이미지를 저장한 마지막 언두 블록의 주소를 나타낸다. 데이터 블록의 itl에서 관리하는 UBA(0x0080002d.14ec.0a)의 첫 번째 필드 값과 동일한 값을 나타내므로, 해당 트랜잭션을 위해서는 한 개의 언두 블록이 사용되었다는 것을 알 수 있다.
- Index(0x19) 및 state(10)분석
- 커밋 후 언두 헤더 블록 덤프 결과 분석
********************************************************************************
Undo Segment: _SYSSMU3$ (3)
********************************************************************************
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
...
0x18 9 0x00 0x3b8b 0x0011 0x0a27.06343daa 0x0080002b 0x0000.000.00000000 0x00000001 0x00000000 1256068612
0x19 9 0x00 0x3b66 0xffff 0x0a27.06343f9c 0x0080002d 0x0000.000.00000000 0x00000001 0x00000000 1256069358
0x1a 9 0x00 0x3b69 0x0002 0x0a27.06343e94 0x0080002b 0x0000.000.00000000 0x00000001 0x00000000 1256068912
....
- state(9)분석
커밋이 수행되면 트랜잭션 슬롯의 state 값이 10에서 9로 변경된다. - scn분석(0x0a27.06343f9c)
커밋이 수행되었으므로, scn이 할당되고 그 값이 해당 트랜잭션 슬롯의 scn칼럼에서 관리된다. 커밋 시점에 해당 데이터 블록의 scn과 트랜잭션 슬롯의 scn은 동일한 값을 가지게 된다.
- state(9)분석
- 커밋 전 언두 블록 덤프 결과 분석
********************************************************************************
Undo Segment: _SYSSMU3$ (3)
xid: 0x0003.019.00003b66
Low Blk : (0, 0)
High Blk : (12, 127)
Object Id : ALL
Layer : ALL
Opcode : ALL
Level : 2
********************************************************************************
UNDO BLK: Extent: 0 Block: 3 dba (file#, block#): 2,0x0000002d
xid: 0x0003.019.00003b66 seq: 0x14ec cnt: 0xa irb: 0xa icl: 0x0 flg: 0x0000
*-----------------------------
* Rec #0xa slt: 0x19 objn: 58891(0x0000e60b) objd: 58891 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x09
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
*-----------------------------
* Rec #0x9 slt: 0x19 objn: 58891(0x0000e60b) objd: 58891 tblspc: 25(0x00000019)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
- XID분석(0x0003.019.00003b66)
데이터 블록의 Itl에서 관리되는 XID와 언두 헤더 블록의 트랜잭션 슬롯에서 관리되는 XID와 동일한 값을 가진다. 이를 통해서 3개 블록 클래스간의 연결고리는 XID임을 알 수 있다. - cnt분석(0xa)
해당 언두 블록에 존재하는 언두 레코드의 개수를 의미한다. 현재 10개(0xa)의 언두 레코드를 저장하고 있다. - irb분석(0xa)
해당 언두 블록에서 마지막으로 저장된 언두 레코드의 번호를 나타낸다. 현재 10번째 언두 레코드가 마지막으로 저장된 언두 레코드이다. - objd분석(58891)
언두 레코드와 관련된 오브젝트 번호를 나타내다. DBA_OBJECTS.OBJECT_ID에서 나타내는 값과 동일하다. - rci(0x09,0x00) 분석
이전 언두 레코드의 번호를 나타낸다. ROLLBACK을 수행하기 위해서는 마지막 언두 레코드부터 시작해서 최초의 언두 레코까지 적용해야만 완료되므로, 항상 현재의 언두 레코드는 이전 언두 레코드 번호를 가진다. - rdba(0x00000000)분석
만일 rci 0x00값을 가지는 언두 레코드의 rdba 값이 설정이 되어있다면, 해당 트랜잭션은 2개 이상의 언두 블록을 사용한 것이며, rdba값이 0x00000000인 경우에는 해당 트랜잭션은 1개의 언두 블록을 사용한 것이다. 현재의 경우에는 1개의 언두 블록내의 2개의 언두 레코드를 가지고 있다고 볼 수 있다. 에를 들어 해당 트랜잭션에 대한 언두 정보를 저장하기 위해 2개의 언두 블록이 사용되었고, 각 언두 블록의 주소를 사용 순서대로 나타내었을 때, 0x008004f8, 0x008004f9이라고 가정하면, 0x008004f9 언두 블록에서 rci 0x00값을 가지는 언두 레코드의 rdba=0x008004f8이다.
- XID분석(0x0003.019.00003b66)
Delayed 블록 클린 아웃
- 트랜잭션이 갱신한 블록 개수가 총 버퍼 캐시 블록 개수의 1/10을 초과할 때 사용하는 방식
- 커밋 이후 해당 블록을 액세스하는 첫 번째 쿼리에 의해 클린아웃이 이루어 지며 아래와 같은 작업을 수행한다.
- ITL슬롯에 커밋 정보 저장
- 레코드에 기록된 Lock Byte 해제
- Online Redo에 Logging
- 블록을 읽는 과정에서 Active상태의 블록, 즉 다른 트랜잭션이 발생시킨 변경사항에 대한 커밋 정보가 아직 ITL에 기록되이 않았다면 읽기 전에 먼저 블록 클린아웃을 시도 한다.
- ITL 슬롯에 기록된 트랜잭션 ID를 이용해 Undo 세그먼트 헤더에 있는 트랜잭션 테이블 슬롯을 찾아가 트랜잭션의 현재 상태를 확인하고 커밋된 트랜잭션이라면 이를 ITL슬롯에 반영하고 로우 Lock 정보를 해제해 블록을 클린아웃시킨다.
- 블록 클린아웃을 위한 갱신내용도 Redo에 logging하며, 블록 SCN도 변경한다.
- Delayed Block Cleanout - 테스트 초기화
SCOTT@GHLEE >create table undo_test1 (c1 number, c2 char(2000));
SCOTT@GHLEE >create sequence test1_sesq;
SCOTT@GHLEE >begin
2 for i in 1..1000 loop
3 insert into undo_test1 values(test1_sesq.nextval, 'c2');
4 end loop;
5 commit;
6 end;
7 /
- Delayed Block Cleanout - 1단계 : 커밋 전 데이터 블록 덤프
SCOTT@GHLEE >select rowid, substr(rowid, 1, 15), c1 from undo_test1 where c1=1000;
ROWID SUBSTR(ROWID,1,15) C1
------------------ ------------------------------ ----------
AAAOZjAAdAAAArXAAA AAAOZjAAdAAAArX 1000
SCOTT@GHLEE >var v_rowid_type number;
SCOTT@GHLEE >var v_object_number number;
SCOTT@GHLEE >var v_relative_fno number;
SCOTT@GHLEE >var v_block_number number;
SCOTT@GHLEE >var v_row_number number;
SCOTT@GHLEE >set serveroutput on
SCOTT@GHLEE >exec dbms_rowid.rowid_info ('&1', :v_rowid_type, :v_object_number, :v_relative_fno, :v_block_number,:v_row_number);
1의 값을 입력하십시오: AAAOZjAAdAAAArXAAA
PL/SQL 처리가 정상적으로 완료되었습니다.
SCOTT@GHLEE >print v_relative_fno
V_RELATIVE_FNO
--------------
29
SCOTT@GHLEE >print v_block_number
V_BLOCK_NUMBER
--------------
2775
SCOTT@GHLEE >delete from undo_test1;
1000 행이 삭제되었습니다.
SYS@GHLEE >alter system dump datafile 29 block 2775;
Start dump data blocks tsn: 25 file#: 29 minblk 2775 maxblk 2775
buffer tsn: 25 rdba: 0x07400ad7 (29/2775)
scn: 0x0a27.06355c73 seq: 0x01 flg: 0x00 tail: 0x5c730601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x07400ad7
Object id on Block? Y
seg/obj: 0xe663 csc: 0xa27.6355c73 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x7400ad8 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.00d.00002f2b 0x00803c99.14a5.28 C--- 0 scn 0x0a27.063559c0
0x02 0x0009.025.000032f3 0x00803ff3.137c.04 ---- 1 fsc 0x07d7.00000000
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 0, @0x17c7
tl: 2 fb: --HDFL-- lb: 0x2
- Delayed Block Cleanout - 2단계 : Buffer Flush, COMMIT 후 데이터 블록 덤프
SYS@GHLEE >alter system flush buffer_cache;;
SCOTT@GHLEE >commit;
SYS@GHLEE >alter system dump datafile 29 block 2775;
Start dump data blocks tsn: 25 file#: 29 minblk 2775 maxblk 2775
buffer tsn: 25 rdba: 0x07400ad7 (29/2775)
scn: 0x0a27.06355c73 seq: 0x01 flg: 0x04 tail: 0x5c730601
frmt: 0x02 chkval: 0x24b3 type: 0x06=trans data
Block header dump: 0x07400ad7
Object id on Block? Y
seg/obj: 0xe663 csc: 0xa27.6355c73 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x7400ad8 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.00d.00002f2b 0x00803c99.14a5.28 C--- 0 scn 0x0a27.063559c0
0x02 0x0009.025.000032f3 0x00803ff3.137c.04 ---- 1 fsc 0x07d7.00000000
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 0, @0x17c7
tl: 2 fb: --HDFL-- lb: 0x2
- Delayed Block Cleanout - 3단계 : READ 후 데이터 블록 덤프
SCOTT@GHLEE >select * from undo_test1;
SYS@GHLEE >alter system dump datafile 29 block 2775;
Start dump data blocks tsn: 25 file#: 29 minblk 2775 maxblk 2775
buffer tsn: 25 rdba: 0x07400ad7 (29/2775)
scn: 0x0a27.06355c95 seq: 0x01 flg: 0x00 tail: 0x5c950601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x07400ad7
Object id on Block? Y
seg/obj: 0xe663 csc: 0xa27.6355c95 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x7400ad8 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.00d.00002f2b 0x00803c99.14a5.28 C--- 0 scn 0x0a27.063559c0
0x02 0x0009.025.000032f3 0x00803ff3.137c.04 C--- 0 scn 0x0a27.06355c89
data_block_dump,data header at 0xc986e5c
===============
data_block_dump:
- 커밋 시에 delayed block cleanout방식이 적용되는 블록은 아무런 변경사항이 발생하지 않는다. 해당 블록은 버퍼 캐시로 적재되는 시점에 full cleanout이 발생하게 된다.
커밋 클린아웃(=Fast 블록 클린아웃)
- 만약 모든 클린아웃을 Delayed 블록 클린아웃 방식으로 처리한다면 select시에 블록을 클린아웃 하는 일이 빈번히 발생한다.
- 블록 클린아웃도 쓰기 작업이므로 Current 블록에 작업을 수행해야 하며, RAC 또는 OPS 환경에서는 이를 위해 Exclusive 모드의 Current 블록을 요청하게 된다. RAC에서는 인스턴스간의 Ping 현상이 과도하게 발생하는 것을 해결하기 위해 버전 7.3에서 커밋 클린아웃 방식이 도입되었다.
- 트랜잭션이 갱신한 블록 개수가 버퍼 캐시 블록 개수의 1/10을 초과하지 않았을 때는 커밋 시점에 곧바로 블록 클린아웃을 수행한다. (버퍼 캐시에서 밀려나면서 데이터파일에 기록되었거나 다른 트랜잭션에 의해 사용 중인 블록은 놔둠으로써 Delayed 블록 클린아웃 방식을 따르도록 함)
- 커밋 시점에는 ITL슬롯에 커밋 정보만 저장하고 로우 헤더에 기록된 lock Byte는 해제하지 않는다.
- 커밋 시점에 이미 완전한 커밋 정보가 ITL에 기록돼 있기 때문에 이후 CR모드 읽기 시 커밋 여부와 커밋 SCN을 확인하려고 트랜잭션 테이블을 조회하지 않아도 되며, Lock Byte를 무시하고 그대로 블록을 읽는다.
- ITL슬롯에 커밋 정보를 저장하면서 lock Byte를 해제하지 않고 그대로 두는 이유는 로깅을 수행하지 않기 위해서 이다. 커밋 클린아웃 시에는 Online Redo에 로그를 남기지 않는다. 로깅 시점을 뒤로 미루는 것이다. 그리고 나서 해당 블록을 갱신하려고 Current모든 읽는 시점에 (ITL슬롯이 필요해지므로) 비로소 lock Byte를 해제하고 완전한 클린아웃을 수행한다. 그리고 그 내역을 Online Redo에 로깅한다.
- Fast Block Cleanout - 1단계 : 커밋 전 데이터 블록 덤프
SCOTT@GHLEE >create table undo_test (c1 number, c2 varchar2(10), c3 char(20));
SCOTT@GHLEE >insert into undo_test values(1, 'A', 'a');
SCOTT@GHLEE >insert into undo_test values(1, 'B', 'b');
SCOTT@GHLEE >insert into undo_test values(3, 'C', 'c');
SCOTT@GHLEE >commit;
SCOTT@GHLEE >select rowid, substr(rowid, 1, 15), c1 from undo_test;
ROWID SUBSTR(ROWID,1,15) C1
------------------ ------------------------------ ----------
AAAOZiAAdAAAAkKAAA AAAOZiAAdAAAAkK 1
AAAOZiAAdAAAAkKAAB AAAOZiAAdAAAAkK 2
AAAOZiAAdAAAAkKAAC AAAOZiAAdAAAAkK 3
SCOTT@GHLEE >var v_rowid_type number;
SCOTT@GHLEE >var v_object_number number;
SCOTT@GHLEE >var v_relative_fno number;
SCOTT@GHLEE >var v_block_number number;
SCOTT@GHLEE >var v_row_number number;
SCOTT@GHLEE >set serveroutput on
SCOTT@GHLEE >exec dbms_rowid.rowid_info ('&1', :v_rowid_type, :v_object_number, :v_relative_fno, :v_block_number,:v_row_number);
1의 값을 입력하십시오: AAAOZiAAdAAAAkKAAA
SCOTT@GHLEE >print v_relative_fno
V_RELATIVE_FNO
--------------
29
SCOTT@GHLEE >print v_block_number
V_BLOCK_NUMBER
--------------
2314
SCOTT@GHLEE >delete from undo_test where c1=1;
SYS@GHLEE >alter system dump datafile 29 block 2314;
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.06355889 seq: 0x07 flg: 0x02 tail: 0x58890607
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0740090a
Object id on Block? Y
seg/obj: 0xe662 csc: 0xa27.63558a7 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.02c.000033e0 0x008039c4.1418.1d C--- 0 scn 0x0a27.06355889
0x02 0x0009.027.000032cf 0x00803da3.137a.0e ---- 1 fsc 0x001b.00000000
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 0, @0x1f83
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f66
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 42
col 2: [20] 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x1f49
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
- Fast Block Cleanout - 커밋 후 데이터 블록 덤프
SCOTT@GHLEE >commit;
SYS@GHLEE >alter system dump datafile 29 block 2314;
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.063558c5 seq: 0x02 flg: 0x02 tail: 0x58c50602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0740090a
Object id on Block? Y
seg/obj: 0xe662 csc: 0xa27.63558a7 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.02c.000033e0 0x008039c4.1418.1d C--- 0 scn 0x0a27.06355889
0x02 0x0009.027.000032cf 0x00803da3.137a.0e --U- 1 fsc 0x001b.063558c5
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 0, @0x1f83
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f66
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 42
col 2: [20] 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x1f49
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
- Fast Block Cleanout - 3단계 : READ 후 데이터 블록 덤프
SCOTT@GHLEE >select count(*) from undo_test;
COUNT(*)
----------
2
SYS@GHLEE >alter system dump datafile 29 block 2314;
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.063558c5 seq: 0x02 flg: 0x02 tail: 0x58c50602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0740090a
Object id on Block? Y
seg/obj: 0xe662 csc: 0xa27.63558a7 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.02c.000033e0 0x008039c4.1418.1d C--- 0 scn 0x0a27.06355889
0x02 0x0009.027.000032cf 0x00803da3.137a.0e --U- 1 fsc 0x001b.063558c5
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 0, @0x1f83
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f66
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 03
col 1: [ 1] 42
col 2: [20] 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x1f49
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
- Fast Block Cleanout - 4단계:DML 후 데이터 블록 덤프
SCOTT@GHLEE >delete from undo_test where c1=2;
SYS@GHLEE >alter system dump datafile 29 block 2314;
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.063558ea seq: 0x01 flg: 0x04 tail: 0x58ea0601
frmt: 0x02 chkval: 0x9bb6 type: 0x06=trans data
Block header dump: 0x0740090a
Object id on Block? Y
seg/obj: 0xe662 csc: 0xa27.63558a7 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.00b.00002f65 0x00803c90.14a5.15 ---- 1 fsc 0x001b.00000000
0x02 0x0009.027.000032cf 0x00803da3.137a.0e --U- 1 fsc 0x001b.063558c5
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 0, @0x1f83
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f66
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1f49
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
- Fast Block Cleanout - 5단계 : 커밋 후 데이터 블록 덤프
SCOTT@GHLEE >commit;
SYS@GHLEE >alter system dump datafile 29 block 2314;
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.063558f4 seq: 0x01 flg: 0x02 tail: 0x58f40601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0740090a
Object id on Block? Y
seg/obj: 0xe662 csc: 0xa27.63558a7 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.00b.00002f65 0x00803c90.14a5.15 --U- 1 fsc 0x001b.063558f4
0x02 0x0009.027.000032cf 0x00803da3.137a.0e --U- 1 fsc 0x001b.063558c5
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 0, @0x1f83
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f66
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1f49
tl: 29 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 04
col 1: [ 1] 43
col 2: [20] 63 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
- Fast Block Cleanout - 6단계:DML후 데이터 블록 덤프
SCOTT@GHLEE >delete from undo_test where c1=3;
SYS@GHLEE >alter system dump datafile 29 block 2314;
Start dump data blocks tsn: 25 file#: 29 minblk 2314 maxblk 2314
buffer tsn: 25 rdba: 0x0740090a (29/2314)
scn: 0x0a27.063558f4 seq: 0x01 flg: 0x02 tail: 0x58f40601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0740090a
Object id on Block? Y
seg/obj: 0xe662 csc: 0xa27.635590b itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.00b.00002f65 0x00803c90.14a5.15 C--- 0 scn 0x0a27.063558f4
0x02 0x0008.01e.00003181 0x00802b7e.12fc.12 ---- 1 fsc 0x001b.00000000
data_block_dump,data header at 0xc986e5c
===============
block_row_dump:
tab 0, row 2, @0x1f49
tl: 2 fb: --HDFL-- lb: 0x2
- 커밋 시에 fast block cleanout 방식이 적용되는 블록은 커밋과 관련된 최소한의 내용반을 변경한다.
- fast block cleanout 방식이 적용된 블록에 대해서는 읽기 작업이나 해당 블록에 DML이 발생하더라도 full cleanout이 발생하지 않는다
- 해당 블록은 itl을 재사용하는 시점에 full cleanout이 발생
- Oracle 9i까지는 fast block cleanout 방식이 적용된 블록에 DML이 발생할 경우, Itl의 재사용 여부와 상관없이 full cleanout이 발생하였던 것이 10g 부터 full block cleanout시점을 최대한 뒤로 미루도록 코드가 변경되었음
| 항목 | Fast block cleanout | Delayed block cleanout |
|---|---|---|
| 대상블록 | 버퍼캐시에 존재하는 블록 | 버퍼캐시에 존재하지 않는 블록 |
| Partial Cleanout 여부 | 가능 | 불가능 |
| Partial Cleanout시에 변경되는 내용 | 블록 Scn# Itl의 Flag 변경(\--U-) Itl의 Scn/Fsc의 Scn Base# | N/A |
| Full Cleanout이 발생하는 시점 | 해당 블록에 트랜잭션이 수행되는 시점 | 해당 블록을 디스크에서 버퍼캐시로 적재하는 시점 |
| Full Cleanout시에 변경되는 내용 | Itl의 Flag 변경(C---) Itl의 Lck 초기화(0) Itl의 Scn/Fsc의 Scn Wrap# Row 레코드 정리 | 블록 Scn# Itl의 Flag 변경(C---) Itl의 Lck 초기화(0) Itl의 Scn/Fsc의 Scn Base#/Wrap# Row 레코드 정리 |
문서에 대하여
- 최초작성자 : 미녀씨
- 최초작성일 : 2009년 10월 22일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*
참고자료
- http://wiki.ex-em.com/index.php/Transaction
- (주)엑셈이 출간한 "Transaction Internals in Oracle 10g R2"
"코어 오라클 데이터베이스 스터디 모임" 에서 2009년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3008
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.