UNDO 공간과 보존 (UNDO_RETENTION, RETENTION GUARANTEE)

#핵심 요약

UNDO 정보는 Active(커밋되지 않음), Unexpired(커밋됐지만 보존 기간 안), Expired(덮어쓰기 가능)로 나뉨

UNDO_RETENTION은 커밋된 UNDO를 최소 몇 초 보존할지 정하는 값임
고정 크기 UNDO 테이블스페이스에서는 무시되고, 자동 확장 테이블스페이스에서만 이 값을 지키려 함

RETENTION GUARANTEE를 켜면 Unexpired UNDO를 덮어쓰지 않음
대신 공간이 모자라면 DML이 실패할 수 있음

#개념과 동작 원리

#UNDO_RETENTION이 동작하는 조건

Oracle은 UNDO 테이블스페이스 크기와 시스템 활동을 보고 보존 기간을 자동으로 조정함
UNDO_RETENTION(초)은 선택적으로 주는 최소 보존 기간임

고정 크기 UNDO 테이블스페이스에서는 UNDO_RETENTION이 무시됨

자동 확장(AUTOEXTEND) UNDO 테이블스페이스에서는 이 값을 지키려 하며, 공간이 모자라면 Unexpired를 덮어쓰는 대신 테이블스페이스를 늘림
MAXSIZE에 도달하면 Unexpired를 덮어쓰기 시작할 수 있음

DBCA가 만드는 UNDOTBS1은 자동 확장임

CDB에서 UNDO_RETENTION은 PDB로 상속되지 않음

#RETENTION GUARANTEE

기본값은 꺼짐임
CREATE DATABASE, CREATE UNDO TABLESPACE 때 지정하거나 나중에 ALTER TABLESPACE로 켜고, RETENTION NOGUARANTEE로 끔

켜면 여러 DML이 실패할 수 있으므로 주의해서 씀

설정 상태는 DBA_TABLESPACES.RETENTION 열에서 확인함

#필수 명령어 또는 SQL

#보존 기간과 UNDO 테이블스페이스 확인

SHOW PARAMETER undo_retention
SHOW PARAMETER undo_tablespace
SELECT tablespace_name, retention FROM dba_tablespaces WHERE contents = 'UNDO';

결과 해석:

#RETENTION GUARANTEE 켜기

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

결과 해석: 개별 실행 출력은 기재 없음

#UNDO 테이블스페이스 생성

CREATE UNDO TABLESPACE undotbs2
  DATAFILE '/home/oracle/undotbs2_01.dbf' SIZE 100M
  RETENTION GUARANTEE;

결과 해석:

실습에서는 새 UNDO 테이블스페이스를 만든 뒤 UNDO_TABLESPACE 파라미터를 바꿔야 사용된다고 적었음
바꾸는 명령은 기재 없음

#UNDO의 용도와 ORA-01555 재현

UNDO 데이터의 용도는 세 가지임: 트랜잭션 롤백, 트랜잭션 복구(인스턴스 복구 때 커밋되지 않은 변경 되돌리기), 읽기 일관성

ORA-01555: snapshot too old는 데이터를 바꾸지 않는 SELECT에서도 남
긴 조회는 시작 시점의 데이터를 끝까지 일관되게 보려 하는데, 그 시점 데이터를 다시 만드는 데 필요한 UNDO가 이미 덮어써졌을 때 생김
예를 들어 월말 리포트 SQL이 큰 테이블을 오래 읽는 동안 배치가 같은 데이터를 대량으로 바꾸고 자주 커밋하면 리포트가 중간에 실패함

실습에서는 작은 UNDO에서 일부러 재현했음(운영 DB에서 하면 안 됨)

SELECT name, value FROM v$parameter WHERE name LIKE 'undo%' ORDER BY name;   -- 원래 UNDO_TABLESPACE 기록

CREATE UNDO TABLESPACE small_undo
  DATAFILE '<DATAFILE_DIR>/small_undo.dbf' SIZE 10M AUTOEXTEND OFF;
ALTER SYSTEM SET undo_tablespace = small_undo;

CREATE TABLE t1 (c1 INT, c2 CHAR(300));
INSERT /*+ append */ INTO t1 SELECT level, 'dummy' FROM dual CONNECT BY level <= 10000;
COMMIT;
CREATE INDEX t1_n1 ON t1(c1);

CREATE OR REPLACE FUNCTION fsleep(v1 INT, vsleep INT) RETURN NUMBER IS
BEGIN
  DBMS_LOCK.SLEEP(vsleep);
  RETURN 1;
END;
/

SELECT /*+ index(t1) */ c1, SUBSTR(c2, 1, 10) AS c2_value
  FROM t1
 WHERE fsleep(c1, 0.01) = 1 AND c1 > 0;     -- 일부러 천천히 읽는 긴 조회

BEGIN
  FOR idx IN 1 .. 1000 LOOP
    FOR idx2 IN 1 .. 10000 LOOP
      UPDATE t1 SET c2 = 'dummy' || idx WHERE c1 = idx2;
      COMMIT;
    END LOOP;
  END LOOP;
END;
/

결과 해석: 세션 B가 커밋할 때마다 그 UNDO는 롤백용이 아니게 되고, 작은 UNDO 안에서 빠르게 재사용됨
세션 A가 시작 시점 데이터를 다시 만들 UNDO를 찾지 못하면 ORA-01555로 실패함

바로 재현되지 않으면 UNDO를 더 작게, 세션 B를 더 오래, fsleep 값을 더 크게 함
실습 자료는 DB_CACHE_SIZE를 작게 줄이는 방법도 들었지만 실습 DB에서만 씀

예방: UNDO 테이블스페이스를 충분히 크게 두고, UNDO_RETENTION을 회사에서 가장 오래 도는 쿼리 시간에 맞춤
NOGUARANTEE이면 공간이 모자랄 때 보존 시간 안의 UNDO도 재사용될 수 있음

실습이 끝나면 ALTER SYSTEM SET undo_tablespace = <원래 UNDO>로 되돌림

#사용 조건과 주의사항

고정 크기 UNDO 테이블스페이스에서 UNDO_RETENTION만 늘려서는 보존 기간이 보장되지 않음

RETENTION GUARANTEE는 DML 실패 위험이 있음
운영 환경에서는 영향을 확인한 뒤 씀