개요
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
'Knowledge > Oracle' 카테고리의 다른 글
| Oracle Create Table 내부 절차 (0) | 2015.10.21 |
|---|---|
| SQL Loader Direct Load (0) | 2015.10.05 |
| null 관련 주의사항 (0) | 2015.07.10 |
| logon trail trigger on oracle (0) | 2015.01.22 |
| oracle 접속 ip 차단하기 (0) | 2015.01.22 |
