snapshot too old(ORA-01555)

# 이 문서는 오라클클럽에서 작성하였습니다. 
# 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^ 
# 출처 : http://wiki.oracleclub.com/display/CORE/Snapshot+too+old? 
 
snapshot too old(ORA-01555)

1. 데이터를 읽어 내려가다가 쿼리 SCN이후에 변경된 블록을 만나(이미 앞서 읽었던 블록을 다시 방문하는 경우 일 수도 있음) 과거 시점으로 롤백한 'Read Consistent' 이미지를 얻으려고 하는데, UNDO 블록이 다른 트랜잭션에 의해 이미 재사용돼 필요한 Undo 정보를 얻을 수 없는 경우.
2. 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인할 수 없는 경우.
    

UNDO 실패

 SELECT /*+ ORDERED USE_NL(B) */ A.고객ID
        , NVL(SUM(과금액), 0) 과금액
         , NVL(SUM(수납액), 0) 수납액
         , NVL(SUM(과금액), 0) - NVL(SUM(수납액), 0) 미납액
 FROM   과금 A, 수납 B
 WHERE  A.과금년월 = :과금년월
 AND    A.과금유형 = :과금유형
 AND    A.고개ID  = B.고객ID(+)
 AND    A.과금년월 = B.수납년월(+)
 GROUP BY A.고객ID

1. SCN 123시점에 이 쿼리가 시작되었다.
2. 쿼리가 진행되는 동안 은행으로부터 고객의 입금내역을 전송받아 일괄 처리하는 배치 프로그램이 수행되었는데, 프로그램은 각 건별로 커밋하도록 작성되었다. 이 프로그램이 돌기 시작한지 얼마 지나지 않아 다른 트랜잭션에 의해 홍길동 고객의 수납액이 10,000원에서 20,000원으로 변경되고 나서 커밋되었다. 이때, 블록 SCN은 129로 변경되었다.
3. 시간이 흘러 홍길동 고객의 수납액 변경내역(수납액 10,000 - 20,000원)을 담은 UNDO블록이 다른 트랜잭션에 의해 재사용 되었다.
4. 위 쿼리를 수행 중인 프로세스가 홍길동 고객의 수납액이 담긴 블록에 도달했을 때, 블록 SCN이 자신의 쿼리 SCN 123보다 129임을 확인하고, 변경된 UNDO레코드를 찾으려고 ITL엔트리에 기?된 UBA를 읽어 UNDO세그먼트 블록을 찾아간다.
5. 하지만 UNDO블록은 이미 다른 트랜잭션에 의해 재사용된 상태이므로 ORA-1555 에러를 발생시키며 진행을 멈춘다.
    

블록 클린아웃 실패

