원문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

출처 : https://kr.forums.oracle.com/forums/thread.jspa?messageID=1661043&

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

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

 

 

ORA-1555 원인 분석 및 조치 사항 
게시일: 2007. 1. 28 오후 11:57    

제품 : ORACLE SERVER

작성날짜 : 2003-06-10
PURPOSE

query시 발생하는 ORA-1555의 발생 원인과 조치 사항에 대해서 자세히 살펴
본다.
Explanation

ORA-1555가 발생하는 원인은 여러가지가 있지만 기본적으로는 사용자가 필요로
하는 롤백 세그먼트의 정보가 다른 트랜잭션에 의해 overwrite되어, 존재하지
않을 때 발생한다.

이 문서를 읽기 전에 기본적으로 알아야 하는 오라클의 read consistency와
관련된 다음 내용들은 이 문서의 마지막에 별첨으로 용어 및 개념에 대해 설명
하였으므로 참고할 수 있다.

(1) SCN (System Change Number)
(2) statement-read level read consistent
(3) read consistent snapshot
(4) rollback segment의 wrap around/overwrite

ORA-1555에 관한 자세한 설명에 앞서, 데이타 블럭과 롤백 세그먼트 사이의
구조에 대해 간단히 알아보도록 한다. 데이타 블럭의 헤더에는, 이 블럭 내에
포함된 데이타를 변경한 트랜잭션의 정보와, 롤백 세그먼트 내의 해당 active
transaction을 가리키는 영역이 존재한다. 롤백 세그먼트는 세그먼트의 첫 번째
블럭을 헤더 블럭으로 사용하는데, 그 안에 이 롤백 세그먼트를 최근에
사용한 트랜잭션들의 정보와, undo record들이 저장되어 있는 롤백 세그먼트 내의
주소가 저장되어 있는 트랜잭션 테이블이 포함되어 있다.
다음 예의 그림을 통해 다음과 같은 사항을 알 수 있다.

(1) 데이타 블럭 500번지의 row 2를 변경한 xid1 트랜잭션은 아직 commit
되지 않은 상태이다. 블럭의 헤더에는 트랜잭션이 아직 commit되지
않았다는 정보와 5번 롤백 세그먼트 헤더 내의 3번째 엔트리에 트랜
잭션의 정보와, undo record를 얻을 수 있는 자세한 정보가 있음을
알려준다.
(2) 롤백 세그먼트 5번의 3번째 슬롯은 이 트랜잭션이 변경한 undo record가
롤백 세그먼트내의 7109번지에 저장되어 있음을 나타낸다. 2, 4, nn번
엔트리의 경우는 이미 트랜잭션이 commit되었으므로, 다른 트랜잭션이
이 엔트리를 overwrite할 수 있다.
(3) xid1 트랜잭션에 의해 변경된 undo record가 포함되어 있는 6900, 7109
블럭은 link로 연결되어 있어 xid1 트랜잭션이 변경한 모든 record들의
before image를 구성할 수 있다.

Data Block 500 Rollback Segment Header 5
+------+-----------+---+ +----+------+----------+------+
tx     uncommitted    5,3    ----+     01     xid4     ACTIVE     9012 

+------+-----------+---+ | | 02 | xid9 | COMMITTED| 8100 |
| row 1 | --->| 03 | xid1 | ACTIVE | 7109 |---
| row 2 changed | | 04 | xid2 | COMMITTED| 7632 | |
... ..         ..    ...    ...    5098      
row n          nn     xidm     COMMITTED    6777      

---------------------- +----+------+----------+------+ |

Block 6900 Block 7109 |
------------- ------------- |
| xid1 |<----+ | xid1 |<----+
| | | | |
undo record               undo record 
                
     +-------    6900 

------------- -------------
rollback segment block rollback segment block
ORA-1555가 발생하는 주요 원인과, 이 오류 발생을 최소화할 수 있는 방법은
다음과 같다.

1. 데이타베이스에 변경을 가하는 트랜잭션은 많고, 롤백 세그먼트는 크기도
작고, 갯수도 적은 경우

