<오라클 성능 고도화 원리와 해법1> Ch01-09 Snapshot too old

오라클 성능 고도화 원리와 해법1 Ch01 오라클 아키텍처 - 09 Snapshot too old

Snapshot too old(ORA-01555)에 대한 정보를 조회해보면 그 발생 원인을 여러 가지로 나누어 설명하고 있는데, 요약하면 두 가지로 정리할 수 있다(undo 세그먼트가 손상되는 등의 물리적인 원인은 제외).

첫째, 데이터를 읽어 내려가다가 쿼리 SCN 이후에 변경된 블록을 만나(이미 앞서 읽었던 블록을 다시 방문하는 경우일 수도 있음) 과거 시점으로 롤백한 ‘Read Consistent’ 이미지를 얻으려고 하는데, Undo 블록이 다른 트랜잭션에 의해 이미 재사용돼 필요한 Undo 정보를 얻을 수 없는 경우(그림 1-16)다. 프로그램 코딩 패턴에 문제가 없다면 Undo 세그먼트가 너무 작다는 신호일 수 있다.

둘째, 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인할 수 없는 경우(그림 1-17)로서, Undo 세그먼트 개수가 적다는 신호일 수 있다.

(1) Undo 실패

Undo 블록을 찾을 수 없어 에러가 발생하는 경우부터 살펴보자. 고객별 미납금액을 계산하는 아래 쿼리가 1시간쯤 걸린다고 가정하자.

여러 세션에서 동시에 트랜잭션이 몰리지 않더라도 아래와 같은 프로그램이 한 세션에서 독립적으로 수행되는 도중에 Snapshot too old 에러가 발생할 수도 있다.

위와 같은 코딩 패턴을 ‘fetch across commit’이라고 한다. 명시적으로 커서를 열어 로우를 하나씩 Fetch 하면서 값 변경하고 루프 내에서 계속해서 커밋을 날리는 방식이다. ANSI 표준에 따르면 열려있던 커서는 커밋하는 시점에 무효화되어야 하므로 사용자는 계속해서 Fetch 해서는 안된다. 오라클은 ANSI 표준에도 불구하고 사용자가 fetch across commit을 할 수 있도록 허용하고 있지만, 그렇게 할 경우 에러가 발생할 수 있다는 사실을 인지한 상태에서 프로그램을 작성해야 한다.

(2) 블록 클린아웃 실패

트랜잭션 테이블 슬롯이 재사용돼 에러가 발생하는 경우를 살펴보자.

대량 업데이트 후에 커밋된 트랜잭션은 변경했던 블록들을 모두 클린아웃하지 않은 상태에서 자신이 사용하던 트랜잭션 테이블 슬롯을 Free 상태로 변경하고 트랜잭션을 완료한다. 이때부터 그 트랜잭션 테이블 슬롯은 다른 트랜잭션에 의해 재사용될 수 있다. 시간이 흘러 그 변경된 블록들이 읽혀야 하는 시점에 Delayed 블록 클린아웃을 위해 트랜잭션 테이블 슬롯을 찾아갔는데, 해당 슬롯이 다른 트랜잭션에 의해 이미 재사용되고 없다면 정상적인 블록 클린아웃과 일관성 모드(Consistent Mode) 읽기가 불가능해질 수 있다(그림 1-17 참조).

하지만 Undo 레코드를 뒤졌는데 그마저도 덮어 쓰이고 없는 상태라면 어떻게 해야 할까?

하지만 블록 클린아웃에 의한 Snapshot too old 에러는 거의 발생하지 않는다.

결론적으로 Delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 원인은, ‘최저 커밋 SCN’이 쿼리 SCN보다 높아질 정도로 갑자기 트랜잭션이 몰리는데에 있으며, 이때는 추정에 의한 블록 SCN이 쿼리 SCN보다 높아지게 된다. 실제로 이 에러를 발생시킨 블록은 훨씬 오래전 시점(1년 또는 10년 전)에 커밋된 것일 수 있지만 이를 확인할 방법이 없으며, 그동안 한 번도 읽히지 않다가 불행하게도 트랜잭션이 몰리는 시점에 읽히다 보니 문제를 유발하게 되는 것이다.

(3) Snapshot too old 회피방법

Snapshot too old 에러를 만나면 난감하기 그지없다. 사실상 이 에러는 재현이 어려워 문제의 발생 원인을 찾는 것부터 쉽지 않다. Undo 메커니즘을 도입한 배경으로 거슬러 올라가면 결국 Lock에 의한 동시성 저하를 방지하려는 것인데, 동시성을 높인 대신 얻은 부작용(side effect) 정도로 이해해야 할 것이다.

9i부터는 AUM(Automatic Undo Management)이 도입돼 트랜잭션 발생 상황에 따라 Undo 세그먼트 크기와 개수를 오라클이 동적으로 자동 조절해주기 때문에 DBA의 수고가 많이 줄었고 Snapshot too old 에러가 발생할 가능성도 확실히 줄었다. DBA가 할 일이라고는 커밋된 Undo 정보가 충분히 오랫동안 보존될 수 있을 정도(가장 오랜 시간 수행되는 쿼리 기준으로 판단)의 큰 Undo Space를 준비해주는 것이 고작이다. 9i에서 도입된 undo_retention 기능까지도 10g부터는 오라클이 알아서 스스로 튜닝해주고 있다.

그런데 AUM 하에서 충분한 크기의 Undo Space를 할당하는 것만으로 Snapshot too old 에러가 완전히 해결된 것은 아니다. DB 관리자 측면에서의 Undo 튜닝 노력은 많이 해소되었지만, 애플리케이션 구현 측면에서의 노력은 여전히 필요하다. 이 에러가 종종 발생한다면 프로그램 로직을 분석해서 가능성 있는 부분들을 고쳐주어야 한다. 이를 위해 지금까지 설명한 오라클만의 독특한 읽기 일관성 모델과 Undo 메커니즘을 이해하는 것이 필수적이다.

Snapshot too old 에러 발생 가능성을 줄이기 위한 애플리케이션 측면에서의 솔루션들을 몇 가지 소개하겠다.

1. 불필요하게 커밋을 자주 수행하지 않는다.
2. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현한다. ANSI 표준에 따르면 커밋 이전에 열려있던 커서는 더는 Fetch하면 안된다. 다른 방식으로 구현하기 어렵다면 커밋 횟수를 줄여본다.
3. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간을 조정한다.
4. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩한다. Snapshot too old 발생 가능성을 줄일 뿐만 아니라 문제가 발생했을 때 특정 부분부터 다시 시작할 수도 있어 유리하다. 물론 그렇게 해도 읽기 일관성에 문제가 없을 때에만 적용해야 한다.
5. 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested Loop 형태의 조인 문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고, 이를 회피할 수 있는 방법(조인 메소드 변경, Full Table Scan 등)을 찾는다.
6. 소트 부하를 감수하더라도 order by 등을 강제로 삽입해 소트 연산이 발생하도록 한다. 많은 데이터를 오랜 시간에 걸쳐 Fetch하는 동안 Undo 정보를 지속적으로 참조하기 때문에 문제가 발생하는 것이므로, 서버 내에서 빠르게 데이터를 읽어 Temp 세그먼트에 저장하는 데에만 성공하면 이후에는 같은 블록을 아무리 재방문하더라도 더는 에러가 발생할까 걱정하지 않아도 된다.
7. 만약 delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 것으로 의심되면 대량 업데이트 후에 곧바로 해당 테이블에 대해 Full Scan 하도록 쿼리를 날리는 것도 하나의 해결 방법이 될 수 있다.