SQLP 튜닝 연습장

테마

튜닝 연습Lock과 동시성 제어
LOCK-018

상품 11개를 지우는 정리 배치가 개점 뒤까지 끝나지 않는다

실전DELETE유통513 / 700

상황 ​

본사 레거시 유통은 지난달 감사 지적("판매상품에 상품 마스터에 없는 번호가 있다")을 받고 상품을 가리키는 FK 셋을 걸었다(CF11, CF13, CF18 → CF01). 분기마다 도는 「미출시 상품 정리」 배치는 판매 중지(SLE_YN = 'N')이면서 판매, 발주, 재고 어디에도 쓰인 적이 없는 상품의 가격과 상품 행을 지운다. FK를 걸기 전에는 1초면 끝났다. 이번 분기에는 새벽 점검이 늦어져 09:05에 배치를 손으로 돌렸다. 매장은 08:30에 문을 열었다. 09:40에도 배치는 돌고 있고, 09:21에는 본사 MD가 단종 예정이던 상품 하나를 「판매 재개」로 바꾸려다 화면이 멈췄다고 알려 왔다. 매장 POS는 정상이다.

테이블과 인덱스 ​

  • CF01 상품(부모): 109행 · 5블록 · PK ITM_NO · SLE_YN · USE_YN · 이번 분기 정리 대상 11개
  • CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · FK CF11_FK_ITM (ITM_NO → CF01) · ITM_NO가 맨 앞인 인덱스는 없다
  • CF13 발주상품: 7,102행 · 57블록 · PK (ORD_NO, ITM_NO) · FK → CF01
  • CF18 일별상품재고: 408행 · 6블록 · PK (SHP_NO, ITM_NO, INV_BS_DT) · FK → CF01
  • CF05 상품가격: 157행 · 5블록 · PK (ITM_NO, UPRC_KN_CD, APL_DT) · FK는 걸지 않았다
  • POS는 손님 한 명의 계산을 트랜잭션 하나로 묶는다. 첫 바코드에서 CF10과 CF11을 넣기 시작해 결제가 끝나면 COMMIT한다(보통 1~3분). 매장 6곳, 계산대 14대.
  • USE_YN = 'N' 상품은 POS 상품 목록과 MD 화면 목록에 나오지 않는다(이미 있는 규칙).

원본 SQL ​

sql
-- 분기 「미출시 상품 정리」 배치
BEGIN
  FOR r IN (SELECT i.ITM_NO
              FROM CF01 i
             WHERE i.SLE_YN = 'N'
               AND NOT EXISTS (SELECT 1 FROM CF11 s WHERE s.ITM_NO = i.ITM_NO)
               AND NOT EXISTS (SELECT 1 FROM CF13 o WHERE o.ITM_NO = i.ITM_NO)
               AND NOT EXISTS (SELECT 1 FROM CF18 v WHERE v.ITM_NO = i.ITM_NO)) LOOP
    DELETE FROM CF05 WHERE ITM_NO = r.ITM_NO;
    DELETE FROM CF01 WHERE ITM_NO = r.ITM_NO;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 · 기다림의 모양은 교재 12화 실측(26ai Free) ​

xplan
[상품 하나 DELETE FROM CF01 — 새벽 복사본에서 혼자]
                              FK 걸기 전    FK 건 뒤
  session logical reads            4        8,276      ← CF11 8,204 + CF13 57 + CF18 6 을 통째로
  CPU                           0 ms       310 ms

[시간표 — 분기 첫 주 화요일]
시각       정리 배치 (SID 58)                               POS 계산대 (14대)                   MD 화면 (SID 93)
09:05:00   대상 11개 확정 (커서는 CF11 을 한 번 훑는다)
09:05:01   상품 1093 · CF05 · CF01 DELETE → FK 확인         계산 중 (COMMIT 전) 9건
           → CF11 을 훑다가 계산 중인 트랜잭션을 만날 때마다 기다림
09:08:40   상품 1093 끝 (3분 39초 · 계산 7건을 차례로 기다림)
09:08:41   상품 1094 …
09:21:10                                                                                       상품 1093 「판매 재개」
                                                                                               UPDATE CF01 … → 기다림
09:40:00   여섯째 상품 진행 중 · COMMIT 전 (지운 상품 다섯 행의 락을 쥔 채)

[V$SESSION — 09:30:00]
SID   PROGRAM       EVENT                            SECS   BLOCKING_SESSION   하고 있는 문장
58    SQL*Plus      enq: TX - row lock contention      74   211                DELETE FROM CF01 WHERE ITM_NO = :B1
211   POS_03_02     SQL*Net message from client        80                      (계산 중 — 트랜잭션을 연 채)
93    MD_CLIENT     enq: TX - row lock contention     530    58                UPDATE CF01 SET SLE_YN = 'Y' … WHERE ITM_NO = 1093

[V$LOCK — 09:30:00 (TM 은 객체 이름으로)]
SID   TYPE  대상                  LMODE  REQUEST  BLOCK
58    TM    CF05                    3      0        0
58    TM    CF01                    3      0        0
58    TX    배치의 트랜잭션           6      0        1
58    TX    SID 211 의 트랜잭션       0      4        0      ← 행 락(6)이 아니라 4
211   TM    CF10                    3      0        0
211   TM    CF11                    3      0        0
211   TM    CF01                    3      0        0      ← 자식에 넣는 세션은 부모에도 TM 3
211   TX    SID 211 의 트랜잭션       6      0        1
93    TM    CF01                    3      0        0
93    TX    배치의 트랜잭션           0      6        0
      (배치는 CF11 · CF13 · CF18 에 TM 을 쥐지도 요청하지도 않았다)

문제 ​

  1. 세션 통계와 시간표, V$LOCK을 근거로 배치가 상품 하나에 몇 분씩 걸리는 이유와 MD 화면이 기다리는 대상을 설명하시오. CF11_PK에 ITM_NO가 들어 있는데도 소용이 없는 이유도 쓰시오.
  2. 자식 FK 컬럼에 인덱스를 만드는 안, 지우지 않고 사용 중지로 표시하는 안, 배치 동안 FK를 끄는 안을 비교하고, 하나를 골라 SQL로 작성하시오.
  3. 고른 안의 대가를 쓰고, "새벽에 돌리면 된다"는 의견에 답하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자