[Oracle] Snapshot too old

출처: http://cafe.daum.net/Oracle-/DHl0/44

그외 참조: http://cafe.daum.net/Oracle-/DHl0/43, http://ncanis.tistory.com/150

09. Snapshot too old

- 원인 : 1. Undo 블록이 다른 트랜잭션에 의해 이미 재사용돼 필요한 Undo 정보를 얻을 수 없는 경우(코딩패턴에 문제가 없다면 Undo 세그먼트가 너무 작은 경우일수도 있음)

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

 

(1)   Undo 실패 (쿼리 코딩 과정은 80p 참조)

-       대량의 데이터를 기준으로 Nested Loop 조인을 하면 수행시간이 오래 걸릴 뿐 아니라 inner 테이블에 대한 같은 블록을 반복해서 방문할 가능성이 커짐, 때문에 여러 세션에서 트랜잭션이 몰리지 않더라도 프로그램이 한 세션에서 독립적으로 수행되는 도중에 Snapshot too old 에러가 발생할 수 있음

 

(2)   블록 클린아웃 실패

 

-       대량 업뎃 후 커밋된 트랜잭션은 변경했던 블록들을 클린아웃 안한 상태서 자신이 사용하던 트랜잭션 테이블 슬롯을 Free 상태로 변경하고 트랜잭션을 완료하면 그 트랜잭션 테이블 슬롯은 재사용 될 수 있음

-       하지만 변경된 블록들이 읽혀야 하는 시점에 Delayed 블록 클린아웃을 위해 트랜잭션 테이블 슬롯을 찾아갔는데, 해당 슬롯이 다른 트랜잭션에 의해 이미 재사용되고 없다면 정상적인 블록 클린아웃과 일관성 모드 읽기가 불가능해질 수 있음

n  트랜잭션 슬롯이 필요해지면 커밋 SCN이 가장 낮은 트랜잭션 슬롯부터 재사용하고, 그 슬롯에 기록돼 있던 커밋 SCN을 Undo 세그먼트 헤더에 ‘최저 커밋 SCN’으로서 기록. 때문에 트랜잭션 슬롯이 재사용되면 그 슬롯을 사용하던 이전 트랜잭션의 정확한 커밋 SCN 확인이 불가능하지만, Undo 세그먼트 헤더에 기록돼 있는 ‘최저 커밋 SCN’ 이전에 커밋 되었다는 사실은 짐작 가능.

u  따라서 클린아웃X인 블록을 클린아웃하려고 ITL이 가리키는 트랜잭션 테이블 슬롯을 찾아갔을 때 커밋 정보가 없다면, Undo 세그먼트 헤더에 있는 ‘최저 커밋 SCN’을 블록 ITL 엔트리에 커밋 SCN으로서 기록하고 블록 SCN도 변경하면 마무리가 지어짐

 

(3)   Snapshot too old 회피 방법

-       에러 발생 가능성을 줄이기 위한 애플리케이션 측면 해결 방법

n  불필요한 커밋X

n  Fetch across commit 형태가 아닌 다른 방식으로 구현한다. 커밋 이전에 열려있던 커서는 더는 Fetch하면 안됨. 구현이 어려우면 커밋을 줄인다

n  트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간 조정

n  큰 테이블을 일정 범위로 나누어서 단계적으로 실행 가능하도록 코딩, 그러면 에러 발생 가능성을 줄일 수 있고, 문제 발생 시 특정 부분부터 다시 시작 가능(읽기 일관성에 문제가 없을 시)

n  오랜 시간에 걸쳐 같은 블록을 재방문하는 Nested Loop 형태의 조인문이나 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고 그에 맞는 회피 방법 탐구

n  소트 부하를 감수하더라도 order by 등을 강제삽입하여 소트연산이 발생하도록 함. 많은 데이터를 오랜 시간에 걸쳐 Fetch 하는 동안 Undo 정보를 지속적으로 참조하기 때문에 문제가 발생하는 것이기 때문

n  Delayed 블록 클린아웃에 의해 에러발생이 의심되면 대량 업뎃 후 해당 테이블에 대해 Full Scan 쿼리를 날림

u  인덱스 블록에서 문제가 발생된다고 판단시, 인덱스 리프 블록을 모두 스캔하도록 쿼리

 

10. 대기 이벤트

(1) 대기 이벤트란?

