undo tablespace

undo tablespace

- 롤백 트랜잭션 - 롤백 문장이 명시되었을 경우

- 데이터 베이스 복구

- 읽기 일관성 제공 (Read Consistency)

- 오라클 Flashback Query에 사용되며 이전 시간 복구지점 분석

- 오라클 Flashback 사용시 논리적으로 복구

 

undo_retention이라는 파라미터 이며 default 값으로 900(초)로 되어 있습니다.

 

위 파라미터에 명시된 시간 만큼 undo segment에 값들이 저장되며 값이 커질수록 보관하는 기간이 늘어나며 그에따라 보관하는 데이터의 량도 증가하게 됩니다.
(해당 값이 적을 경우는 ora-01555 : snapshot too old 이 발생할 경우가 있음)

 

SELECT A.TABLESPACE_NAME, A.STATUS, SUM(A.BYTES)/1024/1024 as USED_MB, count(*)
  FROM DBA_UNDO_EXTENTS A, DBA_TABLESPACES B
 WHERE B.CONTENTS = 'UNDO'
 GROUP BY A.TABLESPACE_NAME, A.STATUS
 ORDER BY 1,2 ;

 

Active : 현재 사용중인 extented

expired extent : undo_retention 시간을 초과한 extented, 트랜잭션이 커밋된 후 언두 이미지가 undo retention 시간을 초과한 경우(언제든지 재사용가능)
unexpired extent : undo_retention 시간을 초과하지 않은 extented, 트랜잭션이 커밋된 후 언두 이미지가 undo retention 시간을 초과히지 않은 경우(경우에따라 재사용가능)

 

undo tablespace의 공간이 충분하다면 oracle이 commit된 정보에 대해서 가능하면 오랬동안 유지하는 방향으로 extent를 할당하게 되므로 이로인해 undo tablespace의 free space를 조회해보면 항상 100% 사용된 것으로 보여지는 경우가 있음

expired, unexpired extented 는 undo tabpesace 에러(ora-30036)가 나기전까지 재사용이 가능.
하지만 undo tablespace autoextend 가 off인 경우에 undo guarantee mode가 아니라면 undo_retention 파라미터는 무시되며 undo tablespace의 크기와 사용량에 의하여 undo retention값이 최대값으로 설정됩니다. (expired, unexpired extented overwrite)

 

show parameter undo;
-- undo_retention : transaction이 commit된 후 undo segment에 보관하고 있는 시간

 

select retention from dba_tablespaces where contents='UNDO';
-- guarantee      : undo_retention에 명시된 시간동안 Transaction 정보를 보관 unexpired extent에 대하여 덮어쓰기 방지
-- noguarantee   : undo_segment가 부족할 시 expired, unexpired extent 상관없이 덮어쓰기(ora-01555 발생할 수 있음)

 

undo tablespace 크기 공식
=[UR * (UPS * DBS)] + (DBS * 24)
UR  : undo_retention(초)
UPS : 초당 생성되는 dundo data block 수
DBS : db_block_size

ups 구하는 sql
select sum(undoblks) / sum((end_time - begin_time) * 86400/*3600초 * 24시간*/) from v$undostat;

 

undo tablespace 크기 공식 sql

Select (UR*UPS*DBS)+(DBS*24) AS "bytes" 
 From (select value AS UR 
         from v$parameter 
         where name='undo_retention'), 
         (select (sum(undoblks)/sum(((end_time-begin_time)*86400))) AS UPS 
          from v$undostat), 
         (select value AS DBS 
          from v$parameter 
          where name='db_block_size');
[출처] undo_retention, undotbs 적정 사이즈 계산?? (flashback) (ProDBA) |작성자 해서

-- undo tablespace 사용량 조회
select  BEGIN_TIME, MAXQUERYLEN,
        trunc(activeblks*8*1024/1024/1024+UNEXPIREDBLKS*8*1024/1024/1024) used_size,
        trunc(EXPIREDBLKS*8*1024/1024/1024) available_size  ,
        TUNED_UNDORETENTION
from v$undostat;

 

관련 view
v$undostat

4일 data를 10단위로 보관

Column	Datatype	Description
begin_time	date	the beginning of the time interval
end_time	date	the end of the time interval
undotsn	number	Undo tablespace id : v$tablespace
		RAC에서는 node 마다 다르다.
		한 instance에 undo tbs가 여러개 일때는 end_time 때 active 한 undo tbs 의 id 가 들어간다.
undoblks	number	Represents the total number of undo blocks consumed.
		사용한 undo blocks
		이 값으로 Undo tbs 사이즈를 측정할 수 있다.
		select begin_time, end_time, undoblks
		, round(undoblks*8192/1024/1024,2) mb
		, tuned_undoretention
		from v$undostat;
txncount	number	the total number of transactions executed within the period
		10분 동안 transaction 수
maxquerylen	number	10분 동안에 가장 오래 수행된 쿼리를 초로 나타낸다.
		Curosr open time 에서 마지막 fetch/execute time 측정
		10분 동안에 마지막 fetch/execute한 쿼리를 기준으로 시간을 잰다.
		이 값으로 undo_retention parameter를 setting 하는데 참조할 수 있다.
		select begin_time, end_time
		, trunc(maxquerylen/60)||'min '||mod(maxquerylen,60)||'sec' max_query_time
		, maxqueryid max_query_id
		from v$undostat;
maxqueryid	varchar2(13)	SQL id
		가장 오래 실행된 sql_text 조회
		select a.begin_time, a.end_time
		, trunc(a.maxquerylen/60)||'min '||mod(a.maxquerylen,60)||'sec' max_query_time
		, a.maxqueryid max_query_id
		, b.sql_text
		from v$undostat a, v$Sql b
		where a.maxqueryid = b.sql_id(+);
maxconcurrency	number	the highest number of transactions executed concurrently within the period.
unxpstealcnt	number	Undo space 얻기 시도한 횟수.
		Number of attempts to obtain undo space by stealing unexpired extents from other transactions
		이 숫자가 높으면 Undo space 가 부족한 것이다.
unxpblkrelcnt	number	Number of unexpired blocks removed from certain undo segments so they can be used by other transactions.
unxpblkreucnt	number	Number of unexpired undo blocks reused by transactions.
ssolderrcnt	number	Snapshot too old(ORA-01555) 발생 건수
		Undo tbs size와 undo_retention 과 관계 있다.
nospaceerrcnt	number	Undo tbs 가 부족해서 발생한 에러 수
		Undo tbs 를 늘려야 한다.
activeblks	number	Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period.
unexpiredblks	number	Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period.
expiredblks	number	Total number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period.
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.

[출처] http://kwoncharlie.blog.me/10149087815