Oracle Undo  관리

1. 목적

- Transaction Rollback : 특정 작업을 수행한 후 커밋을 수행하지 않은 작업에 롤백을 수행하게 되면 작업 수행 전의 데이터로 복구되는 기능
- Read Consistency 유지 : Transaction이 진행되는 동안 Database의 다른 사용자는 이 Consistent Read에 의해 Commit되지 않은 변경 사항을 볼 수 없는 기능
- Transaction Recovery : Transaction이 진행되는 동안 Instance가 실패한 경우 Database가 다시 열릴 때 Commit 되지 않은 사항은 Rollback 되어야 하는데 이때 Undo Segment 정보가 사용

 
2. 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) 발생한다


3. 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 ;


TABLESPACE_NAME                STATUS       USED_MB
-----------------------------------------------------------------
PSAPUNDO                       ACTIVE            8
PSAPUNDO                       EXPIRED          25663
PSAPUNDO                       UNEXPIRED      71100


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


4. 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;

=============================================
TUNED_UNDORETENTION MAXQUERYLEN MAXQUERYID
------------------------------- ------------------- --------------------
2300                                 2000                gpxxh7pysj4fs


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

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

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