다음과 같은 상황을 가정할 수 있다.
(1) 약 30분이 걸려서 A 테이블의 대부분을 읽어야 하는 긴 query 하나를
수행시켰다.
이 때의 SCN이 10이었다.
(2) 위의 query가 결과값을 찾고 있는 동안, xid1 트랜잭션은 A 테이블에
대해서 update작업을 수행하고 commit하여 A table이 저장되어 있는
블럭 중 하나인 500번지 블럭의 SCN이 20으로 변경되었다
(3) query가 진행중인 동안 매우 많은 트랜잭션들이 database를 변경하고
commit하였다.
(4) 이 query가 500번지 블럭을 읽고자 할 때 SCN이 20임을 확인하고,
xid1 트랜잭션에 의해 변경된 undo record를 찾기 위해 롤백 세그먼트를
참조하였다.
(5) 그러나 xid1 트랜잭션은 이미 commit된 상태이고, query가 진행되는
동안 매우 많은 트랜잭션이 데이타베이스 변경 작업을 수행한 결과
롤백 세그먼트내의 xid1 트랜잭션의 undo record가 저장되어 있는
블럭이 다른 트랜잭션들에 의해 overwrite된 상태였다.
(6) ORA-1555가 발생한다.

해결 방법:
(1) 롤백 세그먼트의 크기를 크게 하고 갯수를 늘리면, 롤백 세그먼트가
wrap around/overwrite되는 주기가 늦추어진다.
(2) 트랜잭션의 수행이 많은 때에는 수행 시간이 오래 걸리는 query 문은
수행시키지 않도록 한다.

2. fetch across commit

프로그램내에서 cursor를 선언하고 loop를 수행하면서 fetch하고 데이타를
변경하는 경우 많은 프로그래머들은 롤백 세그먼트의 사용량을 줄이기 위해서
매 loop시마다 commit을 한다. 그러나 cursor의 loop내에서 commit하는
것은 ANSI standard에서는 제공하는 것이 아니며, ORA-1555를 발생시킬 가능
성이 있다.
ORA-1555가 발생하는 경우는 (1)의 경우와 유사하다. cursor는 선언하고,
open시에 데이타를 읽는 것이 아니고 fetch 때마다 읽게 되므로 fetch를
수행하는 것은 long query를 시작하는 것과 같다. 즉, fetch문의 loop를
수행하는 동안, 처음 fetch문 수행시점의 SCN보다 작거나 같은 SCN의 데이
타를 읽어야 한다. 그런데 loop 수행시마다 데이타를 변경하고 commit하게
되면, commit한 block의 SCN은 증가되고 변경된 정보도 다른 트랜잭션에
의해 재사용되어질 수 있다. 이렇게 블럭은 변경되었으나, 변경된 정보가
이미 다른 트랜잭션에 의해 overwrite된 블럭의 데이타를 fetch하고자
하면, 오라클은 read consistent snapshot을 구성할 수 없게 되므로
ORA-1555가 발생하게 된다.

해결 방법:
(1) cursor 내에서 commit하는 횟수를 줄인다. 예를 들어 첨자를 이용해
5만건에 한번씩 commit할 수 있으며, 이렇게 되면 5만건의 데이타를
저장할 수 있는 큰 롤백 세그먼트가 있어야 한다.
(2) cursor 선언 시 구성될 active set의 범위를 줄인다. 즉 한번에 모든
데이타를 읽어 처리하기 보다는, where절을 이용하여 데이타를 나누어,
여러번에 걸쳐 수행한다.
(3) 1번의 경우와 마찬가지로, commit된 정보가 overwrite되는 주기를
늦추기 위해서 롤백 세그먼트의 갯수를 증가시키고 그 크기도 크게하면
도움이 된다.

3. delayed block clean out

