토요일 오후 반품을 막던 UPDATE를 끊었는데 반품은 30분이 지나도 풀리지 않는다

모범 답안 SQL ​

-- 1. 되감기가 얼마나 남았나: USED_UREC 를 1분 간격으로 읽는다 (줄어드는 속도가 되감는 속도다)
SELECT s.sid, s.status, t.start_time, t.used_ublk, t.used_urec,
       TO_CHAR(SYSDATE, 'HH24:MI:SS') AS read_at
  FROM v$session s JOIN v$transaction t ON t.addr = s.taddr
 WHERE s.sid = 77;
--   14:10:00  667,800 → 14:11:00  642,800 : 1분에 25,000 · 642,800 ÷ 25,000 ≈ 26분 → 14:37 무렵

-- 2. 기다린다: 프로세스를 OS 에서 죽이거나 재기동하지 않는다 (남은 되감기를 SMON 이 이어받을 뿐 없어지지 않는다)
--    매장에는 "14:40 까지 반품은 영수증만 받아 두고, 풀리면 처리" 를 알린다

-- 3. (누가 이미 프로세스를 죽였다면) SMON 의 진행은 여기서 본다
SELECT usn, state, undoblocksdone, undoblockstotal
  FROM v$fast_start_transactions;

-- 4. 칸 채우기는 영업이 끝난 뒤(22:00 이후) 다시 돌린다 (41분 · 반품이 없는 시간)
UPDATE CF11
   SET SLE_AMT = SLE_QTY * SLE_UPRC;
COMMIT;

-- 5. 다음부터 큰 트랜잭션을 끊기 전에: 남은 일과 되감을 일을 함께 본다
SELECT l.sid, l.opname, l.target, l.sofar, l.totalwork, l.time_remaining, t.used_urec
  FROM v$session_longops l
  JOIN v$session s ON s.sid = l.sid AND s.serial# = l.serial#
  JOIN v$transaction t ON t.addr = s.taddr
 WHERE l.sid = 77 AND l.sofar < l.totalwork;

개선 후 실행계획 학습용 예시 · 되감기 비용은 교재 12화 §5의 첫 5분 ​

[같은 토요일 · 기다리기로 한 뒤 (학습용 예시)]
  14:11:00   USED_UREC 642,800 · 1분에 25,000 → 14:37 무렵 풀린다고 매장에 공지
  14:20:00   USED_UREC 417,800 (예상대로)
  14:36:45   되감기 끝 · 77 의 트랜잭션이 사라짐 → 기다리던 반품 12건이 1초 안에 진행
  22:10      칸 채우기를 다시 돌림 · 41분 · 기다린 세션 0
  (14:02 에 끊지 않았다면: TIME_REMAINING 544초 → 14:11 무렵 COMMIT · 반품은 26분 일찍 풀리고 칸 채우기도 그때 끝났다)

모범 답안 서술 ​

