[Oracle] Transaction Rollback 상태 확인

1. 정상(DML) 비정상(KILL) 및 Crash Recovery 상황 시 Rollback 모니터링 방법

 

-- #######################################################
-- Rollback & Crash Recovery
--
-- 1. DML(INSERT,UDATE,DELETE)에 의한 TX Rollback 모니터링
-- 2. Kill Session에 의한 TX Rollback 모니터링
-- 3. Rollback 중인 Session Kill 한다면?
-- 4. Crash Recovery (SMON)
-- #######################################################
 
-- 1. DML(INSERT,UDATE,DELETE)에 의한 Rollback 모니터링
SELECT SID
     , SQL_ID
     , SERIAL#
     , TARGET
     , OPNAME
     , TARGET
     , SOFAR
     , TOTALWORK
     , UNITS
     , START_TIME
     , TIME_REMAINING
     , ELAPSED_SECONDS
     , LAST_UPDATE_TIME 
  FROM V$SESSION_LONGOPS
 WHERE 1=1
--   AND USERNAME='ORAKIM'
--   AND OPNAME='TRANSACTION ROLLBACK'
--   AND SID=1060
   AND TOTALWORK>SOFAR
;
 
 
-- 2. Kill Session(비정상 종료)에 의한 TX Rollback 모니터링
SELECT SYSDATE
     , S.SID
     , S.SERIAL#
     , S.USERNAME
     , SUBSTR(S.PROGRAM, 1, 30) AS COMMAND
     , T.USED_UBLK
     , R.NAME AS "ROLLBACK SEG"
  FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
 WHERE S.TADDR=T.ADDR
   AND T.XIDUSN = R.USN
   AND S.SID=9291
;
-- [완료시간 예측]
-- 2022/11/17 09:20:00 -> 200000
-- 2022/11/17 09:30:00 -> 100000
-- 10분에 100000 Block 처리 -> 10분 후 종료 예측
 
 
-- 3. Rollback 중인 Session Kill 한다면?
-- fast_start_parallel_rollback 파라미터에 의해 병렬 처리 가능 (CPU 자원에 따라 유동적)
SELECT * FROM V$FAST_START_SERVERS;
SELECT * FROM V$FAST_START_TRANSACTIONS;
SELECT * FROM V$PARAMETER WHERE NAME = 'fast_start_parallel_rollback';
 
-- 수치를 조정해 가면서 아래 예상시간 조정
ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH;
 
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
 
SELECT USN
     , STATE
     , UNDOBLOCKSTOTAL "TOTAL"
     , UNDOBLOCKSDONE "DONE"
     , UNDOBLOCKSTOTAL-UNDOBLOCKSDONE "TODO"
     , DECODE(CPUTIME,0,'UNKNOWN',SYSDATE+(((UNDOBLOCKSTOTAL-UNDOBLOCKSDONE) / (UNDOBLOCKSDONE / CPUTIME)) / 86400)) "FINISH AT"  
FROM V$FAST_START_TRANSACTIONS
; 
 
-- [완료시간 예측]
SET SERVEROUTPUT ON;
DECLARE
    FLAG NUMBER     := 0;
    T VARCHAR(16)   := '070001025BFEDAB8'; -- 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
            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;
/
 
 
-- 4. Crash Recovery (SMON)
/*
a. Parallel Transaction Recovery:
   ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH
   OR
   ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = LOW
 
b. If the parallel recovery is hanging, enable serial recovery:
   ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = FALSE
*/
 
SELECT USN
     , INST_ID
     , STATE
     , UNDOBLOCKSTOTAL AS "TOTAL"
     , UNDOBLOCKSDONE "DONE"
     , UNDOBLOCKSTOTAL-UNDOBLOCKSDONE "TODO"
     , DECODE(CPUTIME,0,'UNKNOWN',SYSDATE+(((UNDOBLOCKSTOTAL-UNDOBLOCKSDONE) / (UNDOBLOCKSDONE / CPUTIME)) / 86400)) "ESTIMATED TIME TO COMPLETE"
  FROM GV$FAST_START_TRANSACTIONS
;
 
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') DT
     , SUM(UNDOBLOCKSDONE) DONE
     , SUM(UNDOBLOCKSTOTAL)  TOTAL
     , SUM(UNDOBLOCKSTOTAL) - SUM(UNDOBLOCKSDONE)  AS REMAIN
  FROM V$FAST_START_TRANSACTIONS
 ORDER BY 1
;