ORA 1555 SNAPSHOT TOO OLD 에러에 대한 이해

'ORA-1555 snap shot too old'  에러는 개발자들을 당혹케 만드는 오라클 에러 중 하나이다.

해당 에러에 대한 oerr 유틸의 메세지는 아래와 같다. 

[oracle@asmtest ~]$ oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

한 사용자가  A 쿼리를 날렸는데 해당 데이터 블록이 (내가 읽으려고 하는 시점 이전에 다른 사용자에 의해 )
변경이 되어, 롤백 세그먼트에서 해당 블록의 변경전 데이터를 찾으려고 하는데 이미 다른사용자가 
해당 롤백 레코드를 덮어 써버려서 이전 데이터를 찾을수 없어서 발생하는 에러. 

이를 그림으로 표현하자면 아래와 같다. 

 

개발자들이 이 현상을 이해하려면 아래 두가지에 대한 이해가 필요하다. 

- 읽기 일관성

- 언두(롤백) segment 의 매커니즘  

 

1. 읽기 일관성 ( Read consistency)

---

여기서는 읽기 일관성을 최대한 간단하게 설명해보겠다. (자세한건 검색으로..)

읽기 일관성이란 쿼리의 리턴 데이터들의 시점이 일관성이 있어야 한다는 것이다. 

즉, 위의 그림에서 쿼리 A 가 아무리 오래 걸려도 이들 결과 값은 같은 시점의 데이터여야 한다는 것이다. 

마치 쿼리 A 가 시작했을때, 해당 DB 의 그 시점의 스냅샷을 뜬것처럼 데이터를 리턴해야 한다는 것이다. 

위 그림의 예를 들면 table 이 존재하고, pk 가 aa 인 1 row 가 있다고 가정하자.

조회쿼리 Query A 가 08:00 시에 실행되었다고 치면, 그 당시에 col A 의 값은 1 이었다. 

그런데 다른 쿼리 Query B 가 해당 row 를 08:10 에 col A 를 2로 업데이트 했다. 

또다시 Query C 가 해당 row 를 08:15 에 col A 를 3 으로 업데이트 했다.

 그러면 08:00 에 시작한 Query A 가 08:16 분에 해당 row 를 읽으려고 하면 어떤 값을 return 해야하는가?

현재 데이터블록의 해당 row 값은 3 이지만, 읽기 일관성의 관점에서는 Query A 의 시작시점 값인 1 을 리턴해야한다. 

이를위해서 오라클은 과거시점의 데이터를 읽기일관성을 위해 DB의 공간에 저장할 필요가 있는데 

이곳이 언두(롤백) 세그먼트 이다.  

 

2. 언두세그먼트 의 매커니즘

---

언두세그먼트는 디비 전체 (정확히는 instance별) 에서 공유하는 resource 이다. 

설정된 공간을 계속해서 덮어쓰는 구조이다. 언두 공간이 부족하면 추가를 할수 있지만 아무런 기준없이 추가를 계속할수는 없고 관리자의 입장에서 사이트의 특성을 고려해서 전략적으로 선택해야한다.

- undo retention

 언두 사이즈도 중요하지만 언두데이터를 얼마의 시간동안 유지 할것인가도 중요하다. 

  유지시간을 너무 짧게 잡으면 금방 다른 데이터로 덮어씌여져 SNAPSHOT TOO OLD 가 발생할 수 있다. 

 반면 필요 이상으로 길게 잡으면 언두블록을 할당받아야할 세션들에 병목이 발생할수 있다.

 식당으로 치면 회전률이 안나오는 격이니 주인 입장에서는 골치가 아프다. 

 

3. 에러에 대한 조치

---

 - ORA-1555 가 발생한 쿼리가 너무 오래동안 수행되었다면 쿼리성능 개선을 고려해본다.

 - UNDO RETENTION 이 너무 짧다면 좀 더 길게 변경 해준다. 

- UNDO RETENTION 이 적정한데도 에러가 났다면 UNDO DATAFILE 을 추가해준다. 

- 업무시간 중에 대량의 DML 이 발생하지 않게 트랜잭션을 재배치 해준다.