• 대량 업데이트 후에 커밋된 트랜잭션은 변경했던 블록들을 모두 클린아웃하지 않은 상태에서 자신이 사용하던 트랜잭션 슬롯을 Free상태로 변경하고 트랜잭션을 완료 한다.
• 이때부터 그 트랜잭션 테이블 슬록은 다른 트랜잭션에 의해 재사용 될 수 있다.
• 시간이 흘러 그 변경된 블록들이 읽혀야 하는 시점에 Delayed 블록 클린아웃을 위해 트랜잭션 테이블 슬롯을 찾아갔는데, 해당 슬롯이 다른 트랜잭션에 의해 이미 재사용되고 없다면 정상적인 블록 클린아웃과 일관성 모드 읽기가 불가능해질 수 있다.
• 오라클은 일반 데이터 블록과 마찬가지로 UNDO 세그먼트 헤더 블록을 갱신한 내용도 UNDO레코드로서 기록한다.
• 따라서 트랜잭션 테이블 슬롯이 덮어 쓰인 것을 발견하면 우선 UNDO 세그먼트 헤더 블록에 가해진 변경사항을 롤백하려고 시도한다.
• 다행히 찾고자 하는 트랜잭션에 대한 커밋 정보가 UNDO블록에 남아있다면 현재 읽고자 하는 블록의 정확한 커밋 SCN을 가지고 블록 클린아웃을 수행할 수 있다.
• 하지만 UNDO 레코드를 뒤졌는데 그마저도 덮어 쓰이고 있는 상태라면 "Snapshot too old"에러가 발생. (거의 발생하지 않는다.)
• 트랜잭션 슬롯이 필요해지면 커밋 SCN이 가장 낮은 트랜잭션 슬롯부터 재사용하고, 기록돼 있던 커밋 SCN을 UNDO세그먼트 헤더에 '최저 커밋 SCN'을 기록해 둔다.
• 트랜잭션 슬롯이 재사용되고 나면 그 슬롯을 사용하던 이전 트랜잭션의 정확한 커밋 SCN을 확인하는 것은 불가능 해지지만 UNDO SEGMENT헤더에 기록돼 있는 '최저 커밋 SCN' 이전에 커밋 되었다는 사실만큼은 짐작할 수 있으므로 이 SCN을 블록 ITL엔트리에 커밋 SCN으로서 기록('추정된 커밋 SCN'임을 표시하기 위해 ITL슬롯 커밋 Flag에 C---- 대신 C-U-라고 기록)함으로써 클린아웃을 마무리 하고, 블록 SCN도 변경한다.
• Delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 원인은, '최저 커밋 SCN'이 쿼리 SCN보다 높아질 정도로 갑자기 트랜잭션이 몰리는데 있다.
   

Snapshot too old 회피 방법

• 과거버전에서는 UNDO 세그먼트를 수동으로관리했으므로 Snapshot too old 에러 발생을 최소화하기 위해 System Application특성에 맞는 UNDO 튜닝을 DBA가 했음
• 9i부터 AUM(Automatic Undo Management)이 도입돼 UNDO segment 크기와 개수를 오라클이 동적으로 자동 조절해 주기 때문에 Snapshot too old 에러가 발생할 가능성도 줄었음
• Snapshot too old에러 발생 가능성을 줄이기 위한 Application 측변에서의 솔루션

1. 불필요하게 커밋을 자주 수행하지 않는다.
2. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다. ANSI 표준에 따르면 커밋 이전에 열려 있던 커서는 더는 Fetch하면 안 된다. 다른 방식으로 구현하기 어렵다면 커밋 횟수를 줄여본다.
3. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간을 조정한다.
4. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다. Snapshot too old 발생 가능성을 줄일 뿐 아니라 문제가 발생했을 때 특정 부분부터 다시 시작할 수도 있어 유리하다. 물론 그렇게 해도 읽기 일관성에 문제가 없을때에만 적용해야 한다.
5. 오랜 시간에 걸쳐 같은 블록을 여러번 방문하는 Nested Loop형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고, 이를 회피할 수 있는 방법(조인 메소드 변경, Full Table Scan등)을 찾는다.
6. 소트 부하를 감수하더라도 order by등을 강제로 삽입해 소트연산이 발생하도록 한다. 많은 데이터를 오랜 시간에 걸쳐 Fetch하는 동안 Undo 정보를 지속적으로 참조하기 때문에 문제가 발생하는 것이므로, 서버 내에서 빠르게 데이터를 읽어 Temp 세그먼트에 저장하는 데에만 성공하면 이후에는 같은 블록을 아무리 재방문하더라도 더는 에러가 발생할까 걱정하지 않아도 된다.
7. 만약 delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 것으로 의심되면 대량 업데이트 후에 곧바로 해당 테이블에 대해 Full Scan 하도록 쿼리를 날리는 것도 하나의 해결방법이 될 수 있다. 인덱스 블록에서 문제가 발생한다고 되면 인덱스 리프 블록을 모두 스캔하도록 쿼리한다.

[출처] snapshot too old(ORA-01555) - Oracle Club 에서 담아온 글|작성자 아삽