"UNDO REDO"의 두 판 사이의 차이

2.1 목적[편집]

- Transaction Rollback 

특정 작업을 수행한 후 커밋을 수행하지 않은 작업에 롤백을 수행하게 되면 작업 수행 전의 데이터로 복구되는 기능

- Read Consistency 유지 

Transaction이 진행되는 동안 다른 사용자는 이 Consistent Read에 의해 Commit되지 않은 변경 사항을 볼 수 없는 기능

- Transaction Recovery 

Transaction이 진행되는 동안 Instance가 실패한 경우 Database가 다시 열릴 때 Commit 되지 않은 사항은 Rollback 되어야 하는데 이때 Undo Segment 정보가 사용됨

2.2 undo parameter[편집]

Undo 관련 파미미터 조회

sql> show parameter undo;

2.3 Undo Segment 조회[편집]

sql> select segment_name, tablespace_name, status from dba_rollback_segs;

2.4 Undo Tablespace 의 데이터파일 추가[편집]

 추가
 sql> alter tablespace undo add datafile 'filename' size 10M;

2.5 Undo Tablespace 확장[편집]

 확장
 sql> alter database datafile 'filename' resize 20M;
* 추가할때는 tablespace 단위, 확장시에는 database 단위

2.6 undo_retention 파라메터[편집]

- 파라메터의 본래 의미는 commit 된 정보에 대해서도 undo 이미지를 유지하기 위한 시간.

  Oracle Database 10g부터는 긴 쿼리를 수행하는 경우 ORA-1555 에러의 빈도를 줄이기 위해서 undo_retention 파라미터를 자동으로 할당하는 기능을 제공.  
  즉 Undo Table space의 여유가 있다면 더 오랜시간동안 Undo를 보관하기 때문에 Undo Table space의 사용율은 지속적으로 증가할수 있으며, 
  UNDO_RETENTION 파라미터는 현재 Undo 테이블스페이스에UNDO_RETENTION 기간 동안 발생하는 모든 트랜잭션을 수용할 수 있을 만큼 충분한 커야한다.
  하지만 Undo-Table Space가 더이상 확장을 할수 없다면 Default 설정인 노개런티 모드에 의해 시간이 초과하지 않은 Unexpire undo 또한 사용될수 있다.
  이런식으로 Undo table space가 확장되지 않고 Unexpired undo를 다 소진하고도 부족할 경우,ORA-30036(unable to extend segment by # in undo tablespace XXXX) 발생한다

2.7 undo extent 할당 순서[편집]

Extent stealing

1. 자기 자신의 Extent의 프리 블록을 찾는다. 2. 다음 Extent가 만료된 Extent인지 확인한다. 3. 언두 테이블스페이스에서 새로운 Extent를 할당한다. 4. 오프라인 트랜잭션 테이블에서 expired Extent를 가져온다(steal). 5. 온라인 트랜잭션 테이블에서 expired Extent를 가져온다(steal). 6. autoextend가 가능하다면 파일을 확장하여 Extent를 할당한다. 7. 자신의 트랜잭션 테이블에서 unexpired Extent를 재사용한다. 8. 오프라인 트랜잭션 테이블에서 unexpired Extent를 가져온다(steal). 9. 에러(ORA-30036)를 발생한다.

• expired extent : undo_retention 시간을 초과한 Extent.사용 중인 트랜잭션이 없고 undo retention 도 완료되어서 언제든지 트랜잭션에 할당될 수 있는 상태
• unexpired extent : undo_retention 시간을 초과하지 않은 Extent. 사용 중인 트랜잭션이 없으나, 언두 유지 시간이 완료되지 않아서 트랜잭션에 할당되지 않고 보존되어 있는 상태
• active extent :트랜잭션에 할당되어 언두 데이터를 기록하고 있는 상태
• free extent : 생성 이후 트랜잭션에 한 번도 할당되지 않았거나, SMON에 의한 주기적 정리가 완료되어 있는 상태

각각의 언두 Extent의 Expired, Unexpired 상태는 dba_undo_extents에서 확인할 수 있다.

• Undo 테이블 현황 조회

SELECT A.TABLESPACE_NAME
         , A.STATUS
         , SUM(A.BYTES)/1024/1024 as USED_MB
     FROM DBA_UNDO_EXTENTS A
        , DBA_TABLESPACES B
    WHERE B.CONTENTS = 'UNDO'
    GROUP BY A.TABLESPACE_NAME, A.STATUS
    ORDER BY 1,2 ;

• Undo 테이블 현황 퍼센트

SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB,
       ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT
  FROM (SELECT TABLESPACE_NAME, STATUS,
               ROUND(SUM(BYTES/1024/1024)) USE_MB
          FROM DBA_UNDO_EXTENTS
         GROUP BY TABLESPACE_NAME,STATUS
       )A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME LIKE 'UNDO%'
         GROUP BY TABLESPACE_NAME
       )B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 ORDER BY 1,2

