월요일 10시 40분, 정산 화면 스물여섯 개가 멈췄는데 블로킹 트리의 뿌리가 둘이다

실전SELECT한빛유통543 / 700

상황

월요일 10:40에 정산팀과 고객센터에서 동시에 전화가 왔다. 정산 화면은 등록도 정정도 안 되고, 고객센터의 「결제 수단 변경」은 몇 분째 돌고, 정산 대사 리포트는 평소 2초인데 1분이 넘도록 끝나지 않는다. DBA가 10:41:30에 블로킹 트리와 V$LOCK, V$TRANSACTION을 찍어 보냈다. 박팀장의 주문이다. "누구를 끊고 누구를 기다릴지, 그 순서와 근거를 5분 안에 적어 와. 그리고 다음엔 이런 일이 안 생기게 해."

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · PK_T_ORD_STLM (STLM_SEQ) · 정산 화면이 등록(INSERT)하고 정정(UPDATE)한다
T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 고객센터가 결제 수단을 바꾼다(UPDATE)
「결제 대사 반영」 배치: PG사 대사 결과로 주문 약 3만 건의 결제 정보를 고친다. 평소 10:20에 시작해 25분 걸리고, 끝에 한 번 COMMIT한다
개발팀은 오늘 10:35에 「정산 정정 사유」 컬럼을 더하는 패치를 걸어 두었다(DDL_LOCK_TIMEOUT 900)
정산 WAS의 커넥션 풀은 40개다

원본 SQL

sql

-- DBA 가 10:41:30 에 돌린 블로킹 트리 (교재 12화 §5)
SELECT LPAD(' ', 2 * (LEVEL - 1)) || s.sid AS sid_tree,
       s.serial#, s.program, s.status, s.event, s.seconds_in_wait AS secs,
       s.blocking_session AS blocker, s.sql_id, s.prev_sql_id
  FROM v$session s
 WHERE s.blocking_session IS NOT NULL
    OR s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
 START WITH s.blocking_session IS NULL
 CONNECT BY PRIOR s.sid = s.blocking_session
 ORDER SIBLINGS BY s.sid;

실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5

xplan

[블로킹 트리 — 10:41:30]
SID_TREE   SERIAL#  PROGRAM            STATUS     EVENT                            SECS   하는 문장 (막힌 쪽) / 마지막 문장 (뿌리)
131        2207     SQL Developer      INACTIVE   SQL*Net message from client       922   (마지막) UPDATE T_ORD_STLM SET STLM_AMT = 48200 WHERE STLM_SEQ = 96211
  44       1201     JDBC (정산 WAS)    ACTIVE     enq: TX - row lock contention     402   UPDATE T_ORD_STLM SET STLM_AMT = :1 WHERE STLM_SEQ = :2   (:2 = 96211)
  97       3310     sqlplus (패치)     ACTIVE     enq: TM - contention              388   ALTER TABLE T_ORD_STLM ADD (ADJ_RSN VARCHAR2(100))
    52     1180     JDBC (정산 WAS)    ACTIVE     enq: TM - contention              371   INSERT INTO T_ORD_STLM …
    63     902      JDBC (정산 WAS)    ACTIVE     enq: TM - contention              366   UPDATE T_ORD_STLM SET STLM_AMT = :1 WHERE STLM_SEQ = :2   (:2 = 51077)
    …      (같은 모양 23개 · 모두 BLOCKER 97)
77         402      JDBC (대사 배치)   ACTIVE     db file sequential read             0   UPDATE T_ORD SET PAY_TP = :1, PAY_DT = :2 WHERE ORD_SEQ = :3
  118      5530     JDBC (고객센터)    ACTIVE     enq: TX - row lock contention     214   UPDATE T_ORD SET PAY_TP = :1 WHERE ORD_SEQ = :2
  121      7741     JDBC (고객센터)    ACTIVE     enq: TX - row lock contention      95   UPDATE T_ORD SET PAY_TP = :1 WHERE ORD_SEQ = :2

[트리 밖 — 같은 시각]
88         3001     JDBC (리포트)      ACTIVE     db file sequential read             0   SELECT … FROM T_ORD o JOIN T_ORD_STLM s … (정산 대사 리포트 · 시작한 지 64초)

