ORA-01555 snapshot too old

 

원문 : http://www.ezslookingaround.com/blog/tech/?no=950

 

-------------------------

안녕하세요...
rollback segment와 관련해서 질문이 한가지 있습니다.
현재 java source를 보면 대략 1000만건을 Select 하고서 조회한 데이타를 테이블에
insert를 합니다.
source를 잠시 보면

rs = stmt.executeQuery(" Select empno, ename, job from emp ");

pstmt = conn.prepareStatement(
                " Insert Into Temp (empno,  " +
                "                   ename,   " +
                "                   Job)   " +
                " Values (?,?,?)");

while(rs.next()) {
                pstmt.clearParameters();
                pstmt.setString(1, rs.getString("empno"));
                pstmt.setString(2, rs.getString("ename"));
                pstmt.setString(3, rs.getString("Job"));

                try {
                    pstmt.executeUpdate();
                } catch (SQLException se) {
                    System.out.println( "Message(execute) : " + se.getMessage());
                    System.out.println( "sql state(execute) : " + se.getSQLState());
                    System.out.println( "error code(execute) : " + se.getErrorCode());
                } catch (Exception e) {
                    System.out.println( "Insert Exception(execute) Error");
                }
            }

source는 대략 이러한 방식으로 되어있습니다.
그런데 이렇게 하다보니까 700만건도 못되어서 ORA-0155: snapshot too old" error가
발생하였습니다.

그래서 대량의 데이타를 처리할수 있는 rollback segment를 만들고 나서 다음과 같이
하였습니다.

위의 source에서 select를 하기 이전에 다음을 넣었습니다.

try {
                pstmt2 = conn.prepareStatement(
                    " set transaction use rollback segment TEMPRBS " );

                try {
                    pstmt2.executeUpdate();
                } catch (SQLException se) {
                    System.out.println( "Message(RBS) : " + se.getMessage());
                    System.out.println( "sql state(RBS) : " + se.getSQLState());
                    System.out.println( "error code(RBS) : " + se.getErrorCode());
                } catch (Exception e) {
                    System.out.println( "Insert Exception(RBS) Error");
                }
            } catch (SQLException se) {
                System.out.println( "Message(insert : RBS) : " + se.getMessage());
                System.out.println( "sql state(insert : RBS) : " + se.getSQLState());
                System.out.println( "error code(insert : RBS) : " + se.getErrorCode());
            } catch (Exception e) {
                System.out.println( "Insert Exception(RBS) Error");
            }

그런데 이렇게 하여도 ROLLBACK SEGMENT ERROR가 발생을 하는데 잘못된 부분이 어디 있는지
알고 싶습니다.

감사합니다...

제목 : Re: 2가지 방법...
글쓴이: 손님(guest) 2003/12/09 13:03:17 조회수:375 줄수:16  
이게 가능할지는 잘 모르겠네요...

일단 첫번째 방법으로 속도가 조금 떨어질 가능성은 있습니다만...
select를 할 경우에, order by를 사용하지 못할 특별한 이유가 없으면...
order by를 사용해 보는 게 좋을 듯하구요...
이걸로도 해결이 안되면...

두번째 방법은...
이건 오라클 9i일 경우(8i의 경우도 같은지 잘 모르겠네요...)에는...
UNDO_RETENTION을 조금 크게 잡아보심이...
쿼리가 실행되는 데 걸리는 시간 이상의 충분한 시간을 잡아보면 해결이 될지도...

뭐, 확실한 건 아닙니다만...
나쁘지 않다면 한번정도 테스트해 보심이 좋을 듯하네요...

그럼...

제목 : Re: rollback Segment 관리
글쓴이: 손님(claystudio) 2003/12/10 19:18:38 조회수:413 줄수:11  

insert 하다가 읽어 왔던 데이터가 바뀐것 같습니다.

"snapshot too old"오류를 자세히 찾아보시면 원인을 더 정확히 알 수 있을듯합니다...

ps. 데이터 양으로 봐서는 웹상에서 돌아가는 프로그램은 아니고 배치성 프로그램인듯 한데
    쿼리를 바꿔보심이...
    ==> Insert into Temp(empno, ename, job)
             Select empno, ename, job from emp;



제목 : Re: OTN에서 퍼왔습니다.
글쓴이: 손님(guest) 2003/12/12 15:43:22 조회수:905 줄수:147  
No. 20241

