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
'ORACLE > Admin' 카테고리의 다른 글
| date 타입과 timestamp 타입 조회 (0) | 2019.01.28 |
|---|---|
| Automatic Undo Retention 자동 언두 리텐션 기능 (0) | 2019.01.25 |
| 오라클 패스워드 조건 설정, VERIFY_FUNCTION, oracle profile (2) | 2019.01.23 |
| 오라클 ATP 데이터 로드(Autonomous Transaction Processing) (0) | 2019.01.14 |
| 오라클 ATP SH 테이블 생성(Autonomous Transaction Processing) (0) | 2019.01.14 |