ORA-01555, oracle, 오라클, 01555, Snapshot too old

ORA-01555 .. 이해하기 쉬운버전.. 

 java.sql.SQLException: IO 예외 상황: The Network Adapter could not establish the connection

No. 10105

ORA-1555 Snapshot Too Old 에러가 발생하는 원인은 여러가지가 있을 수 있다.

이를 이해하기 위해서 오라클의 내부 메카니즘에 대해서 알아볼 필요가 있다.
오라클은  항상 Statement-Level Read Consistency를 유지한다.  즉, 하나의
Query 에서 읽어들이는 값은  그 Query가 발생한 시점의 값,  즉 Snapshot 을
반영한다.  따라서 Query 가 진행되는 동안  데이타가 변하더라도 Query의
결과값에는 아무런 영향을 끼치지 않는다.

오라클은 특정한 시점을 SCN(System Change Number)로 관리한다. 달리 말하면
SCN은 특정한 시점에서의 데이타베이스의 상태라고도 할 수 있다. Read
Consistency를 유지하기 위하여 Query가 실행되는 순간 해당 시점의 SCN이
세팅된다. 어떠한 Query든 조회 시점 이후의 SCN 값을 갖는 데이타를 읽어들여
서는 안된다.

  Read Consistent Snapshot을  구현하기 위한 방법으로 롤백 세그먼트가
사용된다. 데이타가 변경되면 변경되기 이전의 값은 롤백 세그먼트에 기록되고
데이타 블럭 헤더에는 변경되기 이전 값이 기록된 롤백 세그먼트 블럭의 위치가
기록된다. 모든  데이타 블럭에는 가장 최근의 커밋 시점의 SCN이 기록되어
있다. Query 가 진행되면 데이타 블럭에서는 Query의 SCN 보다 이전의 SCN값을
갖는 블럭만이 읽혀지고 그 이후의 SCN 값을 갖는 블럭과 커밋되지 않은 값을
갖는 블럭에 대한 Query는 롤백 세그먼트에서 읽혀지게 된다. 이 때 만약 롤백
세그먼트에 원하는 블럭이 없어서 읽어들이지 못하게 되면  ORA-1555 에러가
발생하게 된다.

  롤백 세그먼트는 트랜잭션이 끝나기 전까지 변경된 데이타의 원래 상태를
기록하고 있다. 그런데 트랜잭션이 종료되면 롤백 세그먼트에 의해 점유된
영역은 Free  상태로 되면서 다른 트랜잭션에 의해서 덮어씌워질 수도 있게
된다. 따라서 Query가 필요로하는 값을 가진 롤백 세그먼트 블럭이 더 이상
존재하지 않는 상황에서 그  블럭을 요구하게 되면 ORA-1555 에러가 발생하게
되는 것이다.

? ORA-1555 에러가 발생하는 경우

  1.   데이타의 변경이 심한 데이타베이스에서 롤백 세그먼트의 갯수와 크기가
작을 경우에 발생한다. 많은 트랜잭션이 데이타를 자주 변경하고 커밋하게 되면
커밋된 트랜잭션이 이용하던 롤백 세그먼트 공간을 다른 트랜잭션이 이용하게
될 가능성이 많아진다.
   따라서 긴 Query의 경우 원하는 값을 롤백 세그먼트에서 얻고자 할 때 이미
다른  트랜잭션이 그 값이 저장된 공간을 이용해 버리는 결과가 발생할 수
있다. 이와 같은 경우에는 크기가 큰 롤백 세그먼트를 이용하면 어느 정도 예방이
가능하다.

  2.  롤백 세그먼트가 손상되어 읽을 수 없게 된 경우

  3.  Fetch Across Commit

   한 테이블에 대하여 Query가 커서를 열고 루프 내에서 데이타를  Fetch하고
변경하고 커밋하는 과정에서 발생한다. 이 경우에는 ORA-1555 에러가 자주
발생할 소지가 있는데 예를 들어 커서가 SCN=10 에 Open되었다고 하자. 따라서
이 커서에 관련된 Fetch는 SCN<=10 인 블럭만을 읽어들여야 한다. 이
프로그램은 데이타를  Fetch 한 후에 변경하고 다시 커밋하는 과정을 계속
반복하는데 SCN=20에서 커밋했다고 하자. 만약 이후의 Query가 이전의 커밋된
블럭의 데이타를 요구할 경우  그  값이 이미 변경되었으므로 롤백 세그먼트를
검색 하지만  많은  변경이  있어왔기  때문에 SCN=10인 블럭을 찾지 못하고
ORA-1555 에러를 유발할 수 있다. 이를 방지하기  위해서는 커서가 Open된
상태에서는 커밋을 자주하지 않고 롤백 세그먼트 크기를 키워 나가도록 한다.

   4.  Fetch Across Commit 와 Delayed Block Clean Out

   Delayed Block Clean Out 도 이 에러를 유발할 수 있다. 데이타 블럭이
