Automatic Undo Retention 개요 [펌]

[출처] http://blog.naver.com/j0ngmali?Redirect=Log&logNo=44832725

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 guarantee" 모드를 확인한다. SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1'; TABLESPACE_NAME RETENTION ------------------------------ ----------- UNDOTBS1 NOGUARANTEE -- “undo guarantee" 모드로 변경한다. SQL> alter tablespace undotbs1 retention guarantee; -- 현재의 언두 테이블스페이스에 “undo guarantee" 모드를 확인한다. SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1'; TABLESPACE_NAME RETENTION ------------------------------ ----------- UNDOTBS1 GUARANTEE -- “undo noguarantee" 모드로 변경한다. SQL> alter tablespace undotbs1 retention noguarantee; 이러한 자동 언두 리텐션 기능으로 인해 재설정된 언두 리텐션 수치는 V$UNDOSTAT 뷰의 TUNED_UNDORETENTION 칼럼을 통해 확인할 수 있다. V$UNDOSTAT 뷰는 오라클 9iR2부터 제공되며 언두 사용 통계정보를 10분단위로 샘플링하며, 언두 사용에 대한 유용한 통계 정보를 제공한다. Name type --------------------------------- BEGIN_TIME DATE END_TIME DATE UNDOTSN NUMBER UNDOBLKS NUMBER TXNCOUNT NUMBER MAXQUERYLEN NUMBER MAXQUERYID VARCHAR2(13) --오라클 10gR1 부터 추가 MAXCONCURRENCY NUMBER UNXPSTEALCNT NUMBER UNXPBLKRELCNT NUMBER UNXPBLKREUCNT NUMBER EXPSTEALCNT NUMBER EXPBLKRELCNT NUMBER EXPBLKREUCNT NUMBER SSOLDERRCNT NUMBER NOSPACEERRCNT NUMBER ACTIVEBLKS NUMBER --오라클 10gR1 부터 추가 UNEXPIREDBLKS NUMBER --오라클 10gR1 부터 추가 EXPIREDBLKS NUMBER --오라클 10gR1 부터 추가 TUNED_UNDORETENTION NUMBER --오라클 10gR1 부터 추가 이제부터 자동 언두 리텐션 기능의 동작 방식을 테스트를 통해 확인해보자. 테스트를 위한 파라미터 기본 설정 ● _UNDO_AUTOTUNE=TRUE (기본설정 값) ● UNDO_RETENTION=0 테스트 초기화 -- Long query를 수행할 목적의 테이블을 생성한다. SQL> create table undo_layer_t4 as select * from dba_objects nologging; 테스트 1 - Retention Guarantee=NO Autoextend=NO SQL> alter tablespace undotbs1 retention noguarantee; SQL> alter database datafile '/home/oracle/oradata/TRANS/undotbs01.dbf' autoextend off; -- 오라클 재기동 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 09:10:32 09:11:10 0 600 -- 재기동 몇 분 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 09:10:32 09:14:02 0 345600 -- Long query 수행 session A> select count(*) from undo_layer_t4, undo_layer_t4, undo_layer_t4; -- Long query 수행 후에 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 09:20:32 09:30:32 229 9q53jtqa9kk4q 345600 select count(*) 09:10:32 09:20:32 0 345600 테스트 1 - 결과 분석 오라클이 기동된 직후에 TUNED_UNDORETENTION은 600초로 설정되며, 몇 분후 345,600초로 자동 설정된다. 345,600초는 언두 테이블스페이스의 크기와 관련이 있는 것으로 보인다. 즉, 언두 테이블스페이스는 확장이 불가능하므로, 현재의 크기에 해당되는 최대값으로 설정하는 것이며, 쿼리 수행 시에 MAXQUERYLEN, MAXQUERYID 칼럼에 값을 등록한다. 테스트 2 - Retention Guarantee=NO Autoextend=YES SQL> alter tablespace undotbs1 retention noguarantee; SQL> alter database datafile '/home/oracle/oradata/TRANS/undotbs01.dbf' autoextend on next 10m; -- 오라클 재기동 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ----------------------- 09:40:27 09:41:54 0 600 -- 재기동 몇 분 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------------ 09:40:27 09:44:48 0 600 -- Long query 수행 session A> select count(*) from undo_layer_t4, undo_layer_t4, undo_layer_t4; -- Long query 수행 후에 반복적으로 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:51:52 262 9q53jtqa9kk4q 600 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:52:40 262 9q53jtqa9kk4q 922 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:54:11 262 9q53jtqa9kk4q 986 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:54:41 262 9q53jtqa9kk4q 1046 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:55:41 262 9q53jtqa9kk4q 1106 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:56:41 566 9q53jtqa9kk4q 1166 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:57:41 566 9q53jtqa9kk4q 1226 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:58:42 566 9q53jtqa9kk4q 1287 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 09:50:27 09:59:42 566 9q53jtqa9kk4q 1347 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 10:00:27 10:00:45 566 9q53jtqa9kk4q 1410 select count(*) 09:50:27 10:00:27 566 9q53jtqa9kk4q 1410 select count(*) 09:40:27 09:50:27 0 600 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 10:00:27 10:01:45 870 9q53jtqa9kk4q 1470 select count(*) 09:50:27 10:00:27 566 9q53jtqa9kk4q 1410 select count(*) 09:40:27 09:50:27 0 600 테스트 2 - 결과 분석 오라클이 기동된 직후에 TUNED_UNDORETENTION은 600초로 설정되며, 쿼리가 수행되지 않을 경우 TUNED_UNDORETENTION은 600초로 유지한다. 장시간 쿼리가 수행될 경우에, MAXQUERYLEN 칼럼의 값은 300초 단위로 갱신된다. TUNED_UNDORETENTION 값은 장시간의 쿼리를 감지한 직후에는 대략 MAXQUERYLEN + UNDORETENTION + 60초로 증가하며, 그 이후로는 매 60초 단위로 증가하게 된다. 즉, 언두 테이블스페이스가 확장 가능하므로, 쿼리의 수행시간에 근거하여 언두 리텐션을 튜닝 한다. 테스트 3 - Retention Guarantee=YES Autoextend=NO SQL> alter tablespace undotbs1 retention guarantee; SQL> alter database datafile '/home/oracle/oradata/TRANS/undotbs01.dbf' autoextend off; -- 오라클 재기동 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------- 10:26:05 10:26:25 0 600 -- 재기동 몇 분 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 10:26:05 10:27:24 0 345600 -- Long query 수행 session A> select count(*) from undo_layer_t4, undo_layer_t4, undo_layer_t4; -- Long query 수행 후에 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 10:26:05 10:33:20 207 9q53jtqa9kk4q 345600 select count(*) 테스트 3 - 결과 분석 테스트 결과의 내용은 테스트 1의 결과와 동일하다. 다만, "undo guarantee" 모드를 사용함에 따라, 언두 테이블스페이스의 공간 부족이 발생할 시점의 동작 방식이 차이가 있다. "undo guarantee" 모드는 언두 리텐션을 보장하는 것에 우선순위가 있으므로, 언두 세그먼트를 확장하려고 시도하는 트랜잭션은 ORA-30036 이 발생한다. “undo guarantee" 모드를 사용하지 않는다면, 이러한 경우, 다른 언두 세그먼트의 익스텐트를 스틸링(stealing)한다. 테스트 4 - Retention Guarantee=YES Autoextend=YES SQL> alter tablespace undotbs1 retention guarantee; SQL> alter database datafile '/home/oracle/oradata/TRANS/undotbs01.dbf' autoextend on next 10m; -- 오라클 재기동 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ----------------------- 10:44:22 10:45:23 0 600 -- 재기동 몇 분 후 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------------ 10:44:22 10:48:33 0 600 -- Long query 수행 session A> select count(*) from undo_layer_t4, undo_layer_t4, undo_layer_t4; -- Long query 수행 후에 반복적으로 V$UNDOSTAT 확인 SYS session> @chk_undostat BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 10:54:22 10:55:35 377 9q53jtqa9kk4q 600 select count(*) 10:44:22 10:54:22 0 600 BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 10:54:22 10:56:44 377 9q53jtqa9kk4q 1040 select count(*) 10:44:22 10:54:22 0 600 BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 10:54:22 10:57:35 377 9q53jtqa9kk4q 1100 select count(*) 10:44:22 10:54:22 0 600 ... BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT -------- -------- ---------- ------------- ---------- ------------------------ 10:54:22 11:00:39 681 9q53jtqa9kk4q 1281 select count(*) 10:44:22 10:54:22 0 600 테스트 4 - 결과 분석 테스트 결과의 내용은 테스트 2의 결과와 동일하다. 다만, "undo guarantee" 모드를 사용함에 따라, 언두 테이블스페이스의 공간 부족이 발생할 시점의 동작 방식이 차이가 있다는 것이다.   Appendix 3) chk_undostat.sql set linesize 140 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,15) sql_text from v$undostat a, v$sql b where a.maxqueryid=b.sql_id(+) and rownum<=4;