- 활성 상태가 아닌 Expired Extent 및 Unexpired Extent가 많이 남아 있다면, 언두 테이블스페이스의 여유 공간이 얼마 남아 있지 않더라도 트랜잭션에 별 영향을 주지 않는다. 하지만, ORA-1555 에러 발생의 우려가 있기 때문에 v$undo_stat의 tuned_undoretention 값을 참조하여 적정한 언두 테이블스페이스 크기를 조정해 주는 것이 좋다. (ORA-1555 :사용자가 필요로 하는 롤백 세그먼트의 정보가 다른 트랜잭션에 의해 overwrite되어, 존재하지 않을 때 발생)

2.8 Automatic Undo Retention[편집]

초기 파라미터에 정의하였던 UNDO_RETENTION(초)은 트랜잭션이 진행하는 동안 일정량의 UNDO 정보를 유지할 수 있도록 함으로써 Long-running query시 발생 할 수 있는 ORA-1555(snapshot too old) 오류를 만날 가능성을 줄여주었다. 9i에서는 DBA가 UNDO_RETENTION을 적절하게 정의하기위해서는 실질적인 Long-running query의 진행시간과 undo tablespace의 크기를 함게 고려하여 실질적인 UNDO_RETENTION을 정의해야하지만 10g에서는 Oracle server가 대신하여 자동적으로 관리할 수 있게 되었다.

자동 언두 리텐션 기능의 동작 방식은 오라클 10gR1과 오라클 10gR2간에도 차이가 나며, 언두 테이블스페이스의 “undo guarantee" 사용 여부와 언두 데이터 파일의 “autoextend" 사용 여부에 따라 다르게 동작한다.

Retention Gurantee = YES 의 경우 : Undo 공간부족으로 인해 트랜잭션이 commit된 정보에 덮어쓰는 작업을 막아 Undo Retention의 설정값 만큼 보장을 해준다. 즉!! 언두 테이블스페이스의 공간 부족 현상이 발생할 경우에도, UNEXPIRED된 언두 익스텐트를 재사용하는 것을 방지함으로써, 언두 리텐션을 보장하는 것이다.

Retention Gurantee = NO 의 경우 : Undo 공간 부족으로 인해 트랜잭션이 실패 하도록 하기보다는 커밋된 Undo 정보를 덮어쓴다. 즉, TUNED_UNDORETENTION 값은 장시간의 쿼리를 감지한 직후에는 대략 MAXQUERYLEN + 300초로 증가한다. autoextend 모드라면,언두 테이블스페이스가 확장 가능하므로, 쿼리의 수행시간에 근거하여 언두 리텐션을 튜닝한다.             

- Prerequisite

undo_management = auto

undo_retention = 0 (10g: 이 파라미터 값을 0으로 해야 자동 활성화됨)

- Automatic Undo Retention의 튜닝의 방식

