[Oracle] 오라클 에러 ORA-01555: Snapshot too old
Snapshot too old, 말 그대로 Snapshot이 너무 오래됐다는 말이다.
Snapshot이 뭔데?
* Snapshot : Data Buffer Cache 블록에 갱신이 일어날 때 복사해 놓은 Copy본 (CR 블록)
CR 블록이 뭔지 모르겠다면
☑[Oracle] 오라클 다중 버전 읽기 일관성 모델 알아보기
그럼 오라클에서 이런 에러가 나는 이유에 대해 알아보도록 하자.
Undo 실패
- 데이터를 읽어 내려가다가 쿼리 SCN 이후에 변경된 블록을 만나 과거 시점으로 롤백한 'Read Consistent' 이미지를 얻으려고 하는데, Undo 블록이 다른 트랜잭션에 의해 이미 재사용 돼 필요한 Undo 정보를 얻을 수 없는 경우 (Undo 세그먼트가 너무 작을때)
① SCN 123 시점에 1시간 쯤 걸리는 조회 쿼리가 시작됨
② 쿼리가 진행되는 동안 특정 row의 값이 Update 된 후 Commit 됨
→ 그 row를 담고있던 블록의 SCN이 129로 변경됨
③ 얼마후 ②번의 내용을 담고 있던 Undo 블록이 다른 트랜잭션에 의해 재사용
④ ①에서 돌던 쿼리가 특정 row에 도달했을때는 이미 Undo 블록이 덮어쓰여버린 후
▶ Snapshot too old 에러 발생, ①번 쿼리 실행 멈춤
1 for C in (select /*+ ordered use nl(b) */ A.고객ID，A.입금액，B.수납액
2 from 은행입금 A, 수납 B
3 where A.입금일자 = trunc(sysdate)
4 and B.수납년월(+) = to char(sysdate, 'yyyymm')
5 and B.고객ID(+) = A.고객ID)
6 loop
7 if C.수납액 IS NULL then
8 insert into 수납(고객ID，수납년월, 수납액)
9 values ( C.고객ID, to char(sysdate, 'yyyymm'), C.입금액)
10 else
11 update 수납 set 수납액 = 수납액 + C.입금액
12 where 고객ID = C.고객ID
13 and 수납년월 = to_char(sysdate, 'yyyymm')
14 end if;
15 commit;
16 end loop;
① SCN 100 시점에 커서 C 오픈
② 11~13번 라인 update 문에 의해 홍길동 고객의 수납액이 변경되고 15번 라인에서 커밋
→ 변경된 레코드가 담긴 500번 블록 SCN이 120으로 변경됨
* 프로그램 내에서 커밋을 반복하기 때문에 위 insert문과 update문은 모두 개별 트랜잭션 처리
③ 트랜잭션이 계속 반복되기 때문에 홍길동 고객의 수납액 Before Image가 담긴 Undo 블록이 다른 트랜잭션에 의해 재사용 되는 것은 시간 문제, 결국 재사용 되어버림
④ 커서 C가 홍길동 고객과 같은 500번 블록에 저장된 김철수의 수납액을 변경하려고 하는 찰나 Snapshot too old 에러 발생
※ fetch across commit
명시적으로 커서를 열어 row를 하나씩 Fetch하면서 값을 변경하고 loop 내에서 계속해서 commit을 날리는 방식
블록 클린아웃 실패
* 가정: 대량 업데이트 후 커밋된 트랜잭션이 블록 클린아웃 없이 자신이 사용하던 트랜잭션 테이블 슬롯을 Free 상태로 변경
① SCN 90 시점에 쿼리 수행, 클린아웃 되지 않은 블록 Access
② Delayed 블록 클린아웃을 위해 트랜잭션 테이블 슬롯 방문했으나 해당 슬롯이 다른 트랜잭션에 의해 이미 재사용되고 없음
→ 정상적인 블록 클린아웃과 일관성 모드 읽기가 불가능해질 수 있음
③ Undo 레코드에 기록된 Undo 세그먼트 헤더 블록 갱신 내역을 찾아 롤백 시도
* 사실상 블록 클린아웃에 의한 Snapshot too old 에러는 거의 발생하지 않음
- 트랜잭션 슬롯 필요 시 커밋 SCN이 가장 낮은 트랜잭션 슬롯부터 재사용
- 그 슬롯에 기록돼 있던 커밋 SCN을 Undo 세그먼트 헤더에 'low commit SCN'으로 기록
- 아직 클린아웃되지 않은 블록을 클린아웃하려고 ITL이 가리키는 트랜잭션 테이블 슬롯을 찾아갔을 때 커밋 정보가 이미 지워지고 없으면, Undo 세그먼트 헤더에 있는 'low commit SCN'을 블록 ITL 엔트리에 커밋 SCN으로서 기록(추정된 커밋 SCN: C-U-)
(대부분의 low commit SCN은 쿼리 SCN보다 작으므로 정상적인 일관성 모드 읽기가 가능)
* low commit SCN이 쿼리 SCN보다 높아질 정도로 트랜잭션이 몰리는 경우에만 에러 발생
Snapshot too old 회피 방법
- 불필요한 커밋 지양
- fetch across commit 형태의 프로그램 작성 지양
- 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리 수행 지양
- 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩
- Nested Loop Join 지양
- order by 강제 삽입 (서버 내에서 빠르게 데이터를 읽어 Temp 세그먼트에 저장)
- 대량 업데이트 후 해당 테이블에 대해 Full Scan 유도