반품이 지금도 기다리는 것은 77의 트랜잭션이 아직 끝나지 않았기 때문이다. 세션을 끊어도 그 트랜잭션이 고친 85만 5천 행은 그대로 남아 있고, 오라클은 그 변경을 언두로 하나씩 되돌려야 한다. 행 락(TX)은 되감기가 끝나 트랜잭션이 사라질 때 풀린다. 그래서 V$SESSION의 반품 세션들은 KILLED 상태인 77을 여전히 BLOCKING_SESSION으로 가리키며 enq: TX - row lock contention으로 기다린다. 되감기는 고친 블록을 하나씩 다시 고치는 일이라 앞으로 고친 시간과 비슷하게 걸린다. 남은 시간은 V$TRANSACTION의 USED_UREC가 줄어드는 속도로 어림한다. USED_UREC는 이 트랜잭션이 쌓은 언두 레코드 수이고, 인덱스 없는 칸 하나를 고치는 UPDATE라 고친 행 수와 거의 같다. 14:10에 667,800, 14:11에 642,800이므로 1분에 25,000씩 준다. 남은 642,800을 25,000으로 나누면 약 26분이라 14:37 무렵 끝난다. 앞으로 고칠 때의 속도(32분 20초에 855,300행, 1분에 약 26,500행)와 거의 같다. 14:02의 판단은 손해였다. 감시 기록의 V$SESSION_LONGOPS는 CF11 8,204블록 가운데 6,400블록(78%)을 읽었고 남은 시간이 544초라고 적고 있었다. 기다렸다면 14:11 무렵 COMMIT해서 반품이 풀리고 칸 채우기도 끝났다. 끊었기 때문에 반품은 26분 늦게 풀리고, 칸 채우기는 처음부터 다시 해야 한다. 끊기 전에 남은 일(LONGOPS)과 되감을 일(USED_UREC)을 견줬어야 한다. 끝이 가까운 큰 트랜잭션은 끊지 않는 편이 빠르다. 지금은 기다린다. 운영팀의 안은 둘 다 되감을 일을 없애지 못한다. 서버 프로세스를 OS에서 죽이면 PMON이 세션을 정리하고 남은 되감기는 SMON이 이어받는다(트랜잭션 복구). SMON은 FAST_START_PARALLEL_ROLLBACK(기본 LOW) 설정에 따라 여러 프로세스로 나눠 되감을 수 있지만, 큰 트랜잭션 하나가 그만큼 빨라진다는 보장은 없고 진행도 V$TRANSACTION이 아니라 V$FAST_START_TRANSACTIONS에서 봐야 한다. 재기동은 더 나쁘다. 남은 되감기는 재기동 뒤에도 SMON이 이어서 하고, 재기동하는 몇 분 동안 매장 여섯 곳의 계산이 모두 끊기며 진행 중이던 판매도 되감긴다. 그래서 1분마다 USED_UREC를 다시 읽어 예상 시각을 고쳐 가며, 매장에는 14:40까지 반품을 영수증만 받아 두게 알린다. 칸 채우기는 영업이 끝난 뒤(22:00 이후) 다시 돌린다(41분). 영업 중에 해야 한다면 범위를 나눠 조각마다 COMMIT해서, 끊더라도 되감을 일이 한 조각으로 끝나게 한다.

해설 ​

• 세션을 끊는 것은 일을 없애는 것이 아니라 거꾸로 하게 하는 것이다. 끊는 순간부터 되감기가 끝날 때까지 락은 그대로이고, 그 시간은 이미 한 일의 양(USED_UREC)에 따라 정해진다. 몇 시간 돈 배치를 끊으면 몇 시간짜리 되감기가 남을 수 있다(교재 12화 §5의 첫 5분).
• 되감기 시간은 걸린 시간이 아니라 고친 양에 따른다. 조건을 찾느라 읽기에 시간을 쓴 문장은 고친 행이 적어 빨리 되감기고, 인덱스 컬럼을 고친 문장은 행마다 인덱스 항목까지 되돌려야 해서 고친 시간보다 오래 걸리기도 한다. 그래서 경과 시간이 아니라 USED_UREC를 본다.
• 1분 간격 두 번으로 낸 예상은 거칠다. 되감기 속도는 디스크와 버퍼 캐시 상태에 따라 오르내리므로 몇 분 동안 계속 재며 예상 시각을 고치고, 기다리는 쪽(매장)에는 그 시각을 알린다.
• SMON이 이어받은 뒤에는 V$FAST_START_TRANSACTIONS의 UNDOBLOCKSDONE과 UNDOBLOCKSTOTAL로 진행을 본다. 병렬 되감기가 서로 부딪쳐 오히려 느려지는 경우도 있어서, 그때는 FAST_START_PARALLEL_ROLLBACK을 FALSE로 바꿔 SMON 혼자 되감게 하기도 한다. 이번처럼 세션이 스스로 되감는 중에는 이 설정이 상관없다.
• 끊을지 기다릴지는 남은 일(V$SESSION_LONGOPS의 TIME_REMAINING, 처리한 행과 전체 행)과 되감을 일(USED_UREC)을 견줘 정한다. 쉬고 있는 세션의 한 행짜리 트랜잭션처럼 되감을 일이 거의 없으면 바로 끊고, 끝이 가까운 큰 트랜잭션은 기다린다. 결과를 남기면 안 되는 잘못된 문장일 때만 되감기 비용을 치르고 끊는다.

관련 교재 ​

• 12화 · 블로킹 트리와 장애 대응 첫 5분
• 13화 · Undo의 3층