snapshot too old (ORA-01555)

ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 %s 수에 "%s" 이름으로 된 것이 너무 작습니다

운영하다가 본의 아니게 이전 데이터를 찾아 헤멜 때 눈물을 머금으며 as of timestamp 를 치며 '제발제발제발제발있어라..있어라.....!!!!!' 하고 늘 외치지만
.
.
.
'응 없어. 돌아가 ^^'
했던 기억 다들 있으시쥬..?ㅎㅋ 눈물만 흘리지 말고
snapshot too old 가 그래서 뭔지!!!! 함 같이 알아보시쥬

• snapshot too old는 간단히 말하자면 undo 블록에 정보가 없음을 뜻한다.

(1) snapshot too old가 생기는 경우(좀 많음ㅎ)

1. 데이터를 읽어 내려가다가 쿼리 SCN이후에 변경된 블록을 만나 'Read Consistent' 모드 (과거 시점으로 롤백해 이미지를 얻는 것)로 읽다가 UNDO 블록이 다른 트랜잭션에 의해 이미 재사용돼 필요한 Undo 정보를 얻을 수 없는 경우

• 이미 앞서 읽었던 블록을 다시 방문하는 경우도 포함된다.

2. 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인 할 수 없는 경우

3. Rollback Segment가 덮어써진 경우

4. LOB 이미지가 덮어써진 경우

5. Block Corruption이 발생한 경우

• 주로 LOB Corruption과 함께 발생한다.

6. 기타 여러 가지 이유들 - 버그 포함

7. Undo Segment Header의 Transaction Table이 완전히 덮어써진 경우

• Delayed Block Cleanout과 함께 발생하는 경우가 많다.
• 'Delayed 블록 클린아웃' 상황에서 Free 상태가 된 트랜잭션 테이블 슬롯(undo 헤더)이 재사용 될 수 있다.
• 이후 블록 접근시 관련 트랜잭션 테이블 슬롯이 없다면 정상적인 '블록 클린아웃'과 '일관성 모드(Consistent Mode) 읽기'가 불가능할 수 있다.
• delayed Block Cleanout은 Block Cleanout를 Commit 시점에 하지 않고 미루겠다는 것을 의미하는데 이 때 모든 Block에 대해 Commit 여부를 지정하지 않고 Undo Segment Header의 Transaction Table에만 Commit 여부를 지정하므로 덮어써져서 Rollback하지 못하면 ORA-01555 에러가 나는 것이다.
• 하지만! 'Undo 세그먼트 헤더 블록 갱신'도 'Undo 레코드'로 기록 되므로 필요한 시점의 CR 블록 생성 후 블록 클린아웃 수행이 가능하다.

  ✅ 만약 Undo 세그먼트 헤더 블록의 Undo 레코드 조차 없다면Snapshot too old 가 발생할까?

  • Undo 세그먼트 헤더 블록의 트랜잭션 슬롯이 필요하면 커밋 SCN이 가장 낮은 슬롯 부터 재사용된다.
  • 재사용 슬롯의 커밋 SCN을 Undo 세그먼트 헤더에 '최저 커밋 SCN(Low commit SCN)' 으로 기록한다.
  • 따라서 필요한 트랜잭션 테이블 슬롯이 없을 때는 정확한 커밋 SCN 대신 Undo 세그먼트 헤더에 있는 '최저 커밋 SCN'을 ITL 의 커밋 SCN에 '최저 커밋 SCN' 기록하여 '블록 클린아웃'에 사용한다.
  • Flag 는 'C---' 대신 'C-U-' (추정된 커밋 SCN) 설정하고 블록 SCN 도 변경한다.
  • 이후 해당 블록은 일관성 모드(Consistent Mode) 읽기가 불가능 하기 때문에 CR 블록 생성도 불가하다.
  • 따라서 '최저 커밋 SCN' 이 쿼리 SCN 보다 높아질 정도로 갑자기 트랜잭션이 몰릴 때 Delayed 블록 클린아웃에 의해 Snapshot too old가 발생한다.

(2) Snapshot too old 회피 방법

• Snapshot too old는 재현이 어려워 문제 원인 찾기 쉽지 않으며 조치 방법도 확률을 낮출 뿐이며 가능성 0% 는 아니다.😥
• Snapshot too old는 Lock 에 의한 동시성 저하를 해결 하는 댓가이다.

1. AUM(Automatic Undo Management) 사용하기

• AUM은 1 Undo 세그먼트, 1 트랜잭션을 목표로 자동 관리한다.(1:1)
• Undo 세그먼트 부족 시 가장 적게 사용되는 Undo 세그먼트를 할당한다.
• Undo 세그먼트 확장 불가 시 다른 Undo 세그먼트로 부터 Free Undo Space를 회수한다.
• Undo Tablespace 내 Free Undo Space 가 소진 되면 에러를 발생시킨다.

2. DBA 는 충분한 크기의 Undo Space를 준비한다.

3. ⭐️ 어플리케이션 구현 측면 노력 ⭐️

이건 일개 개발자인 우리도 할 수 있는 것이다!!!! 노력해보자 !!!!
지피지기 백전백승 😤

1] 불필요한 잦은 커밋 제거하기

2] fetch across commit 형태를 다른 방식으로 구현하기

✅ fetch across commit이란?

프로그램 내에서 cursor를 선언하고 loop를 수행하면서 fetch하고 데이타를 변경할 때 롤백 세그먼트의 사용량을 줄이기 위해서 매 loop시마다 commit을 하는 것!

• ANSI 표준 : 커밋 이전에 열려 있던 커서는 더 Fetch 하면 안된다.
• 다른 방식으로 구현하기 어렵다면 COMMIT 횟수를 줄인다.

3] 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리 지양하기

• 오래 걸리는 쿼리는 Undo 정보를 지속적으로 참조하므로 동시 수행을 최대한 지양한다.

4] 큰 테이블을 나눠 처리하도록 코딩하기

• Snapshot too old 감소시키고 중간 재시작도 가능하다.

5] 오래 걸리는 NL 조인을(인덱스를 경유하고 테이블까지 액세스하는) 회피하고 HASH 조인이나 FTS(풀테이블 스캔)으로 대체하기

• 같은 블록을 여러번 방문하므로 Snapshot too old 가능성이 높아진다.
• HASH 조인은 PGA를 사용하므로 undo 정보를 지속적으로 참조하지 않는다.
• FTS(풀테이블스캔) 또한 undo 정보를 지속적으로 참조하지 않는다.

6] ORDER BY 쓰기

• 소트 연산을 발생 시켜 데이터가 Temp 세그먼트에 저장 되도록 한다.
• 하지만 소트 부하는 감수해야한다. ㅠ_ㅠ

7] Delayed 블록 클린아웃이 의심되면 대량 업데이트 후 FTS를 실행한다.

• 대량 업데이트 후 Full Scan 하도록 쿼리를 날린다.
• 이 때 인덱스 블록에 문제가 발생한다고 판단된다면 인덱스 리프 블록을 모두 스캔한다.
  • IFFS)(인덱스 풀스캔) 수행