SQLP 튜닝 연습장

테마

튜닝 연습DML과 배치 튜닝
DML-018

입고 배분 재계산에 41초, 앞 줄 결과가 다음 줄을 정해서 한 문장으로는 안 된다고 한다

실전MERGE유통443 / 700

상황 ​

유통 본사는 발주상품마다 입고된 수량(RCV_QTY)을 관리한다. 입고가 들어오면 그 상품의 발주 중 오래된 것부터 채운다(선입선출). 지난주 입고 자료 일부가 두 번 올라온 사고가 있어서, 사고분을 지운 입고 이력으로 RCV_QTY를 처음부터 다시 계산해야 한다. 기존 재계산 프로그램은 입고 줄마다 그 상품의 발주를 오래된 순서로 읽어 가며 채우는데, 리허설에서 41초가 걸렸다. 담당자: "앞 줄에서 얼마 채웠는지에 따라 다음 줄이 달라지니까 한 문장으로는 안 돼요." 재계산은 토요일 06:00 ~ 06:30 점검 시간에 하고, 그동안 발주와 입고 입력을 막는다.

테이블과 인덱스 ​

  • CF12 발주: 734행 · 10블록 · PK ORD_NO · ORD_DT VARCHAR2(8)
  • CF13 발주상품: 7,102행 · 57블록 · PK (ORD_NO, ITM_NO) · ORD_QTY. 이 문제에서는 입고 배분 수량 RCV_QTY 칸이 더 있다. ITM_NO만으로 찾을 인덱스는 없다.
  • CF15 입출고상품: 51,497행 · 349블록 · PK (INOUT_NO, ITM_NO) · INOUT_QTY. 이 문제에서는 입출고 구분 INOUT_TY_CD('I' 입고 · 'O' 출고)를 입출고 머리 테이블에서 옮겨 둔 칸이 더 있다. 입고 줄은 30,212행이다(사고분을 지운 뒤).
  • 상품은 109개다. 검증용으로 CF13의 복사본(CF13_C)을 만들어도 된다.

원본 SQL ​

sql
PROCEDURE REBUILD_RCV IS
  v_left NUMBER;
  v_fill NUMBER;
BEGIN
  UPDATE CF13 SET RCV_QTY = 0;
  FOR g IN (SELECT INOUT_NO, ITM_NO, INOUT_QTY
              FROM CF15
             WHERE INOUT_TY_CD = 'I'
             ORDER BY INOUT_NO) LOOP                          -- 입고 줄마다 (30,212번)
    v_left := g.INOUT_QTY;
    FOR d IN (SELECT d.ROWID AS rid, d.ORD_QTY - d.RCV_QTY AS OPEN_QTY
                FROM CF13 d, CF12 h
               WHERE h.ORD_NO = d.ORD_NO
                 AND d.ITM_NO = g.ITM_NO
                 AND d.RCV_QTY < d.ORD_QTY
               ORDER BY h.ORD_DT, d.ORD_NO) LOOP              -- 오래된 발주부터
      EXIT WHEN v_left = 0;
      v_fill := LEAST(v_left, d.OPEN_QTY);
      UPDATE CF13 SET RCV_QTY = RCV_QTY + v_fill WHERE ROWID = d.rid;
      v_left := v_left - v_fill;
    END LOOP;                                                 -- 남는 수량은 초과 입고 화면이 따로 계산한다
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 ​

xplan
[리허설 — 세션 통계 · REBUILD_RCV 한 번]
  경과 시간                 40.6 ~ 41.9 초
  recursive calls           약 52,900
  execute count             약 52,700   (안쪽 커서 OPEN 30,212 · UPDATE 22,481 · 그 밖)
  session logical reads     약 2,098,000
  sorts (memory)            30,214

[V$SQL — 안쪽 커서]
  EXECUTIONS 30,212 · BUFFER_GETS 2,024,204 (한 번에 67블록: CF13 FULL 57 · CF12 FULL 10) · ELAPSED 39.8 초

[안쪽 커서 한 번의 실행계획 — DISPLAY_CURSOR (ALLSTATS LAST)]
----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     21 |00:00:00.01 |      67 |          |
|   1 |  SORT ORDER BY      |      |      1 |     33 |     21 |00:00:00.01 |      67 | 2048  (0)|
|*  2 |   HASH JOIN         |      |      1 |     33 |     21 |00:00:00.01 |      67 | 1245K (0)|
|   3 |    TABLE ACCESS FULL| CF12 |      1 |    734 |    734 |00:00:00.01 |      10 |          |
|*  4 |    TABLE ACCESS FULL| CF13 |      1 |     33 |     21 |00:00:00.01 |      57 |          |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."ORD_NO"="D"."ORD_NO")
   4 - filter(("D"."ITM_NO"=:B1 AND "D"."RCV_QTY"<"D"."ORD_QTY"))

문제 ​

  1. 41초가 어디서 나오는지 V$SQL과 실행계획으로 설명하시오.
  2. "앞 줄에서 얼마 채웠는지에 따라 다음 줄이 달라지니까 한 문장으로는 안 된다"는 말이 이 배분에서 맞는지 판단하고, 같은 결과를 내는 SQL을 작성하시오.
  3. 지금 루프, BULK COLLECT와 FORALL, 한 문장의 세 방법을 비교하고, 바꾸기 전에 결과가 같은지 어떻게 확인할지 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자