꿈꾸는 개발자, DBA 커뮤니티 구루비

과거에는 DBA가 다음과 같은 사항을 결정해야만 했다.
1. Automatic Undo Retention

과거에는 DBA가 다음과 같은 사항을 결정해야만 했다.

언두 세그먼트 헤더를 두고 벌이는 경합을 줄이기 위해 필요한 롤백 세그먼트의 수
각 롤백 세그먼트에 할당해야 할 익스텐트의 수
익스텐트의 크기
공간 재활용을 위한 롤백 세그먼트의 되돌림 속도

1. Automatic Undo Retention

Oracle 9i에서는 ORA-1555 error가 가끔 발생하여 DBA가 이에 대한 조정을 해줄 필요가 있었다.
그러나 Oracle 10g부터는 UNDO_RETENTION에 대한 자동 튜닝 기능을 제공하게 되었다.
따라서, ORA-1555에러가 발생하지 않도록 자동으로 UNDO RETENTION을 튜닝한다.

1) Acuomatic Undo Retentiion의 기본 세팅

undo_retentiion=0
SMU(System Managed Undo)를 사용한다.

2) 자동 튜닝의 방식

UNDO_RETENTION을 0으로 세팅하면 UNDO_RETENTION의 최소값은 900초가 된다.
MMON process가 매 30초마다 query duration을 계산한다.
MAXQUERYLEN 이라는 값을 계산하는데 이 값에 따라서 MMON은 TUNED_UNDORETENTION이라는 수치를 결정한다.
이것은 새로운 UNDO RETENTION 값이 TUNED_UNDORETENTION로 셋팅이 됨을 의미한다.
TUEND_UNDORETENTION = MAXQUERYLEN+ 300(sec)


SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_retention                       integer     900

3) test..


SQL> create undo tablespace undo_big datafile size 1m
  2  autoextend on next 1m maxsize 2048M ;

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

SQL> 
SQL> create undo tablespace undo_small datafile size 1m
  2  autoextend off ;

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

SQL> 
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
SQL> 
SQL> alter system set undo_tablespace=undo_big scope=memory;

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

SQL> create table t ( x char(2000), y char(2000), z char(2000));

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

SQL> insert into t values ( 'x','x','x');

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

SQL> begin
  2  from i in 1 .. 500
  3  lop
  4  update t set x=i, y=i, z=i;
  5  commit;
  6  end loop;
  7  end;
  8  /
from i in 1 .. 500

SQL> begin
  2  for i in 1 .. 500
  3  loop
  4  update t set x=i, y=i,z=i;
  5  commit;
  6  end loop;
  7  end;
  8  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select bytes, maxbytes
  2   from dba_data_files
  3  where tablespace_name='UNDO_BIG';

     BYTES   MAXBYTES
---------- ----------
   5242880 2147483648

결론. 오라클은 Undo 데이터 파일을 확장하지 않았지만(확장하도록 허용되지 않았지만),
  그렇다고 트잰잭션이 수행되지 않은 것도 아니다.
  오히려 오라클은 전혀 롤백이 필요 없는 언두 정보를 사전에 말료시켰다.

UNDO 테이블 영역에 대한 주의사항

지정가능한 것
데이터 파일의 최대 크기를 제한하거나 데이터 파일의 자동 확장 속성을 해제함으로써 UNDO 테이블스페이스 영역의 궁극적인 크기를 지정할 수 있다.
UNDO 테이블스페이스 영역의 지원에 사용되는 데이터 파일의 이름을 지정할 수 있다.
현행 UNDO 테이블 영역의 세그먼트 확장 알고리즘은 다른 세그먼트의 만료된 익스텐트를 재사용하기 전에 롤백 세그먼트를 키운다. 이러한 이유 때문에 프로덕션 환경에서는 UNDO 테이블 영역에 자동 확장 데이터 화일을 사용하지 않도록 하여야 한다.

UNDO SIZE 줄이는 방법

1. 새로운 UNDO 테이블 영역을 생성한다.


CREATE UNDO TABLESPACE UNDO_NEW DATAFILE SIZE

2. 이 UNDO 테이블 영역을 사용한다.


ALTER SYSTEM SET UNDO_TABLESPACE=UNDO_NEW SCOPE=BOTH

3. 이전의 UNDO 테이블 영역을 삭제한다. "ORA-30013 : undo tablespace is currently in use" 메시지가 나타날 수도 있다. 이 메시지는 여전히 이 UNDO 테이블 영역을 필요로 하는
( 즉, 여전히 이 테이블 영역을 이용하고 있는 ) 트랜잭션이 존재함을 의미한다. 이 경우에는 삭제하기 전에 얼마동안 기다려야 한다.


DROP TABLESPACE OLD_UNDO TABLESPACE