ORA-01555 snapshot too old — 언두 세그먼트 재사용·장기 쿼리 일관성 실패 에러 원인·해결·예방 완전 정리
Oracle Database 운영에서 가장 악명 높은 에러 중 하나가 ORA-01555: snapshot too old다. 야간 배치, 대형 리포트, 통계 쿼리처럼 수십 분 이상 실행되는 쿼리가 갑자기 실패하면서 "왜 다른 트랜잭션이 끝났을 뿐인데 내 쿼리가 죽는가"라는 의문을 남긴다. 본질은 Oracle의 읽기 일관성(Read Consistency) 모델과 언두(undo) 세그먼트 재사용의 충돌이다.
본 글은 ORA-01555의 정확한 의미, MVCC 모델과의 관계, 5가지 발생 원인(UNDO_RETENTION 부족, 테이블스페이스 압박, 장기 쿼리, 동시 DML 폭주, Delayed Block Cleanout), UNDO_RETENTION·RETENTION GUARANTEE·Fetch Across Commit 회피 같은 해결 방법, V$UNDOSTAT 모니터링, 인접 에러 ORA-22924·ORA-30036과의 차이까지 정리한 트러블슈팅 자료다.
이 글의 구성
01 에러 메시지와 MVCC 일관성 원리
| 항목 | 내용 |
|---|---|
| 에러 코드 | ORA-01555 |
| 영문 | snapshot too old: rollback segment number N with name "..." too small |
| 한국어 | 스냅샷이 너무 오래되었습니다 |
| 발생 영역 | 장기 SELECT, FETCH 루프, 통계·배치 쿼리 |
| 근본 원인 | 쿼리 시작 SCN 시점의 일관 이미지 재구성에 필요한 undo가 이미 재사용됨 |
MVCC와 Read Consistency 원리
Oracle은 쿼리가 시작될 때 그 시점의 SCN(System Change Number)을 기록한다. 쿼리는 이 SCN 시점의 일관된 데이터만 보아야 한다. 다른 트랜잭션이 같은 행을 수정하더라도 그 SCN 이후의 변경은 "보이지 않아야" 한다. 이 일관성을 만들기 위해 Oracle은 undo 세그먼트에 저장된 이전 이미지를 사용해 데이터를 SCN 시점 상태로 재구성한다.
문제는 undo 공간이 무한하지 않다는 점이다. 다른 트랜잭션이 commit한 뒤 시간이 흐르면 그 undo는 expired 상태가 되어 재사용 대상이 된다. 쿼리가 너무 오래 실행되면 자신에게 필요한 undo가 이미 다른 트랜잭션에 의해 덮어쓰여진 상황이 발생한다. 이때 Oracle은 일관성을 보장할 수 없어 ORA-01555를 던진다.
💡 핵심 관찰 — "오래된 스냅샷"의 진짜 의미
쿼리가 "오래된" 게 아니라 쿼리가 보고 싶은 SCN 시점의 undo가 사라진 상태다. 따라서 해법은 두 가지로 좁혀진다 ━ 쿼리를 짧게 만들거나, undo를 더 오래 보관하거나.
02 발생 원인 5가지
원인 1 — UNDO_RETENTION 설정 부족
기본값은 900초(15분). 장기 배치·통계 쿼리가 15분 이상 걸리면 그 사이 commit된 다른 트랜잭션의 undo가 expired되어 재사용된다. UNDO_RETENTION을 최장 쿼리 시간 이상으로 늘려야 한다.
원인 2 — UNDO 테이블스페이스 공간 부족
UNDO_RETENTION은 "권고값"이며 공간 부족 시 자동으로 무시된다. UNDO 테이블스페이스가 작거나 AUTOEXTEND 미설정인 환경에서는 retention이 보장되지 않는다. RETENTION GUARANTEE로 보장하거나 테이블스페이스 크기를 늘린다.
원인 3 — 장기 실행 쿼리
수십 분에서 수 시간 단위로 실행되는 통계·리포트·full table scan 쿼리. 쿼리 시간이 길어질수록 그 사이 commit된 트랜잭션의 undo가 사라질 확률이 높다. 인덱스 추가·파티셔닝·쿼리 분할로 실행 시간 단축이 근본책이다.
원인 4 — 동시 DML 폭주
대량 INSERT·UPDATE·DELETE가 동시에 실행되는 환경에서는 undo 생성 속도가 빨라 retention 시간이 짧아진다. 야간 배치와 일과 시간 트랜잭션이 충돌하는 시간대에 발생률이 가장 높다.
원인 5 — Delayed Block Cleanout
대량 DML 트랜잭션이 commit될 때 버퍼 캐시의 10% 이상에 해당하는 블록은 ITL 슬롯 정리를 지연시킨다. 이후 SELECT가 그 블록을 읽을 때 비로소 정리(cleanout)를 수행하는데, 이때 undo 정보가 이미 사라졌으면 ORA-01555가 발생한다. ETL 직후 통계 수집 쿼리에서 자주 마주친다.
03 진단·모니터링 SQL과 해결 방법
모니터링 쿼리
-- 현재 자동 튜닝된 retention
SELECT tuned_undoretention, maxquerylen, maxquerysqlid,
ssolderrcnt -- ORA-01555 발생 횟수
FROM v$undostat
WHERE rownum <= 10
ORDER BY begin_time DESC;
-- UNDO 익스텐트 상태 (EXPIRED/UNEXPIRED/ACTIVE)
SELECT tablespace_name, status, SUM(bytes)/1024/1024 AS mb
FROM dba_undo_extents
GROUP BY tablespace_name, status;
-- 현재 활성 장기 쿼리
SELECT sid, serial#, sql_id, last_call_et, status
FROM v$session
WHERE status = 'ACTIVE'
AND last_call_et > 600
ORDER BY last_call_et DESC;
해결 방법
| 방법 | 설명 |
|---|---|
| UNDO_RETENTION 증가 | ALTER SYSTEM SET UNDO_RETENTION=3600 (최장 쿼리 시간 이상) |
| 테이블스페이스 확장 | UNDO 테이블스페이스 크기 증가 + AUTOEXTEND ON |
| RETENTION GUARANTEE | ALTER TABLESPACE undotbs1 RETENTION GUARANTEE (공간 부족해도 무시 안 됨, DML 실패 위험 있음) |
| 쿼리 분할 | 파티션·범위별 분할, 인덱스 추가, 통계 갱신, 힌트 활용 |
| Fetch Across Commit 회피 | FETCH 루프 안에서 COMMIT 금지 (자기 자신이 자기 undo를 expired 처리) |
-- UNDO_RETENTION 변경
ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=BOTH;
-- 테이블스페이스 확장
ALTER DATABASE DATAFILE '/u01/undo01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
-- RETENTION GUARANTEE (DML 실패 위험 — 신중 적용)
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
-- 12c+ Temporary UNDO 활성화 (GTT undo 분리)
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
04 Fetch Across Commit 안티패턴
가장 직관에 어긋나는 ORA-01555 원인이 Fetch Across Commit이다. PL/SQL 커서 루프 중간에 COMMIT을 호출하면 자신의 트랜잭션 undo를 expired로 만들어 후속 FETCH에서 ORA-01555를 일으킨다.
-- ✗ 안티패턴: FETCH 루프 안 COMMIT
DECLARE
CURSOR c IS SELECT id FROM huge_table;
BEGIN
FOR rec IN c LOOP
UPDATE ... WHERE id = rec.id;
COMMIT; -- ✗ 매 행마다 commit → 자기 undo expired
END LOOP;
END;
-- ✓ 권장: BULK COLLECT + FORALL + 단일 COMMIT
DECLARE
TYPE id_t IS TABLE OF NUMBER;
ids id_t;
BEGIN
SELECT id BULK COLLECT INTO ids FROM huge_table;
FORALL i IN 1..ids.COUNT
UPDATE ... WHERE id = ids(i);
COMMIT;
END;
ORA-01555의 본질은 "쿼리가 자신의 시작 시점을 보고 싶은데 그 시점의 undo가 사라졌다"는 것이다. 쿼리를 짧게 만들거나, undo를 더 오래 보관하거나 — 둘 중 하나만 옳다.
━ Fetch Across Commit은 자기가 자기 undo를 지우는 안티패턴
05 인접 에러와의 차이
| 에러 코드 | 검사 대상 | 전형적 시나리오 |
|---|---|---|
| ORA-01555 | 언두 일관성 | 장기 쿼리 시 undo 재사용 |
| ORA-22924 | LOB 일관성 | LOB PCTVERSION·RETENTION 부족 |
| ORA-30036 | UNDO 공간 부족 | 대량 DML 시 UNDO 테이블스페이스 가득 |
| ORA-30032 | RETENTION GUARANTEE 충돌 | GUARANTEE 모드 + 공간 부족으로 DML 실패 |
핵심 구분 ━ ORA-01555는 읽기 측 일관성 실패, ORA-30036은 쓰기 측 공간 부족, ORA-22924는 LOB 영역의 같은 문제, ORA-30032는 GUARANTEE 모드에서 공간이 모자라 DML 자체가 실패한 경우다.
06 자주 묻는 질문 5가지
Q1UNDO_RETENTION을 늘렸는데도 계속 ORA-01555가 발생한다
UNDO_RETENTION은 권고값이며, 공간 부족 시 자동으로 무시된다. V$UNDOSTAT.TUNED_UNDORETENTION으로 실제 보장된 시간을 확인한다. 설정값보다 작다면 테이블스페이스 크기 부족이 원인이다. RETENTION GUARANTEE로 강제 보장하거나, 테이블스페이스 크기를 늘려야 한다.
Q2RETENTION GUARANTEE의 위험은 무엇인가
GUARANTEE 모드는 retention 시간 내의 undo를 절대 재사용하지 않는다. 대량 DML이 발생하면 undo 공간이 가득 차 새 트랜잭션이 실패한다(ORA-30032). 즉 ORA-01555는 막지만 DML 자체가 막힐 수 있다. 충분한 공간을 확보한 운영 환경에서만 적용하는 것이 안전하다.
Q3Active Data Guard에서 발생 시 처리는
Primary의 undo가 Standby로 전파되므로, Standby의 장기 쿼리가 영향을 받을 수 있다. Primary의 UNDO_RETENTION을 늘리거나 Standby에서 DELAYED_REDO_APPLY 같은 설정으로 적용 지연을 조정한다. 11g+ Active Data Guard에서는 STANDBY_UNDO_RETENTION도 함께 설정 가능하다.
Q412c+ Temporary UNDO는 어떤 도움이 되나
TEMP_UNDO_ENABLED=TRUE로 활성화하면 Global Temporary Table(GTT)의 undo가 일반 UNDO 테이블스페이스가 아닌 TEMP 테이블스페이스로 분리된다. GTT를 많이 쓰는 배치 환경에서 일반 UNDO 압박이 크게 줄어 ORA-01555 발생률이 떨어진다. 물리적 Standby에서도 GTT DML이 가능해진다는 부가 효과가 있다.
Q5Delayed Block Cleanout은 어떻게 막나
대량 DML 직후 통계 수집·full table scan을 즉시 실행하면 cleanout이 강제로 일어나 undo가 살아 있을 때 처리된다. 또 ETL은 INSERT /*+ APPEND */ Direct-Path 방식이 cleanout 자체를 회피한다. UNDO_RETENTION을 충분히 두면 지연 cleanout 시점에 undo가 살아 있어 ORA-01555가 발생하지 않는다.
07 결론
ORA-01555는 단순한 설정 오류가 아니라 Oracle의 MVCC 일관성 모델과 undo 재사용의 본질적 충돌이다. 쿼리가 길어질수록, undo 공간이 작을수록, 동시 DML이 많을수록 발생률이 올라간다.
실무 원칙은 다음과 같다.
첫째, V$UNDOSTAT의 MAXQUERYLEN을 기준으로 UNDO_RETENTION을 최장 쿼리 시간 이상으로 설정한다.
둘째, UNDO 테이블스페이스에 AUTOEXTEND ON과 충분한 MAXSIZE를 둔다. 공간 부족이 retention 보장 실패의 가장 흔한 원인이다.
셋째, 필요한 경우에만 RETENTION GUARANTEE를 켠다. DML 실패 위험을 감수할 수 있는 환경에서만 적용한다.
넷째, PL/SQL FETCH 루프 중간 COMMIT 안티패턴을 절대 사용하지 않는다. BULK COLLECT + FORALL 패턴으로 전환한다.
다섯째, 장기 쿼리는 쿼리 튜닝·파티셔닝·인덱스로 실행 시간을 줄이는 것이 근본 해결책이다.
✅ ORA-01555 트러블슈팅 체크리스트
본 글은 Oracle Database 에러 ORA-01555의 일반적 원인과 해결 방법을 정리한 자료다. 운영 환경 적용 전 테스트 환경에서 충분히 검증한다.
#ORA01555 #snapshotTooOld #오라클에러 #UNDO #UNDORETENTION #RETENTIONGUARANTEE #VUNDOSTAT #FetchAcrossCommit #BULKCOLLECT #FORALL #DelayedBlockCleanout #TemporaryUNDO #ORA22924 #ORA30036 #DBA튜닝
댓글