SQLP 튜닝 연습장

테마

T4 — 장애 포스트모템 (템플릿) ​

12화에서 제정했다. 장애가 끝난 뒤 같은 장애가 다시 나지 않게 하려고 쓰는 문서다. T1이 "이 SQL을 어떻게 고쳤는가"라면, T4는 "무엇이 멈췄고, 그 구조가 왜 거기 있었고, 무엇을 바꾸는가"다. blameless: 사람을 탓하지 않는다. 사람 이름은 타임라인에만 쓴다. 원인 칸에는 구조만 쓴다. 08화 이후의 다른 리포트와 마찬가지로 「정합성 회귀 체크리스트」를 붙인다(9번 칸).

왜 이 문서가 있는가 ​

2019-03-14  hd.kang
  N1 채번: 채번 테이블(T_STLM_NUM) 방식 시험 — 보정 배치가 도는 동안 정산 등록 화면이 대기함. 철회.
  번호는 주간 보정 배치에서만 매긴다 (등록 화면은 번호 없이 저장, 배치가 매김).
  MAX+1 로 변경. 매기는 곳이 하나뿐이라 겹칠 일이 없음.

12화의 장애는 이미 한 번 났던 장애였다. 강도현은 그 일을 커밋 로그 세 줄로 남겼다. 하지만 "번호를 받는 곳은 하나뿐"이라는 전제가 무너진 뒤에도 그 세 줄을 읽은 사람은 없었다. 개발팀은 그가 접어 둔 채번 테이블을 다시 펼쳤다.

12화 실측 (ora-hanorder 재현)값
막힌 곳N1의 SELECT ... FOR UPDATE · enq: TX - row lock contention · 기다리는 세션의 TX REQUEST 6
기다린 시간을 정한 것막은 쪽 트랜잭션의 길이(연습 DB 316~321cs · 운영 약 10분). 번호 받기 자체는 5블록짜리 일
장애가 남긴 흔적alert 로그 한 줄 + 데드락 트레이스 파일 하나(V$에는 아무것도 남지 않았다)

포스트모템은 장애가 아니라 전제를 기록하는 문서다. 다음 사람이 같은 결정을 다시 내릴 때 읽을 수 있어야 한다.

원칙 다섯 ​

#원칙왜
1사람 이름은 타임라인에만"누가 했다"가 원인이 되면 다음에는 아무도 그 일을 하지 않는다. 12화의 핫픽스는 진짜 버그(번호 겹침)를 고친 것이었다
2시각에는 근거를 붙인다기억이 아니라 로그 · V$ 캡처 · 트레이스 · 메신저 시각을 근거로 쓴다. 근거 없는 시각에는 "(추정)"이라고 쓴다
35 Whys는 구조에서 멈춘다"부주의", "실수", "교육 부족"에서 멈추면 반려한다. 멈춘 자리가 바꿀 수 있는 구조(절차 · 설계 · 감시 · 기록)여야 한다
4즉시조치와 항구조치를 나눈다그날 불을 끈 것과 다시 불이 나지 않게 하는 것은 다른 일이다. 즉시조치를 항구조치처럼 두면 같은 장애가 다시 온다
5재발방지 액션마다 리스크를 쓴다조치도 시스템을 바꾼다. 무엇을 바꾸는지 적지 않은 조치는 다음 장애의 원인이 될 수 있다

금지어 ​

원인 칸에 "담당자 부주의", "확인 미흡", "교육 필요"를 쓰지 않는다. T0의 금지어도 그대로 적용한다. "느려졌다 / 멈췄다"로만 쓰지 말고 무엇이 · 몇 개 · 얼마나를 쓴다.

양식 ​