변경되고 커밋되면 오라클은 롤백 세그먼트  헤더에 그 트랜잭션이 커밋되었다
고  기록하지만 데이타 블럭을 바로 변경하지는 않는다 (Fast Commit). 그리고
다음 트랜잭션이 변경된 블럭을 요구할 때야 비로소 변경 시키는데 이를
Delayed  Block Clean Out 이라고 한다.    
   예를 들어   3 과 같은 경우에서 두개의 테이블에 대한  Query를 알아보자.
즉, 하나의 테이블로부터 데이타를 Fetch 하고 다른 테이블의  데이타를
변경한다고 하자. 데이타가 한쪽  테이블에서 커밋되고 있지만 데이타를  
가져오는 테이블에 대해서는 Clean Out이 이루어지지 않았기 때문에  ORA-1555
에러가 발생할 수 있다. 이 문제는 커서를 사용하기 전에 Full Table Scan을
해주면 예방이 가능하다.
   커서가 오픈된 상태에서의 커밋은 ANSI 표준에는 들어있지  않지만  오라클
에서는 지원이 된다. 하지만 ORA-1555 에러를 일으킬 수도 있다는 점에  유의
하여야  한다.

 

[Oracle] ORA-01555 Snapshot too old 에러 원인과 해결 방법

오라클 ORA-01555 에러

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

ORA-01555: snapshot too old:rollback segment number %s with name "%s" too small

위와 같이 메시지가 출력되며 ORA-01555는 데이터 훼손이나 데이터 손실과는 아무 관련이 없습니다. 그런 점에서 안전한 오류이고, 단지 이 오류를 만나는 순간 쿼리를 계속 진행할 수 없습니다. 최근 이 에러를 맞닥뜨리게 되어 원인 및 해결방법을 공유하고자 합니다.

에러 발생 시나리오

먼저 ORA-01555가 발생할 수 있는 시나리오의 예를 타임라인을 통해 보겠습니다. UNDO 세그먼트, 일관적인 읽기 등의 용어에 대해 익숙치 않은 경우에도 우선 다음 내용을 읽어보시길 바랍니다.

블록이 1,000,000개가 있는 테이블이 있다. 이에 대해 느린 쿼리를 실행하고 ORA-01555 에러가 발생하는 타임라인의 예이다.

 

시간(분:초)	진행사항
0:00	쿼리 시작
0:01	다른 세션이 블록 1,000,000을 UPDATE 한다. 이 블록에 대한 UNDO 정보는 UNDO 세그먼트에 기록한다.
0:01	이 UPDATE 세션이 COMMIT 한다. 이 세션이 생성한 UNCO 데이터는 아직도 그대로 남아 있지만, 이후로 공간이 필요해지는 순간 언제든 덮어 쓰일 수 있다.
1:00	쿼리 진행이 블록 200,000에 도달했다.
1:01	많은 활동 이 계속 진행되어 그 만큼 많은 UNDO 데이터를 생성 했다.
3:00	쿼리 진행이 블록 600,000에 도달했다.
4:00	UNDO 세그먼트가 덮여 쓰여지기 시작 하고 쿼리를 시작할 때 활성화되어 있던( 트랜잭션이 진행 중이던) 공간을 재사용한다. 특히, 0:01에 블록 1,000,000 에 대한 UPDATE 시 사용되었던 UNDO 세그먼트 공간을 방금 재사용했다.
5:00	쿼리가 블록 1,000,000에 도달했다. 쿼리가 시작한 이후로 그 블록이 변경되었음을 발견한다. 일관적인 읽기를 위해 UNDO 세그먼트로 가서 그 블록에 대한 UNDO 를 찾으려고 시도한다. 이 시점에, 필요한 UNDO 정보가 더 이상 존재하지 않음 을 발견하고 ORA-01555 에러와 함께 쿼리가 실패한다.

원인

이 오류의 원인을 한 줄로 정의하면 다음과 같습니다.

ORA-01555 에러는 쿼리를 수행하는 동안 발생하는 다른 Transaction들에 의해 UNDO 세그먼트(롤백 세그먼트) 덮어씌워져서 발생한다.

 

1. UNDO 세그먼트
- 언두 세그먼트는 첫째, 실패한 트랜잭션을 롤백하고 둘째, 읽기 일관성을 제공하는 두 가지 용도를 가진다. 이를 위해 트랜잭션에 대한 UNDO 정보/UNDO 데이터를 UNDO 세그먼트에 기록한다.
- 이 UNDO 세그먼트는 만료되거나 공간 재사용에 의해 덮어씌워질 여지가 있다.
- 이로 인해 해당 쿼리 결과에 대한 읽기 일관성이 유효하지 않게 되는 것이 ORA-01555 에러의 핵심이다.