[V$LOCK — 같은 시각 · 일부]
SID   TYPE  대상 / 이름표          LMODE  REQUEST  BLOCK
131   TM    T_ORD_STLM               3       0       1
131   TX    131 의 트랜잭션           6       0       1
44    TM    T_ORD_STLM               3       0       0
44    TX    131 의 트랜잭션           0       6       0
97    TM    T_ORD_STLM               0       6       0
52    TM    T_ORD_STLM               0       3       0      (63 · 그 밖 23개도 같다)
77    TM    T_ORD                    3       0       0
77    TX    77 의 트랜잭션            6       0       1
118   TX    77 의 트랜잭션            0       6       0      (121 도 같다)
88    (줄 없음)

[V$TRANSACTION — 뿌리 둘]
SID   START_TIME   USED_UBLK   USED_UREC   비고
131   10:26:08          1            2     조민우의 정정 시험 · 저장하지 않고 회의에 들어갔다
77    10:20:01      1,046       52,310     대사 배치 · 3만 건 가운데 약 2만 6천 건째

[88 의 세션 통계 — 시작 뒤 64초]
  consistent gets 412,300 (평소 한 번에 약 38,000) · data blocks consistent reads - undo records applied 371,800 · 기다린 enqueue 0

문제

블로킹 트리와 V$LOCK을 근거로 사슬을 모두 그리시오. 정정할 행이 96211이 아닌 정산 화면 25개가 왜 멈췄는지, 정산 대사 리포트(88)는 무엇을 기다리는지도 설명하시오.
지금 할 일을 순서대로 SQL과 함께 쓰시오. 뿌리마다 끊을지 기다릴지와 그 근거(되감을 일의 양)를 쓰시오.
같은 일이 다시 생기지 않게 할 조치를 셋 이상 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 0. 뿌리마다 되감을 일의 양 (끊기 전에 꼭 본다)
SELECT s.sid, s.serial#, s.program, s.status, s.last_call_et, t.start_time, t.used_urec
  FROM v$session s JOIN v$transaction t ON t.addr = s.taddr
 WHERE s.sid IN (131, 77);

-- 1. 패치(97)를 먼저 멈춘다 — 쥔 것이 없어 되감을 일이 0 · 줄 맨 앞의 TM 6 요청이 사라지면 정산 화면 25개가 바로 지나간다
ALTER SYSTEM CANCEL SQL '97,3310';                 -- 18c 미만이면 ALTER SYSTEM KILL SESSION '97,3310' IMMEDIATE

-- 2. 131 — 조민우에게 연락해 ROLLBACK, 안 되면 끊는다 (되감을 일 2건) → 44 가 풀린다
ALTER SYSTEM KILL SESSION '131,2207' IMMEDIATE;

-- 3. 77 (대사 배치) 은 끊지 않는다 — 약 4분 뒤 끝난다 · 고객센터에 "10:46 까지 결제 수단 변경 지연" 공지
-- 4. 88 (리포트) 은 건드리지 않는다 — 락이 아니라 읽기 일관성 비용이다

개선 후 실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5

xplan

[10:41:50 이후 (학습용 예시)]
  10:41:52   97 문장 취소 (ORA-01013) → 정산 화면 25개가 1초 안에 진행 · 커넥션 반납
  10:42:30   131 끊김 · 되감기 0.01초 → 44 진행
  10:45:58   77 COMMIT (평소대로) → 118 · 121 진행
  10:46:10   88 리포트 끝 · 다음 실행은 평소대로 2초
  패치는 03:00 에 DDL_LOCK_TIMEOUT 5 로 다시

모범 답안 서술

