길리랩blog

ORA-1555 Snapshot too old와 Oracle UNDO 관리

Oracle ORA-1555 Snapshot too old 오류의 원인과 읽기 일관성, UNDO 관리, 커밋 패턴 및 예방 방법을 정리합니다.

2026-08-14 · 최초 발행 2025-08-10

긴 조회가 과거 버전을 잃는 순간

Oracle에서 ORA-1555: Snapshot too old는 장시간 실행 중인 쿼리가 자신이 시작한 시점의 데이터 상태를 더 이상 복원할 수 없을 때 발생한다. 원인은 대개 쿼리 자체의 실패가 아니라, 그 쿼리가 필요로 하는 UNDO 정보가 유지되지 못한 데 있다.

Oracle의 다중 버전 읽기 일관성은 쿼리 시작 시점의 SCN(System Change Number)을 기준으로 데이터를 보게 한다. 실행 중 다른 트랜잭션이 데이터를 바꾸고 커밋하더라도, 조회는 시작 시점의 일관된 결과를 반환해야 한다. 현재 데이터가 그 시점보다 새로우면 Oracle은 롤백 세그먼트에서 이전 버전을 찾아 사용한다.

⤢✕YesYesNoNo쿼리 시작SCN 획득데이터 읽기현재 데이터의 SCN 쿼리 SCN?롤백 세그먼트에서 이전 버전검색이전 버전 찾음?이전 버전 데이터 사용ORA-1555 오류 발생현재 데이터 사용

필요한 이전 버전이 롤백 세그먼트에서 이미 삭제됐거나, 공간 부족 또는 다른 트랜잭션의 사용으로 재사용됐다면 조회는 더 이상 일관된 스냅샷을 구성할 수 없다.

변경 작업과 조회가 겹칠 때

사용자 A가 대량 데이터를 읽는 장시간 쿼리를 수행하는 동안 사용자 B가 같은 데이터를 여러 차례 업데이트하고 커밋하는 상황을 생각할 수 있다.

사용자 A는 쿼리 시작 시점의 SCN을 기준으로 데이터를 읽는다. 사용자 B의 변경 내용은 UNDO 데이터로 기록되지만, UNDO 공간이 부족하거나 해당 영역이 재사용되면 사용자 A가 나중에 필요로 하는 이전 버전이 사라질 수 있다. 이때 사용자 A의 쿼리가 ORA-1555로 끝난다.

문제가 자주 나타나는 패턴은 다음과 같다.

  • 대량 데이터를 오래 처리하는 쿼리
  • Stored Procedure 안에서 커서를 순회하며 중간에 커밋하는 처리
  • 커서를 열어 Fetch하는 도중 커밋하는 Fetch Across Commit
  • 대용량 업데이트 뒤 테이블 또는 인덱스 전체 스캔을 수행하는 작업

루프 안의 커밋이 만드는 문제

다음 코드는 ORA-1555를 유발할 수 있는 전형적인 형태다.

-- 문제가 있는 PL/SQL 코드 예시
BEGIN
  FOR rec IN (SELECT * FROM large_table WHERE some_condition = 1) LOOP
    -- 각 레코드마다 처리 로직
    UPDATE another_table SET column1 = rec.value WHERE id = rec.id;

    -- 각 레코드마다 커밋 (문제 발생 원인)
    COMMIT;
  END LOOP;
END;

루프마다 커밋이 수행되면, 커서가 다음 행을 가져오는 동안 필요한 롤백 세그먼트 정보가 재사용될 수 있다. 커서가 유지해야 하는 조회 시점과 반복 커밋이 충돌하는 구조다.

UNDO 용량과 보존 시간을 먼저 확인한다

ORA-1555 대응은 UNDO의 현재 구성과 사용량을 확인하는 것에서 시작한다.

-- UNDO 파라미터 확인
SELECT name, value FROM v$parameter WHERE name LIKE 'undo%';

-- UNDO 공간 사용 상태 확인
SELECT tablespace_name, status, sum(bytes)/1024/1024 MB
FROM dba_undo_extents
GROUP BY tablespace_name, status;