- 오라클에서 하나의 프로세스가 일을 마칠 때까지 다른 프로세스는 일을 계속 진행할 수 있는 조건이 충족될 때 가지 수면상태에 빠지는데, 이를 대기 이벤트라고 함, 프로세스를 기다려야하는 상황에서 CPU를 쥔채 대기하면 불필요하게 CPU 자원을 낭비하는 것이라서 OS에 반환하고 수면상태에 빠짐

- 그때마다 그 상태정보를 파일 or SGA 메모리 내에 저장

- 선행 프로세스가 일을 마치면 OS에게 사실을 알려 수면상태의 프로세스를 깨우게함 →  OS는 그 프로세스를 runnable queue에 옮김으로써 가능한 한 빨리 CPU를 할당 받아 일을 재개할 수 있게 함

 

(2) 대기 이벤트는 언제 발생할까?

           1. 자신이 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용 중일 때

           2. 다른 프로세스에 의해 선행작업이 완료되기를 기다릴 때

           3. 할일 없을 때

 

(3)   대기 이벤트가 사라지고 프로세스가 활동을 재개할 때

                         i.         대기 상태에 빠진 프로세스가 기다리던 리소스가 사용 가능해질 때

                        ii.         작업을 계속 진행하기 위한 선행작업이 완료될 때

                       iii.         해야할일이 있을때

 

(4)   래치와 대기 이벤트 개념 명확화

-       래치를 얻는 가정 자체가 경합을 의미하는 것은 아님.

-       공유된 자원을 읽으려면 래치를 얻어야 함. 접근 요청이 많다고 문제될게 아님. 다만 다른 프로세스와 경합이 발생하는지를 두고봐야함

n  Gets : 래치 요청 횟수