1-1. 읽기 일관성
읽기 일관성이란 쿼리 결과는 미리 확정돼있고 오라클이 첫 번째 행을 조회하러 가기도 전에 결과가 잘 정의되어 있다는 의미이다. 오라클은 쿼리가 수행된 이후로 변경된 블록을 롤백하기 위해 언두 세그먼트를 사용하여 DB 스냅샷을 일관적인 시점을 기준으로 제공한다. 테이블을 조회하는 모든 쿼리는 읽기 일관성을 이용한다.

2. UNDO 기록이 만료되거나 덮어씌워지는 이유
2-1. 만료
- 현재 시점에서 행의 커밋시간을 뺀 시간이 UNDO_RETENTION 보다 크다는 것을 의미

2-2. Overwrite
- UNDO 테이블스페이스가 가득 차서 'UNEXPIRED' 언두 레코드가 덮어씌워지고 있는 상황
- 해당 UNDO 테이블스페이스에 대해 RETENTION GUARANTEE 가 활성화 되지 않음
- 쿼리를 실행하는 동안 덮여 쓰여질 가능성이 있는 크기로 언두 세그먼트를 설정한 상태에서 변경될 수도 있는 데이터를 그 쿼리가 액세스할 때

2-2. 작업량에 비해 작은 언두 세그먼트
2-3. 쿼리 수행 시간이 오래 걸릴 경우
쿼리를 실행하는 데 장시간이 소요되면 실행하는 동안 다른 트랜잭션에 의해 롤백 세그머트 정보가 덮어 쓰여질 것이므로, 쿼리를 시작한 이후로 생성된 언두 정보가 없어질 수 있다.

2-4. 데이터를 fetch하는 중간중간에 commit(fetch across commit)하도록 프로그래밍했다

즉 작업량에 비해 UNDO 세그먼트가 작거나 쿼리 수행 시간이 오래 걸리는 것과 같은 이유로 인해 현재 쿼리를 실행하는 동안 다른 트랜잭션에 의해 UNDO 세그먼트 정보가 Overwrite 되어 읽기 일관성이 유효하지 않아 발생한다고 정리할 수 있습니다.

해결 방법

1. 쿼리 튜닝을 통한 수행 시간 단축
- ORA-01555 오류의 핵심 원인을 방지하는 가장 최선의 방법이며 가장 먼저 시도해야 한다.
- 쿼리 수행 시간 단축은 더 큰 언두 세그먼트에 대한 필요성을 줄여준다.

2. 적절한 UNDO 세그먼트 사이즈의 구성
쿼리를 실행하는 동안 덮여 쓰여질 가능성이 있는 크기로 UNDO 세그먼트를 설정한 상태에서 변경될 수도 있는 데이터를 그 쿼리가 액세스한다면, ORA-01555 에러가 발생할 수 있다.

2-1. UNDO_RETENTION 파라미터를 적절하게 설정한다
- 가장 오래 수행하는 트랜잭션에 걸리는 시간보다 많게 설정한다.
- V$UNDOSTAT는 장시간 수행되는 쿼리에 걸리는 시간을 확인하는 데 사용될 수 있다.
- 요청한 UNDO_RETENTION을 근거로 필요한 만큼 UNDO 세그먼트 크기를 증가시킬 수 있도록 디스크에 충분한 공간을 확보해야 한다.

2-2. 수동 UNDO 관리를 사용할 때는 UNDO 세그먼트를 늘리거나 좀 더 많은 UNDO 세그먼트를 추가한다.
- 그러나 그리 권장할 만한 방법은 아니며, 자동 UNDO 관리가 추천된다.

쿼리 튜닝(Hint 추가)를 통해 해당 에러를 해결할 수 있었습니다. 운영중인 시스템에서 어느날 갑자기 UNDO 세그먼트의 사이즈가 변경될 일은 거의 없으니, 갑자기 이 에러가 발생하는 경우에는 우선 쿼리 변경 이력이 있는지 검토하고 이력이 있을 경우 쿼리의 성능 분석 및 튜닝을 먼저 시도해보시기를 추천합니다

 

ORA-01555 Snapshot too old 를 바라보는 우리의 자세

ORA-01555 snapshot too old 에러는 일반적으로 쿼리 수행시간이 오래 걸려서 발생하는 undo 관련 에러입니다. 오라클에서는 읽기 일관성을 보장하기 위해 select 문장을 수행한 시점의 데이터를 client 로 반환을 하게됩니다. 쿼리가 수행되고 있는 중간에 데이터가 변경이 되고 commit 되어도 select 수행시점의 과거 데이터 스냅샷을 undo 에서 읽어들입니다. 하지만 undo 의 공간은 overwite 되는 공간이므로 과거 데이터를 읽지 못할 때 스냅샷이 오래되었다는 해당 에러가 발생합니다. 

해결방안은 쿼리 성능을 개선하는 방법이 가장 좋습니다.^^

