프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

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


DB 환경 : Oracle Database 11.2.0.4


방법 : Undo Retention 동작방식 테스트 

Undo Retention 개요

언두 리텐션이란 지정된 수치(오라클 9i까지는 UNDO_RETENTION 파라미터의 설정 값) 동안은 트랜잭션이 종료된 후에라도

트랜잭션과 관련되었던 언두 세그먼트를 재사용하지 못하도록 하는 기능임


오라클 9i까지는 언두 테이블스페이스의 공간 부족 현상이 발생할 경우 지정된 수치에 도달하기 전에도 언두 세그먼트의 재사용이 가능하였음 

언두 리텐션 기능은 언두 세그먼트의 익스텐트 단위로 관리되며, 각 인스텐트 당 Commit Time 을 관리하여 언두 리텐션 기간을 계산하게 됨

언두 리텐션과 관련된 내용은 언두 세그먼트 헤더 블록의 리텐션 테이블에서 관리됨



Case 1: 언두 세그먼트의 익스텐트 중에 UNEXPIRED 상태가 존재하는 경우 

커밋 전과 커밋 후의 리텐션 테이블의 변화 사항 확인 테스트

일반 세션

테이블 생성 및 데이터 삽입

1
2
3
4
5
6
7
SQL> create table undo_layer_t2(c1 number ,c2 varchar2(10), c3 char(20));
 
Table created.
 
SQL> insert into undo_layer_t2 values (1'A''a');
 
1 row created.


일반유저 SID 확인

1
2
3
4
5
SQL> select sid from v$mystat where rownum = 1 ;
 
       SID
----------
       50


SYS 세션

SID로 트랜젝션 정보 확인

1
2
3
4
5
6
7
SQL> select xidusn, xidslot, xidsqn
from v$transaction
where addr=(select taddr from v$session where sid = 50);
 
    XIDUSN    XIDSLOT      XIDSQN
---------- ---------- --------
      13           30        1451


SID로 언두헤더 확인

1
2
3
4
5
6
7
SQL> select ubafil, ubablk, ubarec
from v$transaction
where addr = (select taddr from v$session where sid = 50);
 
    UBAFIL    UBABLK     UBAREC
---------- ---------- ----------
         4      22438          4


언두 세그먼트 조회(file_id에 UBAFIL값, 그다음에 UBABLK값 삽입)

1
2
3
4
5
6
7
8
9
SQL> select segment_name 
from dba_extents 
where file_id = 4 
and 22438 between block_id 
and block_id + blocks - 1;
 
SEGMENT_NAME
----------------------
_SYSSMU13_220842927$


언두 헤더 덤프(위 세그먼트 네임 입력)

1
2
3
SQL> alter system dump undo header '_SYSSMU13_220842927$';
 
System altered.


언두 세그먼트에서 어떤 익스텐트 사용중인지 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL> select extent_id, file_id, block_id, blocks, status 
from dba_undo_extents
where segment_name = '_SYSSMU13_220842927$';
 
 EXTENT_ID    FILE_ID    BLOCK_ID     BLOCKS   STATUS
---------- ---------- ---------- ---------- ---------
        0              4          160           8    UNEXPIRED
        1              4          168           8    UNEXPIRED
        2              4         5120         128    UNEXPIRED
        3              4         7424         128    UNEXPIRED
        4              4         8064         128    UNEXPIRED
        5              4         8448         128    UNEXPIRED
        6              4         9728         128    UNEXPIRED
        7              4        12032         128    UNEXPIRED
        8              4          336           8    UNEXPIRED
        9              4          640         128    UNEXPIRED
       10              4        14464         128    UNEXPIRED
       11              4        15488         128    UNEXPIRED
       12              4        16256         128    UNEXPIRED
       13              4        17536         128    UNEXPIRED
       14              4        19328         128    UNEXPIRED
       15              4        21248         128    UNEXPIRED
       16              4        22016         128    UNEXPIRED
       17              4        22400         128    ACTIVE
       18              4         2432         128    UNEXPIRED
 
19 rows selected.

EXTENT_ID 17번이 ACTIVE 인것을 확인


일반 세션

커밋작업

1
2
3
SQL> commit;
 
Commit complete.


SYS 세션

언두 세그먼트에서 어떤 익스텐트 사용중인지 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL> select extent_id, file_id, block_id, blocks, status 
from dba_undo_extents
where segment_name = '_SYSSMU13_220842927$';
 
 EXTENT_ID    FILE_ID    BLOCK_ID     BLOCKS    STATUS
