매장 상품 월 집계를 FORALL로 바꿨는데 논리 읽기가 그대로다

중급MERGE버거442 / 700

상황

버거원 본사의 「매장별 상품 판매」 화면은 집계 테이블 SHOP_ITEM_MON을 읽는다. 이번 달 집계는 매일 밤 다시 계산해 덮어쓴다. 원래는 커서 루프로 (매장, 상품) 조합마다 MERGE를 한 번씩 실행했는데(3.1초), "BULK로 바꾸면 빨라진다"는 말을 듣고 개발자가 BULK COLLECT와 FORALL로 고쳤다. 경과 시간은 2.3초로 조금 줄었지만 세션의 논리 읽기는 95,412 → 93,806으로 거의 그대로다. 개발자: "FORALL이면 한 번에 처리되는 거 아닌가요?"

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · 2013년 12월 42,408행 · SHOP_NO 200개
ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 2013년 12월 106,342행(ORD_DT는 ORD에서 복사한 값)
SHOP_ITEM_MON(집계, 이 문제용 테이블): SHOP_ITEM_MON_PK (ORD_YM, SHOP_NO, ITEM_ID) · SALE_QTY · LINE_CNT · 24개월 약 430,000행 · 약 1,900블록. 12월 조합은 17,934개이고, 그중 17,760개는 어젯밤 집계로 이미 있다.
통계는 최신이다.

원본 SQL

sql

DECLARE
  TYPE t_shop IS TABLE OF SHOP_ITEM_MON.SHOP_NO%TYPE;
  TYPE t_item IS TABLE OF SHOP_ITEM_MON.ITEM_ID%TYPE;
  TYPE t_num  IS TABLE OF NUMBER;
  v_shop t_shop;  v_item t_item;  v_qty t_num;  v_cnt t_num;
BEGIN
  SELECT o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY), COUNT(*)
    BULK COLLECT INTO v_shop, v_item, v_qty, v_cnt
    FROM ORD o, ORD_ITEM i
   WHERE i.ORD_NO = o.ORD_NO
     AND o.ORD_DT BETWEEN '20131201' AND '20131231'
     AND i.ORD_DT BETWEEN '20131201' AND '20131231'
   GROUP BY o.SHOP_NO, i.ITEM_ID;

  FORALL k IN 1 .. v_shop.COUNT                    -- "BULK 로 바꾸면 빨라진다"
    MERGE INTO SHOP_ITEM_MON t
    USING (SELECT '201312' AS ORD_YM, v_shop(k) AS SHOP_NO, v_item(k) AS ITEM_ID,
                  v_qty(k) AS QTY, v_cnt(k) AS CNT
             FROM dual) s
       ON (t.ORD_YM = s.ORD_YM AND t.SHOP_NO = s.SHOP_NO AND t.ITEM_ID = s.ITEM_ID)
     WHEN MATCHED THEN UPDATE SET t.SALE_QTY = s.QTY, t.LINE_CNT = s.CNT
     WHEN NOT MATCHED THEN INSERT (ORD_YM, SHOP_NO, ITEM_ID, SALE_QTY, LINE_CNT)
          VALUES (s.ORD_YM, s.SHOP_NO, s.ITEM_ID, s.QTY, s.CNT);
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — 두 판 비교 (12월분 · 17,934조합)]
                               루프 판(지난달까지)     FORALL 판(이번 주)
  경과 시간                    3.1 초                 2.3 초
  recursive calls              18,046                     34
  execute count                17,951                     22
  session logical reads        95,412                 93,806
  db block changes             36,180                 35,940
  redo size                    7.9 MB                 7.7 MB

[V$SQL — FORALL 판의 MERGE]
  ROWS_PROCESSED 17,934 · BUFFER_GETS 89,940 (원소 하나에 약 5블록: PK 탐색 약 3 · 고치기 약 2)

