메뉴 건너뛰기

bysql.net

9._Snapshot_too_old

2012.03.19 06:40

박영창 조회 수:9722

09. snapshot too old


* snapshot too old 발생 원인
- UNDO 실패 (undo segment 사이즈 부족)
 consistent read 이미지를 얻는 과정에서 undo 데이터가 유실된 경우

- 블록 클린아웃 실패 (undo segment 개수 부족)
 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인할 수 없음.

(1) undo 실패


UNDO 실패 시나리오

1. SELECT 트랜잭션 (SCN 123)
2. UPDATE 배치 트랜잭션 (SCN 129)
3. 2번 트랜잭션의 변경 내역을 담은 UNDO 블록이 다른 트랙잭션에 의해서 재사용.
4. 1번 트랜잭션이 2번 트랜잭션이 변경한 블록을 액세스.
   -> SCN 123 < 129 이므로 CR 블록 생성을 위해서 UNDO 세그먼트 블록 검색
5. SCN129 UNDO 블록이 재사용된 이후이므로 Snapshot Too Old.



ordered use_nl 힌트 : 쿼리 수행 시간 증가, 같은 블록의 inner 테이블 방문 가능성 증가.


1. 커서 오픈 (SCN 100)
2. 11~13 ▶ update 수납액 변경 후
   15 line ▶ commit
   ※ 개별 commit 구조이기 대문에 insert, update 모두 개별 트랜잭션 처리

   500번 블록 SCN 120 으로 변경
3. 트랜잭션이 반복되면서, 변경 블록의 undo 블록 (before image) 재사용
4. 커서 C 가 fetch 하면서 변경된 블록 데이터를 fetch하는 시점에서 CR 블록 생성 목적으로
   undo 블록 데이터 요청
5. SCN120 이전의 데이터를 보관하던 undo 블록은 이미 재사용.
6. Snapshot Too Old 발생

* FETCH ACROSS COMMIT
 명시적으로 커서를 열어서 단위 레코드를 fetch 하면서 루프에서 commit 을 날리는 방식

(2) 블록 클린아웃 실패


트랜잭션 테이블 슬롯의 재사용으로 인한 snapshot too old



블록 클린아웃 실패 시나리오

오라클은 성능을 위해서 변경했던 블록들을 개별적으로 cleanout 하지 않고 사용하던 트랜잭션 테이블 슬롯을 free 상태로 변경하고 트랜잭션을 완료. ( > 대량 데이터 변경 상황을 가정 )

변경된 블록을 읽어야하는 시점에서 delayed 블록 클린아웃을 위해 트랜잭션 테이블 슬롯을 검색. 이미 다른 트랜잭션이 재사용. ( > 이 SCN 시점의 데이터를 읽지 못할 수가 있음 )

이런 상황에서 Delayed Cleanout + Consistent Read 작업 보장하기 위해서 오라클은 아래와 같이 동작.
UNDO 세그먼트 헤더 블록 갱신 역시 UNDO 블록에 기록. 우선 재사용된 UNDO 세그먼트 헤더 블록 (트랜잭션 테이블 슬롯)를 롤백 시도. ( > 성공시 UNDO 블록을 찾아서 CR 블록 생성 )

재사용된 UNDO 세그먼트 헤더 블록를 롤백할 UNDO 블록이 재사용된 경우라면 Snapshot Too Old 발생


블록 클린아웃 실패 오해

상기와 같은 오라클의 처리 프로세스에 문제가 있다고 오해가 쉬움.
실제로 delayed block cleanout 원인의 snapshot too old 문제는 발생 빈도가 거의 없음.

트 랜잭션 슬롯이 필요해지면 오라클은 Undo 세그먼트 헤더에 저장되어있는 lowest commit SCN 을 참고해서, 최저 commit SCN 트랜잭션 슬롯부터 재사용하고 lowest commit SCN 갱신. ▶ 슬롯을 재사용 이후에는 이전 트랜잭션의 정확한 커밋 SCN 확인은 불가능 ▶ 하지만, 이 시점에 갱신된 lowest commit SCN 이전에도 commit 이 있었다는 사실은 추정이 가능함. ▶ 클린아웃 시점에 있는 블록이 클린아웃을 시도하려고 ITL이 가리키는 트랜잭션 테이블 슬롯을 찾아갔을때 커밋 정보가 지워지고 없으면, Undo Segment 헤더에 lowest commit SCN을 ITL 엔트리의 SCN 으로 기록하고 블록 클린아웃을 마무리.

