오라클 19c undo 사용률 관련 expired, unexpired 익스텐트 조회 방법

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c undo expired, unexpired 사용 익스텐트 조회 방법

언두 사용 현황 조회

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
------------------------------ --------- ---------- ----------
UNDOTBS2                       EXPIRED         1.49        717
UNDOTBS2                       UNEXPIRED        .97        311

status 설명

1. active :
트랜잭션이 현재 진행 중이며, 해당 트랜잭션이 변경한 데이터를 롤백할 수 있도록 유지되고 있는 익스텐트
이 상태의 익스텐트는 다른 트랜잭션이 사용하지 못함(아직 사용 중인 상태의 언두 익스텐트)

2. expired :
이 익스텐트는 더 이상 사용되지 않으며, 이전 트랜잭션에서 사용되었으나 트랜잭션이 완료된 후 해당 익스텐트에 있는 언두 데이터는 더 이상 롤백이 필요하지 않은 상태
다른 트랜잭션이 이 익스텐트를 재사용할 수 있는 상태

3. unexpired :
완료된 트랜잭션의 익스텐트지만, 아직 언두 보존 기간 동안 롤백이 필요할 수 있어 다른 트랜잭션에서 재사용할 수 없는 상태
undo_retention 파라미터에 의해 정의된 기간 동안 데이터 일관성을 유지하기 위해 보존되는 언두 익스텐트임

 

 

undo autotune 관련

10g 이상부터 사용가능한 언두 자동 튜닝 기능임

만약 undo 사용률이 계속 올라가고 dba_undo_extents 확인시 unexpired 가 지속적으로 많은 경우 (expried 로 변경되지 않는 문제 발생시) _highthreshold_undoretention 파라미터 설정 가능(autotune 으로 설정하는 max retention 값을 설정하는 파라미터)

권장 값 : _highthreshold_undoretention = 86400 [10800)
권장 값 변경후에도 이슈가 발생시 _undo_autotune 을 false 로 변경 후 undo_retention 을 10800 로 설정(또는 v$undo_stat 의 tuned_undoretention 값을 보고 적절히 설정)

https://blog.naver.com/blacksmail/150164286222

 

 

undo 모니터링 관련

dba_undo_extents 의 각 status 별로 백분률을 구해서 active 만 특정 퍼센트 이상 올라갈때 모니터링 할수도 있음

아래 쿼리를 수정하여 사용가능

SQL>
SELECT 
    ROUND((active_extents / total_extents) * 100, 2) AS active_percentage,
    ROUND((expired_extents / total_extents) * 100, 2) AS expired_percentage,
    ROUND((unexpired_extents / total_extents) * 100, 2) AS unexpired_percentage
FROM (
    SELECT 
        COUNT(*) AS total_extents,
        SUM(CASE WHEN STATUS = 'ACTIVE' THEN 1 ELSE 0 END) AS active_extents,
        SUM(CASE WHEN STATUS = 'EXPIRED' THEN 1 ELSE 0 END) AS expired_extents,
        SUM(CASE WHEN STATUS = 'UNEXPIRED' THEN 1 ELSE 0 END) AS unexpired_extents
    FROM DBA_UNDO_EXTENTS
);
 
ACTIVE_PERCENTAGE EXPIRED_PERCENTAGE UNEXPIRED_PERCENTAGE
----------------- ------------------ --------------------
                0              58.71                41.29

 

 

참조 : 

1578639.1, 2582183.1, 1579081.1, 1575667.2, 1112431.1

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_UNDO_EXTENTS.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-UNDOSTAT.html

https://positivemh.tistory.com/399

https://neo-orcl.tistory.com/93

https://kwomy.tistory.com/129

http://www.koreaoug.org/tuning/7808

https://blog.naver.com/kimsy1800/220563074258

https://blog.naver.com/blacksmail/150164286222