월 마감 상품 집계가 상품 수에 비례해 느려진다

기초PLSQL유통431 / 700

상황

본사 레거시 유통의 월 마감 배치에는 「상품별 월 판매 집계」 단계가 있다. 2023년 6월분은 7.6초가 걸렸다. 다음 달에 신상품 40개가 들어온다는 공지가 나오자 배치 담당자가 물었다. "상품이 늘면 집계 시간도 그만큼 느는 건가요?" 소스를 열어 보니 상품 마스터를 한 건씩 돌면서, 상품마다 그 달 판매를 합계 내는 SELECT를 따로 실행하고 있었다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 2023년 6월 20,953건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 6월분 45,912행
CF01 상품: 109행 · 5블록 · PK ITM_NO · 6월에 팔린 상품은 107개다.
인덱스는 CF10_PK (SLE_NO)와 IX_CF10_SLE_DT (SLE_DT)(03화에서 만든 것)이다. CF11에는 PK 말고 인덱스가 없다.
CF11_MON_SUM(집계 결과, 이 문제용 테이블): PK (SLE_YM, ITM_NO) · 배치는 그 달 행을 지우고 다시 넣는다. 통계는 최신이다.

원본 SQL

sql

PROCEDURE SUM_ITEM_MONTH(p_ym VARCHAR2) IS      -- p_ym = '202306'
  v_qty NUMBER;
  v_amt NUMBER;
BEGIN
  DELETE FROM CF11_MON_SUM WHERE SLE_YM = p_ym;
  FOR r IN (SELECT ITM_NO FROM CF01) LOOP          -- 상품 109개를 하나씩
    SELECT SUM(b.SLE_QTY), SUM(b.SLE_QTY * b.SLE_UPRC)
      INTO v_qty, v_amt
      FROM CF10 a, CF11 b
     WHERE b.SLE_NO = a.SLE_NO
       AND a.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
       AND b.ITM_NO = r.ITM_NO;
    IF v_qty > 0 THEN                              -- 팔린 상품만 넣는다
      INSERT INTO CF11_MON_SUM (SLE_YM, ITM_NO, SLE_QTY, SLE_AMT)
      VALUES (p_ym, r.ITM_NO, v_qty, v_amt);
    END IF;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — SUM_ITEM_MONTH('202306') 한 번]
  경과 시간                 7.4 ~ 7.8 초
  session logical reads     약 908,600
  recursive calls           약 230
  execute count             약 220

[V$SQL — 루프 안의 SELECT]
  SQL_TEXT                                                        EXECUTIONS  BUFFER_GETS  ELAPSED
  SELECT SUM(B.SLE_QTY), SUM(B.SLE_QTY * B.SLE_UPRC) FROM CF10 A …        109      907,970    7.5 초

