태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

ORA-01555 Snapshot Too Old 에러의 저주

오라클 2008.11.26 15:36

Oracle의 모든 에러 중 가장 Oracle다운 에러를 뽑으라면?

주저없이 그 악명높은 ORA-01555, 즉 Snapshot Too Old 에러를 뽑는다.


이름부터 희한한 이 에러는 아주 먼 옛날부터 지금까지 여전히 많은 관리자들을 괴롭히는 문제다. 아주 보편적으로 발생하지만 의외로 그 원인을 파악하기가 어려운 악질적인 문제이기도 하다.

이 에러가 왜 가장 Oracle다운 에러인가? 오로지 Oracle에만 존재하는 Undo에 기반한 독특한 Read Consistency 메커니즘에 의해 발생하는 에러이기 때문이다.

Oracle의 전매 특허인 효율적인 Row Level Lock을 가능하게 해주는 Read Consistency 메커니즘의 어두운 측면이 바로 Snapshot Too Old 에러라고 볼 수 있다.

일반적으로 알려진 Snapshot Too Old 에러의 원인은 다음과 같다. 아마 이 글을 읽는 분들 대부분이 아래 현상 중 적어도 한가지는 이미 경험했을 것이다.

  1. Rollback Segment가 덮어써진 경우
  2. Undo Segment Header의 Transaction Table이 완전히 덮어써진 경우 - Delayed Block Cleanout과 함께 발생하는 경우 많음
  3. LOB 이미지가 덮어써진 경우
  4. Block Corruption이 발생한 경우 - 주로 LOB Corruption과 함께 발생함
  5. 기타 여러 가지 이유들 - 버그 포함

가장 보편적이라고 할 수 있는 1번, 2번의 Snapshot Too Old 에러를 데모를 통해 재현해 보자.

우선 크기가 작은(10M) Undo Tablespace를 만든다.

drop tablespace small_undo including contents and datafiles;

create undo tablespace small_undo
datafile size 10m autoextend off
;

방금 만든 Undo Tablespace를 사용한다.

alter system set undo_tablespace = small_undo;


Table t1과 Index t1_n1을 만들고 Data를 생성한다.

drop table t1 purge;

create table t1(c1 int, c2 char(300));

insert /*+ append */ into t1
select level, 'dummy'
from dual
connect by level <= 10000
;

commit;

create index t1_n1 on t1(c1);


Select Query의 속도를 제어하기 위해 Sleep 함수를 만든다.

create or replace function fsleep(v1 int, vsleep int)
return number
is
begin
  dbms_lock.sleep(vsleep);
 
  return 1;
end;
/


확실한 테스트를 위해 Buffer Cache의 크기를 최소화한다.


alter system set db_cache_size = 1m;


SGA를 살펴보면 Buffer Cache의 크기가 1M가 아닌 8M인 것을 알 수 있다. 최소 할당 단위가 8M라는 것을 의미한다.

show sga

Total System Global Area  130023424 bytes
Fixed Size                  1247636 bytes
Variable Size             109053548 bytes
Database Buffers            8388608 bytes
Redo Buffers               11333632 bytes



첫번째 Case는 Rollback 정보가 덮어써진 경우이다.

Session #1에서는 다음과 같이 Table t1에 대해 Long-Running Query를 수행한다.

set arraysize 1

@mon_on userenv('sid')

select /*+ index(t1) */
  c1, substr(c2,1,10)
from t1
where fsleep(c1, 0.01) = 1
      and c1 > 0
;

@mon_off
spool result1.txt
@mon_show
spool off


Session #2에서는 다음과 같이 Table t1에 대해 DML을 과다하게 발생시킨다. AUM(Automatic Undo Management)에서는 Undo Segment가 Transaction마다 할당되기 때문에 하나의 Undo Segment만을 사용하도록 set transaction use rolllback segment 명령을 사용하는 기법에 유의한다.

-- get rollback segment name
col rollback_seg new_value v_rollback_seg
 
select '_SYSSMU'||max(segment_id)||'$' as rollback_seg
from dba_rollback_segs
where segment_name <> 'SYSTEM'
;
 
-- do very frequent commit on t1
begin
  for idx in 1 .. 1000 loop
    for idx2 in 1 .. 10000 loop
      set transaction use rollback segment "&v_rollback_seg";
      update t1 set c2 = 'dummy'||idx
       where c1 = idx2;
      commit;
    end loop;
  end loop;
end;
/

Session#1에서 일정 시간이 지난 후 다음과 같이 ORA-01555 에러가 발생한다.

...
      2251 dummy
      2252 dummy
      2253 dummy
ERROR:
ORA-01555: snapshot too old: rollback segment number 30 with name "_SYSSMU30$" too small


왜 에러가 발생하는가? 과도한 Commit으로 인해 Rollback Segment가 완전히 덮어써졌기 때문이다. Session #1은 Query가 시작된 시점의 이미지를 Rollback 할 수 없기 때문에 ORA-01555 에러를 발생시키게 된다.




두번째 Case는 Undo Header의 Transaction Table이 덮어써진 경우이다.