오라클은 기본적으로 transaction이 commit하면, fast commit을 수행한다.
즉, 트랜잭션이 데이타를 변경시키고 commit하면, 변경된 데이타 블럭의
header부분에 트랜잭션이 commit되었음을 기록하는 것이 아니고 일단 롤백
세그먼트의 헤더부분에만 commit되었음을 기록한다. 이 후 그 데이타 블럭을
다른 트랜잭션이 access하게 되면, 그때 롤백 세그먼트의 정보를 이용하여
데이타 블럭에 commit된 상태를 반영하여 clean out시키는 것을 delayed
block clean out이라고 한다.

이 delayed block clean out이 어떻게 ORA-1555를 발생하게 되는지 다음의
상황을 살펴보면 된다.

(1) 다음과 같은 초기 상태를 가정할 수 있다.
500번지 데이타 블럭의 데이타를 변경하는 트랜잭션은 존재하지 않고,
rollback segment 5번 header의 3, 4, nn번째 트랜잭션 엔트리는 다른
트랜잭션에 의해 재사용되어 질 수 있다.

Data Block 500 Rollback Segment Header 5
+-------+-------------+ +----+------+-------------------+

tx     none          01     xid4     ACTIVE 

+-------+-------------+ | 02 | xid9 | ACTIVE |
row 1          03     xid7    COMMITTED 
row 2          04     xid2    COMMITTED 
... ..         ..    ...    ...
row n          nn     xidm     COMMITTED 

--------------------- +----+------+-------------------+
(2) xid1 트랜잭션이 update문을 이용하여 500번지 데이타 블럭의 2번째
데이타를 변경하였다.
500번지 데이타 블럭의 헤더에는 xid1 트랜잭션의 정보가 저장되고, 롤백
세그먼트 5번의 트랜잭션 슬롯 3 (5,3)을 가리키게 된다. COMMITTED로 표시
되었던 트랜잭션 슬롯 3번은 이제 ACTIVE 상태로 변경되었다.

Data Block 500 Rollback Segment Header 5
+-------+-------------------+ +----+------+-------------------+

xid1     5.3uncommitted     ----+     01     xid4     ACTIVE 

+-------+-------------------+ | | 02 | xid9 | ACTIVE |
row 1    +--->    03     xid1    ACTIVE
row 2 changed          04     xid2    COMMITTED 
... ..         ..    ...    ...
row n         nn     xidm     COMMITTED 

--------------------------- +----+-------+------------------+

(3) xid1 트랜잭션이 commit을 수행하였다.
오라클은 롤백 세그먼트 헤더의 트랜잭션 테이블에서 xid1 트랜잭션의 정보를
찾아서 commit되었다고 기록하였다. 그러나 500번지 블럭의 헤더에는 commit
되었다는 정보를 기록하지 않는다. (fast commit)

Data Block 500 Rollback Segment Header 5
+-------+-------------------+ +----+-------+------------------+
xid1     5.3uncommitted     ----+     01     xid4     ACTIVE 

+-------+-------------------+ | | 02 | xid9 | ACTIVE |
row 1    +--->    03     xid1    COMMITTED 
row 2 changed          04     xid2    COMMITTED 
... ..         ..    ...    ...
row n         nn     xidmm     COMMITTED 

--------------------------- +----+-------+------------------+

(4) 데이타베이스에 변경을 가하는 매우 많은 트랜잭션이 수행되었다.
매우 많은 트랜잭션이 수행되어 롤백 세그먼트 헤더내에 있는 트랜잭션 테이블
의 엔트리가 대부분 재사용되었다. 트랜잭션 xid50이 롤백 세그먼트 5번의
3번째 슬롯이 COMMITTED로 표시되어 있으므로, 비어있는 엔트리로 인식하여
xid50에 관한 정보를 저장하였다.
Data Block 700 Rollback Segment Header 5
+-------+-------------------+ +----+-------+------------------+

xid50     5.3uncommitted     ----+     01     xid31     COMMITTED 

+-------+-------------------+ | | 02 | xid46 | ACTIVE |
row 1 changed     +--->    03     xid50    ACTIVE
row 2         04     xid60    COMMITTED 
... ..         ..    ..    ...
row n         nn     xidmm     ACTIVE