n  Misses : 래치를 요청했는데 다른 프로세스에 의해 자원이 사용중이어서 첫 번째 시도에서 곧바로 래치를 얻지 못한 횟수 (get-misses=곧바로 래치획득에 성공한 횟수(simple_gets)

n  Spin_gets: 래치를 요청한 첫 번째 시도에서 곧바로 얻지는 못했지만 이후 spin하는 과정에서 래치 획득에 성공한 횟수(misses – sleeps)

n  Sleeps: 래치를 요청했지만 자원이 사용중이어서 곧바로 래치를 얻지 못했고 정해진 횟수만큼 계속 spin했지만 래치를 얻지 못해 대기상태로 빠진 횟수(이때 latch free 대기 이벤트 발생)

 

11. Shared Pool

           (1) 딕셔너리 캐시

           - 오라클 딕셔너리 정보를 저장해두는 캐시영역

           - Row 단위로 읽고 쓰기 때문에 로우캐시라고도 함

- 사용자가 Sequence 객체를 하나 만들면 오라클 딕셔너리에 저장이 되고, 로우 캐시를 거쳐 읽고 쓰기가 이루어짐. 사용자가 Sequence로부터 새 값을 인출하기 위해 nextval을 호출할 때 마다 로우캐시를 통해 업뎃이 이루어짐

* Sequence Cache 옵션 : 잦은 채번 = 로우캐시 경합 -> 해소를 위해 Cache 옵션 사용 필요, Cache 크기가 10이면 Nocache일 때보다 로우 캐시를 갱신하는 횟수가 1/10가량 감소한다. 기본 설정값은 20

- 딕셔너리 캐시의 활동성에 대한 통계조회가 가능한 뷰가 v$rowcache인데 여기서 히트히트 조사했을 때 수치가 낮게 나오면 Shared Pool 사이즈를 늘려야함

 

(2) 라이브러리 캐쉬

           - 사용자가 던진 SQL과 그 실행계획을 저장해 두는 캐시영역

           - 실행계획 : 빠른 쿼리 수행을 위해 내부적으로 생성한 일종의 프로시저

           - 같은 SQL에 대한 반복적인 하드파싱을 최소화하기 위한 새로운 캐시 공간

 

2. 트랜잭션과 Lock

           01. 트랜잭션 동시성 제어

           (1) 동시성 제어

- 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터의 무결성이 유지될 수 있도록 노력하는 것

- 여러 개 트랜잭션이 동시에 수행될 때, 데이터베이스 애플리케이션은 이들 트랜잭션이 서로 간섭을 일으키는 현상을 최소화하며 데이터의 일관성과 무결성이 보장되도록 개발되어야 함

           * 동시성: 다중 사용자가 같은 데이터를 동시에 액세스

* 일관성: 자신이 발생시킨 변경 사항과 다른 트랜잭션의 변경 사항을 포함해 일관성 있는 상태로 데이터를 제공

           - 동시성과 일관성은 반비례관계, 때문에 두마리 토끼를 다 같이 잡기 힘듬

 

(2) 트랜잭션이란?

- 여러 개의 수정작업이 하나의 작업처럼 전부 처리되거나 아예 전부 처리가 안되게 하는것

 

(4)   트랜잭션의 특징

1.     원자성: 더 이상 분해가 불가능한 업무의 최소단위

2.     일관성: 트랜잭션 실행의 결과로 데이터베이스의 상태가 모순되지 않음

3.     격리성: 트랜잭션이 실행 중에 생성하는 연산의 중간 결과는 다른 트랜잭션이 접근 불가

4.     영속성: 트랜잭션이 한 실행을 완료하면, 그 결과는 DB에 영속적으로 저장

-       트랜잭션의 처리결과가 일관성을 해치지 않도록 하려면 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화 장치가 필요 = Lock 매커니즘, 오라클에서는 Undo 데이터 활용

 

02. 트랜잭션 수준 읽기 일관성

(1) 트랜잭션 수준 읽기 일관성이란?

- 문장수준 읽기 일관성: 쿼리가 시작된 시점을 기준으로 데이터를 일관성 있게 읽어들이는 것

- 트랜잭션 수준 읽기 일관성: 트랜잭션이 시작된 시점을 기준으로 일관성 있게 데이터를 읽어들이는것(강화를 위해서는 트랜잭션 고립화 수준을 높여줘야함)

 

(2) 트랜잭션 고립화 수준

1. 레벨 0(= Read Uncommitted)

- 트랜잭션에서 처리중인, 아직 커밋X 데이터를 다른 트랜잭션이 읽는 것을 허용(→Dirty Read, Non-Repeatable Read, Phantom Read 현상 발생)

- 오라클X

           2. 레벨 1(=Read Committed)

                     - Dirty Read 방지: 트랜잭션이 커밋되어 확정된 데이터만 읽기 허용

                     - 대부분의 DBMS가 기본모드로 채택

                     - Dirty Read를 제외한 레벨 0에서의 문제점 여전히 발생

- DB2, SQL Server, Sybase의 경우 읽기 공유 Lock을 이용해 구현, 하나의 레코드를 읽을 때 Lock 설정, 빠져나가는 순간 해제

- 오라클은 쿼리시작 시점의 Undo 데이터 제공방식으로 구현

 

           3. 레벨 2(=Repeatable Read)

- 선행 트랜잭션이 읽은 데이터는 트랜잭션 종료시까지 뒤 트랜잭션이 갱신 or 삭제를 불허 → 같은 데이터를 두 번 쿼리했을 때 일관성 있는 결과 리턴

- Phantom Read 현상 여전히 있음

- DB2, SQL Server의 경우 트랜잭션 고립화 수준을 Repeatable Read로 변경하면 읽은 데이터에 걸린 공유 Lock을 커밋할때까지 유지하는 방식

- 오라클 X, but for update 절 이용하여 구현 가능, SQL Server 등에서도 for update 사용 가능하나 커서를 명시적으로 선언할 때만 사용 가능

 

4. 레벨 3(=Serializable Read)

- 선행 트랜잭션이 읽은 데이터를 뒤 트랜잭션이 갱신 or 삭제X, 중간에 새로운 레코드 삽입X

- 완벽한 읽기 일관성 모드

 

-       Set transaction isolation level serializable: 트랜잭션 고립화 수준 조정기능을 이용하여 읽기 일관성을 확보하는 명령문

-       낮은 단계의 트랜잭션 고립화 수준을 사용시 발생하는 현상

n  Dirty Read: 아직 커밋X 데이터를 다른 트랜잭션에서 읽기허용할 때 발생

n  Non-Repeatable Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행할 때, 그 사이에 다른 트랜잭션이 값을 수정 or 삭제함으로써 두 쿼리의 결과가 상이하게 나타나는 비일관성이 발생하는 것

n  Phantom Read: 한 트랜잭션 안에서 일정범위의 레코드들을 두 번 이상 읽을 때, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상(트랜잭션 도중 새로운 레코드가 삽입되는 것을 허용하기 때문에 발생)

-       오라클은 고립화 수준을 높이더라도 Lock을 사용하지 않아 동시성 저하 X