Table t2를 생성한다.

drop table t2 purge;

create table t2(c1 int);

insert into t2 values(1);

commit;


Session #2에서 Table t1을 대량 Update하고 Commit을 수행한다. Commit을 수행한다는 사실을 기억하자.

-- get rollback segment name
col rollback_seg new_value v_rollback_seg
 
select '_SYSSMU'||max(segment_id)||'$' as rollback_seg
from dba_rollback_segs
where segment_name <> 'SYSTEM'
;

-- update t1 and "commit"
set transaction use rollback segment "&v_rollback_seg";

update t1 set c2 = 'dummyx';
commit;


Session #1에서 Table t1에 대해 Long-Running Query를 수행한다.

set arraysize 1

@mon_on userenv('sid')


select /*+ index(t1) */
  c1, substr(c2,1,10)
from t1
where fsleep(c1, 1) = 1
      and c1 > 0
;


@mon_off
spool result2.txt
@mon_show
spool off


Session #2에서는 Table t2에 대해 과도한 Commit을 수행한다.

-- do very frequent update on t2(not 1)
begin
  for idx in 1 .. 1000 loop
    for idx2 in 1 .. 10000 loop
      set transaction use rollback segment "&v_rollback_seg";
      update t2 set c1 = c1;
      commit;
    end loop;
  end loop;
end;
/

Session#1에서 일정 시간이 지난 후 다음과 같이 ORA-01555 에러가 발생한다.

...
        68 dummyx
        69 dummyx
ERROR:
ORA-01555: snapshot too old: rollback segment number 30 with name "_SYSSMU30$" too small

여기서의 핵심은 이것이다.

- Session#1은 이미 Commit이 완료된 Table t1에 대해 Query를 수행한다.
- Session#2는 Table t1이 아닌 Table t2에 대해 과도한 DML과 commit을 수행한다.
- Session#1의 Select Query는 이미 Commit이 완료된 Table을 읽기 때문에 Rollback에 실패할 이유가 없다.
- 하지만!!! Session#1은 ORA-01555 에러를 만난다.

왜 이런 일이 발생하는가? Delayed Block Cleanout 때문이다.

Table t1에 대해 Commit이 수행될 때 모든 Data Block에 대해 Commit 여부를 지정하지 않고(즉 일부에만 Commit 여부 지정) Undo Segment Header의 Transaction Table에만 Commit 여부를 지정한다. 이 정보가 완전히 덮어써져서 Rollback하지 못하면 ORA-01555 에러가 나게 된다.

Block에 Commit 여부를 저장하는 것을 Block Cleanout이라고 부른다. Delayed Block Cleanout는 Block Cleanout를 Commit 시점에 하지 않고 미루겠다(Delay)는 것을 의미한다.

그렇다면 언제 Block Cleanout이 이루어지는가? 다른 Session이 해당 Block을 Access하는 시점에 이루어진다.

즉 위의 예에서 Session#1은 Delayed Block Cleanout를 수행하기 위해 Transaction Table을 읽을려고 했는데 다른 DML에 의해 해당 정보들이 완전히 덮어써져서 Rollback이 안되기 때문에 ORA-01555 에러를 발생시키는 것이다.

두번째 경우의 Snapshot Too Old 에러는 첫번째 경우의 확장판이라고 할 수 있다.




두 현상을 좀 더 비교해보면 재미있는 사실을 알 수 있다.

아래 결과는 V$SESSTAT 뷰를 이용해서 일량을 비교한 것이다. 첫번째 경우에는 data blocks consistent reads - undo records applied 값이 증가하는 반면(일반적인 Rollback이 이루어졌음을 의미), 두번째 경우에는 transaction tables consistent reads - undo records applied 값이 증가한다(Transaction Table에 대해 Rollback이 이루어졌음을 의미). 이런 Data를 함께 관찰하면 더욱 정확하게 원인을 추적할 수 있다.


-- 첫번째 경우
session logical reads                           5,760

....
data blocks consistent reads - undo reco        3,013
rds applied                                         

-- 두번째 경우
session logical reads                          71,466
...
transaction tables consistent reads - un       71,049
do records applied                                  


보통 ORA-01555 에러가 나기 전에는 과도한 Rollback 시도가 이루어지기 마련이다. 이 과정에서 Undo에 대한 과도한 Physical I/O가 같이 발생할 수 있다. Wait Event로는 db file sequential read가 목격된다.

이런 일련의 현상은 효율적인 Row Level Lock의 구현을 위해 우리가 감내해야하는 희생이다. 때로는 참기 어려울 정도로 고통스러운 경우도 있지만... ㅠㅠ


PS) SQL Server 2005에서는 Oracle과 흡사한 Read Consistency를 제공하기도 하지만, 거의 사용되지 않으며 급조한 느낌이 나는 짝퉁 버전에 가까우므로 논의로 하는 것이 맞을 것이다.