Undo Size를 크게 하는 방법도 있긴하지만, 대부분의 운영DB는 이미 적절한 Undo Size로 충분히 셋팅 되어 있는 경우가 많습니다. 물론 MOS 찾아보면 ORA-01555를 진단하고 해결하는 다양한 방법들을 제공합니다.

ORA-01555 "Snapshot too old" in Very Large Databases (if using Rollback Segments) (문서 ID 45895.1)

Solution 4c:     ------------     Add an "order by 1" statement to the query.  This creates a temp      segement to hold the results of the order by and will not require      consistent read.    쿼리 마지막줄에 order by 1 를 추가하는 재미있는 방법도 소개되고 있습니다. 이렇게 하면 temp segment 에 result를 holding 시키기 때문에 ORA-1555 snapshot too old 에러 발생을 억제시킬 수 있습니다. 단점이 치명적인데요. fetch 시작하기까지 시간이 좀 걸립니다.

Explaining ORA-1555 Error (문서 ID 467872.1)

오라클 입장에서는 굴욕적일 수도 있는 방법도 있습니다. 바로 table full scan 시 SCN_ASCENDING 힌트를 적용하는 방법입니다. 이 힌트를 사용하면  select 수행한 시점의 읽기일관성을 보장하지 않는 대신, 에러 발생하지 않고 쿼리가 정상적으로 수행될 수 있다는 겁니다.

테스트 해놓은 괜찮은 사이트가 있네요.

Read Consistency, "ORA-01555 snapshot too old" errors and the SCN_ASCENDING hint

Oracle uses for its read consistency model a true multi-versioning approach which allows readers to not block writers and vice-versa, writer...

oracle-randolf.blogspot.com

https://oracle-randolf.blogspot.com/2009/04/read-consistency-ora-01555-snapshot-too.html

scn_ascending 힌트를 사용하면 select 수행 이후에 SCN이 변경된 cr 블록이 아닌 curern블록을 그대로 읽습니다. 하지만 dirty read 는 아니며 commit 된 데이터에 한해서 읽습니다

예를들어 select 시점에 SCN 100이라면, table을 full scan중에 scn이 200인 블록을 만났을 경우 undo에서 SCN 100인 블록을 찾아서 읽지않고 SCN 200인인 블록을 그대로 읽게 됩니다. 즉,  "읽기 일관성"을 유지할 수 없습니다. fault tolerance 가 어느 정도 허용되는 시스템이라면 유용할 수도 있을 거란 생각이듭니다.

하지만, 오라클의 고질병인 ORA-01555의 문제를 이런식으로 밖에 풀수 없었다는 것은 오라클 입장에서는 굴욕이 아닐까~ 조심스럽게 생각합니다.

Snapshot too old 에러는 Oracle DBMS 의 Undo Segment (롤백세그먼트) 와 관련된 에러입니다. 여기에서 Snapshot 이란 Oracle Materialized View 의 옛이름인 Snapshot 과는 아무 관계가 없습니다. 그냥 Undo Segment 에 저장된(스냅샷된) 옛날 블록 이미지란 의미입니다.

아래와 같은 시나리오에서 ORA-01555 에러를 만나게 됩니다.

  1) P1 세션이 T1 테이블의 1번째 블록부터 순차적으로 조회를 시작했습니다.

  2) P2 세션이 T1 테이블의 20000번째 블록 데이터를 변경해버렸습니다.

      (변경된 데이터는 롤백세그먼트로 이동합니다.)

  3) 그런데, 다른 세션들이 변경 작업이 많아서 롤백세그먼트에 들어있는 T1 테이블의 20000번째 블록이 날라갔습니다.

      (롤백세그먼트는 재활용되어 사용되어지기 때문에 흔한 일입니다)

  4) P1 세션이 T1 테이블의 20000번째 데이터를 조회를 하려고 하는데, 없습니다.

      (여기에서 ORA-01555 에러가 납니다)

즉, Snapshot too old 에서 Snapshot 이란 이전 변경전 블록이미지를 롤백세그먼트에 스냅샷(보관) 찍어 놨음을 의미합니다. 이 스냅샷 찍어놓은 이미지가 너무 오래되어(too old) 날라가는 통에 롤백세그먼트에 없으니 조회를 할 수 없음을 의미합니다. 여기에서 다음과 같은 상황이었다면, P1 세션은 ORA-01555 에러를 만나지 않았을 가능성이 높습니다.

   - 롤백세그먼트(Undo Segment) 가 충분히 큰 경우 (재활용되어 20000번째 Old 이미지가 날라갈 확률이 적어집니다)

   - P1 세션의 조회작업이 빨리 끝나버리는 경우 (Long SQL 은 ORA-01555 에러를 만날 가능성이 더 많아집니다)

결론적으로 ORA-01555 에러의 조치방법은

   - 롤백세그먼트 크기를 늘려주고,

   - Long SQL 을 튜닝해서 빨리 끝날 수 있도록

하면 되겠습니다.

 

[Oracle] 오라클 에러 ORA-01555: Snapshot too old

