네 갈래로 나눠 돌린 판매 집계 재계산이 데드락으로 죽고, 다시 돌리니 숫자가 늘었다

실전PLSQL버거516 / 700

상황

버거 본사는 메뉴 코드를 정정할 때마다 「일별 상품 판매 집계」를 2년 치 전부 다시 계산한다. 한 세션으로 43분 걸리던 일을, 지난달 개발팀이 매장 번호로 네 갈래로 나눠 잡 넷이 동시에 돌도록 바꿨다(MOD(매장 번호, 4)). 7월 3일 22:10에 처음 돌렸더니 잡 넷 가운데 셋이 ORA-00060으로 죽었다. 담당자가 다음 날 죽은 잡 셋을 처음부터 다시 돌렸고 이번에는 끝까지 돌았다. 그런데 2012년 초의 판매 수량이 POS 보고서보다 훨씬 많다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · SHOP_NO VARCHAR2 'SH0001' ~ 'SH0200' · ORD_PK (ORD_NO)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_NO · ITEM_ID · ORD_ITEM_QTY · ORD_DT · ORD_ITEM_PK (ORD_NO, ITEM_ID)
ITEM_DAY_SUM 일별 상품 판매 집계(이 문제의 파생 테이블): 상품 92개 × 730일 = 67,160행 · 약 250블록 · PK (ITEM_ID, ORD_DT) · SALE_QTY
집계는 본사 보고 화면이 읽기만 한다. 재계산은 마감 뒤(22:00 이후)에 돈다. 같은 시간에 집계를 고치는 다른 작업은 없다.

원본 SQL

sql

-- 0단계 (한 번): 집계를 0으로
UPDATE ITEM_DAY_SUM SET SALE_QTY = 0;
COMMIT;

-- 1단계: 잡 넷 (p_part = 0 · 1 · 2 · 3) 을 동시에
PROCEDURE RECALC_PART(p_part NUMBER) IS
  n PLS_INTEGER := 0;
BEGIN
  FOR r IN (SELECT i.ITEM_ID, i.ORD_DT, i.ORD_ITEM_QTY
              FROM ORD o, ORD_ITEM i
             WHERE i.ORD_NO = o.ORD_NO
               AND MOD(TO_NUMBER(SUBSTR(o.SHOP_NO, 3)), 4) = p_part
             ORDER BY o.ORD_NO) LOOP
    UPDATE ITEM_DAY_SUM
       SET SALE_QTY = SALE_QTY + r.ORD_ITEM_QTY
     WHERE ITEM_ID = r.ITEM_ID AND ORD_DT = r.ORD_DT;
    n := n + 1;
    IF MOD(n, 1000) = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[잡 넷 — 7월 3일 22:10 시작 (0단계로 67,160행을 0으로 만든 뒤)]
잡    SID   매장   처리할 줄     결과
J0    330   50곳   618,204     끝 · 23:01 (51분)
J1    301   50곳   619,515     ORA-00060 · 22:14:07 · 52,000줄을 COMMIT 한 뒤 죽음
J2    288   50곳   617,880     ORA-00060 · 22:19:52 · 127,000줄을 COMMIT 한 뒤 죽음
J3    317   50곳   617,118     ORA-00060 · 22:27:30 · 211,000줄을 COMMIT 한 뒤 죽음
(한 세션으로 돌리던 지난달까지: 2,472,717줄 · 43분)

[V$SESSION — 22:12:00 · 넷 다 같은 문장]
SID   EVENT                            SECS   BLOCKING_SESSION
301   enq: TX - row lock contention      2    288
288   enq: TX - row lock contention      1    330
317   enq: TX - row lock contention      3    301
330   (CPU)
하고 있는 문장: UPDATE ITEM_DAY_SUM SET SALE_QTY = SALE_QTY + :B3 WHERE ITEM_ID = :B2 AND ORD_DT = :B1

[J1 (SID 301) 의 트레이스 — Deadlock graph]
Resource Name                       process session holds waits   process session holds waits
TX-000A0015-00001F2C-469D09C8-...        52     317     X              47     301           X
TX-0003001B-00002A61-469D09C8-...        47     301     X              52     317           X
Rows waited on:
  Session 301: ITEM_DAY_SUM · ITEM_ID 12 · ORD_DT '20120115'
  Session 317: ITEM_DAY_SUM · ITEM_ID 7  · ORD_DT '20120115'

