3.4 테스트를 통한 Undo Retention 및 Automatic Undo Retention 동작 방식 분석 (by darkturtle) [2011.05.25]
Undo Retention 개요
언두 리텐션이란 지정된 수치(오라클 9i까지는 UNDO_RETENTION 파라미터의 설정 값) 동안은 트랜잭션이 종료된 후에라도,
트랜잭션과 관련되었던 언두 세그먼트를 재사용하지 못하도록 하는 기능이다.
하지만, 오라클 9i까지는 언두 테이블스페이스의 공간 부족 현상이 발생할 경우 지정된 수치에 도달하기 전에도 언두 세그먼트의
재사용이 가능하였다. 언두 리텐션 기능은 언두 세그먼트의 인그텐트 단위로 관리되며, 각 인스텐트 당 Commit Time 을 관리하여
언두 리텐션 기간을 계산하게 된다. 언두 리텐션과 관련된 내용은 언두 세그먼트 헤더 블록의 리텐션 테이블에서 관리된다.
커밋 전과 커밋 후의 리텐션 테이블의 변화 사항을 테스트를 통해 확인해보자.
Session_A>create table undo_layer_t2 ( c1 number ,c2 varchar2(10), c3 char(20)) ;
Table created.
Session_A>insert into undo_layer_t2 values ( 1, 'A','a');
1 row created.
Session_A>select sid from v$mystat where rownum = 1 ;
SID
----------
148
SYS_Session>select xidusn, xidslot, xidsqn
2 from v$transaction
3 where addr=(select taddr from v$session where sid = 148 ) ;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 34 973
SYS_Session>alter system dump undo header '_SYSSMU2$';
System altered.
SYS_Session>select extent_id, file_id, block_id, blocks, status
2 from dba_undo_extents
3 where segment_name ='_SYSSMU2$' ;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS STATUS
---------- ---------- ---------- ---------- ---------------
0 2 25 8 EXPIRED
1 2 33 8 EXPIRED
2 2 204169 1024 ACTIVE
Session_A>commit ;
Commit complete.
SYS_Session>select extent_id, file_id, block_id, blocks, status
2 from dba_undo_extents
3 where segment_name ='_SYSSMU2$' ;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS STATUS
---------- ---------- ---------- ---------- ---------------
0 2 25 8 EXPIRED
1 2 33 8 EXPIRED
2 2 204169 1024 EXPIRED
SYS_Session>alter system dump undo header '_SYSSMU2$';
System altered.
-- Commit 전
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1305867652
Extent Number:1 Commit Time: 1305867652
Extent Number:2 Commit Time: 1305867652
-- Commit 후
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1305867652
Extent Number:1 Commit Time: 1305867652
Extent Number:2 Commit Time: 1305867652
테스트 결과
( 결과 )
예상과 달리 DBA_UNDO_EXTNETS 뷰의 STATUS 칼럼 값은 EXPIRED 로 변경되고,
언두 리텐션 테이블의 Commit Time 수치에 아무런 변화가 없다.
( 예상 )
커밋 수행 후에는 언두 리텐션 테이블의 Commit Time 이 커밋 시점으로 변경되고, DBA_UNDO_EXTENTS 뷰의
STATUS 칼럼 값은 UNEXPIRED 로 변경될 것 이라고 예상
어떠한 트랜잭션을 수행해야만 UNEXPIRED 상태로 변경되는 것일까 ?
Undo Retention 동작방식 테스트
Case 1: 언두 세그먼트의 익스텐트를 1개 이상 사용하는 경우
Session_A>create table undo_layer_t3 ( c1 number, c2 varchar2(10), c3 char(20)) ;
Table created.
Session_A>begin
2 for i in 1 .. 10000 loop
3 insert into undo_layer_t3 values (i, 'c2','c3');
4 end loop ;
5 commit ;
6 end ;
7 /
PL/SQL procedure successfully completed.
Session_A>delete from undo_layer_t3 where rownum <= 5000 ;
5000 rows deleted.
Session_A>select sid from v$mystat where rownum = 1 ;
SID
----------
148
SYS_Session>select xidusn, xidslot, xidsqn
2 from v$transaction
3 where addr=(select taddr from v$session where sid = 148 ) ;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
8 9 925
SYS_Session>alter system dump undo header '_SYSSMU8$';
System altered.
SYS_Session>select extent_id, file_id, block_id, blocks, status
2 from dba_undo_extents
3 where segment_name ='_SYSSMU8$';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS STATUS
---------- ---------- ---------- ---------- ---------------
0 2 121 8 ACTIVE
1 2 385 8 ACTIVE
2 2 393 128 ACTIVE
Session_A>commit ;
Commit complete.
SYS_Session>alter system dump undo header '_SYSSMU8$';
System altered.
SYS_Session>select extent_id, file_id, block_id, blocks, status
2 from dba_undo_extents
3 where segment_name ='_SYSSMU8$';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS STATUS
---------- ---------- ---------- ---------- ---------------
0 2 121 8 UNEXPIRED
1 2 385 8 UNEXPIRED
2 2 393 128 UNEXPIRED
-- 컷밋 전
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1306119643
Extent Number:1 Commit Time: 1305869453
Extent Number:2 Commit Time: 1306119643
-- 컷밋 후
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1306123682
Extent Number:1 Commit Time: 1306123682
Extent Number:2 Commit Time: 1306123682
커밋 수행 후에는 언두 리텐션 테이블의 Commit Time 이 커밋 시점으로 변경되고,
DBA_UNDO_EXTENTS 뷰의 STATUS 칼럼 값은 UNEXPIRED 로 변경된 것을 확인 할 수 있다.
Case 2: 언두 세그먼트의 익스텐트 중에 UNEXPIRED 상태가 존재하는 경우
Session_A>delete from undo_layer_t3 where rownum = 1 ;
1 row deleted.
Session_A>select sid from v$mystat where rownum = 1 ;
SID
----------
148
SYS_Session>select xidusn, xidslot, xidsqn
2 from v$transaction
3 where addr=(select taddr from v$session where sid = 148 ) ;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
4 27 944
SYS_Session>alter system dump undo header '_SYSSMU4$';
System altered.
SYS_Session>select extent_id, file_id, block_id, blocks, status
2 from dba_undo_extents
3 where segment_name ='_SYSSMU4$';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS STATUS
---------- ---------- ---------- ---------- ---------------
0 2 57 8 EXPIRED
1 2 81 8 EXPIRED
2 2 1929 128 ACTIVE
3 2 2185 128 UNEXPIRED ( 책내용 )
Session_A>commit ;
Commit complete.
SYS_Session>alter system dump undo header '_SYSSMU4$';
System altered.
SYS_Session>select extent_id, file_id, block_id, blocks, status
2 from dba_undo_extents
3 where segment_name ='_SYSSMU4$';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS STATUS
---------- ---------- ---------- ---------- ---------------
0 2 57 8 EXPIRED
1 2 81 8 EXPIRED
2 2 1929 128 UNEXPIRED
3 2 2185 128 UNEXPIRED ( 책내용 )
-- 컷밋 전
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1305880256
Extent Number:1 Commit Time: 1305885617
Extent Number:2 Commit Time: 1305885617
Extent Number:3 Commit Time: 1305885617 ( 책내용 )
-- 컷밋 후
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1305880256
Extent Number:1 Commit Time: 1305885617
Extent Number:2 Commit Time: 1305885617
Extent Number:3 Commit Time: 1305885617 ( 책내용 )
커밋 수행 후에 DBA_UNDO_EXTENTS 뷰의 STATUS 칼럼 값은 UNEXPIRED 로 변경되었으나,
레텐션 테이블의 Commit Time 은 변경되지 않는다.
언두 세그먼트 익스텐트의 상태를 관리하는 원칙
● 일정 크기 이상의 언두 레코드를 발생시키는 트랜잭션의 경우에만
DBA_UNDO_EXTENTS 뷰의 STATUS 칼럼과 리텐션 테이블의 내용이 변경된다.
● 일정 크기 이하의 언두 레코드를 발생시키는 트랜잭션은
DBA_UNDO_EXTENTS 뷰의 STATUS 칼럼과 테이블의 내용을 변경시키지 않는다.
● 일정 크기 이하의 언두 레코드를 발생시키는 트랜잭션이 UNEXPIRED 상태의 익스텐트를
소유한 언두 세그먼트를 사용할 경우에는,
DBA_UNDO_EXTENTS 뷰의 STATUS 컬럼만 변경하고, 리텐션 테이블의 내용은 변경하지 않는다.
일정 크기 이하의 언두 레코드를 발생시키는 트랜잭션의 동작방식은 언두 세그먼트의 공간을 효율적으로 관리하고자
하는 목적에는 부합, 하지만, 커밋 수행 후에 언두 세그먼트의 인스텐트를 UNEXPIRED 로 변경하지 않고 EXPIRED로
변경함에 따라 ORA-0155의 발생가능성이 있는 것은 아닐까 ?
아마도, 오라클에서는 이러한 경우에도, 몇 가지 기법을 더 적용하여 효율적으로 ORA-1555의 발생을 최적화시킬 것으로
생각되지만, 테스트를 통해 이 부분을 파악하는 것은 무리이므로.. 여기까지만..
Automatic Undo Retention 개요
자동 언두 리텐션(automatic undo retention) 기능은 오라클 10g부터 제공하는 기능으로써,
다이나믹하게 언두 리텐션의 수치를 조절한다.
자동 언두 리텐션 기능을 제공함으로써 부적절하게 설정된 UNDO_RETENTION 파라미터로 인한 ORA-01555
발생을 최소화할 수 있다. 자동 언두 리텐션 기능의 동작 방식은 오라클 10gR1 과 오라클 10gR2간에도 차이가 나며,
undo guarantee 사용 여부와 언두 데이터 파일의 autoextend 사용 여부에 따라 다르게 동작한다.
언두 테이블스페이스를 undo guarantee 모드로 사용할 경우, 언두 리텐션을 보장한다.
즉, 언두 테이블스페이스의 공간 부족 현상이 발생할 경우에도, UNEXPIRED 된 언두 익스텐트를 재사용하는 것을
방지함으로써, 언두 리텐션을 보장하는 것이다.
오라클 9i까지는 언두 테이블스페이스가 부족한 경우, UNEXPIRED 된 언두 익스텐스를 재사용함에 따라 언두 리텐션을
보장하지 못하였다. 언두 테이블스페이스의 undo guarantee 사용 여부는 언두 테이블스페이스 생성 시점 또는 생성
후에 변경이 가능하다.
SYS_Session>select tablespace_name, retention
2 from dba_tablespaces
3 where tablespace_name ='UNDOTBS1';
TABLESPACE_NAME RETENTION
------------------------------ ---------------------------------
UNDOTBS1 NOGUARANTEE
SYS_Session>alter tablespace undotbs1 retention guarantee ;
Tablespace altered.
SYS_Session>select tablespace_name, retention
2 from dba_tablespaces
3 where tablespace_name ='UNDOTBS1';
TABLESPACE_NAME RETENTION
------------------------------ ---------------------------------
UNDOTBS1 GUARANTEE
SYS_Session>alter tablespace undotbs1 retention noguarantee ;
Tablespace altered.
SYS_Session>select tablespace_name, retention
2 from dba_tablespaces
3 where tablespace_name ='UNDOTBS1';
TABLESPACE_NAME RETENTION
------------------------------ ---------------------------------
UNDOTBS1 NOGUARANTEE
자동 언두 리텐션 기능으로 인해 재설정된 언두 리텐션 수치는 V$UNDOSTAT 뷰의 TUNED_UNDORETENTION 칼럼을 통해 확인할 수 있다.
V$UNDOSTAT 뷰는 오라클 9iR2부터 제공되며 언두 사용 통계정보를 10분 단위로 샘플링하며, 언두 사용에 대한 유용한 통계정보를 제공
Automatic Undo Retention 동작 방식 테스트
h3. 테스트를 위한 파라미터 기본 설정
- _UNDO_AUTOTUNE = TURE ( 기본설정 값 )
- UNDO_RETENTION = 0
h3. 테스트 초기화
Session_A>create table undo_layer_t4 as select * from dba_objects nologging ;
Table created.
테스트1 - Retention Guarantee = No Autoextend = No
Session_A>alter tablespace undotbs1 retention noguarantee ;
Tablespace altered.
Session_A>alter database datafile '/u01/app/oracle/db/TLO/undotbs01.dbf' autoextend off ;
Database altered.
SYS_Session>startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1223540 bytes
Variable Size 880805004 bytes
Database Buffers 188743680 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SYS_Session>@chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
14:27:34 14:28:28 0 600
SYS_Session>@chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
14:27:34 14:28:49 0 345600
SQL> !more t111.sql
set linesize 140
col sql_text for a15
select count(*) from undo_layer_t444, undo_layer_t444, undo_layer_t444 ;
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
;
SQL> @t111.sql
COUNT(*)
----------
1.5161E+10
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ------------------------------
17:07:34 17:15:55 846 cw0wvgj78aq76 345600 select count(*) from undo_laye
16:57:34 17:07:34 246 cw0wvgj78aq76 345600 select count(*) from undo_laye
16:47:34 16:57:34 1327 cw0wvgj78aq76 345600 select count(*) from undo_laye
16:37:34 16:47:34 727 cw0wvgj78aq76 345600 select count(*) from undo_laye
테스트 1 - 결과 분석
오라클이 기동된 직후에 TUNED_UNDORETENTION 은 600초로 설정되며,
몇 분 후 345,600(96H) 초로 자동 설정된다. 345,600초(는 언두 테이블스페이스의 크기와 관련이 있는 것으로 보인다.
즉, 언두 테이블스페이스는 확장이 불가능하므로, 현재 크기에 해당되는 최대값으로 설정하는 것이며,
쿼리 수행 시에 MAXQUERYLEN, MAXQUERYID 컬럼에 값을 등록 한다.
테스트 2 - Retention Guarntee = No Autoextend = YES
SQL> alter tablespace undotbs1 retention noguarantee ;
Tablespace altered.
SQL> alter database datafile '/u01/app/oracle/db/TLO/undotbs01.dbf' autoextend on next 10m ;
Database altered.
SYS_Session>shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS_Session>startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1223540 bytes
Variable Size 880805004 bytes
Database Buffers 188743680 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
17:42:21 17:43:22 0 600
....
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
17:42:21 17:43:25 0 600
SQL> @t111.sql
COUNT(*)
----------
1.5161E+10
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ------------------------------
17:52:21 18:01:28 948 0t9kys4buvt4y 1728 select ENQUEUE_ENABLED, DEQUEU
17:42:21 17:52:21 348 0t9kys4buvt4y 1188 select ENQUEUE_ENABLED, DEQUEU
테스트 2 - 결과 분석
오라클이 가동된 직후에 TUNED_UNDORETENTION 은 600초로 설정되며,
쿼리가 수행되지 않을 경우 TUNED_UNDORETNTION 은 600초로 유지한다.
장시간 쿼리가 수행될 경우에, MAXQUERYLEN 컬럼의 값은 300 초 단위로 갱신된다.
TUNED_UNDORETENTION 값은 장시간의 쿼리를 감지한 직후에는 대략 MAXQUERYLEN + UNDORETENTION + 60 초로 증가하며,
그 이후로는 매 60초 단위로 증가하게 된다.
즉, 언두 테이블스페이스가 확장 가능하므로, 쿼리의 수행시간에 근거하여 언두 리텐션을 튜닝한다.
테스트 3 - Retention Gurantee = YES Autoextentd = No
SQL> alter tablespace undotbs1 retention guarantee ;
Tablespace altered.
SQL> alter database datafile '/u01/app/oracle/db/TLO/undotbs01.dbf' autoextend off ;
Database altered.
SYS_Session>shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS_Session>startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1223540 bytes
Variable Size 880805004 bytes
Database Buffers 188743680 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
18:25:06 18:25:58 0 600
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
18:25:06 18:26:16 0 345600
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
18:25:06 18:25:58 0 600
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
18:25:06 18:26:16 0 345600
테스트 결과3 의 내용은 테스트 1의 결과와 동일하다.
다만, undo guarantee 모드를 사용함에 따라, 언두 테이블스페이스의 공간 부족이 발생할 시점의 동작 방식이 차이가 있다.
undo guarantee 모드는 언두 리텐션을 보장하는 것에 우선순위가 있으므로, 언두 세그먼트를 확장하려고 시도하는
트랜잭션은 ORA-30036 이 발생한다.
undo gurantee 모드를 사용하지 않는다면, 이러한 경우, 다른 언두 세그먼트의 익스텐트를 스틸링(stealing)한다.
테스트 4 - Retention Gurantee = YES Autoextend = YES
SQL> alter tablespace undotbs1 retention guarantee ;
Tablespace altered.
SQL> alter database datafile '/u01/app/oracle/db/TLO/undotbs01.dbf' autoextend on next 10m ;
Database altered.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1223540 bytes
Variable Size 880805004 bytes
Database Buffers 188743680 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SQL>
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
08:40:40 08:41:51 0 600
SQL> @chk_undostat.sql
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ---------------
08:40:40 08:43:03 0 600
SQL> @t111.sql
COUNT(*)
----------
1.5161E+10
BEGIN END MAX_Q_LEN MAX_Q_ID TUNED_UR SQL_TEXT
------------------------ ------------------------ ---------- --------------------------------------- ---------- ------------------------------
08:50:40 08:59:36 795 cw0wvgj78aq76 1518 select count(*) from undo_laye
08:40:40 08:50:40 195 cw0wvgj78aq76 1035 select count(*) from undo_laye
테스트 4 - 결과분석
테스트 결과의 내용은 테스트 2의 결과와 동일하다.
다만, undo guarantee 모드를 사용함에 따라, 언두 테이블스페이스의 공간 부족이 발생할 시점의
동작 방식이 차이가 있다는 것이다.