ORA-01555 (Snapshot too old) 에러 발생 원인과 대처 방법

Application 수행 중 자주 발생하는 Snapshot too old (ORA-01555) 에러에 대하여 많은 개발자 및 DBA들이

에러 발생의 원인에 대하여 빈번하게 문의를 하고 있어 간단하게 원인 및 대처 방안에 대하여 알아보고자 한다.

 

이 에러의 발생 원인은 SQL 수행 중 읽고자 하는 데이터가 다른 Transaction에 의해 변경이 되었고,

변경 전 데이터를 RBS에서 읽고자 했으나 찾지 못하는 경우에 발생하는 에러이다.

 

이러한 에러 발생의 주요 원인으로는 RBS (or Undo)가 너무 작기 때문에 Commit 된 Old Image가 RBS에서

삭제 (Re-write)되는 일이 발생할 수도 있으나, 대부분의 경우는 에러가 발생한 Application의 SQL이 너무

오래 돌기 때문에 (Long-running Query) 발생하는 경우가 대부분이다.

 

즉, SQL이 실행된 시점에는 읽고자 하는 데이터가 A 였으나 다른 Transaction에 의해 B로 Update되었고,

Commit이 실행 되었으므로 RBS에서 다른 Transaction에 의해 해당 RBS의 Extent가 재사용되는 경우에

A라는 Old-Image가 없어지게 되는 것이다.

 

해당 데이터를 읽고자 했던 프로세스가 오랜시간 이후에 해당 데이터를 RBS에서 읽고자 했으나,

이미 다른 Transaction에 의해 A라는 Image가 없어서 버린 이후 이므로 Snapshot too old 에러를 발생하게 되는 것이다.

 

일반적으로 운영 DB에서는 RBS 또는 UNDO Tablespace를 충분히 설정하는 경우가 대부분이며, 9i 이후 부터 적용된

UNDO_RETENTION도 충분히 크게 설정했다면 SQL의 수행시간이 너무 긴 것이 에러 발생의 주요 원인일 것이며,

RBS 또는 UNDO Size의 검토 와 SQL의 성능을 검토하는 것이 좋다.

 

SQL이 오래 수행되는 주요 원인은 아래와 같다.

 

1. 비효율적인 실행 계획 (Exection plan)

    - 의도하지 않은 Cartesian merge join

    - 비효율적인 조인 순서등으로 불필요한 블록 액세스가 발생하는 경우

    - 특정 테이블이 FULL SCAN으로 액세스되면서 Nested Loop 조인되는 경우

    - 비효율적인 인덱스를 사용하는 Nested Loop

    - Hash Join시 대용량 테이블이 Proning되는 경우 (Direct path read/write waiting, Disk I/O)

    - Non-mergeable view에 bind변수로 검색조건을 사용하거나 다른 테이블과의 조인에 사용하는 경우

2. 검색범위가 너무 넓은 경우

    - 처리해야 할 데이터 (READ 해야 할 데이터)의 범위가 너무 넓은 경우 (예: 3년치 SALES 데이터 SUMMARY)

    - 검색조건에 아무것도 넣지 않고 SEARCH 하는 경우

 

위의 경우 말고도 SQL의 성능이 저하되는 경우는 많이 있으므로 ORA-01555 에러가 발생할 경우

SQL의 응답속도에 문제가 없는지를 먼저 검토하는 것이 필요하다.