by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

블록 클린아웃




참고 자료

  • 본 문서는 이가혜님의 스터디 내용을 전적으로 의존하였습니다.

블록 클린 아웃

블록 클린 아웃이란.?
  1. Block Cleanout은 말 그대로 "블록 청소"를 의미한다.
  2. 트랜잭션에 의해 설정된 로우 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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.