---------- ---------- ---------- ---------- ---------
          0              4          160            8   UNEXPIRED
          1              4          168            8   UNEXPIRED
          2              4         5120          128   UNEXPIRED
          3              4         7424          128   UNEXPIRED
          4              4         8064          128   UNEXPIRED
          5              4         8448          128   UNEXPIRED
          6              4         9728          128   UNEXPIRED
          7              4        12032          128   UNEXPIRED
          8              4          336            8   UNEXPIRED
          9              4          640          128   UNEXPIRED
         10              4        14464          128   UNEXPIRED
         11              4        15488          128   UNEXPIRED
         12              4        16256          128   UNEXPIRED
         13              4        17536          128   UNEXPIRED
         14              4        19328          128   UNEXPIRED
         15              4        21248          128   UNEXPIRED
         16              4        22016          128   UNEXPIRED
         17              4        22400          128   UNEXPIRED
         18              4         2432          128   UNEXPIRED
 
19 rows selected.

ACTIVE였던 EXTENT_ID 17번이 UNEXPIRED 인것을 확인


다시 언두 헤더 덤프

1
2
3
SQL> alter system dump undo header '_SYSSMU13_220842927$';
 
System altered.


trace 파일 확인

1
2
3
4
5
SQL> show parameter user_dump_dest
 
NAME                     TYPE       VALUE
-------------- -------- -----------------------------------------------
user_dump_dest    string     /oracle/app/oracle/diag/rdbms/orcldb/ORCL/trace


해당경로로 이동해서 trace 파일 확인

1
2
3
4
5
6
7
8
$ cd /oracle/app/oracle/diag/rdbms/orcldb/ORCL/trace
$ ls -ltr 
.
.
-rw-r----- 1 oracle dba      477 Jan 25 15:24 ORCL_ora_2850.trm
-rw-r----- 1 oracle dba    37690 Jan 25 15:24 ORCL_ora_2850.trc
.
.


ORCL_ora_2850.trc 파일 확인(Extent Number:17확인)

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Commit 전 
 Retention Table
  -----------------------------------------------------------
 Extent Number:16  Commit Time: 1548370819
 Extent Number:17  Commit Time: 1548370819
 Extent Number:18  Commit Time: 1547784013
 
-- Commit 후
 Retention Table
  -----------------------------------------------------------
 Extent Number:16  Commit Time: 1548370819
 Extent Number:17  Commit Time: 1548370819
 Extent Number:18  Commit Time: 1547784013



Case 1 결과

DBA_UNDO_EXTNETS 뷰의 STATUS 칼럼 값은 EXPIRED 로 변경되고,

언두 리텐션 테이블의 Commit Time 수치에 아무런 변화가 없음

언두 세그먼트 익스텐트의 상태를 관리하는 원칙에 따라 아무런 변화가 없는 것


언두 세그먼트 익스텐트의 상태를 관리하는 원칙

- 일정 크기 이상의 언두 레코드를 발생시키는 트랜잭션의 경우에만

DBA_UNDO_EXTENTS 뷰의 STATUS 칼럼과 리텐션 테이블의 내용이 변경됨

- 일정 크기 이하의 언두 레코드를 발생시키는 트랜잭션은

DBA_UNDO_EXTENTS 뷰의 STATUS 칼럼과 테이블의 내용을 변경시키지 않음

- 일정 크기 이하의 언두 레코드를 발생시키는 트랜잭션이 UNEXPIRED 상태의 익스텐트를

소유한 언두 세그먼트를 사용할 경우에는, DBA_UNDO_EXTENTS 뷰의 STATUS 컬럼만 변경하고, 

리텐션 테이블의 내용은 변경하지 않음




Case 2: 언두 세그먼트의 익스텐트를 1개 이상 사용하는 경우

일반 세션

테이블 생성 및 데이터 삽입

1
2
3
4
5
6
7
8
9
10
11
SQL> create table undo_layer_t3(c1 number ,c2 varchar2(10), c3 char(20));
 
Table created.
 
SQL> begin
for i in 1 .. 500000 loop
insert into undo_layer_t3 values (i, 'c2','c3');
end loop;
commit;
end;
/


데이터 삭제

1
SQL> delete from undo_layer_t3 where rownum <= 25000;


일반유저 SID 확인

1
2
3
4
5
SQL> select sid from v$mystat where rownum = 1 ;
 
       SID
----------
       43


SYS 세션

SID로 트랜젝션 정보 확인

1
2
3
4
5
6
7
SQL> select xidusn, xidslot, xidsqn
from v$transaction
where addr=(select taddr from v$session where sid = 43);
 
    XIDUSN    XIDSLOT      XIDSQN
---------- ---------- --------
          16           22        1022


SID로 언두헤더 확인

1
2
3
4
5
6
7
SQL> select ubafil, ubablk, ubarec
from v$transaction
where addr = (select taddr from v$session where sid = 43);
 
    UBAFIL    UBABLK     UBAREC
---------- ---------- ----------
         4      7680         46


언두 세그먼트 조회(file_id에 UBAFIL값, 그다음에 UBABLK값 삽입)

1
2
3
4
5
6
7
8
9
SQL> select segment_name 
from dba_extents 
where file_id = 4 
and 7680 between block_id 
and block_id + blocks - 1;
 
SEGMENT_NAME
----------------------
_SYSSMU16_3713993816$