> review more !!!

블록 클린아웃의 발생 원인

delayed block cleanout 이 원인이되는 snapshot too old 는 최저 커밋 SCN이 쿼리 SCN 보다 높아질 정도로 갑자기 트랜잭션이 몰리는데 있음.

(3) Snapshot Too Old 회비 방법


오라클이 UNDO 매커니즘을 도입하면서 발생한 side effect 임. (Lock 원인의 동시성은 증가함)
9i 이전에는 UNDO 관리가 수동. 9i 이후 Automatic Undo Manangement 도입이 되면서 undo 세그먼트의 크기와 개수를 오라클이 동적으로 자동 조절하면서 snapshot too old 감소.
10g 부터는 undo_retention 기능까지도 오라클이 자체적으로 관리함.

Snapshot Too Old Solution (Application Perspective)

1. 블필요한 커밋을 줄임.
2. fetch across commit 프로그램 지양.
   > ansi 표준은 커밋 이전에 열려 있던 커서는 fetch 해서는 안됨.
3. 트랜잭션이 많은 시간대에 수행되는 쿼리는 짧은 시간안에 끝나도록 시간을 조정
4. 큰 테이블은 일정 범위로 나누어서 읽고, 단계적으로 실행할 수 있도록 코딩.
   > snapshot too old 감소, 문제가 발생했을 때에도 특정 부분부터 재시작 가능.
5. NL-join 형태의 쿼리에서 long-time 으로 동일 테이블에 방문하지 않도록 주의
   > join 방법 변경. FTS 이용.
6. 소트 부하를 감수하고 order by 를 강제로 삽입.
   > fetch 를 하는 동안 undo 정보가 바뀌기 때문에 발생하는게 snapshot too old 이기 때문에
      이 방식으로 서버내에서 빠르게 데이터를 읽어서 temporary segments 에 저장하면 이후에는
      동일 블록 재방문이 문제 안됨.
7. delayed  block cleanout 으로 snapshot too old 발생하는 경우
   > 대량 업데이트 이후 곧바로 해당 테이블에 full scan 쿼리하는 것도 방법.
   인덱스 블록에서 문제가 발생한다면 인덱스 리프 블록을 모두 스캔하도록 쿼리
   > select count(*) from table_name where index_column > 0

> review more !!!
번호 제목 글쓴이 날짜 조회 수
66 Front Page file 운영자 2012.02.21 131191
65 6._문장수준_읽기_일관성 file 정찬호 2012.03.12 57261
64 3._SQL트레이스 sapius 2012.04.04 26836
63 5._Undo dasini 2012.03.11 25058
62 2._SQL_처리과정 dasini 2012.04.06 21652
61 6._RAC_캐시_퓨전 file 남송휘 2012.05.21 17500
60 4._커서_공유 file 남송휘 2012.04.27 16072
59 5._오라클_Lock file 시와처 2012.03.26 12566
58 8._블록_클린아웃 시와처 2012.03.19 11986
57 4._동시성_구현_사례 [1] dasini 2012.03.27 11678
56 8._Statspack_AWR 시와처 2012.04.01 11468
55 2._DB_버퍼_캐시 file 시와처 2012.03.04 10416
54 10._대기_이벤트 박영창 2012.03.19 10362
» 9._Snapshot_too_old 박영창 2012.03.19 9722
52 1._기본_아키텍처 file AskZZang 2012.03.02 8332
51 7._세션_커서_캐싱 박영창 2012.04.22 7772
50 5._Direct_Path_IO 남송휘 2012.05.21 7494
49 3._버퍼_Lock 박영창 2012.02.24 7220
48 7._PLSQL_함수의_특징과_성능_부하 file 남송휘 2012.05.15 6617
47 10._V$SQL file 정찬호 2012.04.09 6535