ORA-01555: Snapshot too old (Oracle)

Oracle 'ORA-01555: snapshot too old' 오류는 쿼리가 일관된 읽기에 필요한 이전(undo) 데이터가 이미 덮어써졌음을 나타낸다. 이 트리는 오류의 원인(긴 쿼리 + UNDO 부족, 높은 undo 회전율, 또는 LOB)을 좁혀 올바른 해결책으로 안내한다.

오류는 어디서 발생하는가?

먼저 오류의 맥락을 파악한다. 전형적인 ORA-01555가 장기 실행 SELECT/리포트 중에 발생하는가, 아니면 LOB(CLOB/BLOB) 컬럼에 접근할 때 발생하는가? LOB 관련 ORA-01555는 다른 메커니즘(PCTVERSION/RETENTION)이며 따로 해결한다.

SELECT sql_id, elapsed_time/1e6 AS sec, sql_text
FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

• 장기 실행 쿼리/리포트 중
• LOB 컬럼에 접근할 때

UNDO는 충분한가, retention은 적절한가?

긴 쿼리는 시작 이후 변경된 블록의 이전 이미지를 UNDO에서 읽는다. UNDO 테이블스페이스가 작거나 undo_retention이 쿼리 시간보다 짧으면 이전 undo가 덮어써진다. UNDO 사용량과 retention을 측정한다.

SHOW PARAMETER undo_retention;
SELECT tablespace_name, SUM(bytes)/1024/1024 AS mb, status
FROM dba_undo_extents GROUP BY tablespace_name, status;
SELECT MAX(maxquerylen) AS longest_query_sec, MAX(tuned_undoretention) AS tuned_retention
FROM v$undostat;

• UNDO가 작음 / retention이 쿼리보다 짧음
• UNDO는 충분하지만 커밋이 매우 잦음
• 쿼리가 지나치게 오래 실행됨

근본 원인: UNDO / retention 부족

retention 기간이 만료되었거나 테이블스페이스가 가득 차서 긴 쿼리에 필요한 이전 undo가 덮어써졌다. UNDO를 늘리고 쿼리 시간을 감당하도록 retention을 설정한다.

근본 원인: 높은 undo 회전율 (잦은 커밋)

매우 잦은 커밋을 동반한 대량 DML은 undo 블록을 빠르게 재활용하여 긴 쿼리에 필요한 이전 이미지를 지운다. 'commit cleanout' 효과가 이를 악화시킨다.

근본 원인: 지나치게 긴 쿼리 실행 시간

쿼리가 너무 오래 실행되어 합리적인 undo_retention으로도 충분하지 않다. 영구 해결책은 쿼리를 빠르게 하거나 워크로드를 분산하는 것이다.

근본 원인: LOB retention / PCTVERSION

LOB을 읽는 중의 ORA-01555는 이전 LOB 버전을 보관하는 공간(RETENTION 또는 PCTVERSION)이 부족함을 나타낸다. LOB에는 별도의 버전 관리 메커니즘이 있다.