언두 헤더 덤프(위 세그먼트 네임 입력)

1
2
3
SQL> alter system dump undo header '_SYSSMU16_3713993816$';
 
System altered.


언두 세그먼트에서 어떤 익스텐트 사용중인지 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> select extent_id, file_id, block_id, blocks, status 
from dba_undo_extents
where segment_name = '_SYSSMU16_3713993816$';
 
 EXTENT_ID    FILE_ID    BLOCK_ID     BLOCKS   STATUS
---------- ---------- ---------- ---------- ---------
          0             4         208            8    ACTIVE
          1             4         320            8    ACTIVE
          2             4        4096          128    ACTIVE
          3             4        7680          128    ACTIVE
          4             4        8704          128    EXPIRED
          5             4        8960          128    EXPIRED
          6             4       10240          128    EXPIRED
          7             4       11008          128    EXPIRED
          8             4       12544          128    EXPIRED
          9             4       13696          128    EXPIRED
         10             4       14976          128    EXPIRED
         11             4       16512          128    EXPIRED
         12             4       17920          128    EXPIRED
         13             4       18816          128    EXPIRED
         14             4       19968          128    EXPIRED
         15             4       21120          128    ACTIVE
         16             4        1792          128    ACTIVE
         17             4         768          128    ACTIVE
 
18 rows selected.

EXTENT_ID 0~3번,15~17번이 ACTIVE 인것을 확인


일반 세션

커밋작업

1
2
3
SQL> commit;
 
Commit complete.


SYS 세션

언두 세그먼트에서 어떤 익스텐트 사용중인지 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> select extent_id, file_id, block_id, blocks, status 
from dba_undo_extents
where segment_name = '_SYSSMU16_3713993816$';
 
 EXTENT_ID    FILE_ID    BLOCK_ID     BLOCKS   STATUS
---------- ---------- ---------- ---------- ---------
          0             4         208           8    UNEXPIRED
          1             4         320           8    UNEXPIRED
          2             4        4096         128    UNEXPIRED
          3             4        7680         128    UNEXPIRED
          4             4        8704         128    EXPIRED
          5             4        8960         128    EXPIRED
          6             4       10240         128    EXPIRED
          7             4       11008         128    EXPIRED
          8             4       12544         128    EXPIRED
          9             4       13696         128    EXPIRED
         10             4       14976         128    EXPIRED
         11             4       16512         128    EXPIRED
         12             4       17920         128    EXPIRED
         13             4       18816         128    EXPIRED
         14             4       19968         128    EXPIRED
         15             4       21120         128    UNEXPIRED
         16             4        1792         128    UNEXPIRED
         17             4         768         128    UNEXPIRED
 
18 rows selected.

ACTIVE였던 EXTENT_ID 0~3번,15~17번이 UNEXPIRED 인것을 확인


다시 언두 헤더 덤프

1
2
3
SQL> alter system dump undo header '_SYSSMU16_3713993816$';
 
System altered.


trace 파일 확인

1
2
3
4
5
SQL> show parameter user_dump_dest
 
NAME                     TYPE       VALUE
-------------- -------- -----------------------------------------------
user_dump_dest    string     /oracle/app/oracle/diag/rdbms/orcldb/ORCL/trace


해당경로로 이동해서 trace 파일 확인

1
2
3
4
5
6
7
8
$ cd /oracle/app/oracle/diag/rdbms/orcldb/ORCL/trace
$ ls -ltr 
.
.
-rw-r----- 1 oracle dba      477 Jan 25 15:24 ORCL_ora_10461.trm
-rw-r----- 1 oracle dba    37690 Jan 25 15:24 ORCL_ora_10461.trc
.
.


ORCL_ora_10461.trc 파일 확인(Extent Number:0~3번,15~17확인)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Commit 전 
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1547780427
 Extent Number:1  Commit Time: 1547780427
 Extent Number:2  Commit Time: 1547827212
 Extent Number:3  Commit Time: 1547866824
 Extent Number:15  Commit Time: 1548331214
 Extent Number:16  Commit Time: 1547730045
 Extent Number:17  Commit Time: 1547776803
 
-- Commit 후
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1548399823
 Extent Number:1  Commit Time: 1548399823
 Extent Number:2  Commit Time: 1548399823
 Extent Number:3  Commit Time: 1548399823
 Extent Number:15  Commit Time: 1548399823
 Extent Number:16  Commit Time: 1548399823
 Extent Number:17  Commit Time: 1548399823


Case 2 결과

커밋 수행 후에는 언두 리텐션 테이블의 Commit Time 이 커밋 시점으로 변경되고,

DBA_UNDO_EXTENTS 뷰의 STATUS 칼럼 값은 UNEXPIRED 로 변경됨

(EXPIRED로 표시된 EXTENT가 있는데 재기동을 하고 아무 작업을 안한 상태에서는 EXPIRED로 표시되고 

한번 사용하고 난뒤에는 계속 UNEXPIRED 상태인 것으로 보임)





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