무엇이 가장 많거나 적은 언두를 생성하는가?

• 인덱스로 인해 매우 많은 언두 정보를 생성할 수 있다.
• INSERT는 일반적으로 가장 적은 언두를 생성한다. delete 할 rowid 만 기록.
• UPDATE는 두 번째로 언두 양이 적다. 변경된 바이트만 기록하기 때문.
• DELETE는 일반적으로 가장 많은 언두를 생성한다. 삭제하는 데이터 전체에 대한 언두를 생성한다.

• 인덱스로 인해 생성되는 언두량 비교테스트

SQLPLUS > create table t
  2  as
  3  select object_name unindexed,
  4         object_name indexed
  5    from all_objects
  6  /

Table created.

SQLPLUS > create index i_idx on t(indexed);

Index created.

SQLPLUS > exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

• 다음 쿼리로 언두 양을 조회한다.

SQLPLUS > select used_ublk
  2  from v$transaction
  3  where addr = ( select taddr
  4		     from v$session
  5		   where sid = ( select sid from v$mystat where rownum = 1 )
  6		   )
  7 /


 USED_UBLK
----------
	69

SQLPLUS > commit ;

Commit complete.


-- UPDATE 가 언두를 저장하는데 69개 블록을 사용함.

-- commit 이후에는 해당 정보를 없애거나 해제하므로,
위 select 쿼리로 다시 조회하면 no rows selected 결과가 출력된다.

• 인덱스 컬럼을 수정할 경우

SQLPLUS >update t set indexed = lower(indexed) ;

17679 rows updated.

SQLPLUS >select used_ublk
from v$transaction
where addr = ( select taddr
		     from v$session
 		   where sid = ( select sid from v$mystat where rownum = 1 )
		   )
/


 USED_UBLK
----------
       337


-- 3배가 넘는 언두가 생성됐다.

• 결론 : 복잡한 인덱스 구조 자체모습대로 언두를 생성하고 테이블의 모든 row 마다 수정을 했기 때문.

ORA-01555: snapshot too old Error

에러의 원인

1. 작업량에 비해 언두 세그먼트가 너무 작다
2. 데이터를 fetch 하는 중간 commit 하도록 프로그래밍 했다.
3. 블록 클린아웃

ORA-01555 오류의 일반적인 해결책

1. UNDO_RETENTION 파라미터를 적절하게 설정 (가장 오래 수행하는 트랜잭션에 걸리는 시간보다 많게.)
   • V$UNDOSTAT 은 장시간 수행되는 쿼리에 걸린시간을 확인할 때 사용할 수 있다.
   • UNDO_RETENTION 을 근거로 필요한 만큼 언두세그먼트 크기를 증가시킬 수 있도록 공간확보.
2. 수동 언두관리 시 좀 더 많은 언두 세그먼트를 추가하라. 그러나 자동 언두관리를 적극 추천한다.
3. 쿼리 수행시간을 단축하도록 튜닝하라. 위 세가지 원인에 대한 해결책.
4. 객체에 대한 통계정보를 수집하라. 블록클린아웃을 피하기 위함.

• 블록 클린아웃은 대량 UPDATE/INSERT의 결과로, 이후 통계 재수집이 필요하다.

언두 세그먼트가 너무 작다

• 언두 세그먼트는 순환방식으로 평균 약 3~4분마다 언두 공간을 재사용한다.
• 만일 5분 소요되는 리포팅 쿼리가, 쿼리 시작시점 기준 데이터 뷰가 필요할 경우 ORA-01555오류 발생 확률이 높다.
• 쿼리 실행중 덮어써진 데이터에 대한 블록을 다시 찾아간다면, 언두정보가 없기 때문에 에러발생후 종료된다.

(예시) 장시간 실행하는 쿼리의 타임라인

언두 관리 방법 (오라클9i)

• 자동 언두 관리 : UNDO_RETENTION 파라미터로 오라클이 얼마 동안 언두를 유지해야 하는지 지정.
  • DBA가 설정한 UNDO_RETENTION 목표치에 맞추기 위해 실행 시에 개별 언두 세그먼트 간에 익스텐스를 재할당 할 수 있다.
• 수동 언두 관리 : DBA가 언두를 관리. 예측 또는 관찰된 작업량에 기반하여 수동으로 언두세그먼트를 만들지 결정한다.
  • 수동 관리 언두 세그먼트는 미리 크게 잡아서, 언두 세그먼트 스스로 증가할 기회를 주지 말고 언두 세그먼트 공간에 고정되도록 할당할 필요가 있다.

자동 언두관리 : UNDO_RETENTION

• 쿼리가 얼마나 오래 실행하는지 확인하고 UNDO_RETENTION 파라미터 값을 설정해야 한다.
• 설정 시간만큼 언두를 보존하려 한다.

수동 언두관리