[J0 (SID 330) — 세션 통계]
  UPDATE 실행 618,204번 · user commits 619 · enq: TX 기다림 2,914번 · 합계 31분 (경과 51분의 61%)

[다시 돌린 뒤 — POS 보고서와 비교한 판매 수량]
  2012년 1 ~ 2월 1.75배 · 3 ~ 5월 1.5배 · 6 ~ 8월 1.25배 · 9월 초부터 같음

문제

잡 넷의 결과와 V$SESSION, 트레이스를 근거로 매장 번호로 나눈 잡끼리 왜 서로 기다리고 원을 만드는지 설명하시오. 다시 돌린 뒤 숫자가 늘어난 이유도 쓰시오.
한 문장으로 다시 계산하기, 잡을 상품 번호로 나누기, 매장으로 나누되 잡마다 먼저 모아서 정해진 순서로 갱신하기를 비교하고 하나를 골라 SQL로 작성하시오.
고른 방법의 대가와, 실패한 뒤 다시 돌려도 안전한지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 세션 · 한 문장: 집계 행마다 한 번만, 합계로 덮어쓴다 · COMMIT 은 끝에 한 번
UPDATE ITEM_DAY_SUM SET SALE_QTY = 0;          -- 팔리지 않은 날의 옛 값을 지운다 (같은 트랜잭션)

MERGE INTO ITEM_DAY_SUM t
USING (SELECT ITEM_ID, ORD_DT, SUM(ORD_ITEM_QTY) AS QTY
         FROM ORD_ITEM                         -- ORD_DT 가 ORD_ITEM 에 있다: 매장으로 나누려던 ORD 조인이 필요 없다
        GROUP BY ITEM_ID, ORD_DT) s
   ON (t.ITEM_ID = s.ITEM_ID AND t.ORD_DT = s.ORD_DT)
 WHEN MATCHED THEN UPDATE SET t.SALE_QTY = s.QTY
 WHEN NOT MATCHED THEN INSERT (ITEM_ID, ORD_DT, SALE_QTY) VALUES (s.ITEM_ID, s.ORD_DT, s.QTY);

COMMIT;                                        -- 실패하면 0단계까지 전부 되돌려진다 → 몇 번을 다시 돌려도 같은 값

개선 후 실행계획 학습용 예시

xplan

[고친 판 — 한 세션 (학습용 예시) · 0단계 UPDATE 67,160행 0.9초 뒤의 MERGE]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |              |      1 |        |      0 |00:00:07.40 |    193K |          |
|   1 |  MERGE                  | ITEM_DAY_SUM |      1 |        |      0 |00:00:07.40 |    193K |          |
|   2 |   VIEW                  |              |      1 |        |  67160 |00:00:03.60 |   55515 |          |
|*  3 |    HASH JOIN OUTER      |              |      1 |  67160 |  67160 |00:00:03.50 |   55515 | 8192K (0)|
|   4 |     VIEW                |              |      1 |  67160 |  67160 |00:00:03.20 |   55265 |          |
|   5 |      HASH GROUP BY      |              |      1 |  67160 |  67160 |00:00:03.10 |   55265 | 5120K (0)|
|   6 |       TABLE ACCESS FULL | ORD_ITEM     |      1 |   2472K|   2472K|00:00:00.90 |   55265 |          |
|   7 |     TABLE ACCESS FULL   | ITEM_DAY_SUM |      1 |  67160 |  67160 |00:00:00.02 |     250 |          |
-------------------------------------------------------------------------------------------------------------
   3 - access("T"."ITEM_ID"="S"."ITEM_ID" AND "T"."ORD_DT"="S"."ORD_DT")

  경과 약 9초 (43분 · 네 갈래 51분) · 잡끼리의 기다림 0 · ORA-00060 0 · 다시 돌려도 같은 값

모범 답안 서술

