[Oracle] Undo Extent의 동작 원리와 튜닝 요소 식별, 튜닝 방법

* 사전 지식 : Undo Extent 상태(Status)별 이해

튜닝 요소

ORA-30036 unable to extend segment in Undo tablespace 에러

해당 에러는 Undo Tablespace의 용량이 부족할 때 발생합니다.

 

만약 Undo Extent 중 Active 만 존재하여

더이상 해당 트랜잭션에 대한 undo 공간을 확보할 수 없을 경우 

ORA-30036 unable to extend segment in Undo tablespace 에러를 뱉고 자동으로 트랜잭션은 rollback 됩니다.

 

이러한 요청이 얼마나 일어났는지 확인은 아래 sql을 통해 가능합니다.

SQL> select sum(NOSPACEERRCNT) from v$undostat;

Column	Data Type	Description
NOSPACEERRCNT	NUMBER	Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.

---

ORA-01555 snapshot too old 에러

해당 에러가 발생하는 경우는 크게 두 가지입니다.

 

1) Unexpired Extent -> Active Extent로 Steal 되는 경우

2) Unexpired Extent -> Expired Extent로 만료되는 경우

 

1) Unexpired Extent -> Active Extent로 Steal 되는 경우

 

만약 Active 상태의 Extent이 더 필요한데 Expired Extent가 부족하다면

Unexpired(undo_retention 기간이 아직 지나지 않은 상태) Extent를 빼앗게(Steal) 되는데,

이렇게 Steal된 Unexpired Extents에 대해 읽기 일관성(Read Consisteny)을 수행(Rollback 등)하려 할 때,

ORA-01555 snapshot too old 에러가 발생합니다.

참고!
Unexpired Extent가 Steal되는 경우는 Undo 테이블스페이스가 No guarantee 모드일 때 발생하며,
Steal을 못하게 하려면 Undo 테이블스페이스에 대해 retention guarantee 옵션을 주면 됩니다.

SQL> alter tablespace undotbs1 retention guarantee;

 

* Unexpired Extent가 Steal된 횟수 조회

SQL> select sum(unxpstealcnt) from v$undostat;

Column	Data Type	Description
UNXPSTEALCNT	NUMBER	Number of attempts to obtain undo space by stealing unexpired extents from other transactions

 

2) Unexpired Extent -> Expired Extent로 만료되는 경우

 

반면, undo_retention 설정값을 초과하여

Unexpired Extent가 Expired Extent로 만료될 경우

Unexpired Extents에 대해 읽기 일관성을 수행하려 할 때,

ORA-01555 snapshot too old 에러가 발생합니다.

 

* 총 ORA-01555 에러 발생 횟수 조회

SQL> select sum(ssolderrcnt) from v$undostat;

 

Column	Data Type	Description
SSOLDERRCNT	NUMBER	Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.

 

ORA-01555 에러 발생 횟수에서 Steal이 차지하는 비중이 크다면,

undo_retention 설정값 보단 용량을 확장하는 것이 도움이 될 수 있습니다.

 

※ Undo Extent 크기와 퍼센트를 확인하는 쿼리. 단 free는 포함되지 않음.

select status,
  round(sum_bytes / (1024*1024), 0) as MB,
  round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
  select status, sum(bytes) sum_bytes
  from dba_undo_extents
  group by status
),
(
  select sum(a.bytes) undo_size
  from dba_tablespaces c
    join v$tablespace b on b.name = c.tablespace_name
    join v$datafile a on a.ts# = b.ts#
  where c.contents = 'UNDO'
    and c.status = 'ONLINE'
);

 

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

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
);

---

undo size tuning

 

tuned_undoretention 값은 최근 작업 부하와 undo 공간 사용 패턴에 따라

Oracle이 산출한 실제 undo retention 기간(초 단위)을 보여줍니다.

 

* 최근 tuned_undoretention 값 조회

SQL> select BEGIN_TIME, END_TIME, tuned_undoretention from v$undostat order by 1;

Column	Data Type	Description
TUNED_UNDORETENTION	NUMBER	Amount of time (in seconds) for which undo will not be recycled from the time it was committed. At any point in time, the latest value of TUNED_UNDORETENTION is used to determine whether data committed at a particular time in the past can be recycled. The value of this column is not meaningful on an Oracle Active Data Guard standby database instance, because the system does not tune this value on such instances.

 

 

tuned_undoretention < UNDO_RETENTION 경우

이는 시스템이 요구하는 undo 보관 기간보다 undo 세그먼트가 더 빨리 재사용되고 있다는 의미입니다.

• 이 경우, 특히 flashback 쿼리나 장기 트랜잭션 처리를 위해 더 긴 undo 보관 기간이 필요한 상황이라면,
  undo 테이블스페이스의 크기를 늘려 undo 공간이 충분하도록 해야 합니다.

반대로 tuned_undoretention > UNDO_RETENTION 경우

현재 시스템은 추가적인 undo 보관 기간을 제공할 수 있는 여력이 있다는 뜻입니다.

• 필요에 따라 UNDO_RETENTION 설정값을 높여 더 긴 기간 동안 이전 데이터 버전 접근을 지원할 수 있습니다.

참고!
undo retention autotune은 '_undo_autotune' hidden parameter를 설정해 비활성화할 수도 있습니다.

 

undo_retention 값을 가장 긴 쿼리에 맞춰 설정하기

 

ORA-01555 에러를 피하기 위해 가장 긴 쿼리에 맞춰 undo retention을 설정하는 방법을 생각할 수 있습니다.

 

* 최근 7일간 가장 긴 쿼리의 길이(초)를 확인하는 방법

SQL> select max(maxquerylen) from v$undostat;

 

---

참고사이트

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

undo 관련 좋은 링크