--------------------------- +----+-------+------------------+
(5) 다른 트랜잭션이 데이타 블럭 500번지를 방문하였다.
새로운 트랜잭션인 xid70 트랜잭션이 500번지 블럭을 읽고자 하였다. (3)번의
그림에서 보듯이, 500번지 블럭 헤더에는 아직 commit되지 않은 트랜잭션이
이 블럭을 변경하였으며, before image를 구성할 수 있는 정보가 롤백 세그먼트
5번, 엔트리 3번에 있음을 나타낸다. 그러나 5번 롤백 세그먼트 헤더 내에 있는
트랜잭션 테이블의 3번 슬롯은 xid1번이 아닌 xid50번의 정보가 저장되어 있다.
즉, delayed block cleanout이 이루어지기 전에 롤백 세그먼트 헤더가 overwrite
된 것이다.

(6) xid7 트랜잭션은 read consistent snapshot을 구성할 수 없으므로
ORA-1555가 발생한다.

해결 방법:
(1) ORA-1555를 발생시킬 상황 이전에 읽고자 하는 테이블에 대해 full
scan을 실시한다면, 롤백 세그먼트안의 정보가 overwrite되기 전에
delayed block cleanout이 이루어지도록 할 수 있다.

(2) 1 ~ 4번의 모든 원인에 대해서 롤백 세그먼트를 크게 유지하면, 롤백
세그먼트의 정보가 overwrite되는 주기를 늦출 수 있어 ORA-1555를
피하는 데 도움이 될 수 있다.

4. OPTIMAL 크기가 아주 작을 때

롤백 세그먼트는 트랜잭션의 사용에 의해 한번 크기가 늘어나면 기본적으로
그 롤백 세그먼트를 지우고 다시 만들기까지는 크기가 줄어들지 않는다.
그러나 optimal size를 지정하게 되면, 롤백 세그먼트에서 새로운 extent를
요구하는 시점에, 현재 할당된 롤백 세그먼트의 크기와 optimal에 지정된
크기를 비교하게 된다. 할당된 공간이 optimal 크기보다 큰 경우, 할당된
extent중 active한 트랜잭션이 사용하고 있지 않은 extent들은 release시켜,
롤백 테이블스페이스의 공간으로 환원된다.
그러므로 이 optimal size가 지나치게 작다면, 트랜잭션이 commit되자마자
롤백 세그먼트 내의 정보는 잃게 될 것이다. 그러나, 위의 1 ~ 4번에서 살펴보
았듯이 이미 commit된 트랜잭션의 정보라 하더라도 이후에 필요하게 되는
경우가 발생하므로 이렇게 빈번히 commit된 트랜잭션의 정보가 포함되어 있는
롤백 세그먼트의 extent를 release시키는 것은 바람직하지 않을 수 있다.

해결 방법:
(1) optimal을 지정할 때는 20개의 extents정도의 크기정도로 지정하는
것이 적당하며, 그것보다 더 작게 지정하지 않도록 한다.
(2) 롤백 세그먼트를 많이 필요로 하는 batch job의 경우 set transaction
use rollback segment rollback_segment_name; 구문을 이용하여 특정
롤백 세그먼트를 사용하게 하고 나머지 롤백 세그먼트들은 OLTP job이
사용하도록 한다. 이렇게 하면 OPTIMAL을 지정하지 않아도 모든 롤백
세그먼트가 불필요하게 확장되는 일을 막을 수 있다.

별첨: 용어 및 기본 개념 설명--------------------------------------------------------
(1) SCN(System Change Number)
오라클은 특정한 시점의 데이타베이스 상태를 SCN으로 관리한다. 트랜잭션이
commit되면, SCN은 최근의 SCN 보다 크고 유일한 값이 할당되며, 이 값은 그
트랜잭션이 변경시킨 블럭에 반영되고, 그 데이타화일의 가장 최근의 SCN은
데이타화일의 헤더(header)에 기록된다.