Snapshot too old, 말 그대로 Snapshot이 너무 오래됐다는 말이다. Snapshot이 뭔데?

* Snapshot : Data Buffer Cache 블록에 갱신이 일어날 때 복사해 놓은 Copy본 (CR 블록)

CR 블록이 뭔지 모르겠다면 

☑[Oracle] 오라클 다중 버전 읽기 일관성 모델 알아보기

그럼 오라클에서 이런 에러가 나는 이유에 대해 알아보도록 하자.

Undo 실패

- 데이터를 읽어 내려가다가 쿼리 SCN 이후에 변경된 블록을 만나 과거 시점으로 롤백한 'Read Consistent' 이미지를 얻으려고 하는데, Undo 블록이 다른 트랜잭션에 의해 이미 재사용 돼 필요한 Undo 정보를 얻을 수 없는 경우 (Undo 세그먼트가 너무 작을때)

① SCN 123 시점에 1시간 쯤 걸리는 조회 쿼리가 시작됨

② 쿼리가 진행되는 동안 특정 row의 값이 Update 된 후 Commit 됨

→ 그 row를 담고있던 블록의 SCN이 129로 변경됨

③ 얼마후 ②번의 내용을 담고 있던 Undo 블록이 다른 트랜잭션에 의해 재사용

④ ①에서 돌던 쿼리가 특정 row에 도달했을때는 이미 Undo 블록이 덮어쓰여버린 후

▶ Snapshot too old 에러 발생, ①번 쿼리 실행 멈춤

1 for C in (select /*+ ordered use nl(b) */ A.고객ID，A.입금액，B.수납액

2             from 은행입금 A, 수납 B

3            where A.입금일자 = trunc(sysdate)

4              and B.수납년월(+) = to char(sysdate, 'yyyymm')

5              and B.고객ID(+) = A.고객ID)

6 loop

7    if C.수납액 IS NULL then

8       insert into 수납(고객ID，수납년월, 수납액)

9       values ( C.고객ID, to char(sysdate, 'yyyymm'), C.입금액)

10   else

11      update 수납 set 수납액 = 수납액 + C.입금액

12       where 고객ID = C.고객ID

13         and 수납년월 = to_char(sysdate, 'yyyymm')

14   end if;

15   commit;

16 end loop;

① SCN 100 시점에 커서 C 오픈

② 11~13번 라인 update 문에 의해 홍길동 고객의 수납액이 변경되고 15번 라인에서 커밋

→ 변경된 레코드가 담긴 500번 블록 SCN이 120으로 변경됨

* 프로그램 내에서 커밋을 반복하기 때문에 위 insert문과 update문은 모두 개별 트랜잭션 처리

③ 트랜잭션이 계속 반복되기 때문에 홍길동 고객의 수납액 Before Image가 담긴 Undo 블록이 다른 트랜잭션에 의해 재사용 되는 것은 시간 문제, 결국 재사용 되어버림

④ 커서 C가 홍길동 고객과 같은 500번 블록에 저장된 김철수의 수납액을 변경하려고 하는 찰나 Snapshot too old 에러 발생

※ fetch across commit

명시적으로 커서를 열어 row를 하나씩 Fetch하면서 값을 변경하고 loop 내에서 계속해서 commit을 날리는 방식

 

블록 클린아웃 실패

* 가정: 대량 업데이트 후 커밋된 트랜잭션이 블록 클린아웃 없이 자신이 사용하던 트랜잭션 테이블 슬롯을 Free 상태로 변경

① SCN 90 시점에 쿼리 수행, 클린아웃 되지 않은 블록 Access

② Delayed 블록 클린아웃을 위해 트랜잭션 테이블 슬롯 방문했으나 해당 슬롯이 다른 트랜잭션에 의해 이미 재사용되고 없음

→ 정상적인 블록 클린아웃과 일관성 모드 읽기가 불가능해질 수 있음

③ Undo 레코드에 기록된 Undo 세그먼트 헤더 블록 갱신 내역을 찾아 롤백 시도

 

* 사실상 블록 클린아웃에 의한 Snapshot too old 에러는 거의 발생하지 않음

- 트랜잭션 슬롯 필요 시 커밋 SCN이 가장 낮은 트랜잭션 슬롯부터 재사용

- 그 슬롯에 기록돼 있던 커밋 SCN을 Undo 세그먼트 헤더에 'low commit SCN'으로 기록

- 아직 클린아웃되지 않은 블록을 클린아웃하려고 ITL이 가리키는 트랜잭션 테이블 슬롯을 찾아갔을 때 커밋 정보가 이미 지워지고 없으면, Undo 세그먼트 헤더에 있는 'low commit SCN'을 블록 ITL 엔트리에 커밋 SCN으로서 기록(추정된 커밋 SCN: C-U-)

(대부분의 low commit SCN은 쿼리 SCN보다 작으므로 정상적인 일관성 모드 읽기가 가능)