[집계 SELECT — DISPLAY_CURSOR (ALLSTATS LAST)]
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |  17934 |00:00:00.19 |    3750 |          |
|   1 |  HASH GROUP BY                        |              |      1 |  18400 |  17934 |00:00:00.19 |    3750 | 2318K (0)|
|*  2 |   HASH JOIN                           |              |      1 |    106K|    106K|00:00:00.12 |    3750 | 3021K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD          |      1 |  42408 |  42408 |00:00:00.02 |    1067 |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01      |      1 |  42408 |  42408 |00:00:00.01 |     131 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |    106K|    106K|00:00:00.03 |    2683 |          |
|*  6 |     INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |    106K|    106K|00:00:00.01 |     381 |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ORD_NO"="O"."ORD_NO")
   4 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')
   6 - access("I"."ORD_DT">='20131201' AND "I"."ORD_DT"<='20131231')

문제

FORALL로 바꿨는데 recursive calls만 줄고 논리 읽기가 그대로인 이유를 설명하시오.
같은 결과를 내도록 다시 작성하고, 고친 뒤의 숫자를 적으시오.
FORALL이 맞는 경우는 언제인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 집계를 USING 에 그대로 — 한 문장 · 집계 결과와 대상 12월분을 해시 조인으로 한 번에 맞댄다
MERGE INTO SHOP_ITEM_MON t
USING (SELECT o.SHOP_NO, i.ITEM_ID,
              SUM(i.ORD_ITEM_QTY) AS QTY, COUNT(*) AS CNT
         FROM ORD o, ORD_ITEM i
        WHERE i.ORD_NO = o.ORD_NO
          AND o.ORD_DT BETWEEN '20131201' AND '20131231'
          AND i.ORD_DT BETWEEN '20131201' AND '20131231'
        GROUP BY o.SHOP_NO, i.ITEM_ID) s
   ON (t.ORD_YM = '201312' AND t.SHOP_NO = s.SHOP_NO AND t.ITEM_ID = s.ITEM_ID)   -- 대상은 12월분만
 WHEN MATCHED THEN UPDATE SET t.SALE_QTY = s.QTY, t.LINE_CNT = s.CNT
 WHEN NOT MATCHED THEN INSERT (ORD_YM, SHOP_NO, ITEM_ID, SALE_QTY, LINE_CNT)
      VALUES ('201312', s.SHOP_NO, s.ITEM_ID, s.QTY, s.CNT);
COMMIT;

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

xplan

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                           |                  |      1 |        |      0 |00:00:00.41 |   22.1K |          |
|   1 |  MERGE                                    | SHOP_ITEM_MON    |      1 |        |      0 |00:00:00.41 |   22.1K |          |
|   2 |   VIEW                                    |                  |      1 |        |  17934 |00:00:00.24 |    3911 |          |
|*  3 |    HASH JOIN OUTER                        |                  |      1 |  17934 |  17934 |00:00:00.24 |    3911 | 2905K (0)|
|   4 |     VIEW                                  |                  |      1 |  17934 |  17934 |00:00:00.19 |    3750 |          |
|   5 |      HASH GROUP BY                        |                  |      1 |  18400 |  17934 |00:00:00.19 |    3750 | 2318K (0)|
|*  6 |       HASH JOIN                           |                  |      1 |    106K|    106K|00:00:00.12 |    3750 | 3021K (0)|
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| ORD              |      1 |  42408 |  42408 |00:00:00.02 |    1067 |          |
|*  8 |         INDEX RANGE SCAN                  | ORD_X01          |      1 |  42408 |  42408 |00:00:00.01 |     131 |          |
|   9 |        TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM         |      1 |    106K|    106K|00:00:00.03 |    2683 |          |
|* 10 |         INDEX RANGE SCAN                  | ORD_ITEM_X01     |      1 |    106K|    106K|00:00:00.01 |     381 |          |
|  11 |     TABLE ACCESS BY INDEX ROWID BATCHED   | SHOP_ITEM_MON    |      1 |  17934 |  17760 |00:00:00.02 |     161 |          |
|* 12 |      INDEX RANGE SCAN                     | SHOP_ITEM_MON_PK |      1 |  17934 |  17760 |00:00:00.01 |      64 |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SHOP_NO"(+)="S"."SHOP_NO" AND "T"."ITEM_ID"(+)="S"."ITEM_ID")
   6 - access("I"."ORD_NO"="O"."ORD_NO")
   8 - access("O"."ORD_DT">='20131201' AND "O"."ORD_DT"<='20131231')
  10 - access("I"."ORD_DT">='20131201' AND "I"."ORD_DT"<='20131231')
  12 - access("T"."ORD_YM"(+)='201312')

