참고 자료
http://ukja.tistory.com/178#recentComments

Snapahot too old( ORA-01555)

데이터를 읽어 내려가다가 쿼리 SCN이후에 변경된 블록을 만나( 이미 앞서 읽었던 블록을 다시 방문하는 경우 일 수도 있음) 과거 시점으로 롤백한 'Read Consistent' 이미지를 얻으려고 하는데, UNDO 블록이 다른 트랜잭션에 의해 이미 재사돼 필요한 Undo 정보를 얻을 수 없는 경우
커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인 할 수 없는 경우

일반 적인 사례들....

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

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

TEST..

설정


SQL> create undo tablespace small_undo datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERM\S_UNDO.DBF' SI
ZE 10M AUTOEXTEND OFF;

테이블스페이스가 생성되었습니다.

SQL> alter system set undo_tablespace = 'SMALL_UNDO';

시스템이 변경되었습니다.

SQL> alter system set db_cache_size=1M scope=both  ;

SQL>  show parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 4M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> show sga
Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             113249156 bytes
Database Buffers          490733568 bytes
Redo Buffers                7135232 bytes

SQL> --Select Query의 속도를 제어하기 위해 Sleep 함수를 만든다.
SQL> create or replace function fsleep(v1 int, vsleep int) 
  2  return number                                         
  3  is                                                    
  4  begin                                                 
  5    dbms_lock.sleep(vsleep);                            
  6                                                        
  7    return 1;                                           
  8  end;                                                  
  9  /                                                     

함수가 생성되었습니다.

SQL> drop table t1 purge;

테이블이 삭제되었습니다.

SQL> create table t1(c1 int, c2 varchar2(10)); 

테이블이 생성되었습니다.

SQL> insert into t1           
  2  select level, 'AAA'      
  3  from dual                
  4  connect by level < 10000 
  5  ;                        

9999 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> create index t1_n1 on t1(c1);

인덱스가 생성되었습니다.

SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno,
  2     dbms_rowid.rowid_block_number(rowid)as blkno           
  3   from t1 where rownum = 1;                                

ROWID                     FNO      BLKNO
------------------ ---------- ----------
AAAN/eAABAAAPBSAAA          1      61522

SQL> alter system dump datafile 1 block 61522;

시스템이 변경되었습니다.
--SESSION 1 번 실행중에 실행
SQL> alter system dump datafile 1 block 61522;

시스템이 변경되었습니다.
--SESSION 1 번 실행중에 실행
SQL>  alter system dump datafile 1 block 61522;

시스템이 변경되었습니다.

SESSION 1


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

...
---------- ----------
      3356 AAA
      3357 AAA
      3358 AAA
      3359 AAA
      3360 AAA
ERROR:
ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 14 수에 "_SYSSMU14$" 이름으로 된 것이
너무 작습니다

SESSION 2

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;
/

데이타 덤프


첫번째 덤프
Block header dump:  0x0040f052
 Object id on Block? Y
 seg/obj: 0xdfde  csc: 0x00.a750a5  itc: 2  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0010.02e.00000020  0x01400224.000a.10  --U-    1  fsc 0x0000.00a750a8
0x02   0x000d.025.00000020  0x0140020e.0007.18  --U-    1  fsc 0x0000.00a750a6

두번째 덤프
Block header dump:  0x0040f052
 Object id on Block? Y
 seg/obj: 0xdfde  csc: 0x00.a750a5  itc: 2  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0010.02e.00000020  0x01400224.000a.10  --U-    1  fsc 0x0000.00a750a8
0x02   0x000d.025.00000020  0x0140020e.0007.18  --U-    1  fsc 0x0000.00a750a6

세번재 덤프
Block header dump:  0x0040f052
 Object id on Block? Y
 seg/obj: 0xdfde  csc: 0x00.a83265  itc: 2  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000e.02b.0000004d  0x0140003e.000d.36  C---    0  scn 0x0000.00a83264
0x02   0x000d.023.00000066  0x0140002d.0012.21  --U-    1  fsc 0x0000.00a83266

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 회피 방법

과거 버전에는 UNDO 세그먼트를 수동으로 관리 했으므로 Snapshot too old 에러 발생을 최소화하기 위해 System Application 특성에 맞는 UNDO 튜닝을 DBA가 했음
9i부터 AUM( Automatic Undo Management)이 도입돼 UNDO segment 크기와 개수를 오라클이 동적으로 자동 조절해 주기 때문에 Snapshot too old에러가 발생할 가능성도 줄었음
Snapshot too old에러 발생 가능성을 줄이기 위한 Application 측변에서의 솔루션

1. 불필요한 커밋을 자주 수행하지 않는다.
2. fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현현다. ANSI 표준에 따르면 커밋 이전에 열려 있던 커서는 더는 Fetch하면 안 된다.
다른 방식으로 구현하기 어렵다면 커밋 횟수를 줄여본다.
3. 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간을 조정한다.
4. 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행 할 수 있도록 코딩한다. Snapchot too old 발생 가능성을 줄일 뿐 아니라 문제가 발생했을 때 특정 부분부터 다시 시작할 수 있어 유리하다. 물론 그렇게 해도 읽기 일관성에 문제가 없을때에만 적용해야 한다.
5. 오랜 시간에 걸쳐 같은 블록을 여러번 방문하는 Nested loop형태의 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 체크하고 이를 회피할 수 있는 방법을( 조인 메소드 변경, Full Table Scan등)을 찾는다.
6. 소트 부하를 감수하더라도 order by 등을 강제로 산입해 소트 연산이 발생하도록 한다. 많은 데이터를 오랜 시간에 걸쳐 Fetch하는 동안 Undo 정보를 지속적으로 참조하기 때문에 문제가 발생하는 것이므로, 서버내에서 빠르게 데이터를 읽어 Temp 세그먼트에 저장하는 데에만 성공하면 이후에는 같은 블록을 아무리 재방문 하더라도 더는 에러가 발생할까 걱정하지 않아도 된다.
7. 만약 delayed 블록 클린아웃에 의해 Snspshot too old가 발생하는 것으로 의심되면 대량 업데이트 후에 곧바로 해당 테이블에 대해 Full Scan 하도록 쿼리를 날리는 것도 하나의 해결 방법이 될 수 있다. 인덱스 블록에서 문제가 발생한다고 되면 인덱스 리프 블록을 모두 스캔하도록 쿼리한다.

Snapshot too old (by ljh1148) [2010.03.17]