* low commit SCN이 쿼리 SCN보다 높아질 정도로 트랜잭션이 몰리는 경우에만 에러 발생

 

Snapshot too old 회피 방법

- 불필요한 커밋 지양

- fetch across commit 형태의 프로그램 작성 지양

- 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리 수행 지양

- 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩

- Nested Loop Join 지양

- order by 강제 삽입 (서버 내에서 빠르게 데이터를 읽어 Temp 세그먼트에 저장)

- 대량 업데이트 후 해당 테이블에 대해 Full Scan 유도

 

오라클 데이터베이스 기술지원 뉴스 제57권- 2015년 11월호 (문서 ID 1529796.1)

오라클 데이타베이스를 관리하다보면 아마도 ORA-01555: snapshot too old 에러를 만나게 될 수 있습니다. 이 에러에 대한 원인 분석  및 문제해결 방법에 대해 알고 싶으시다면 아래 내용들을 읽어주시기 바랍니다.

ORA-01555: snapshot too old: rollback segment number XX with name "XX XX XX XX XX" too small

ORA-1555 에러는 쿼리수행시 데이타 복사를 위해 관련 언두(UNDO) 정보에 접속할 수 없을 때 발생합니다. 커밋된(commit) 블럭의 "versions" 과 커밋되지 않은(uncommitted) 블럭의 "versions" 들로 관리가 되어 쿼리가 시작할 때의 데이타베이스에 존재하는 데이타에 접근할 수 있게됩니다. 이는 "읽기 일관성" 블럭으로 알려져 있고, 오라클 언두 관리에 사용되어집니다.

아래 타임라인에 따른 에러발생 예제를 살펴 보시기 바랍니다.

타임 1	세션 #1 이 테이블 A 에 쿼리를 시작합니다.
타임 2	세션 #2 에서 테이블 A의 행 X 에 대해 업데이트 합니다.
타임 3	세션 #1의 쿼리가 행 X 에 다달았고, 타임 1 이후(SCN number 에 기반하여) 에 마지막 변경시각을 확인합니다. 이때 언두 또는 LOB 세그먼트(세그먼트 유형에 따라 다름) 가 읽기 일관성 뷰로 사용되고 쿼리가 계속 수행됩니다.
타임 4	세션 #2 가 A 테이블에서 행 Y 를 업데이트 합니다. 그리고 커밋을 수행합니다.(이로 인해 이 트랜잭션 슬롯이 덮어씌워질 수 있습니다.)
타임 5	세션 #2 에서 테이블 B 의 행 Z 를 업데이트 합니다. 공간부족으로 인해 타임 4 의 테이블 A 의 행 Y 는 읽기 일관성 뷰는 덮어씌워집니다.(이 시점에 덮어씌워졌는지에 대해서는 확인하지 않습니다.)
타임 6	세션 #1 의 쿼리가 행 Y 에 다다르고, 타임 1 이후에 (SCN number 에 기반하여) 행의 변경사항을 확인하려합니다. 하지만 뷰를 보관하고 있던 트랜잭션 슬롯이 타임 5 에 덮어 씌워졌고 읽기 일관성 뷰가 없어 ORA-1555 를 발생시킵니다.

노트: 언두(UNDO) 는 System managed UNDO(또는 Automatic Undo Management AUM) 또는 롤백 세그먼트를 의미합니다.

에러는 어떤 파일에 기록되나요?

에러는 일반적으로 아래 파일들에 기록됩니다.

• Alert Log 파일
  아래와 같은 메시지로 기록됩니다."
  ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

• 이슈가 발생할 시각에 트레이스 파일이 생성됩니다.
  기본적으로 1555 이벤트를 설정하지 않으면 ORA-1555 발생시 자동으로 트레이스 파일을 생성하지 않습니다.
  alter system set events '1555 trace name errorstack level 3';
  위와 같이 설정하는 경우, 1555 에러가 발생하면 트레이스 파일명은 아래 예제와 같이 alert 로그에 기록됩니다.:
  Wed Jul 22 09:20:11 2015
  Errors in file /u01/sq/diag/rdbms/DB1/ DB1/trace/ DB1_ora_35236.trc:
  ORA-01555: snapshot too old: rollback segment number 34 with name "_SYSSMU34_417799848$" too small

 에러가 왜 발생하나요?

이 이슈의 근본적인 원인은 쿼리가 수행될 시점에 데이타 복사본을 쿼리가 가져갈 수 없게 덮어씌워졌기 때문입니다. 일반적으로 알려진 이슈들은 아래와 같습니다.