[그 SELECT 한 번의 실행계획 — DISPLAY_CURSOR (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |      1 |00:00:00.07 |    8330 |          |
|   1 |  SORT AGGREGATE                       |                |      1 |      1 |      1 |00:00:00.07 |    8330 |          |
|*  2 |   HASH JOIN                           |                |      1 |    421 |    430 |00:00:00.07 |    8330 | 1824K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  20953 |  20953 |00:00:00.01 |     257 |          |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20953 |  20953 |00:00:00.01 |      62 |          |
|*  5 |    TABLE ACCESS FULL                  | CF11           |      1 |  10062 |  10114 |00:00:00.06 |    8073 |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   4 - access("A"."SLE_DT">=:B1||'01' AND "A"."SLE_DT"<=:B1||'31')
   5 - filter("B"."ITM_NO"=:B2)

문제

7.6초가 어디서 나오는지 V$SQL과 실행계획으로 설명하고, 상품이 40개 늘면 어떻게 되는지 쓰시오.
결과가 바뀌지 않게 배치를 고치고, 고친 배치의 일량을 숫자로 적으시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE SUM_ITEM_MONTH(p_ym VARCHAR2) IS
BEGIN
  DELETE FROM CF11_MON_SUM WHERE SLE_YM = p_ym;

  -- 상품마다 109번 읽던 판매를 한 번 읽고, 상품별로 묶어 한 번에 넣는다
  INSERT INTO CF11_MON_SUM (SLE_YM, ITM_NO, SLE_QTY, SLE_AMT)
  SELECT p_ym, b.ITM_NO, SUM(b.SLE_QTY), SUM(b.SLE_QTY * b.SLE_UPRC)
    FROM CF10 a, CF11 b
   WHERE b.SLE_NO = a.SLE_NO
     AND a.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
   GROUP BY b.ITM_NO;                     -- 팔린 상품만 나온다 (루프의 v_qty > 0 과 같다)

  COMMIT;                                 -- DELETE 와 INSERT 를 한 트랜잭션으로
END;

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

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                       |                |      1 |        |      0 |00:00:00.09 |    8352 |          |
|   1 |  LOAD TABLE CONVENTIONAL               | CF11_MON_SUM   |      1 |        |      0 |00:00:00.09 |    8352 |          |
|   2 |   HASH GROUP BY                        |                |      1 |    109 |    107 |00:00:00.09 |    8330 | 1263K (0)|
|*  3 |    HASH JOIN                           |                |      1 |  45890 |  45912 |00:00:00.08 |    8330 | 1824K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  20953 |  20953 |00:00:00.01 |     257 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20953 |  20953 |00:00:00.01 |      62 |          |
|   6 |     TABLE ACCESS FULL                  | CF11           |      1 |   1096K|   1096K|00:00:00.05 |    8073 |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."SLE_NO"="A"."SLE_NO")
   5 - access("A"."SLE_DT">=:B1||'01' AND "A"."SLE_DT"<=:B1||'31')

[세션 통계]  경과 0.1초 · session logical reads 약 8,600 · 107행 적재 (루프 판과 같은 107행 · 같은 합계)

모범 답안 서술

루프 안의 SELECT가 상품 109개마다 한 번씩, 모두 109번 실행됐다(V$SQL EXECUTIONS 109). 한 번 실행할 때 6월 판매 20,953건은 IX_CF10_SLE_DT로 읽지만(3번 줄 257블록), CF11에는 ITM_NO로 찾을 인덱스가 없어서 110만 행을 FULL로 읽고 그중 한 상품 것만 남긴다(5번 줄 8,073블록). 109 × 8,330 = 907,970블록이고, 7.6초의 거의 전부가 이 문장에서 나온다. recursive calls는 230 정도라서 엔진 왕복이 원인은 아니다. 같은 테이블을 109번 다시 읽은 것이 원인이다. 그래서 상품이 40개 늘면 FULL도 40번 늘어 일량은 상품 수에 비례해 약 124만 블록이 된다. 루프를 없애고 INSERT … SELECT 한 문장으로 바꾼다. CF11을 한 번만 FULL로 읽어 6월 판매와 해시 조인하고 ITM_NO로 묶는다. Buffers는 907,970 → 8,352, 경과 시간은 7.6초 → 0.1초로 준다. 상품이 149개로 늘어도 일량은 그대로다. 결과도 같다. 루프는 팔린 상품만 넣었고(v_qty > 0), GROUP BY도 팔린 상품만 만든다(107행). CF11 (ITM_NO) 인덱스를 만들어 루프를 살리는 안도 있다. 한 번에 읽는 양은 줄지만 109번 실행은 그대로이고, 110만 행 테이블에 인덱스를 하나 더 유지해야 한다.

해설

함정: CF11에 ITM_NO 인덱스를 만들면 루프 한 번의 일량은 준다. 하지만 SQL 실행 109번과 인덱스 유지 비용(판매가 들어올 때마다 인덱스도 고친다)이 남는다. 한 번 읽고 묶으면 인덱스도 필요 없다.
이 배치는 recursive calls가 230뿐이라 PL/SQL과 SQL 사이의 왕복은 문제가 아니었다. 루프의 비용은 "몇 번 건너가나"와 "한 번 건너가서 무엇을 하나"를 곱한 값이다. 여기서는 뒤의 것이 컸다(교재 15화의 보정 배치는 앞의 것이 컸다).
GROUP BY 결과에는 팔린 상품만 나온다. 루프가 판매 0인 상품도 0으로 넣었다면 CF01에서 출발하는 아우터 조인과 NVL을 써야 결과가 같아진다.
집계 테이블에서 그 달만 지우고 다시 넣으므로 몇 번을 돌려도 결과가 같다. DELETE와 INSERT를 한 트랜잭션으로 묶어 두면 중간에 실패해도 반쯤 빈 집계가 화면에 보이지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황