(2) statement-level read consistent
하나의 query는 그 query가 시작되어 데이타를 읽기 시작하면, 모든 데이타를
읽어 query가 끝날 때까지 일관된 상태를 유지한다. 즉 query가 진행되는 동안
다른 트랜잭션이 읽고자하는 데이타를 변경하더라도 그 query는 변경 이전의
데이타 값을 읽게 된다.
데이타들이 query가 시작될 때와 같은 시점인지는 SCN을 통해 관리된다. 즉
SCN이 10인 상태에서 query가 시작되었다면 query가 진행되는 동안 항상 SCN이
10이하 상태의 데이타만을 읽게 되며, 이것은 롤백 세그먼트(rollback segment)
를 이용하여 read consistent snapshot을 구성함으로써 가능하다.

(3) read consistent snapshot (read consistent view)
트랜잭션이 변경작업을 수행할 때 마다, 오라클은 변경 작업이 이루어지기
전의 before image(snapshot)을 롤백 세그먼트에 저장해둔다. 한 트랜잭션이
commit되기 전에 변경된 데이타를 다른 트랜잭션이 읽거나 수정하고자 한다면,
롤백 세그먼트의 정보를 이용하여 read consistent snapshot을 구성한 후 이
데이타값을 이용하여 operation을 수행한다.
또한 (2)에서 설명한 statement-level read consistent를 이루기 위해서도
query가 진행되는 동안 읽고자 하는 블럭의 SCN이 증가하면, 롤백 세그먼트의
정보를 이용하여 원하는 SCN상태의 read consistent snapshot을 구성한 후
데이타를 읽게 된다.

(4) rollback segment의 wrap around/overwrite
롤백 세그먼트는 하나의 롤백 세그먼트를 여러개의 트랜잭션이 함께 사용하며,
하나의 extent도 여러개의 트랜잭션이 동시에 사용가능하다. 단 각 블럭은
하나의 트랜잭션에 할당된다. 트랜잭션들이 사용 중인 extent에 정보를 저장
하고 다음 extent가 필요하면, 해당 롤백 세그먼트에 이미 할당되어 있는 다음
extent가 active한 undo 정보를 가지고 있는지를 검사한다. active한 undo
정보를 담고 있지 않은 다음 extent가 current extent가 되며, 트랜잭션들은
이 extent에 undo image를 저장한다. 할당된 맨 마지막 extent를 확인하게
되면, 다시 첫번째 extent부터 extent로 돌아와 다시 사용하는 것을 wrap
around라고, 모두 commit된 트랜잭션의 정보만 담고 있는 extent는 overwrite
된다.
이렇게 롤백 세그먼트의 undo image를 담고 있는 블럭 뿐 아니라 롤백 세그먼트
헤더 내의 트랜잭션 테이블의 엔트리도 wrap around/overwrite될 수 있다.
트랜잭션 테이블은 고정된 수의 엔트리를 가지고 있으며, 트랜잭션이 이미
COMMITTED된 엔트리는 비어있는 것으로 인식하여 다음 트랜잭션이 사용 가능하게
된다.

==================================================================================================

 

* 두번째 수집자료

 

ORA-1555 : snapshot too old (rollback segment too small)

이러한 문제는 "활동중"인 데이타의 정의로 부터 발생한다. 그림 7-4의"Transaction A"라고 하는 커다란 트랜잭션을 생각해보자. 만약 긴 질의가 동일한 Transaction A에 접근하려한다면 Transaction A의 롤백 세그먼트 엔트리에 의해 저장된 데이타블럭을 사용할 필요가 있다. 그러나 일단 Transaction A가 완수되면 이러한 블럭은 "비활동"으로 표시된다. 이들 블럭은 그러면 다른 트랜잭션에 의해 덮어쓰기 될 것이다. 이들 블럭상에서의 별도의 길다란 질의가 끝나지 않았다 하더라도 상황을 변하지 않는다. 이미 덮어쓰기가 된 블럭을 일고자 하는 질의는 실패로 끝날 것이다.이러한 상황이 그림 7-5에 나타나 있다.

