undo 관련 좋은 링크

개요

---

http://blog.oracle48.nl/oracle-database-undo-space-explained/

 

만약 active undo extents만 존재하여 더이상 해당 트랜잭션에 대한 undo 공간을 확보할 수 없을 경우 ORA-30036 unable to extend segment in Undo tablespace 에러를 받게 되고 자동으로 트랜잭션은 rollback되게 된다.

이 현상이 얼마나 일어났는지 확인은 아래 sql을 통해 가능하다.

 

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

 

만약 active상태의 extents이 필요한데 expired extents이 없다면 unexpired(undo_retention 기간이 아직 지나지 않은 상태) extents를 빼앗게(steal) 되는데 해당 count를 확인하는 법은 아래와 같다.

 

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

 

※ steal 못하게 하려면 undo 테이블스페이스에 대해 retention guarantee 옵션을 주면 된다.

SQL> alter tablespace undotbs1 retention guarantee;

단.. 이러면 이제 unexpired extent를 뺏지 않기 때문에 조금 긴 dml의 경우 실패할 경우가 자주 생길 것이다. 이는 즉 ORA-30036과 ORA-01555 중 어느것을 선택하느냐이다.

 

만약 이렇게 steal된 unexpired extents에 대해 read consisteny read 수행하면 ORA-01555 snapshot too old 발생하고 이 역시 v$undostat에서 확인 가능하다.

 

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

 

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

 

■ undo size tune

---

undo tablespace size가 고정되어있다면 오라클이 retention time을 undo 생성량에 따라 최적값을 찾게 된다. 하지만 undo tablespace 크기가 크다면 이를 크게 잡게 될 것이다.

이는 아래 쿼리로 확인 가능하다.

 

SQL> select tuned_undoretention from v$undostat;

 

이 경우 undo_retention parameter는 "최소 undo retention"이 된다.

undo retention autotune은 '_undo_autotune' hidden parameter를 설정해 끌 수도 있다.

 

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

---

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

 

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

SQL> select max(maxquerylen) from v$undostat