• 행의 UNDO 기록이 만료되었습니다.
  이 말은 현재 시점에서 행의 커밋시간을 뺀 시간이 UNDO_RETENTION 보다 크다는 것을 의미합니다. UNDO 기록이 행을 커밋하여 만료(expired) 되면 재사용이 가능합니다.
  아래와 같은 문의가 있을 수 있습니다.
  왜 어떤 쿼리는 ORA-1555 발생전에 UNDO_RETENTION 보다 더 오랫동안 수행되기도 하나요 또 어떤 때는 에러가 빨리 발생하기도 하는데요?
  이 부분은 명확히 답변드리기 어렵습니다. 왜냐면 에러발생은 작업의 양과 UNDO 테이블스페이스가 사용되고 있는지에 달려있습니다.
  
  노트: 사용되고 있는 UNDO 레코드 또는 커밋되지 않은 트랜잭션은 'ACTIVE' 로 표시됩니다. 트랜잭션이 커밋되면, 각각의 UNDO 레코드는 'UNEXPIRED' 로 기록됩니다. 예를 들어 UNDO_RETENTION (또는 AUM 의 경우 시스템에 의해 계산된 TUNED_UNDORETENTION )에 정의된 시간동안 유지됩니다. 유지시간이 정해진 만큼 언두 레코드를 보관한 후, 재사용을 위해 'EXPIRED' 로 기록됩니다.
• 행의 UNDO 기록이 만료되지 않았으나 덮어씌워졌습니다.
  이 시나리오는 UNDO 테이블스페이스가 가득차서 'UNEXPIRED' 언두 레코드가 덮어씌워지고 있는 상황입니다.
  이는해당 UNDO 테이블스페이스에 대해 RETENTION GUARANTEE 가 활성화 되지 않아서 발생합니다.
• LOB 세그먼트에 대한 LOB 세그먼트 읽기 일관성이 더이상 유효하지 않습니다.
  LOB 컬럼이 in-row 또는 out-row 중 어떻게 구성되어 있느냐에 달렸습니다. In-row LOB 은 UNDO 테이블스페이스의 일반 UNDO 알고리즘에 따릅니다. Out-of-row LOB 은 아래를 참고 바랍니다.
  
  2 가지 방법으로 제어되는 LOB 의 읽기 일관성
  
  • PCTVERSION 사용(과거의 방법)
    LOB 의 버전 관리를 위해 사용되는 전체 LOB 스토리지 공간의 최대 % 를 명시합니다. 기본값은 10 이고 이것은 전체 LOB 스토리지 공간의 10 % 를 사용할 때까지 오래된 LOB 데이타를 덮어쓰지 않습니다.
    이 방법이 사라지게 된 이유로 LOB 에 대한 잦은 업데이트 및 삭제로 인해 100% PCTVERSION 을 종종 초과하게 되기 때문입니다.
  • RETENTION 사용(AUM 과 함께 사용되는 최신 방법)
    이 절을 통해 오라클 데이카베이스는 LOB 컬럼의 오래된 version 을 유지해야합니다.
    오라클 데이타베이스는 데이타베이스에서 커밋된 UNDO 데이타의 양을 결정하기 위해 초기 매개변수인 UNDO_RETENTION 값을 사용합니다.
    이 방법은 UNDO 세그먼트와같이 만료 시간을 사용합니다. 만약 LOB 세그먼트에 ORA-1555 가 발생하면 이는 아래를 의미합니다.:
    
    • PCTVERSION 이 초과되었고 LOB 의 읽기 일관성 뷰가 덮어씌워진 경우이거나
      또는
    • LOB 이 RETENTION 을 초과하고 행의 LOB 이 덮혀씌여져 ORA-1555 가 발생됩니다.

문제해결

아래는 이 이슈를 해결하는 단계목록입니다.:

1. 에러 메세지를 확인
   ORA-1555 에러에 여러 형태가 있어 자세한 내용 확인을 위해 alert 로그(또는 에러를 포함하고 있는 로그파일)를 확인합니다. :
   1. 세크먼트 명을 확인
      ORA-01555: snapshot too old: rollback segment number  with name "" too small
      --> 세그먼트 명이 NULL ""
      또를/그리고
      ORA-22924: snapshot too old
      이 경우 1555 에러는 LOB 세그먼트 내에 저장된 UNDO 데이타에 접근할 때 기록됩니다. LOB 세그먼트 ORA-01555 는 아래의 이유중 하나때문에 발생합니다.:
      
      1. LOB 세그먼트 손상:
         LOB 테이블의 손상을 확인이 필요하며, 아래 문서를 참조 바랍니다.:
      2. LOB 소산이 확인되지 않으면, Retention/ Pctversion 값을 확인합니다.:
         Retention/Pctversion 값을 올려야할 수도 있습니다. 아래 문서를 참조 바랍니다.
         
         • Document 846079.1 LOBs and ORA-01555 troubleshooting
         ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small
         -> 세그먼트 명은 "_SYSSMU107_1253191395$" 이고 UNDO 테이블스페이스내의 UNDO data 임을 의미합니다. 이 경우 ORA-1555 에러는 UNDO 테이블스페이스 내의 UNDO 데이타에 접근하려할 때 발생되는 것으로 아래에서 문제해결 방법에 대해 안내해 드리겠습니다.
   2. 쿼리 수행시간(duration) 확인
      실패한 쿼리의 수행시간은 ORA-1555 에러 메세지가 alert 로그 또는 어플리케이셔 로그에 기록됩니다.
      ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7):
      쿼리 수행시간이 0 또는 몇 초라면 아래 문서를 확인해 보시기 바랍니다.
      
      • Document 1131474.1 ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds
      만약 쿼리 수행시간이 UNDO_RETENTION 설정된 값보다 높다면, UNDO_RETENTION 값을 쿼리 수행시간만큼 증가시켜주시기 바랍니다. 그에 따라 UNDO 테이블스페이스 사이즈도 증가되어야 합니다.
      만약 쿼리 수행시간이 UNDO_RETENTION 과 같거나 근접했다면, 다음 분석방법을 진행하십시오.
