블록 클린아웃 (by ljh1148) [2010.03.16]
참고 자료
- 본 문서는 이가혜님의 스터디 내용을 전적으로 의존하였습니다.
- http://wiki.ex-em.com
- http://wiki.ex-em.com/index.php/Block_Dump
- http://wiki.ex-em.com/index.php/Transaction
- (주)엑셈이 출간한 "Transaction Internals in Oracle 10g R2"
블록 클린 아웃
블록 클린 아웃이란.?
- Block Cleanout은 말 그대로 "블록 청소"를 의미한다.
- 트랜잭션에 의해 설정된 로우 Lock을 해제하고 블록 헤더에 커밋 정보를 기록하는 오퍼레이션이다.
트랜잭션 돌아가는 순서
트랜잭션에 따른 Undo Header Block, Undo Block, Data Block Dump
SQL> conn sys/sys@erm as sysdba
연결되었습니다.
SQL> drop table test;
테이블이 삭제되었습니다.
SQL> create table test(c1 number, c2 varchar2(10));
테이블이 생성되었습니다.
SQL> create table test(c1 number, c2 varchar2(10));
create table test(c1 number, c2 varchar2(10))
*
1행에 오류:
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.
SQL> insert into test values( 1, 'AAA');
1 개의 행이 만들어졌습니다.
SQL> select sid, username from v$session where username ='SYS';
SID USERNAME
---------- ------------------------------
145 SYS
SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno,
2 dbms_rowid.rowid_block_number(rowid)as blkno
3 from test;
ROWID FNO BLKNO
------------------ ---------- ----------
AAAN+EAABAAAPBKAAA 1 61514
SQL> show parameter user;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
user_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ERM\UDUMP
SQL> select xidusn, xidslot,xidsqn
2 from v$transaction
3 where addr = (select taddr from v$session where sid =
4 145);
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
6 6 8686
SQL>
SQL> --인서트 커밋전 테스트
SQL> alter system dump datafile 1 block 61514;
시스템이 변경되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> delete from test;
1 행이 삭제되었습니다.
SQL> select xidusn, xidslot,xidsqn
2 from v$transaction
3 where addr = (select taddr from v$session where sid =
4 145);
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
8 19 11241
SQL> lter system dump datafile 1 block 61514;
SP2-0734: "lter syste..."(으)로 시작되는 알 수 없는 명령 - 나머지 줄은 무시되었습니다.
SQL> alter system dump datafile 1 block 61514;
시스템이 변경되었습니다.
SQL> alter system dump undo header '_SYSSMU8$';
시스템이 변경되었습니다.
SQL> alter system dump undo block '_SYSSMU8$' xid 8 19 11241;
시스템이 변경되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> alter system dump datafile 1 block 61514;
시스템이 변경되었습니다.
SQL> alter system dump undo header '_SYSSMU8$';
시스템이 변경되었습니다.
SQL> alter system dump undo block '_SYSSMU8$' xid 8 19 11241;
시스템이 변경되었습니다.
인서트 커민 전 블록 덤프 분석
Start dump data blocks tsn: 0 file#: 1 minblk 61514 maxblk 61514
buffer tsn: 0 rdba: 0x0040f04a (1/61514)
scn: 0x0000.00a6184c seq: 0x04 flg: 0x00 tail: 0x184c0604
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07A18400 to 0x07A1A400
7A18400 0000A206 0040F04A 00A6184C 00040000 [....J.@.L.......]
...
Block header dump: 0x0040f04a
Object id on Block? Y
seg/obj: 0xdf84 csc: 0x00.a62b36 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.006.000021ee 0x008004c0.096a.08 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x7a1845c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x07a1845c
bdba: 0x0040f04a
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f96
avsp=0x1f82
tosp=0x1f82
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f96
block_row_dump:
tab 0, row 0, @0x1f96
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2 <--
col 0: [ 2] c1 02
col 1: [ 3] 41 41 41
end_of_block_dump
- ITL에 Insert할 트랜잭션이 ITL=0x01번지로 등록 되었고 아직 커밋되지않은 액티브(Active)상태이며 TX락을 회득하고 있다.
커밋이 된 상태라면 Flag에 'C'나'U'표시가 된다. - XID(xidusn+xidslot+XIDSQN) : 0x0006.006.000021ee <-- 10진수 표현 6 6 8686
6번 언두에 롤백의 위한 정보가 정장하고 있다.
6번째 슬롯에 트랜잭션과 관련된 정보를 저장하고있으며, 해당 트랜잭션 슬롯은 8686 번 재사용 되었다는 것을 알수있다.
SQL> select xidusn, xidslot,xidsqn
2 from v$transaction
3 where addr = (select taddr from v$session where sid =
4 145);
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
6 6 8686
- Uba 분석(0x008004c0.096a.08) == UDBA, SEQ, SLOT
UDBA : 언두의 DBA를 나타냄
SEQ : 시퀀스 번호
SLOT : 언두 블록내의 래코드 번호를 나타낸다. - Flag 분석 트랜잭션이 완료되지 않았으므로, Flag에는 아무런 정보도 가지고 있지 않다.
- Lck 분석(1)
트랜잭션과 관련되 레코드의 수이다. 즉 해당 트랜잭션은 2개의 레코드에 대해서 로우 레벨 락을 설정한 상태이다
- block_row_dump 영역에서 첫번째 로우의 lb값은 0x1로 세팅되어 있다. lb는 Lock Byte의 약자로, 현재 로우에 대해
락을 건 ITL 번호를 말한다. 1번 ITL이 현재 로우를 인서트 했음을 의한다. Lock Byte의 값은 실제로 컷밋이 발생해도
정리(Cleanout)되지 않으며, 나중에 Delayed block cleanout이 발생하거나 다른 프로세스에 의해 블록이 변경되는 경우
에 정리된다.
한가지 주의할 것은 첫번째 로우를 인서트한 1번 트랜잭션(ITL에 등록된)에서 커밋이 이루어졌는지 아니지의 여부는 실제로
언두 세그먼트 헤더의 트랜잭션 테이블을 조회해보아야 알 수 있다는 것이다. 오라클은 컷밋이 이루어졌을때, 트랜잭션에 의해
변경된 모든 데이터 블록의 ITL에 대해 일일이 커밋이 처리를 하지 않는다. 변경된 블록 중 현재 버퍼 캐시에 올라와 있는 것을
일부만을 커밋 처리하며, 나머지 블록들에 대해서는 트랜잭션에 할당된 언두 세그먼트 헤더의 트랜잭션 테이블 슬롯에만 커밋를 처리한다.
이 데이터 블록들은 나중에 다른 세션에 의해 스캔될 때 실제 Cleanout 처리된다. 이러한 매커니즘을 Delayed block cleanout 이라고 부른다.
deleter 커민 전 블록 덤프 분석
Start dump data blocks tsn: 0 file#: 1 minblk 61514 maxblk 61514
buffer tsn: 0 rdba: 0x0040f04a (1/61514)
scn: 0x0000.00a6189f seq: 0x01 flg: 0x02 tail: 0x189f0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07A18400 to 0x07A1A400
7A18400 0000A206 0040F04A 00A6189F 02010000 [....J.@.........]
...
Block header dump: 0x0040f04a
Object id on Block? Y
seg/obj: 0xdf84 csc: 0x00.a62b83 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.006.000021ee 0x008004c0.096a.08 C--- 0 scn 0x0000.00a62b7f
0x02 0x0008.013.00002be9 0x008007ee.06ef.2f ---- 1 fsc 0x0008.00000000
data_block_dump,data header at 0x7a1845c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x07a1845c
bdba: 0x0040f04a
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f96
avsp=0x1f82
tosp=0x1f8c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f96
block_row_dump:
tab 0, row 0, @0x1f96
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 61514 maxblk 61514
- XID(0x0008.013.00002be9) == usn : 8, slot : 19, sqn : 11241
Transaction -> Undo Segment 할당 -> XID를 할당 받게 된다.
9번 언두에 롤백의 위한 정보가 정장하고 있다.
33번째 슬롯에 트랜잭션과 관련된 정보를 저장하고있으며, 해당 트랜잭션 슬롯은 8214번 재사용 되었다는 것을 알수있다. - Uba 분석(0x008007ee.06ef.2f) == UDBA, SEQ, SLOT
UDBA : 언두의 DBA를 나타냄
SEQ : 시퀀스 번호
SLOT : 언두 블록내의 래코드 번호를 나타낸다. - Flag 분석 트랜잭션이 완료되지 않았으므로, Flag에는 아무런 정보도 가지고 있지 않다.
- Lck 분석(1)
트랜잭션과 관련되 레코드의 수이다. 즉 해당 트랜잭션은 2개의 레코드에 대해서 로우 레벨 락을 설정한 상태이다 - Scn/Fsc 분석(fsc 0x0008.00000000)
Scn은 System Change Number를 나타내며, Fsc는 Free Space Credit를 나타낸다. Fsc는 해당 트랜잭션으로 인해, 데이터 블록 내에
발생되는 프리 공간을 의미한다. 현재 트랜잭션이 진행중이므로 Scn이 할당되지 않았으며. 해당 트랜잭션으로 인해 54byte(0x0036)의
Free 공간이 발생 한것을 알수있다.
deleter 커민 후 블록 덤프 분석
Block header dump: 0x0040f04a
Object id on Block? Y
seg/obj: 0xdf84 csc: 0x00.a62b83 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.006.000021ee 0x008004c0.096a.08 C--- 0 scn 0x0000.00a62b7f
0x02 0x0008.013.00002be9 0x008007ee.06ef.2f --U- 1 fsc 0x0008.00a62cdd
- XID(0x0008.013.00002be9) == usn : 8, slot : 19, sqn : 11241
커밋 전과 동일하여, 향후 언두 정보를 이용한 읽기 일관성 및 블록 클린아웃시에 사용된다. - Uba 분석(0x008007ee.06ef.2f) == UDBA, SEQ, SLOT
커밋 전과 동일하다. - Flag : --U-
U : Upper bound Commit이라는 의미이다.
Upper bound Commit은 오라클에서 커밋 시에 사용하는 Fast Commit방식과 관련이있다.
Fast Commit 방식이란 트랜색션이 종료하게 되면, 언두 헤더 블록내의 트랜잭션 슬롯의 state를 변경하고 트랜잭션과 관련된 데이터 블록 중에
버퍼 캐시에 상주해 있는 블록에 대해서만 itl의 Flag 필드를 U로 변경하고 Scn/Fsc 필드(Wrap#2byte, Scn Base#.4Byte)중에서 Scn Base# 부분만을 설정한 후에 커밋을 완료하는 방식이다. 예를 들어, 트랜잭션과 관련되 블록이 10개이고 이중 9개가 버퍼 캐시 내에서 age out 되었을 경우에 10개의 블록 전체에 대한 커밋 처리를 하기 위해서
9번의 single block i/o가 발생하게 되고, 이로 인해 커밋 처리가 지연되어기 때문이다. U Flag는 해당 블록에 대한 delayed block cleanout이 발생하면 'C'로 변경된다. - Lck 분석(1)
트랜잭션과 관련되 레코드의 수이다. 커밋이 완료되어 로우 레벨 락은 해제 되었지만, fast block cleanout시에는 해당 내용을 변경하지 않으므로 여전히 커밋 전 데이타를
유지하고 있다. 해당 내용은 delayed block cleanout 발생하는 시점에 0으로 초기화 된다. - Scn/Fsc 분석(fsc 0x0008.00a62cdd)
커밋이 완료되었지만 fast block cleanout 시에는 Scn Base#만을 변경하고 Scn Wrap#은 변경하지않는다.
Scn Wrap# delayed block cleanout이 발생하는 시점에 itl의 XID정보를 이용해서 해당 트랜잭션과 관련되었던 언데 헤더 블록의 트랜잭션 슬롯의 정보를 이용하여 변경하게된다.
커밋 전 언두 헤더 블록 덤프
********************************************************************************
Undo Segment: _SYSSMU8$ (8)
********************************************************************************
...
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x12 9 0x00 0x2be7 0x0000 0x0000.00a628f0 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1268733939
0x13 10 0x80 0x2be9 0x0003 0x0000.00a62b7f 0x008007ee 0x0000.000.00000000 0x00000001 0x00000000 0
0x14 9 0x00 0x2be7 0x0019 0x0000.00a626db 0x008007e8 0x0000.000.00000000 0x00000001 0x00000000 1268733330
...
- index : 트랜잭션 슬롯 정보를 나타냄. 데이타 블록의 Itl에서 관리되는 XID(0x0008.013.00002be9)의 두번째 필드(013) 0x13 과 동일하다.
- state 10 : Active한 트랜잭션의 대한 정보를 가지고 있는 슬롯을 말함.
- state 9 : 트랜잭션의 종료한 경우에 9로 설정됨
- wrap# : 트랜댁션 슬롯이 재사용될 때 마다 증가하게 된다.XID(0x0008.013.00002be9)의 세 번째 필드와 동일하다.
- dba( 0x008007ee ) : 트랜잭션의 이전 이미지를 저정한 언두 블록 주소. UBA(0x008007ee.06ef.2f ) 첫번재 필드 값을 나타내므로, 해당 트랜잭션을 위해서 한개의 언두 블록이 사용되었다는 것을 알 수 있다.
커밋 후 언두 헤더 블록 덤프
*******************************************************************************
Undo Segment: _SYSSMU8$ (8)
********************************************************************************
...
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
...
0x12 9 0x00 0x2be7 0x0000 0x0000.00a628f0 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1268733939
0x13 9 0x00 0x2be9 0x0028 0x0000.00a62cdd 0x008007ee 0x0000.000.00000000 0x00000001 0x00000000 1268735001
0x14 9 0x00 0x2be8 0x0019 0x0000.00a626db 0x008007e8 0x0000.000.00000000 0x00000001 0x00000000 1268735025
- state 10 -> 9 변경
- 커밋이 수행되었으므로, SCN이 할당되고 그 값이 해당 트랜잭션 슬롯의 SCN칼럼에서 관리된다.커밋 시점에 해당 데이타 블록의 SCN과 트랜잭션 슬롯의 SCN은 동일한 값을 가지게 된다.
커밋 전 언두 블록 덤프 결과 분석
********************************************************************************
Undo Segment: _SYSSMU8$ (8)
xid: 0x0008.013.00002be9
Low Blk : (0, 0)
High Blk : (3, 127)
Object Id : ALL
Layer : ALL
Opcode : ALL
Level : 2
********************************************************************************
UNDO BLK: Extent: 3 Block: 101 dba (file#, block#): 2,0x000007ee
xid: 0x0008.013.00002be9 seq: 0x6ef cnt: 0x2f irb: 0x2f icl: 0x0 flg: 0x0000
*-----------------------------
* Rec #0x2f slt: 0x13 objn: 57220(0x0000df84) objd: 57220 tblspc: 0(0x00000000)
* 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분석(0x0008.013.00002be9)
데이터 블록의 Itl에서 관리되는 XID와 언두 헤더 블록의 트랜잭션 슬롯에서 관리되는 XID와 값은 동일하다.
3개의 블록 클래스 간의 연결고리는 XID임을 알 수 있다. - cnt: 0x2f
해당 언두 블록에 존재하는 언두 레코드 개수를 의미한다. 현재 47개(0x2f)의 언두 레코드를 저장하고 있다. - irb: 0x2f
해당 언두 블록에서 마지막으로 저장된 언두 레코드의 번호를 나타낸다. 현재 47개(0x2f)의 언두 레코드가 마지막으로 저장된 언두 레코드이다. - objd: 57220
언두 레코드와 관련된 오브젝트 번호를 나타낸다. DBA_OBJECTS.OBJECT_ID에서 나타내는 값과 동일하다. - rci : 0x00
이전 언두 레코드의 번호를 나타낸다. ROLLBACK을 수행하기 위해서는 마지막 언두 레코드부터 시작해서 최초의 언두 레코까지 적용되므로, 항상 현재의 언두 레코드는이전 언두 레코드 번호를 가진다. - rdba: 0x00000000
만일 rci 0x00값을 가지는 언두 레코드의 rdba 값이 설정이 되어있다면, 해당 트랜잭션은 2개 이상의 언두 블록을 사용한 것이며, rdba값이 0x00000000인 경우에는 해당 트랜잭션은 1개의 언두 블록을 사용한 것이다. 현재의 경우에는 1개의 언두 블록내의 2개의 언두 레코드를 가지고 있다고 볼 수 있다. 에를 들어 해당 트랜잭션에 대한 언두 정보를 저장하기 위해 2개의 언두 블록이 사용되었고, 각 언두 블록의 주소를 사용 순서대로 나타내었을 때, 0x008004f8, 0x008004f9이라고 가정하면, 0x008004f9 언두 블록에서 rci 0x00값을 가지는 언두 레코드의 rdba=0x008004f8이다.
커밋 후 언두 블록 덤프 결과 분석
********************************************************************************
Undo Segment: _SYSSMU8$ (8)
xid: 0x0008.013.00002be9
Low Blk : (0, 0)
High Blk : (3, 127)
Object Id : ALL
Layer : ALL
Opcode : ALL
Level : 2
+++++++++++ XID mismatch. Some records may not belong to specified transaction.
+ WARNING + Block xid: 0x0008.019.00002be8 dba (file#, block#): 2,0x000007ee
+++++++++++ Input xid: 0x0008.013.00002be9
********************************************************************************
UNDO BLK: Extent: 3 Block: 101 dba (file#, block#): 2,0x000007ee
xid: 0x0008.019.00002be8 seq: 0x6ef cnt: 0x38 irb: 0x38 icl: 0x0 flg: 0x0000
*-----------------------------
* Rec #0x2f slt: 0x13 objn: 57220(0x0000df84) objd: 57220 tblspc: 0(0x00000000)
* 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(0x0008.019.00002be8) 에러가 발생하는데.. ㅠ,.ㅠ
Delayed 블록 클린 아웃
- Delayed block cleanout이란 변경된 데이터 블록들 중 Fast commit에 의해 cleanout 되지 않은 블록들을 나중에(Delayed) cleanout 처리한다는 의미이다. Delayed block cleanout는 다음 번에 해당 블록을 스캔하는 프로세스에 의해 수행된다. Delayed block cleanout이 발생하는 경우 순수한 Select 작업인 경우에도 cleanout 작업 자체에 대한 리두 데이터가 생성될 수 있다.
Fast commit이란?
- Fast commit이란 커밋 시점에 모든 블록에 대해 cleanout을 수행하지 않는다는 것을 의미한다. 오라클은 성능상의 문제로 변경된 데이터 블록들 중 버퍼 캐시에 올라와 있는 일부 블록들에 대해서만 cleanout을 수행한다. Fast Commit이 발생하는 경우, 변경되는 정보의 량을 최소화하기 위해 ITL 정보에서 Flag와 SCN 정보만 변경되고 lock byte 정보는 변경되지 않는다. Lock byte 정보는 트랜잭션에 의해 변경된 모든 로우에 저장되므로 변경해야 할 데이터의 양이 많기 때문이다. Fast commit 기법은 변경된 일부 데이터 블록들의 헤더에 대해서만 변경 작업을 수행하기 때문에 리두 데이터가 생성되지 않고, 커밋 마크(Commit Mark)만이 리두에 저장된다. 수백만 건의 데이터를 변경한 후에 커밋을 수행하는 경우에도 매우 빠른 속도로 커밋 처리가 되는 것은 이러한 기법 덕분이다.
어떻게 돌아가나?
1) 트랜잭션이 갱신한 블록 개수가 총 버퍼 캐시 블록 개수의 1/10을 초과할 때 사용하는 방식.
2) 커밋 이후 해당 블록을 액세스하는 첫 번째 쿼리에 의해 클린 아웃이 이루어진다.
- ITL 슬롯에 커밋 정보 저장
- 레코드에 기록된 Lock Byte 해제
- Online Redo에 Logging
Snapahot too old
일반적으로 알려진 Snapshot Too Old 에러의 원인은 다음과 같다. 아마 이 글을 읽는 분들 대부분이 아래 현상 중 적어도 한가지는 이미 경험했을 것이다.
- Rollback Segment가 덮어써진 경우
- Undo Segment Header의 Transaction Table이 완전히 덮어써진 경우 - Delayed Block Cleanout과 함께 발생하는 경우 많음
- LOB 이미지가 덮어써진 경우
- Block Corruption이 발생한 경우 - 주로 LOB Corruption과 함께 발생함
- 기타 여러 가지 이유들 - 버그 포함
TEST..
- 설정
SQL> create undo tablespace small_undo datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERM\S_UNDO.DBF' SI
ZE 10M AUTOEXTEND OFF;
테이블스페이스가 생성되었습니다.
SQL> alter system set undo_tablespace = 'SMALL_UNDO';
시스템이 변경되었습니다.
SQL> alter system set db_cache_size=1M scope=both ;
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 4M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> show sga
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 113249156 bytes
Database Buffers 490733568 bytes
Redo Buffers 7135232 bytes
SQL> --Select Query의 속도를 제어하기 위해 Sleep 함수를 만든다.
SQL> create or replace function fsleep(v1 int, vsleep int)
2 return number
3 is
4 begin
5 dbms_lock.sleep(vsleep);
6
7 return 1;
8 end;
9 /
함수가 생성되었습니다.
SQL> drop table t1 purge;
테이블이 삭제되었습니다.
SQL> create table t1(c1 int, c2 varchar2(10));
테이블이 생성되었습니다.
SQL> insert into t1
2 select level, 'AAA'
3 from dual
4 connect by level < 10000
5 ;
9999 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.
SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno,
2 dbms_rowid.rowid_block_number(rowid)as blkno
3 from t1 where rownum = 1;
ROWID FNO BLKNO
------------------ ---------- ----------
AAAN/eAABAAAPBSAAA 1 61522
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
--SESSION 1 번 실행중에 실행
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
--SESSION 1 번 실행중에 실행
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
SESSION 1
select /*+ index(t1) */
c1, c2
from t1
where fsleep(c1,0.01) = 1
and c1 > 0;
...
---------- ----------
3356 AAA
3357 AAA
3358 AAA
3359 AAA
3360 AAA
ERROR:
ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 14 수에 "_SYSSMU14$" 이름으로 된 것이
너무 작습니다
SESSION 2
- Session #2에서는 다음과 같이 Table t1에 대해 DML을 과다하게 발생시킨다. AUM(Automatic Undo Management)에서는 Undo Segment가 Transaction마다 할당되기 때문에 하나의 Undo Segment만을 사용하도록 set transaction use rolllback segment 명령을 사용하는 기법에 유의한다.
-- get rollback segment name
col rollback_seg new_value v_rollback_seg
select '_SYSSMU'||max(segment_id)||'$' as rollback_seg
from dba_rollback_segs
where segment_name <> 'SYSTEM'
;
-- do very frequent commit on t1
begin
for idx in 1 .. 1000 loop
for idx2 in 1 .. 10000 loop
set transaction use rollback segment "&v_rollback_seg";
update t1 set c2 = 'dummy'||idx
where c1 = idx2;
commit;
end loop;
end loop;
end;
/
데이타 덤프
첫번째 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfde csc: 0x00.a750a5 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.02e.00000020 0x01400224.000a.10 --U- 1 fsc 0x0000.00a750a8
0x02 0x000d.025.00000020 0x0140020e.0007.18 --U- 1 fsc 0x0000.00a750a6
두번째 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfde csc: 0x00.a750a5 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.02e.00000020 0x01400224.000a.10 --U- 1 fsc 0x0000.00a750a8
0x02 0x000d.025.00000020 0x0140020e.0007.18 --U- 1 fsc 0x0000.00a750a6
세번재 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfde csc: 0x00.a83265 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000e.02b.0000004d 0x0140003e.000d.36 C--- 0 scn 0x0000.00a83264
0x02 0x000d.023.00000066 0x0140002d.0012.21 --U- 1 fsc 0x0000.00a83266
- Session#1은 이미 Commit이 완료된 Table t1에 대해 Query를 수행한다.
- Session#2는 Table t1이 아닌 Table t2에 대해 과도한 DML과 commit을 수행한다.
- Session#1의 Select Query는 이미 Commit이 완료된 Table을 읽기 때문에 Rollback에 실패할 이유가 없다.
- 하지만!!! Session#1은 ORA-01555 에러를 만난다.
왜 이런 일이 발생하는가? Delayed Block Cleanout 때문이다.
Table t1에 대해 Commit이 수행될 때 모든 Data Block에 대해 Commit 여부를 지정하지 않고(즉 일부에만 Commit 여부 지정) Undo Segment Header의 Transaction Table에만 Commit 여부를 지정한다. 이 정보가 완전히 덮어써져서 Rollback하지 못하면 ORA-01555 에러가 나게 된다.
Block에 Commit 여부를 저장하는 것을 Block Cleanout이라고 부른다. Delayed Block Cleanout는 Block Cleanout를 Commit 시점에 하지 않고 미루겠다(Delay)는 것을 의미한다.
그렇다면 언제 Block Cleanout이 이루어지는가? 다른 Session이 해당 Block을 Access하는 시점에 이루어진다.
즉 위의 예에서 Session#1은 Delayed Block Cleanout를 수행하기 위해 Transaction Table을 읽을려고 했는데 다른 DML에 의해 해당 정보들이 완전히 덮어써져서 Rollback이 안되기 때문에 ORA-01555 에러를 발생시키는 것이다.
커밋 클린 아웃 (= Fast 블록 클린 아웃)
- 트랜잭션이 갱신한 블록 개수가 버퍼 캐시 블록 개수의 1/10을 초과하지 않을 때는 커밋 시점에 곧바로 블록 클린 아웃을 수행한다( 불완전한 형태의 클린 아웃)
- 해당 블록을 다른 트랜잭션에 의해 갱신 되면 완전한 형태의 클린 아웃
- 도입 시기 : 7.3
session 1
SQL> drop table t1 purge;
테이블이 삭제되었습니다.
SQL> create table t1(c1 int, c2 varchar2(10));
테이블이 생성되었습니다.
SQL> insert into t1
2 select level, 'AAA'
3 from dual
4 connect by level <
5 4;
3 개의 행이 만들어졌습니다.
SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno,
2 dbms_rowid.rowid_block_number(rowid)as blkno
3 from t1
4 ;
ROWID FNO BLKNO
------------------ ---------- ----------
AAAN/gAABAAAPBSAAA 1 61522
AAAN/gAABAAAPBSAAB 1 61522
AAAN/gAABAAAPBSAAC 1 61522
SQL> --커밋 전 덤프
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> --커밋 후 덤프
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
SQL> --다른 세션에서 트랜젼선으로 해당 로우 변경 (커밋전)
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
SQL> --다른 세션에서 트랜잭션으로 해당로우 변경 ( 커밋후)
SQL> alter system dump datafile 1 block 61522;
시스템이 변경되었습니다.
SQL>
session 2
SQL> conn sys/sys@erm as sysdba
연결되었습니다.
SQL>
SQL> update t1 set c2 = 'BBB'
2 ;
3 행이 갱신되었습니다.
SQL> commit;
SQL>
덤프
--session1 에서 인서트 후 커밋전 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfe0 csc: 0x00.107ca09 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0014.002.0000164a 0x0140028d.0329.2d ---- 3 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--session1 에서 인서트 후 커밋 후 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfe0 csc: 0x00.107ca09 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0014.002.0000164a 0x0140028d.0329.2d --U- 3 fsc 0x0000.0107dcfb
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--session2 에서 업데이트 후 커밋 전 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfe0 csc: 0x00.107de79 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0014.002.0000164a 0x0140028d.0329.2d C--- 0 scn 0x0000.0107dcfb
0x02 0x000b.029.00001662 0x014003cc.032c.22 ---- 3 fsc 0x0000.00000000
--session2 에서 업데이트 후 커밋 후 덤프
Block header dump: 0x0040f052
Object id on Block? Y
seg/obj: 0xdfe0 csc: 0x00.107de79 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0014.002.0000164a 0x0140028d.0329.2d C--- 0 scn 0x0000.0107dcfb
0x02 0x000b.029.00001662 0x014003cc.032c.22 --U- 3 fsc 0x0000.0107e006