[세션 통계]  경과 0.42초 · recursive calls 11 · session logical reads 약 22,400 · redo 약 5.1 MB
             UPDATE 17,760행 · INSERT 174행 (FORALL 판과 같은 결과)

모범 답안 서술

FORALL은 PL/SQL이 SQL 엔진에 문장을 넘기는 왕복을 묶을 뿐, 원소마다 하는 일은 그대로다. 그래서 recursive calls는 18,046 → 34로 줄었지만 논리 읽기는 95,412 → 93,806으로 거의 그대로다. SQL 엔진은 넘겨받은 17,934개 원소마다 MERGE … USING (… FROM dual)을 따로 실행한다. 원소 하나마다 SHOP_ITEM_MON_PK를 처음부터 탐색해 대상 행을 찾고(약 3블록), 있으면 UPDATE, 없으면 INSERT한다(약 2블록). 17,934 × 약 5블록 = 약 9만 블록이 MERGE 문장의 일량이고, 이 부분은 루프 판과 같다. 시간이 3.1초 → 2.3초로 조금 준 것은 왕복이 사라진 몫뿐이다. 집계 SELECT를 MERGE의 USING에 그대로 넣어 한 문장으로 바꾼다. 집계 결과 17,934행과 SHOP_ITEM_MON의 12월분을 해시 조인으로 한 번에 맞대고, 맞는 행은 UPDATE, 없는 행은 INSERT한다. 대상은 ON 절에 t.ORD_YM = '201312'를 주어 PK 범위로 12월분만 읽는다(161블록). 원소마다 인덱스를 탐색하던 일이 사라져 논리 읽기는 93,806 → 약 22,400, 경과 시간은 2.3초 → 0.42초로 준다. 결과는 UPDATE 17,760행과 INSERT 174행으로 같다. FORALL은 한 문장으로 바꿀 수 없을 때 쓰는 차선이다. 행마다 PL/SQL 규칙을 거쳐야 하거나 외부 프로시저를 불러야 해서 원천을 SQL로 만들 수 없을 때가 그렇다. 여기는 원천이 이미 SQL 집계이므로, 그 결과를 PL/SQL 배열로 꺼냈다가 다시 넣을 이유가 없다.

해설

함정: "BULK가 빠르다"는 PL/SQL과 SQL 사이의 왕복이 일량일 때만 맞다. 원소마다 행 단위로 찾아가는 문장(MERGE … USING dual, 키로 찾는 UPDATE)은 FORALL로 묶어도 원소마다 같은 일을 한다(교재 15화 §5.3: 원소마다 MERGE는 논리 읽기 426,669, 배열 INSERT는 22,363).
ON 절에 대상의 12월 조건이 없으면 대상 43만 행 전체(약 1,900블록)를 해시 조인에 올린다. 결과는 같지만 읽는 양과 해시 영역이 커진다.
다른 답: 이번 달 집계를 통째로 다시 만드는 것이라면 DELETE … WHERE ORD_YM = '201312' 뒤 INSERT … SELECT … GROUP BY도 답이다. 문장은 단순하지만 지운 행과 넣은 행만큼 인덱스를 두 번 고치므로, 바뀌는 조합이 적은 날에는 MERGE가 싸다.
값이 어제와 같은 조합까지 UPDATE하면 redo가 그만큼 생긴다. WHEN MATCHED THEN UPDATE … WHERE t.SALE_QTY <> s.QTY OR t.LINE_CNT <> s.CNT로 바뀐 조합만 고칠 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황