2. Undo 데이타파일 확인
   select tablespace_name, file_id, sum(bytes)/1024/1024 a, sum(maxbytes)/1024/1024 b, autoextensible
   from dba_data_files
   where tablespace_name in
   (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' )
   group by file_id, tablespace_name, autoextensible
   order by tablespace_name;
   non-autoextensible UNDO 데이타파일을 사용중이라면, TUNED_UNDORETENTION 에 의해 높게 산정되어 에러가 발생될 수 있고 그로 인해 UNDO 데이타파일을 많이 사용하게됩니다.
   이 현상을 피하기 위해서는 여유 공간이 있다면, UNDO 데이타파일을 autoextensible( MAXSIZE 를 명시하여) 로 두어야 합니다.
   노트: UNDO 테이블스페이스 구성시 non-autoextensible 과 autoextensible 을 모두 사용하는 UNDO 데이타파일로 구성하지 마십시오. TUNED_UNDORETENTION 가 잘못 산정될 수 있습니다.
     
3. TUNED_UNDORETENTION 확인
   SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
   SQL> select max(maxquerylen),max(tuned_undoretention) from dba_hist_undostat;
   
   1. TUNED_UNDORETENTION 가 MAXQUERYLEN 값 보다 작음:
      이 경우 UNDO 테이블스페이스에 공간부족이 있다는 것을 의미합니다. 따라서, UNDO 레코드는 충분한 시간동안 유지하지 않게 됩니다. 이 경우에는 UNDO tablespace 에 공간을 추가하여야 합니다.
   2. TUNED_UNDORETENTION 가 MAXQUERYLEN 에 비해 너무 높음:
      이런 경우 일반적으로 UNDO 테이블스페이스가 non-autoextensible 데이타파일인 경우입니다. 내부 알고리즘에 따라 가능한한 오랫동안 UNDO 레코드를 유지하려고 하는데, 이 때문에 TUNED_RETENTION 이 높게 산정됩니다. 회피책으로 모든 UNDO 데이타파일을 autoextensible 모드로 변경하는 것입니다.(데이타파일에 MAXSIZE 를 설정할 수 있습니다.)
      오랫동안 수행되는 쿼리들로 TUNED_UNDORETENTION 가 높아질 수 있습니다.
      이런 쿼리들의 UNDO 데이타들이 너무 오랫동안 유지되는 것을 막을 필요가 있습니다. 이런 오랫동안 수행되는 쿼리를 확인하기 위해서는 아래를 사용하십시오.:
      select maxquerysqlid, maxquerylen from dba_hist_undostat order by maxquerylen desc;
      select maxqueryid, maxquerylen from v$undostat order by maxquerylen desc;
      
4. ACTIVE/UNEXPIRED 익스텐트의 높은 사용율
   select distinct status,tablespace_name, sum(bytes), count(*) from dba_undo_extents group by status, tablespace_name;
   ACTIVE/UNEXPIRED 익스텐트의 과도한 할당은 다음 이유들 중 하나때문일 수 있습니다.
   
   1. 앞서 안내해드렸듯이 UNDO_RETENTION or TUNED_UNDORETENTION 의 값이 높거나
   2. 특정 시점의 높은 UNDO 데이타 생성때문인 것으로 아래 쿼리를 사용하여 확인할 수 있습니다.:
      alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
      select begin_time , undoblks from dba_hist_undostat order by undoblks desc ;
      select begin_time , undoblks from v$undostat order by undoblks desc;
   3. 대량의 dead 트랜잭션 롤백
   4. 플래시백 데이타 아카이브 사용
   상세정보는 아래 문서를 참조 바랍니다.
   
   • Document 1337335.1 How To Check the Usage of Active Undo Segments in AUM
5. UNDO_RETENTION :
   UNDO_RETENTION 은 최소 MAXQUERYLEN 의 평균값으로 설정할 것을 권고드리며 만약 ORA-1555 에러가 발생하면 증가시켜주시기 바랍니다.
   select avg(maxquerylen) from dba_hist_undostat;
   select avg(maxquerylen) from v$undostat;