• 쿼리를 처리하는 동안 가장 작은 언두 세그먼트를 선택해서 순환하므로 ORA-01555 발생 확률이 크다.
• 필자는 기존 언두 세그먼트를 사용할 때 롤백 세그먼트 크기를 동일하게 설정한다.

언두세그먼트 공간 사용 테스트

• 작은 언두 테이블스페이스 생성, 작은트랜잭션을 많이 발생시킨다.
• 언두 세그먼트를 사용하는 세션 A 는 테이블 T를 변경할 것. A는 테이블 T전체 스캔 / B세션에서 인덱스를 통해 테이블 T를 읽음

-- 세션 A
SQLPLUS > create undo tablespace undo_small
  2  datafile '/tmp/undo.dbf' size 2m
  3  autoextend off ;

Tablespace created.


SQLPLUS > alter system set undo_tablespace = undo_small ;

System altered.

• 테이블 T 생성. 테이블에서는 데이터를 무작위로 정렬한다.

SQLPLUS > create table t
  2  as
  3  select * from all_objects
  4  order by dbms_random.random ;

Table created.

SQLPLUS > alter table t add constraint t_pk primary key (object_id) ;

Table altered.


SQLPLUS > exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

• 테이블T 변경작업 실행

SQLPLUS > begin
for x in ( select rowid rid from t )
loop
        update t set object_name = lower(object_name) where rowid = x.rid ;
        commit;
end loop ;
end;
/

• 세션B 에서 쿼리를 실행한다. T 테이블을 읽고 각 로우를 처리한다.

SQLPLUS > declare
cursor c is
select /*+ first_rows */ object_name
  from t
order by object_id ;

l_object_name t.object_name%type ;
l_rowcnt  number := 0 ;

begin
    open c;
    loop
        fetch c into l_object_name ;
        exit when c%notfound ;
        dbms_lock.sleep( 0.01 ) ;
        l_rowcnt := l_rowcnt +  1 ;
    end loop ;
    close c ;
exception
    when others then
        dbms_output.put_line ( 'rows fetched = ' || l_rowcnt ) ;
        raise;
    end ;
/

rows fetched = 301
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 46 with name
"_SYSSMU46_2555494716$" too small
ORA-06512: at line 21

위 현상을 해결 하기위해서는

• 읽기 프로세스가 충분히 완료할 수 있을 만큼의 UNDO_RETENTION 을 크게 설정해야 함.
• 프로세스가 완료될 때 까지 충분한 언두를 보유하도록 한다.
• 언두 테이블스페이스가 증가하도록 설정하거나, 수동으로 디스크 공간을 더 많이 할당한다.

• 오랫동안 실행하는 프로세스 완료가 약 720초 수행 걸린다고 했을 때, UNDO_RETENTION 을 900 ( 초 단위. 15분 ) 으로 설정.
• 한 번에 1MB 씩 증가해서 2GB까지 증가되도록 언두 테이블스페이스 데이터를 변경.

• 설정 이후 완료됨. 언두 테이블스페이스의 데이터파일 증가.

• snap shot too old 에러 발생 원인은 T 테이블을 인덱스를 경유하여 무작위로 읽어 발생했다.
• 테이블을 빠르게 전체 스캔하면 오류가 나지 않을 가능성이 크다.
• SELECT 와 UPDATE 둘 다 테이블 T 전체를 스캔하나, SELECT스캔하는 동안 UPDATE 를 앞질러 갈 수 있기 때문.
• (SELECT는 읽기만 / UPDATE 는 읽기 + 변경)

지연된 블록 클린아웃

1. 블록을 변경한 후 이 블록을 액세스하는 다음 세션이 최종으로 블록을 변경한 트랜잭션이 여전히 활동중인지 체크
2. 블록을 변경한 트랜잭션이 활동하지 않음이 확인되면, 해당 블록을 액세스하는 다음 세션이 똑같은 과정을 겪지 않도록 해당 블록을 클린아웃.
3. 블록 클린아웃을 위해 블록 헤더로부터 이전 트랜잭션이 사용한 언두 세그먼트를 확인
4. 해당 트랜잭션이 커밋했는지, 언제 커밋했는지 확인하는 방법
   • COMMIT SCN이 언두 세그먼트 트랜잭션 테이블에 존재하는지와 그 트랜잭션 슬롯이 덮어 써지지 않았다는 것을 확인하는 것.

지연된 블록 클린아웃으로 ORA-01555 오류 발생하기 위한 조건

