OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c rac 환경에서 undo 사용률 확인시 주의사항
오라클에서 언두 사용량을 조회할때 싱글 db의 경우
아래 쿼리로 active, expired, unexpired가 각각 얼마나 존재하는지 확인해보고 언두 실시간 사용량을 확인해볼 수 있음
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
select tablespace_name tablespace, status, round(sum(bytes)/1024/1024/1024,2) sum_gb, count(*) counts
from dba_undo_extents
group by tablespace_name, status
order by 1,2;
TABLESPACE STATUS SUM_GB COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1 EXPIRED 1.49 717
UNDOTBS1 UNEXPIRED .97 311
|
참고 : 오라클 19c undo 사용률 관련 expired, unexpired 익스텐트 조회 방법 ( https://positivemh.tistory.com/1182 )
하지만 rac의 경우 노드별로 언두 테이블스페이스가 존재하기 때문에 dba_undo_extents를 확인해도 언두 테이블스페이스가 모두 출력됨
특이한점은 rac에서 이 뷰를 조회했을때 양쪽노드의 값이 다르게 나올수도 있다는점임
오라클 공식문서에서도 이렇게 적혀있음
원문
DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database.
Note : The status of the undo space distribution reported by DBA_UNDO_EXTENTS is correct for the undo tablespace that is active on the instance on which DBA_UNDO_EXTENTS is queried.
However, due to the use of in-memory information that is different on each instance,
there can be a discrepancy in the status of the undo space distribution of undo tablespaces active on other instances when queried from one instance.
This does not affect undo functionality and is only a reporting discrepancy for other instances' undo tablespace space distribution status.
As a best practice, query the space distribution for an undo tablespace from the instance on which it is active.
번역
DBA_UNDO_EXTENTS는 데이터베이스의 모든 언두 테이블스페이스에서 세그먼트를 구성하는 익스텐트(extent)를 설명하는 뷰입니다.
참고 : DBA_UNDO_EXTENTS를 조회했을때 보이는 언두 공간 분배 상태는 쿼리가 실행된 인스턴스에서 활성화된 언두 테이블스페이스에 대해서는 정확합니다.
그러나 인스턴스마다 서로 다른 메모리 내 정보를 사용하기 때문에,
하나의 인스턴스에서 다른 인스턴스에서 활성화된 언두 테이블스페이스의 공간 분배 상태를 쿼리할 경우 상태에 불일치가 발생할 수 있습니다.
이러한 불일치는 보고상의 문제일 뿐 언두 기능에는 영향을 미치지 않습니다.
최선의 방법으로, 특정 언두 테이블스페이스의 공간 분배 상태를 확인하려면 해당 테이블스페이스가 활성화된 인스턴스에서 쿼리하는 것이 좋습니다.
참고 : https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_UNDO_EXTENTS.html
그렇기 때문에 rac 환경에서 dba_undo_extents를 이용해 언두 테이블스페이스의 active, expired, unexpired를 확인하고 싶은경우 해당노드의 결과만 확인하는것이 가장 정확함
본문에서는 2노드 rac 환경에서 1번노드에만 세션을 여러개 열어 dml를 수행해 undo를 사용하고 각각의 노드에서 언두 사용률을 확인해봄
테스트
1번 노드 1번 세션에서 실행
|
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
28
29
30
31
32
33
34
35
36
37
38
|
SQL>
drop table sample_t purge;
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
);
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END;
/
update sample_t set id1 = 213912398;
update sample_t set id1 = 6543534;
update sample_t set id1 = 534123;
update sample_t set id1 = 12354;
update sample_t set id1 = 76546785647;
rollback;
|
동시에 1번 노드 2번 세션에서 실행
|
1
2
3
4
5
6
7
8
9
|
SQL>
drop table sample_t2 purge;
create table sample_t2 as select * from sample_t;
update sample_t set id1 = 213912398;
update sample_t set id1 = 6543534;
update sample_t set id1 = 534123;
update sample_t set id1 = 12354;
update sample_t set id1 = 76546785647;
commit;
|
동시에 1번 노드 3번 세션에서 실행
|
1
2
3
4
5
6
7
8
9
|
SQL>
drop table sample_t3 purge;
create table sample_t3 as select * from sample_t;
update sample_t set id1 = 213912398;
update sample_t set id1 = 6543534;
update sample_t set id1 = 534123;
update sample_t set id1 = 12354;
update sample_t set id1 = 76546785647;
commit;
|
쿼리가 수행중일때 동시에 각각의 노드에서 dba_undo_extents 조회
|
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
28
29
30
31
32
33
34
35
36
37
|
#1번 노드
SQL>
set lines 200 pages 1000
select tablespace_name tablespace, status, round(sum(bytes)/1024/1024,2) sum_mb, count(*) counts
from dba_undo_extents
group by tablespace_name, status
order by 1, 2;
TABLESPACE STATUS SUM_MB COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1 ACTIVE 52.13 54
UNDOTBS1 EXPIRED 7.69 18
UNDOTBS1 UNEXPIRED 150.56 159
UNDOTBS2 ACTIVE 44.13 46
UNDOTBS2 EXPIRED 8.06 39
UNDOTBS2 UNEXPIRED 19 12
6 rows selected.
#2번 노드
SQL>
set lines 200 pages 1000
select tablespace_name tablespace, status, round(sum(bytes)/1024/1024,2) sum_mb, count(*) counts
from dba_undo_extents
group by tablespace_name, status
order by 1, 2;
TABLESPACE STATUS SUM_MB COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1 ACTIVE 5.13 7
UNDOTBS1 EXPIRED 7.69 18
UNDOTBS1 UNEXPIRED 150.56 159
UNDOTBS2 ACTIVE 44.13 46
UNDOTBS2 EXPIRED 8.06 39
UNDOTBS2 UNEXPIRED 19 12
6 rows selected.
|
1번 노드에서 조회할때는 1번 노드에서만 dml이 이루어지고 UNDOTBS1를 사용하기 때문에 UNDOTBS1 언두의 ACTIVE SUM_MB가 52.13MB으로 나오는 반면
2번 노드에서 조회할때는 UNDOTBS1 언두의 ACTIVE SUM_MB가 5.13MB으로 표시됨
결론 :
1번 노드와 2번 노드에서 UNDOTBS1 사용량이 다르게 표시됨
참고로 나의 테스트 환경이 19c 환경이고 dml을 조금만 수행했기때문에 몇초뒤에는 2번 노드도 값이 동일하게 표시되었음
하지만 실제 업무환경에서는 dml들이 양쪽 노드에서 계속 수행되고 있기때문에 본인 노드가 아닌 반대편 노드의 언두 사용량을 확인하면 부정확하게 측정할 수 있음
그렇기 때문에 꼭 조회하는 노드의 언두만 확인해야함
참조 :
https://positivemh.tistory.com/1182
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_UNDO_EXTENTS.html
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 xplan의 SQL Analysis Report (0) | 2025.02.03 |
|---|---|
| 오라클 23ai 신기능 xplan의 Hint Report 개선 (0) | 2025.01.27 |
| 오라클 AHF Fleet Insights 구성 가이드 (0) | 2025.01.12 |
| Oracle 23ai New Features Part 2 (Oct 2024) (0) | 2025.01.01 |
| Oracle 23ai New Features Part 1 (June 2024) (0) | 2025.01.01 |