질의열의 실패에 대한 두가지 진정한 이유를 파악하는 것이 가능하다. 첫째 긴 질의열은 데이타 조작 트랜잭션과 동일한 시간에 진행된다. 다시 말해서 데이타베이스에서는 배치 프로세싱과 온라인 프로세싱이 동시에 진행된다는 것이다. 롤백 세그먼트의 기능에 대해 앞에서 했던 논의로 부터 이 전략이 가지고 있는 문제가 분명하게 드러난다:

Extent1\tExtent2\tExtent3\tExtent4\tExtent5\tExtent6\tExtent7

\t\tTransaction A extent1\tTransaction A extent2\tTransaction A extent3 \tTransaction A extent4\tTransaction A extent5

(a)Transaction A 가 진행중임 : 그 데이타는 커다란 질의에 의해 사용됨

Extent1\tExtent2\tExtent3\tExtent4\tExtent5\tExtent6\tExtent7

Transaction B, extent 1\t\tOLD Transaction A, extent 1\tOLD Transaction A, extent 2\tOLD Transaction A, extent 3\t0LD Transaction A, extent 4\tOLD Transaction A, extent 5

(b) Transaction A 완성. 그 엔트리 데이타는 긴 질의열에 의해 사용되고 그대로 남겨진다.

Transaction B 시작.

Extent1\tExtent2\tExtent3\tExtent4\tExtent5\tExtent6\tExtent7

Transaction B extent1\tTransaction B extent2 \tTransaction B extent 3\tOLD Transaction A extent 2\tOLD Transaction A extent 3\tOLD Transaction A extent 4\tOLD Transaction A extent 5

(C) 커다란 질의열을 사용해 Transaction B를 블럭위에 덮어쓰기 함 , 질의 실패

그림 7-5

" sanpshot too old" 에러로 인한 질의 실패

데이타의 연속적인 버전으로 돌아가기 위해서는 긴 질의열은 완성해야 할 모든 테이블과 인덱스에 접근해야 할 뿐아니라 롤백 세그먼트에 저장된 데이타에도 접근해야 한다.

그것이 끝날때까지 롤백 세그먼트로의 접근은 계속되기 때문에 긴 질의열은 그것이 사용하고 있는 롤백 세그먼트 엔트리가 덮어쓰기 되어서는 안된다. 그러나 이러한 엔트리가 일단 종료되면 상황은 달라질 것이다. 에러 메세지에서 얻을 수 있는 충고는 (" rollback segment too small) 두번째 관련된 문제를 해결함으로써 풀 수 있다: 롤백 세그먼트에서 기존의 엔트리의 덮어쓰기를 피하는 것 , 그것에 스페이스를 추가함으로써 첫번째 확장(extent)로 돌아가는데 시간이 오래 걸리도록 하는 것.

이것은 완전한 해결책은 아니다. 다만 시간을 버는 술수일 뿐이다. 왜냐하면 롤백 세그먼트는 결국은 그 데이타 블럭의 모든 것을 덮어쓰기 할 것이기 때문이다. 적절한 대안은 아마도 온라인 트랜잭션이 최소일때 긴 질의를 시도하는 것일 것이다.

 

 

===================================================================================================

 

* 해법 : 오랜 시간이 걸리는 큰 작업이 원인이 되는 경우~

 

1) 공간 여유있는 tablespace 내에 큰 rbs를 생성

 

create rollback segment large_rbs

storage ( initial 10m next 10m minextents 8 optimal 100m)

tablespace large_rbs_ts;

 

2) 만든 rbs를 procedure 내에서 set

 

if v_commit_cnt = 1000 then

v_commit_cnt := 0;

commit;

set transaction use rollback segment large_rbs; -- 삽입된 내용

end if;

 

3) 다른 사용자가 없는 시간에 procedure를 다시 돌린다.

 

* 해법 : 보존기간이 작은 경우

SQL> alter system set undo_retention = 10800;   <= 초단위로 기록.  10800이면 3시간.  default 값이 10800임.

 

 

* 해법 : SQL 의 튜닝.