[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 유도