ORA-1555 (SNAPSHOT TOO OLD)의 일반적인 원인 및 조치사항
===========================================

PURPOSE
-------

ORA-1555 (snapshot too old)는 db 관리 업무에 익숙하지 않은 경우, rollback
관련된 오류 중 혼란을 일으키기 쉬운 오류이다.
이미 문서 <bulletin:11152>와 그외 자료가 이 오류를 설명하고 해결하기 위해
만들어져 있지만, ORA-1555 원인 파악을 위해 내용이 다소 길고 복잡하게
구성되어 있는 편이다.
여기에서는 발생 가능한 여러가지 원인 중 일반적인 원을을 위주로, 초보자도
쉽게 이해할 수 있도록 간단히 설명한다.


Explanation
-----------

일반적으로 ORA-1555에 혼란을 일으키는 원인은 한편으로는 오류 메시지 자체에
있다고 볼 수 있다.

ORA-1555: snapshot too old: rollback segment %s too small

이와 같은 오류에서 마치 ora-1555가 rollback segment에 write시 space가
부족해서 발생하는것으로 착각하는 사용자가 많다.

중요한 것은 ORA-1555는 rollback segment에 정보를 write시에 발생하는 것이
아니고 rollback segment로 부터 before image를 읽으려는 시점에서 발생한다는
것이다.

쉬운 예를 들어보자.

(1) 사원이 천명인 회사에서 select한 문장으로 그 전체 사원의 정보를 읽는데
    10분이 걸린다고 가정한다.
(2) 100번 사원 정보를 읽는데, 아직 읽지 않은 700번 사원에 대해 다른 session에서
    급여를 인상하는 update문장을 수행하고 commit을 한다.
    select문장은 lock을 걸지 않기 때문에 select도중 다른 update문장이
    수행되고 commit하는데 아무 문제가 없다.
(3) 1번에서 수행중인 select문장이 계속 진행되면서 700번 사원 정보를 읽으려고
    하면 이 정보가 수정되어 변경되었음을 알게 된다.
    그럼 select문장은 정보의 일관성을 위해 첫번째 사원을 읽기 시작한 시점의
    700번 사원에 대한 정보를 읽기 위해, 즉 before image를 읽기 위해
    rollback segment를 찾아간다.
(4) rollback segment내에 급여 인상 전 정보가 있으면 읽는다.
    단 이때,
    이 시스템에 트랜잭션이 매우 많아서 commit이 매우 많이 발생한 경우
    이미 2번에서 변경하고 commit한 정보는 다른 트랜잭션에서 overwrite했을
    수 있다.
    이런 경우 before image를 읽으러 간 select문장은 ora-1555를 만나게 되는
    것이다.
(5) 4번에서 ora-1555를 만난 경우 다시 동일한 select문장을 수행하면,
    이번에는 이미 급여가 인상된 후의 시점에서 시작하므로 700번 사원을
    읽는 경우에도 급여 인상전의 before image가 필요하지 않아 ora-1555는
    다시 발생하지 않을 수 있다.
    이러한 이유로 ora-1555는 발생했다 안했다 하는 식으로 일정하게 발생되지
    않고, 조치 방법이라는것도 100% 안전하기보다는 확률적으로 충분히 만나지
    않을 수 있는 환경을 만드는것이라고 볼 수 있다.

결국 ora-1555가 발생하는 것은 읽어야 하는 before image가 다른 트랜잭션에
의해 이미 overwrite되어 읽을 수 없는 경우 발생하므로, 발생하지 않게 하기
위해서는 데이타를 조회시 consistency를 유지해야 하는 시점동안 가능하면
오래 동안 rollback의 image가 유지되어야 하는것이다.

이렇게 이미 기록된 정보를 가능하면 오랜 기간동안 유지한다는 것은 새로운
트랜잭션의 기록을 위해 space를 확보해야 하는 작업과는 반대된다.
즉, ORA-1562와 같이 rollback segment를 write시에 space가 부족하여
space를 확보하기 위한 조치 방법과, 이 ORA-1555의 조치 방법을 서로 상충되어
trade-off가 있음을 주의해야 한다.

두 오류를 모두 피해가기 위해서는 일반적으로 매우 큰 rollback space가
도움이 된다.

ORA-1555의 일반적인 발생 경우 및 해결 방법을 정리한다.