-- UNDO 테이블스페이스 크기 증가
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/path/to/undofile.dbf' SIZE 500M;

장시간 쿼리가 필요한 이전 버전을 확보할 수 있도록 UNDO_RETENTION도 워크로드 특성에 맞춰 검토한다.

-- 현재 UNDO_RETENTION 확인
SHOW PARAMETER undo_retention;

-- UNDO_RETENTION 증가 (초 단위)
ALTER SYSTEM SET undo_retention = 10800; -- 3시간

UNDO 공간 확대와 보존 시간 조정은 필요한 대응이 될 수 있지만, 애플리케이션의 트랜잭션 패턴까지 그대로 둔 채 파라미터만 바꾸는 방식으로는 문제가 반복될 수 있다.

커서와 트랜잭션의 경계를 다시 설계한다

레코드마다 커밋하던 처리는 배치 단위로 커밋하도록 바꿀 수 있다.

-- 개선된 PL/SQL 코드
DECLARE
  CURSOR c_data IS
    SELECT * FROM large_table WHERE some_condition = 1;

  -- 배치 처리를 위한 변수
  v_batch_size NUMBER := 1000;
  v_count NUMBER := 0;
BEGIN
  FOR rec IN c_data LOOP
    -- 각 레코드마다 처리 로직
    UPDATE another_table SET column1 = rec.value WHERE id = rec.id;

    v_count := v_count + 1;

    -- 일정 배치 크기에 도달했을 때만 커밋
    IF v_count >= v_batch_size THEN
      COMMIT;
      v_count := 0;
    END IF;
  END LOOP;

  -- 남은 변경사항 커밋
  COMMIT;
END;

커서를 사용하는 동안 커밋하지 않도록 구조를 잡고, 불필요한 커밋을 줄이는 것이 기본이다. 대량 작업은 처리 순서를 일정하게 유지하기 위해 ORDER BY를 활용하고, 데이터 자체를 작은 단위로 나누어 처리하는 방식도 검토할 수 있다.

조회 범위와 인덱스도 함께 점검한다

조회가 오래 지속될수록 UNDO에 의존하는 시간도 길어진다. 필요한 열만 선택하고 조건절에 맞는 인덱스를 활용해 실행 시간을 줄이는 접근이 필요하다.

-- 문제가 있는 쿼리
SELECT * FROM large_table WHERE condition = value;

-- 개선된 쿼리 (필요한 열만 선택)
SELECT id, name, status FROM large_table WHERE condition = value;

-- 인덱스 활용
CREATE INDEX idx_large_table_condition ON large_table(condition);

대용량 데이터는 분할 가능한지 먼저 판단한다. 분할할 수 있다면 각 단위를 독립적으로 처리하고 병렬 처리를 고려할 수 있다. 분할이 어렵다면 적절한 배치 크기를 정해 배치별로 커밋한 뒤 결과를 병합하는 흐름이 필요하다.

운영 중 확인할 UNDO 지표

UNDO 테이블스페이스 사용량과 재사용 상태, 활성 트랜잭션, UNDO 통계를 정기적으로 확인해야 한다.

-- UNDO 세그먼트 사용 현황
SELECT segment_name, status, tablespace_name, bytes/1024/1024 "Size(MB)"
FROM dba_segments
WHERE segment_type = 'UNDO';

-- 활성 트랜잭션 확인
SELECT t.start_time, s.sid, s.serial#, s.username, s.status,
       t.used_ublk "Used RBS Blocks", t.used_urec "Undo Records"
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr
ORDER BY t.start_time;

-- UNDO 통계 확인
SELECT * FROM v$undostat ORDER BY begin_time DESC;

ORA-1555는 읽기 일관성과 UNDO 관리가 맞물려 있다는 사실을 드러낸다. UNDO 공간만 늘리는 방식에 그치지 않고, 장시간 쿼리의 범위, 커밋 위치, 대용량 처리 방식, 모니터링 체계를 함께 조정해야 안정적인 Oracle 운영으로 이어진다.

Oracle DatabaseORA-1555UNDO트랜잭션성능 튜닝