UNDO_RETENTION을 0으로 셋팅하면 UNDO_RETENTION의 최소값은 900초(15분)가 된다. MMON process가 매 30초마다 query duration을 계산하며, MAXQUERYLEN(데이터 수집 구간 중 가장 오래 수행된 SQL의 수행시간) 이라는 값을 계산하는데 이 값에 따라서 MMON은 TUNED_UNDORETENTION 이라는 수치를 결정한다. 이때 UNDO RETENTION 값이 TUNED_UNDORETENTION 로 셋팅이 된다.

TUNED_UNDORETENTION = MAXQUERYLEN + 300 Sec

select tuned_undoretention, maxquerylen, maxqueryid from v$undostat;

MAXQUERYLEN : 데이터 수집 구간 중 가장 오래 수행된 SQL의 수행시간(단위: 초) TUNED_UNDORETENTION : 오라클이 인스턴스에서 최적으로 설정한 언두 유지 시간

이와 같은 Automatic Undo Retention의 튜닝으로 ORA-1555 ERROR 예방이 가능하게 된다.

그러나, undo tablespace가 autoextend off 이면 DML 수행 시 UNDO SPACE 부족현상이 발생할 수 있으므로 충분한 Undo tablespace 공간이 확보되어야 한다.

2.9 현재 사용중인 세션 과 블럭[편집]

V$SESS_IO 뷰와 V$SESSION view를 조인한다. V$SESS_IO 뷰의 BLOCK_CHANGES 라는 컬럼은 얼마나 많은 block들이 그 session에 의해서 변화되었는지를 보여준다. 이 값이 높으면 session이 많은 양의 redo를 생성시켰음을 알 수 있다.

SELECT s.sid
     , s.serial#
     , s.username
     , s.program
     , i.block_changes
  FROM v$session s, v$sess_io i
 WHERE s.sid = i.sid
 ORDER BY 5 desc, 1, 2, 3, 4;

V$TRANSACTION 뷰를 조회하면 해당 transaction에 의하여 액세스된 undo block과 undo record들의 양에 대한 정보를 알 수 있다. 그 정보는 USED_UBLK 컬럼과 USED_UREC 컬럼을 보고 알 수 있다.

SELECT s.sid
     , s.serial#
     , s.username
     , s.program
     , t.used_ublk
     , t.used_urec
     , s.module
     , s.action
     , S.SQL_ID
  FROM v$session s
     , v$transaction t
 WHERE s.taddr = t.addr
-- AND SQL_ID ='7u69zymppwnss'
 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

위 SQL 문을 여러번 수행해서 USED_UBLK 컬럼과 USED_UREC 컬럼의 각 발생 사이의 delta 값을 비교해보면, 그 값이 클 수록 그 session이 많은 양의 redo를 생성시켰음을 알 수 있다.

위의 query에서 첫번째 query는 많은 양의 redo를 generate한 program을 check할 필요가 있을 때 사용하고, 두번째 query는 많은 양의 redo를 generate한 특정 transaction을 찾을 필요가 있을 때 사용하면 도움이 된다.

3.1 REDO LOG[편집]

오라클은 'Data Files'과 'Control File'에 가해지는 모든 변경사항을 하나의 Redo Log에 기록함. Redo Log는 'Online Redo Log'와 'Offline Redo Log(Archived)'로 구성된다. 'Online Redo Log' Redo Log Buffer에 버퍼링된 로그 엔트리를 기록하는 파일로, 최소 두 개 이상의 파일로 구성 현재 사용장인 Redo Log File이 꽉 차면 다음 Redo Log File로 Log Switching을 하고 모든 Redo Log File이 꽉 차면 다시 첫 번째 Redo Log File부터 재사용되는 'Round-Roin' 방식 사용 'Offline Redo Log(Archived)' 'Online Redo Log'가 재사용되기 전에 다른 위치로 백업해 둔 파일