Automatic Undo Retention 자동 언두 리텐션 기능

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

방법 : Automatic Undo Retention 자동 언두 리텐션 기능

자동 언두 리텐션(automatic undo retention) 기능은 오라클 10g부터 제공하는 기능으로써, 다이나믹하게 언두 리텐션의 수치를 조절함

자동 언두 리텐션 기능을 제공함으로써 부적절하게 설정된 UNDO_RETENTION 파라미터로 인한 ORA-01555 발생을 최소화할 수 있음

자동 언두 리텐션 기능의 동작 방식은 오라클 10gR1 과 오라클 10gR2간에도 차이가 나며,

undo guarantee 사용 여부와 언두 데이터 파일의 autoextend 사용 여부에 따라 다르게 동작함

언두 테이블스페이스를 undo guarantee 모드로 사용할 경우, 언두 리텐션을 보장함

즉, 언두 테이블스페이스의 공간 부족 현상이 발생할 경우에도, UNEXPIRED 된 언두 익스텐트를 재사용하는 것을 방지함으로써, 언두 리텐션을 보장하는 것

오라클 9i까지는 언두 테이블스페이스가 부족한 경우, UNEXPIRED 된 언두 익스텐스를 재사용함에 따라 언두 리텐션을 보장하지 못하였음 

언두 테이블스페이스의 undo guarantee 사용 여부는 언두 테이블스페이스 생성 시점 또는 생성후에 변경이 가능함

undo guarantee 활성화 비활성화 방법

undo retention guarantee 확인

SQL> select tablespace_name, retention
from dba_tablespaces
where tablespace_name ='UNDOTBS1';
 
TABLESPACE_NAME      RETENTION
------------------ -----------
UNDOTBS1               NOGUARANTEE
 
1 row selected.

undo retention guarantee로 변경

SQL> alter tablespace undotbs1 retention guarantee ;
 
Tablespace altered.

undo retention guarantee 확인

SQL> select tablespace_name, retention
from dba_tablespaces
where tablespace_name ='UNDOTBS1';
 
TABLESPACE_NAME      RETENTION
-----------------  -----------
UNDOTBS1               GUARANTEE
 
1 row selected.

undo retention noguarantee로 변경

SQL> alter tablespace undotbs1 retention noguarantee ;
 
Tablespace altered.

undo retention guarantee 확인

SQL> select tablespace_name, retention
from dba_tablespaces
where tablespace_name ='UNDOTBS1';
 
TABLESPACE_NAME      RETENTION
------------------ -----------
UNDOTBS1               NOGUARANTEE
 
1 row selected.

자동 언두 리텐션 기능으로 인해 재설정된 언두 리텐션 수치는 

V$UNDOSTAT 뷰의 TUNED_UNDORETENTION 칼럼을 통해 확인할 수 있음

V$UNDOSTAT 뷰는 오라클 9iR2부터 제공되며 언두 사용 

통계정보를 10분 단위로 샘플링하며, 언두 사용에 대한 유용한 통계정보를 제공함

V$UNDOSTAT 뷰를 사용한 쿼리

SQL> 
select to_char(a.begin_time,'HH24:MI:SS') begin,
       to_char(a.end_time,'HH24:MI:SS') end,
       a.maxquerylen max_q_len,
       a.maxqueryid max_q_id,
       a.tuned_undoretention tuned_ur,
       substr(b.sql_text,1,30) sql_text
from v$undostat a, v$sql b
where a.maxqueryid = b.sql_id(+)
and rownum <= 4;
 
BEGIN       END          MAX_Q_LEN  MAX_Q_ID      TUNED_UR SQL_TEXT
-------- -------- ---------- ------------- ---------- -------------------------------
16:40:11 16:49:11          406  89w8y2pgn25yd    35705     select ts# from ts$ where ts$.
16:30:11 16:40:11          949  0rc4km05kgzb9    29760     select 1 from obj$ where name=
16:20:11 16:30:11          345  0rc4km05kgzb9    22343     select 1 from obj$ where name=
16:10:11 16:20:11            0  (null)            14960     (null)
 
4 rows selected.

참조 : http://wiki.gurubee.net/pages/viewpage.action?pageId=9601094