신고
Trackback 1 : Comments 9
  1. oracler 2008.12.06 15:59 신고 Modify/Delete Reply

    선생님 책 언제 나오나요. 이해는 다 못하지만 그래도 기다려집니다 ㅋㅋ

  2. 욱짜 2008.12.06 16:46 신고 Modify/Delete Reply

    2008년 12월 19일에 입고가 되니까, 12월 말이면 구입이 가능할 거 같습니다.

    이번 책은 최대한 쉽고 상세하게 적었습니다.

    아래 URL을 통해 상세한 정보를 얻을 수 있습니다. 참조하세요.

    http://wiki.ex-em.com/index.php/Optimizing_oracle_optimizer

  3. Stargazer 2009.04.08 14:55 신고 Modify/Delete Reply

    set transaction use rollback segment 구문은 AUM에서는 무시된다고 메뉴얼에 나와 있습니다.
    혹시..MUM 에서 테스트 하셨는지...
    위 구문의 "set transaction use rollback segment ~ " 구문을 생략해도 ORA-01555: snapshot too old
    에러는 발생합니다..

    아마 제생각에는
    "set transaction use rollback segment ~ " 이 구문이 적용되었지만 내부적으로 무시된듯 합니다..

  4. 욱짜 2009.04.08 17:21 신고 Modify/Delete Reply

    이런 것을 흔히 Documentation Bug(문서 버그)라고 합니다. Documentation Bug가 얼마나 많은지 아마 깜짝 놀라실겁니다.

    set transaction use rollback segment 명령은 AUM에서도 여전히 동작은 합니다. 다만 더 이상 사용할 필요가 없죠. 위의 테스트에서는 결과를 최대한 빨리 볼려고 사용하고 있습니다.

    다음과 같은 테스트를 해보시면 동작 여부를 확인할 수 있습니다.

    drop table t1 purge;

    create table t1(c1 int);

    insert into t1 values(1);

    commit;

    set serveroutput on

    declare
    v_usn number;
    begin
    for idx in 1 .. 100 loop
    update t1 set c1 = 1;

    select xidusn into v_usn from v$transaction
    where addr = (select taddr from v$session where sid = userenv('sid'));

    dbms_output.put_line('usn1 = ' || v_usn);

    commit;
    end loop;
    end;
    /


    col rollback_seg new_value v_rollback_seg

    select '_SYSSMU'||max(segment_id)||'$' as rollback_seg
    from dba_rollback_segs
    where tablespace_name = 'UNDOTBS1'
    and status = 'ONLINE'
    ;


    declare
    v_usn number;
    begin
    for idx in 1 .. 100 loop
    set transaction use rollback segment "&v_rollback_seg";

    update t1 set c1 = 1;

    select xidusn into v_usn from v$transaction
    where addr = (select taddr from v$session where sid = userenv('sid'));

    dbms_output.put_line('usn2 = ' || v_usn);

    commit;

    end loop;
    end;
    /

  5. Stargazer 2009.05.22 16:13 신고 Modify/Delete Reply

    제가 테스트한 버젼입니다.
    BANNER
    --------------------------------------------------------------------------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for HPUX: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    Elapsed: 00:00:00.10

    테스트 결과 지정한 undo segment 번호가 37 인데.

    테스트 결과..
    usn1 = 21
    usn1 = 23
    usn1 = 14
    usn1 = 10
    usn1 = 27
    usn1 = 17
    usn1 = 35
    usn1 = 29
    usn1 = 9
    usn1 = 20
    usn1 = 31
    usn1 = 34
    usn1 = 1
    usn1 = 33
    usn1 = 26
    usn1 = 12
    usn1 = 16
    usn1 = 3
    usn1 = 18
    ....
    37번을 쓰는게 아니라 다양한 undo sgement를 쓰는걸로 나왔습니다.
    아마 이것도 버젼에 따라 영향을 받지 않나봅니다.

    답변 감사합니다..

  6. 욱짜 2009.05.22 16:26 신고 Modify/Delete Reply

    과연 그렇군요!

    제가 테스트한 버전이 10.2.0.1이고 위의 테스트 버전이 10.2.0.4이니까 최소한 10.2.0.2 이상 혹은 적어도 10.2.0.4 이상부터는 더 이상 동작하지 않게 수정된 것으로 보입니다.

  7. 코요 2009.07.07 21:17 신고 Modify/Delete Reply

    스냅샷 에러가 났을때 1번문제인지 2번문제인지 판단할 수 있는 방법이 있을까요?

  8. 욱짜 2009.07.08 10:05 신고 Modify/Delete Reply

    10g 환경이라면 AWR에서 도움이 되는 정보를 얻을 수 있습니다.

    1. dba_hist_sysstat 뷰에서 "data blocks consistent reads - undo records applied" 지표값과 "transaction tables consistent reads - undo records applied" 지표값의 추이를 분석해서 뚜렷한 패턴이 나오는지 분석해볼 수 있습니다.

    2. dba_hist_latch 뷰에서 ORA-01555 에러와 함께 "row cache objects" latch에 대한 획득회수가 많으면 2번째 패턴일 수 있습니다.

  9. 김민성 2012.01.10 19:26 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바