┌──────────────────────────────────────────────────────────────────────────────┐
│ T4 장애 포스트모템 — N호                                                       │
├──────────────────────────────────────────────────────────────────────────────┤
│ 1. 사건 요약 (한 줄)  : 언제 · 무엇이 · 얼마나 · 무엇 때문에                    │
│ 2. 타임라인 (분 단위) : 시각 | 일어난 일 | 근거                                 │
│    최초 증상 → 탐지 → 인지 → 조치 시작 → 완화 → 복구 (그 앞의 "원인이 들어온 시각" 까지) │
│ 3. 영향              : 멈춘 시간 · 영향받은 화면 · 사용자 · 거래 수 · 데이터 영향   │
│ 4. 탐지              : 누가 · 무엇으로 알았나 · 왜 더 일찍 몰랐나 (감시 지표가 있었나) │
│ 5. 블로킹 체인 증거   : 블로킹 트리 · V$LOCK (LMODE / REQUEST / BLOCK) · TX 이름표 ·   │
│                        데드락 트레이스의 Deadlock graph                          │
│ 6. 근본 원인 (5 Whys): 다섯 번 묻고, 구조에서 멈춘다                             │
│ 7. 조치              : 즉시조치 (그날) | 항구조치 (리비전 · 설계 · 절차) — 나눠서   │
│ 8. 잘 된 점 / 운이 좋았던 점                                                     │
│ 9. 정합성            : 장애 동안 · 조치 뒤 데이터가 맞는가 — 「정합성 회귀 체크리스트」 │
│10. 재발방지 액션      : 아래 표                                                   │
└──────────────────────────────────────────────────────────────────────────────┘

10번 — 재발방지 액션 표 ​

#조치구분 (항구 · 탐지 · 예방 · 절차)담당기한검증 방법리스크
열적는 것비었을 때
조치무엇을 바꾸나. 리비전 번호 · 객체 이름 · 절차 이름까지반려
검증 방법조치가 효과가 있었는지 무엇을 재서 확인하나(블로킹 트리 · V$ENQUEUE_STATISTICS · 재현 스크립트)반려
리스크이 조치가 바꾸는 것 · 그 때문에 새로 생길 수 있는 일

블로킹 체인 증거를 모으는 쿼리 ​

① 블로킹 트리 — 장애 중에 가장 먼저 찍는다 (읽기만 한다) ​

sql
SELECT LPAD(' ', 2 * (LEVEL - 1)) || s.sid AS sid_tree,
       s.username, s.program, 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;
  • 뿌리(들여쓰기 없는 줄)가 막은 쪽이다. 뿌리의 event가 SQL*Net message from client면 트랜잭션을 연 채 쉬고 있는 세션이다.
  • 막힌 쪽의 sql_id로는 무엇을 하려다 막혔는지, 뿌리의 prev_sql_id로는 무엇을 하고 나서 쉬고 있는지를 본다.

② V$LOCK — 무엇을 쥐고 무엇을 원하나 ​

sql
SELECT l.sid, l.type, l.lmode, l.request, l.block, l.ctime,
       CASE l.type WHEN 'TX' THEN 'usn ' || TRUNC(l.id1/65536) || ' slot ' || MOD(l.id1,65536) || ' wrap ' || l.id2
                   WHEN 'TM' THEN (SELECT owner || '.' || object_name FROM dba_objects WHERE object_id = l.id1) END AS what
  FROM v$lock l
 WHERE l.type IN ('TX','TM') AND (l.block = 1 OR l.request > 0 OR l.sid IN (/* 트리의 SID */))
 ORDER BY l.id1, l.id2, l.request;

LMODE와 REQUEST는 직접 재서 적는다. 교과서 값을 옮겨 적지 않는다. 12화에서 무인덱스 FK의 동작이 교과서와 달랐다.

③ 데드락 — 지나간 장애의 흔적 ​

sql
SELECT originating_timestamp, REGEXP_SUBSTR(message_text, '/[^ ]+\.trc') AS trace_file
  FROM v$diag_alert_ext
 WHERE message_text LIKE '%ORA-00060%'
 ORDER BY originating_timestamp DESC FETCH FIRST 5 ROWS ONLY;

트레이스는 DB 서버 안에 있다. Deadlock graph 부분만 꺼내 붙인다(꺼내는 방법은 12화 3_실습과_기록.md STEP 7).

작성 예 ​

12화 · 포스트모템 1호(5월 20일 정산 등록 화면 전체 대기): 10_본편_튜너의_1년/12화_아무도_안_건드렸는데_전체가_멈췄다/3_실습과_기록.md STEP 10

운용 ​

언제무엇
장애가 끝난 그날타임라인과 블로킹 체인 증거부터 남긴다. 기억과 캡처가 사라지기 전에
일주일 안에5 Whys · 재발방지 액션 · 정합성 체크리스트까지 채워 관련 팀 모두에 보낸다
재발방지 기한이 올 때마다「검증 방법」 칸대로 재고, 결과를 이 문서 끝에 덧붙인다
같은 원인이 두 번째로 나오면그 원인은 「SQL 코딩 가이드」의 조문 후보가 된다(18화 승격 규칙)

개정 이력: 12화에 제정했다(1호 = 5월 20일 정산 등록 대기).