• 변경 후 커밋했지만, 블록은 자동 클린아웃이 안됨 ( ex. 트랜잭션이 SGA블록 버퍼 캐시의 10% 초과 블록을 변경 )
• 클린아웃되지 않은 블록을 방문하는 세션이 장시간 존재하지 않음.
• 오랜시간 걸리는 쿼리가 시작된 직후, 클린아웃되지 않은 일부 블록을 읽는다. 해당 쿼리가 SCN t1 에 시작, 읽기일관성을 위해 T1 시점으로 롤백한 읽기 일관적인 데이터 뷰를 제공해야 한다. 변경 트랜잭션에 대한 트랜잭션 엔트리는 쿼리가 시작할 때 언두 세그먼트 트랜잭션 테이블에 있다.
• 쿼리 실행중 시스템에서 커밋이 많이 일어나지만, 이 트랜잭션은 해당 블록을 방문하지 않는다.
• 언두 세그먼트에서 트랜잭션 테이블은 순환하면서 잦은 커밋으로 인해 슬롯을 재사용한다. 시스템이 언두 세그먼트 익스텐트를 재사용하다보니 언두 세그먼트 헤더 블록 자체에 대한 읽기가 불가능하게 됐다.
• 언두 세그먼트에 기록된 가장 작은 SCN이 너무 잦은 커밋으로 인해 t1 (쿼리의 읽기 일관성 SCN보다 큼) 보다 커졌다.

지연된 블록 클린아웃으로 발생하는 ORA-01555 오류 발생 테스트

• 큰 테이블 생성 ( 클린아웃 필요한 테이블 블록을 많이 생성 )

SQLPLUS > create undo tablespace undo_small
2 datafile '/tmp/undo.dbf'  size 4m
3 autoextend off
4 /
Tablespace created.

SQLPLUS > create table big
2 as
3 select a.*, rpad ('*', 1000, '*') data
4  from all_objects a;
Table created.

 SQLPLUS > alter table big add constraint big_pk
2 primary key (object_id);
Table altered.

SQLPLUS > exec dbms_stats.gather_table_stats(user, 'BIG') ;
PL/SQL procedure successfully completed.

• 여러개의 작은 트랜잭션이 변경할 테이블 생성

SQLPLUS > create table small ( x int, y char(500) );
Table created.

SQLPLUS > insert into small select rownum , 'x' from all_users ;
44 rows created.

SQLPLUS > commit ;
Commit complete.

SQLPLUS > exec dbms_stats.gather_table_stats(user, 'SMALL') ;
PL/SQL procedure successfully completed.

• big 테이블 변경하여 dirty 블록을 만든다. 언두가 작기때문에 가능한 언두를 작게 생성하면서 big테이블 블록 수 만큼 수정.
• 모든 블록을 수정하여 버퍼 캐시에 dirty 블록이 다수 존재하도록 하되 언두 테이블스페이스 용량을 초과하지 않도록 첫 번째 row 만 update 한다.

SQLPLUS > alter system set undo_tablespace = undo_small ;
System altered.

-- 블록의 첫 번째 row id 를 찾아 temporary ( VARCHAR2(1) 필드를 수정한다 )
SQLPLUS > update big
 set temporary = temporary
where rowid in
( select r from (
      select rowid r, row_number() over ( partition by dbms_rowid.rowid_block_number(rowid) order by rowid) rn
     from big ) where rn = 1
)
/
12014 rows updated.

SQLPLUS > commit ;
Commit complete.

• 결과집합을 확정시키기 위해 커서를 오픈하여, 방금 UPDATE 하고 커밋한 데이터를 가져온다.
• 최근 변경 블록이면 오라클이 해당 트랜잭션이 쿼리를 시작하기 전 커밋됐는지 확인할 의무가 있고, 만일 그 정보가 이미 덮어 쓰였다면 쿼리는 실패할 것이다.

• 9개의 세션으로 실행한다.

• 각 세션이 실행한 스크립트는 test2.sql 은 다음과 같다.

• 9개 세션은 각 루프 내에서 많은 update 트랜잭션을 발생시키고, 모든 세션이 작업 완료할 때 까지 기다리고 커서를 오픈한 세션으로 리턴한다. 결과 출력하면 다음과 같다.

• 지연된 블록 클린아웃으로 인한 ORA-01555 에러 현상은 흔히 발생하진 않는다.
• 대량 UPDATE 작업 후 통계정보 재수집 시 클린아웃 되지않은 블록이 해소되기 때문.
• 대부분 트랜잭션은 버퍼 캐시에 수용가능한 블록의 10%이내로 작업하기 때문에 클린아웃이 필요한 블록을 생성하지 않음.

DML이 적은 테이블 대상으로 SELECT 가 ORA-01555 에러 발생 시 해결책

• 적절한 크기의 트랜잭션을 사용하는지 확인. 필요 이상으로 자주 커밋하지 않는지 확인.
• DBMS_STAT 을 사용하여 통계정보를 수집하라.
• 언두 테이블스페이스 확장공간을 제공하고, 언두 보유시간을 늘려라.
• 쿼리 실행시간을 줄이도록 튜닝하라. 항상 최선의 해결책이자 우선적으로 시도할 작업이다.