(1) 트랜잭션에 비해 rollback segment 갯수가 적은 경우
   
    rollback segment하나에 동시에 기록 가능한 트랜잭션의 수는 rollback
    segment header내의 transaction table의 entry갯수로 제한되어 있다.
    이 수는 oracle version마다 다르지만 8i이상부터는 약 20개 정도이다.
    (transactions_per_rollback_segment의 지정과는 무관한다.)
    기본적으로 install시 생성되는 rollback segment는 4개인데, 이대로 놓고
    사용한다면, 결국 80 (20 * 4) 만큼의 commit이 발생하고 난 뒤에는
    다시 처음부터 transaction table의 entry 중 commit된 트랜잭션의
    정보를 가지는 entry의 정보를 overwrite하게 되는 것이다.

    해결 방법: rollback segment갯수를 증가시킨다.
               즉 새로운 rollback segment를 create시킨다.

    부작용: 제한된 rollback tablespace공간 내에서, 여러개의 rollback
            segment를 유지하는것은 하나의 rollback segment가 평균 가질 수
            있는 space가 그만큼 줄어드는 셈이다.
            이 부작용까지 줄이려면, rollback tablespace자체가 충분히
            커야 하고 space를 많이 요구하는 트랜잭션은 'set transaction
            use rollback segment' 문장을 이용하여 큰 rollback을 지정하여
            사용하도록 한다.

(2) rollback segment를 shrink하거나 optimal이 설정된 경우

    rollback segment를 shrink하거나 optimal을 지정하게 되면 이미 쓰여진
    rollback의 before image를 다른 트랜잭션이 overwrite도 하기 전에 미리
    지워 버리게 되는 셈이다.
    그러므로 이런 경우도 ora-1555의 원인이 된다.

    해결 방법: optimal을 너무 적게 지정하지 말고, shrink를 너무 자주
               하지 않는다. shrink를 수행 후 ora-1555가 발생하는 경우,
               단지 다시 조회하는것만으로 앞의 예제 (5)번에서 설명한
               이유로 인해, 해결되는 경우가 많다.

(3) proc와 같은 application에서 loop내의 fetch문장에서 자주 commit을
    하는 경우

    fetch문장은 loop를 도는 동안 일정하게 read consistency를 유지해야 한다.
    그리고 미리 cursor를 정의시에 데이타를 읽어두는것이 아니고, fetch시에
    loop를 돌면서 그때그때 데이타를 읽게 된다.
    그런데 loop내의 dml에 대해 너무 자주 commit을 하게 되면 그만큼
    여러개의 트랜잭션이 처리된 결과로 rollback segment의 transaction table이
    빨리 사용되고 overwrite되게 된다.

    해결 방법: loop내의 commit횟수를 줄인다. 예를 들어 loop를 돌때마다
               commit하게 하였다면 천번에 한번 혹은 만번 loop를 돈 후
               commit하는 식으로 늘려준다.

이 외에도 rollback tablespace자체의 space가 부족하여 transaction table의
entry들이 아직 overwrite되지도 않았는데, commit된 transaction이 사용한
rollback segment내의 space가 먼저 overwrite되는 경우도 있다.
그러나 일반적으로 rollback segment의 space를 너무 작게 유지하지는 않기
때문에 이렇게 space부족으로 ora-1555를 만나는 경우는 많지 않다.
이렇게 space가 절대적으로 부족한 경우는 rollback에 write하는 시점에서,
ora-1562가 먼저 발생하게 된다.

ora-1562에 대해서는 <bulletin:10823> "ORA-1562 분석 및 해결 방법
(ROLLBACK SEGMENT 크기 문제)"를 참조하고,
좀더 자세한 ora-1555의 개념에 대해서는 <bulletin:11152> "ORA-1555 원인
분석 및 조치 사항" 을 참조한다.

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

write할 rbs가 모자라서 그런 게 아니랍니다.
제 생각엔 루프안의 SQL에 대한 명시적인 트랜잭션 컨트롤 구문이 없는 것으로 보아
(commit or rollback) autocommit으로 그냥 사용중이신 것같은데요
autocommit을 꺼주시고 트랜잭션에 대한 매뉴얼한 컨트롤을 해주셔야할 듯합니다.
그냥... 명시적으로 commit과 rollback을 해주셔야할 듯하단 말입니다. ^^

직접적인 관련은 없는 듯하지만, rbs가 적다면 적절하게 늘려주시는 게 좋을 듯합니다.