매장 번호로 나누면 읽는 쪽(주문)은 겹치지 않지만 고치는 쪽은 겹친다. 모든 매장이 같은 메뉴를 팔므로 네 잡이 같은 집계 행(상품, 날짜)을 고치고, 네 잡 모두 주문 번호 순서, 곧 날짜 순서로 나아가므로 늘 같은 날의 92행 근처에서 부딪친다. 잡마다 1,000줄을 COMMIT 없이 고치는 동안 그 행들의 락을 쥐고, 다른 잡은 그 행에서 TX 6으로 기다린다(V$SESSION의 enq: TX - row lock contention, blocking_session이 서로를 가리킨다). 한 주문 안의 상품 순서는 잡마다 달라서 J1은 (7, 0115)를 쥔 채 (12, 0115)를, J3은 (12, 0115)를 쥔 채 (7, 0115)를 원해 원이 닫혔다(트레이스의 Rows waited on). 원이 생기지 않을 때도 서로의 COMMIT을 기다리느라 J0은 경과 51분의 61%를 기다림으로 썼다. 넷으로 나눴는데 한 세션(43분)보다 느리다. 숫자가 늘어난 것은 증분 갱신(SALE_QTY = SALE_QTY + 수량)과 중간 COMMIT이 만났기 때문이다. 죽은 잡 셋은 죽기 전에 52,000줄, 127,000줄, 211,000줄을 이미 COMMIT했다. 그 상태에서 처음부터 다시 돌리니 그 줄들이 두 번 더해졌다. 잡은 날짜 순서로 나아가므로 2012년 초가 가장 많이 부풀었다(1~2월은 세 잡이 겹쳐 1.75배). 세 방법을 비교한다. 한 문장 MERGE는 ORD_ITEM을 한 번 훑어(55,265블록) 상품과 날짜로 모은 뒤 집계 행마다 한 번만 합계로 덮어쓴다. 잡끼리 부딪칠 일이 없고 9초 남짓이면 끝난다. ORD_DT가 ORD_ITEM에 있어서 매장으로 나누려고 붙였던 ORD 조인도 필요 없다. 잡을 상품 번호로 나누면(MOD(ITEM_ID, 4)) 잡마다 고치는 행이 겹치지 않아 기다림과 원이 사라진다. 대신 잡마다 ORD_ITEM 전체를 훑으므로 읽기가 네 번이고, 행마다 UPDATE하는 일량(247만 번)은 그대로다. 매장으로 나누되 잡마다 먼저 모아서 (ITEM_ID, ORD_DT) 순서로 갱신하면 원은 생기지 않지만, 같은 행을 두고 서로의 COMMIT을 기다리는 줄은 남고 증분 갱신이라 다시 돌리면 또 부풀 수 있다. 그래서 한 문장을 고른다. 0단계(0으로 만들기)와 MERGE를 한 트랜잭션에 넣고 끝에 한 번 COMMIT한다. 합계로 덮어쓰므로 몇 번을 다시 돌려도 같은 값이 되고, 중간에 실패하면 0단계까지 전부 되돌려져 반쯤 반영된 상태가 남지 않는다. 지금 부풀어 있는 숫자도 이 문장을 한 번 돌리면 바로잡힌다. 대가는 두 가지다. 첫째, COMMIT까지 67,160행 전부의 락을 쥔다(10초 안팎). 집계를 읽기만 하는 보고 화면은 막지 않고 COMMIT 전까지 옛 값을 읽는다. 같은 시간에 집계를 고치는 작업이 생기면 그 작업이 기다리므로 마감 뒤에 돌리는 규칙은 지킨다. 둘째, 해시 그룹핑에 PGA 몇 MB가 든다(Used-Mem 5,120K). 지금 규모에서는 문제가 되지 않는다.

해설

병렬로 나누는 키는 고치는 쪽에서 고른다. 읽는 데이터가 아니라 고치는 행이 겹치지 않게 나눠야 한다. 매장으로 나누면 읽기는 나뉘지만 집계 행은 모두가 함께 쓴다.
증분 갱신과 중간 COMMIT이 만나면 재실행이 위험해진다. 다시 돌려도 같은 결과가 나오게(합계로 덮어쓰기) 짜거나, 어디까지 했는지 기록해 그 뒤부터 이어 돌려야 한다(교재 13화의 반쯤 반영).
잡이 ORA-00060으로 죽은 것은 예외를 잡지 않아서다. 잡지 않은 예외는 그 호출의 일을 되돌리지만, 이미 COMMIT한 1,000줄 묶음들은 되돌리지 못한다. 그래서 '죽은 잡을 처음부터 다시'가 곧 두 번 더하기가 됐다.
행마다 UPDATE하는 247만 번의 왕복을 한 문장으로 바꾸는 것은 락 문제 이전에 배치 튜닝의 기본이다(교재 15화). 행을 하나씩 고치는 배치를 병렬로 돌리는 것은 왕복은 그대로 두고 경합만 더하는 일이 되기 쉽다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황