사슬은 둘이다. 첫째는 131 ← 97 ← 정산 화면 25개, 그리고 131 ← 44다. 131은 10:26에 SQL Developer로 정산 96211을 고치고 COMMIT하지 않은 채 쉬는 세션으로, T_ORD_STLM에 TM 3과 행 락(TX 6)을 쥐고 있다. 44는 그 행 96211을 고치려다 131의 TX를 REQUEST 6으로 기다린다. 97은 컬럼을 더하는 ALTER TABLE이라 TM 6을 요청하고 131의 TM 3 뒤에 섰다(DDL_LOCK_TIMEOUT 900). 정산 화면 25개는 96211과 상관없는 정산을 등록하거나 고치려 한다. 이들이 요청한 TM 3은 131의 TM 3과 부딪치지 않지만, enqueue는 끼어들기를 허락하지 않으므로 줄 맨 앞의 97 뒤에 섰다. 그래서 BLOCKER가 131이 아니라 97이다. 둘째 사슬은 77 ← 118, 121이다. 대사 배치 77이 T_ORD 주문 약 2만 6천 건을 고치며 행 락을 쥐었고, 고객센터 둘은 그중 한 행을 고치려다 TX 6으로 기다린다. 88은 트리에도 V$LOCK에도 없다. 락을 기다리는 것이 아니다. 88의 리포트는 77이 고치는 중인 T_ORD 블록을 읽을 때마다 언두로 그 블록을 리포트 시작 시점으로 되돌린 사본(CR 블록)을 만들어 읽는다. undo records applied가 371,800이라 consistent gets가 평소의 열 배를 넘었고, 그만큼 오래 걸릴 뿐이다. 77이 COMMIT하면 다음 실행부터 평소대로 돌아온다. 건드리지 않는다. 할 일의 순서는 되감을 일의 양과 풀리는 세션 수로 정한다. 먼저 V$TRANSACTION의 USED_UREC를 본다. 1번은 패치 97을 멈추는 것이다. 97은 아직 아무것도 쥐지 않았으므로 되감을 일이 없고, 줄 맨 앞의 TM 6 요청이 사라지면 뒤의 정산 화면 25개가 곧바로 지나간다. 131을 끊는 것보다 먼저다. 2번은 131이다. 고친 것이 한 행(USED_UREC 2)이라 되감기가 순식간이다. 조민우에게 ROLLBACK을 부탁하고 연락이 안 되면 끊는다. 그러면 44가 풀린다. 3번, 77은 끊지 않는다. 52,310건을 되감는 몇 분 동안에도 행 락이 그대로 남고, 21분 동안 한 일을 처음부터 다시 해야 한다. 2만 6천 건째이니 평소대로면 약 4분 뒤에 끝난다. 고객센터에는 10:46까지 결제 수단 변경이 늦는다고 알린다. 재발 방지는 셋이다. ① DDL은 한가한 시간에 짧은 DDL_LOCK_TIMEOUT(5초)으로 돌리고, 못 잡으면 물러났다가 다시 시도한다. 900초가 정산 화면 25개를 세웠다. ② 도구 세션이 트랜잭션을 연 채 쉬지 못하게 한다. 프로파일의 IDLE_TIME이나 다른 세션을 막는 유휴 세션 한도(MAX_IDLE_BLOCKER_TIME)를 두고, 운영 DB에서는 SQL Developer의 자동 커밋을 켜고 쓴다. ③ 블로킹 트리를 1분마다 찍어 30초 넘게 막힌 세션이 있으면 알림을 보낸다(교재 12화 포스트모템의 재발방지 3번). 지나간 사슬은 ASH의 blocking_session으로 다시 그린다.

해설

뿌리를 먼저 끊는 것이 늘 답은 아니다. 이 장면에서 가장 많은 세션(25개)을 막은 것은 뿌리 131이 아니라 그 바로 아래의 DDL 97이다. 97은 쥔 것이 없어 멈추는 비용이 0이다. 트리를 그리면 '누구를 치우면 몇 개가 풀리는가'와 '치우는 비용'을 함께 볼 수 있다.
되감기 비용은 USED_UREC로 어림한다. 끊으면 그 트랜잭션이 한 일을 모두 되돌리고, 되돌리는 동안에도 락이 남는다(교재 12화 §5의 첫 5분). 끝이 보이는 배치는 기다리는 편이 빠르다.
131을 먼저 끊어도 정산 화면은 풀린다. 다만 그러면 97이 TM 6을 잡고 컬럼을 더하는 DDL이 업무 시간에 실행되고, 그 순간 T_ORD_STLM을 쓰는 커서가 모두 무효가 된다. 배포는 계획한 시간에 다시 하는 편이 맞다.
ALTER SYSTEM CANCEL SQL은 세션을 남기고 문장만 멈춘다(18c 이상). 패치 담당자의 세션을 끊지 않아도 되므로 먼저 쓴다.
박팀장에게 보내는 메모에는 트리를 찍은 시각(10:41:30)을 적는다. V$는 장애가 풀리면 사라지므로 그 시각이 증거의 시각이다. 지나간 장애라면 ASH로 같은 트리를 그린다(교재 17화 §6).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황