Kill된 세션의 Clean-Up 작업 예상 시간 추정 방법
(Estimated Clean-Up Time for a Killed Session)
==============================================

(1) 개요

사용자 에플리케이션의 문제 등으로 강제로 종료된 세션은 완전히 롤백 작업
(clean-up)이 종료되기 전에는 resource에 대한 점유가 유지된다.
(Bul:10852 -"ALTER SYSTEM KILL SESSION 에 대하여(ORA-30)" 참조)

따라서 롤백 작업 종료 시간의 예상은 개발자나 운영자에게 상당한 도움이 될 수
있으나, 적절한 도구가 없었다.

본 자료에서는 다음 이슈를 다룬다.

1. 트랜잭션 유형별 롤백 엔트리 성향 분석.
2. 롤백 엔트리 clean-up 완료 시간을 예측하는 스크립트.


(2) 트랜잭션 유형별 롤백 엔트리 성향 분석.

롤백 엔트리가 보관되는 딕셔너리는 V$TRANSACTION 중 USED_UBLK와 USED_UREC
이며, 다음과 같은 명령어로 조회가 가능하다.

SQL> select sid, serial#, username, taddr, used_ublk, used_urec
2 from v$transaction t, v$session s
3 where t.addr = s.taddr;

SID SERIAL# USERNAME TADDR USED_UBLK USED_UREC
---------- ---------- ----------------- -------- ---------- ----------
13 33 IJYEO 3129FEE8 78 1000

여기서 TADDR은 'transaction address'이며, USED_UBLK과 USED_UREC은 각각
롤백 엔트리가 저장된 블럭 및 레코드를 나타낸다.

다음은 테이블 및 트랜잭션 유형에 따른 테스트 내역이다.

ㅇ DML 유형: I(Insert)/U(Update)/D(Delete)

ㅇ 테이블 구성:
- Tab1: NUMBER 타입 1 개의 컬럼으로 구성된 테이블
- Tab2: CHAR(10) 타입 1 개의 컬럼으로 구성된 테이블
- Tab3: NUMBER 타입 5 개의 컬럼으로 구성된 테이블
- Tab4: CHAR(10) 타입 5 개의 컬럼으로 구성된 테이블

ㅇ 테스트에 사용된 테이블 레코드 수: 1,000 개

ㅇ 테스트 결과: 테스트 유형별 롤백 엔트리 량 및 clean-up 소요 시간

DML Tab1:NUMBER Tab2:CHAR(10) Tab3:NUMBER*5 Tab4:CHAR(10)*5
====== =============== =============== =============== ===============
Insert I1:38/1000/1:51 I2:37/1000/1:58 I3:38/1000/1:58 I4:37/1000/1:58
Update U1:44/1000/2:00 U2:48/1000/2:06 U3:58/1036/2:13 U4:77/1000/2:07
Delete D1:53/1006/2:13 D2:57/1009/2:10 D3:64/1013/2:06 D4:84/1034/2:13


[범례]: 테스트 코드/USED_UBLK/USED_UREC/경과 시간(분:초)

ㅇ 테스트 결과: 롤백 엔트리의 성향

1. init parameter CLEANUP_ROLLBACK_ENTRIES(=20: default) 단위로
USED_UREC이 감소한다.
2. 컬럼의 갯수나 타입은 Insert나 Update시에만 USED_UBLK 량에 영향을
준다.
3. 블럭의 크기(USED_UBLK)는 롤백 시간에 영향을 주지 않고 엔트리 갯
(USED_UREC)만 영향을 준다.
4. Insert보다는 Update가, Update보다는 Delete가 롤백 블럭을 상대적으로
많이 발생시킨다.
5. 한 테이블에 대해서 많은 건 수의 DML이 수행된 경우(동일한 DML의 반복
수행일 경우에도), USED_UBLK와 USED_UREC가 누적되어 기록된다.
6. 시스템 성능이나 Oracle 버젼에 따른 처리 시간의 차이는 크지 않으나,
Update와 Update의 경우 Oracle8이 Oracle7 보다 롤백 블럭을 약간 많이
(30% 가량) 발생한다.


(3) 롤백 엔트리 clean-up 완료 시간을 예측하는 스크립트.

다음 스크립트는 일정 시간(10초)동안 USED_UREC의 변화를 기초로 하여 전체 완료
시간을 예측하는 유틸리티이다. 수행 시간을 크게 조정하여 보다 정확한 완료 시간
을 얻을 수 있다.

[주의] 위에서 확인한 조회를 통해 V$SESSION에서 TADDR 값을 확인하여 반드시
decalre 문에 지정 하여야 함.

-------------------------- Cut Here --------------------------
set serveroutput on;
declare
flag number := 0;
t varchar(8) := '30C7B450'; -- V$SESSION의 TADDR 값
urec_init number;
urec_next number;
time_init number;
time_next number;
time_left number;
begin
select used_urec into urec_init from v$transaction where addr = t;
select hsecs into time_init from v$timer;

while (flag <= 0) loop
select used_urec into urec_next from v$transaction where addr = t;
select hsecs into time_next from v$timer;
if (time_init + 1000 < time_next) then
-- 수행 시간 설정 (단위: 1/100 초)
flag := flag + 1;
end if;
end loop;

time_left := round(urec_next/(urec_init - urec_next)*(time_next - time_init)/100);
dbms_output.put_line('Estimate Clean-Up Time: '
||to_char(trunc(time_left/3600))||' Hours '
||to_char(trunc(mod(time_left,3600)/60))||' Minutes '
||to_char(trunc(mod(mod(time_left,3600),60)))||' Seconds');

exception
when NO_DATA_FOUND then
dbms_output.put_line('Clean-Up Completed.');
when ZERO_DIVIDE then
dbms_output.put_line('Can''t Estimate Time Now. Run later.');

end;
/

Reference Documents
--------------------