협력사 포털의 상품별 판매 조회가 상품을 하나 더 고를 때마다 0.11초씩 느려진다

중급PLSQL유통674 / 700

상황

본사 유통의 협력사 포털 「상품별 월 판매」는 협력사 담당자가 자기 상품 가운데 볼 것을 체크하고 월을 고르면 상품마다 판매 수량과 금액을 보여 준다. 포털(Java)은 체크한 상품 번호를 SQL 컬렉션 타입 ITM_NO_TAB 하나로 바인드해 프로시저 PTNR_ITM_SALES를 부른다. 푸드 협력사(1005)는 상품 21종을 모두 체크하는 일이 많은데, 그러면 2.34초가 걸린다. 3종만 고르면 0.34초다. 개발자는 이렇게 말한다. "고르는 상품 수가 매번 달라서 한 문장으로는 못 짜고 상품마다 돌렸어요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SLE_DT VARCHAR2(8) · 2023년 12월 20,864건 · SLE_DT로 시작하는 인덱스는 이 문제에서 없다
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · ITM_NO로 시작하는 인덱스는 없다 · 2023년 12월 46,322줄
협력사 1005의 상품 21종(ITM_NO 1047 ~ 1067): 판매상품 105,736줄(2년 치) · 2023년 12월 4,536줄
ITM_NO_TAB: CREATE TYPE ITM_NO_TAB AS TABLE OF NUMBER로 만든 SQL 타입(스키마에 있다)
TMP_PTNR_SALES: 임시 테이블(GTT) · ITM_NO · QTY · AMT
판매가 없는 상품은 수량과 금액을 0으로 보여 준다(지금 프로시저가 그렇게 한다) · 통계 최신

원본 SQL

sql

PROCEDURE PTNR_ITM_SALES(p_itms IN ITM_NO_TAB, p_ym IN VARCHAR2, p_cur OUT SYS_REFCURSOR) IS
  v_qty NUMBER;
  v_amt NUMBER;
BEGIN
  DELETE FROM TMP_PTNR_SALES;
  FOR i IN 1 .. p_itms.COUNT LOOP                      -- 고른 상품마다
    SELECT NVL(SUM(d.SLE_QTY), 0), NVL(SUM(d.SLE_QTY * d.SLE_UPRC), 0)
      INTO v_qty, v_amt
      FROM CF10 h, CF11 d
     WHERE h.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
       AND d.SLE_NO = h.SLE_NO
       AND d.ITM_NO = p_itms(i);
    INSERT INTO TMP_PTNR_SALES (ITM_NO, QTY, AMT) VALUES (p_itms(i), v_qty, v_amt);
  END LOOP;
  OPEN p_cur FOR SELECT ITM_NO, QTY, AMT FROM TMP_PTNR_SALES ORDER BY ITM_NO;
END;

실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈

xplan

[포털 로그: 고른 상품 수별 경과 (2023년 12월)]   3종 0.34초 · 7종 0.78초 · 21종 2.34초

[V$SQL: 21종을 고른 호출 한 번]
  SQL_TEXT                                                                   EXECUTIONS  BUFFER_GETS  ELAPSED
  SELECT NVL(SUM(D.SLE_QTY), 0), NVL(SUM(D.SLE_QTY * D.SLE_UPRC), 0) FROM …          21      268,527   2.31 초
  INSERT INTO TMP_PTNR_SALES (ITM_NO, QTY, AMT) VALUES (:B3 , :B2 , :B1 )            21           24   0.00 초
  DELETE FROM TMP_PTNR_SALES                                                          1            3   0.00 초
  SELECT ITM_NO, QTY, AMT FROM TMP_PTNR_SALES ORDER BY ITM_NO                         1            3   0.00 초

[루프 안 SELECT 한 번: ITM_NO = 1067 (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.11 |   12787 |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.11 |   12787 |          |
|*  2 |   HASH JOIN         |      |      1 |    519 |    272 |00:00:00.11 |   12787 | 1557K (0)|
|*  3 |    TABLE ACCESS FULL| CF11 |      1 |  12463 |   6247 |00:00:00.06 |    8204 |          |
|*  4 |    TABLE ACCESS FULL| CF10 |      1 |  20833 |  20864 |00:00:00.03 |    4583 |          |
--------------------------------------------------------------------------------------------------

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

문제

고르는 상품이 많을수록 느려지는 이유를 V$SQL과 실행계획의 숫자로 설명하시오.
고른 상품 수와 상관없이 판매와 판매상품을 한 번씩만 읽도록 프로시저를 고치시오. 판매가 없는 상품도 지금처럼 0으로 나오게 하시오.
고친 문장의 실행계획에서 컬렉션 줄의 E-Rows가 무엇을 뜻하는지, 어떤 문장에서 문제가 되고 그때 어떻게 다루는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE PTNR_ITM_SALES(p_itms IN ITM_NO_TAB, p_ym IN VARCHAR2, p_cur OUT SYS_REFCURSOR) IS
BEGIN
  -- 루프와 임시 테이블을 없앤다: 컬렉션을 SQL 안에서 펼쳐 판매와 판매상품을 한 번씩만 읽는다
  OPEN p_cur FOR
    SELECT t.COLUMN_VALUE         AS ITM_NO,
           NVL(g.qty, 0)          AS QTY,          -- 판매가 없는 상품은 지금처럼 0
           NVL(g.amt, 0)          AS AMT
      FROM TABLE(p_itms) t,
           (SELECT d.ITM_NO,
                   SUM(d.SLE_QTY)              AS qty,
                   SUM(d.SLE_QTY * d.SLE_UPRC) AS amt
              FROM CF10 h, CF11 d
             WHERE h.SLE_DT BETWEEN p_ym || '01' AND p_ym || '31'
               AND d.SLE_NO = h.SLE_NO
               AND d.ITM_NO IN (SELECT c.COLUMN_VALUE FROM TABLE(p_itms) c)
             GROUP BY d.ITM_NO) g
     WHERE g.ITM_NO(+) = t.COLUMN_VALUE                 -- 컬렉션 쪽을 기준으로 (상품을 잃지 않는다)
     ORDER BY t.COLUMN_VALUE;
END;

개선 후 실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈

xplan

[고친 판 · 21종 · 2023년 12월 (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.16 |   12787 |          |
|   1 |  SORT ORDER BY                          |      |      1 |   8168 |     21 |00:00:00.16 |   12787 | 2048  (0)|
|*  2 |   HASH JOIN RIGHT OUTER                 |      |      1 |   8168 |     21 |00:00:00.16 |   12787 | 1290K (0)|
|   3 |    VIEW                                 |      |      1 |     88 |     21 |00:00:00.16 |   12787 |          |
|   4 |     HASH GROUP BY                       |      |      1 |     88 |     21 |00:00:00.16 |   12787 | 1046K (0)|
|*  5 |      HASH JOIN                          |      |      1 |  45696 |   4536 |00:00:00.15 |   12787 | 2286K (0)|
|*  6 |       TABLE ACCESS FULL                 | CF10 |      1 |  20833 |  20864 |00:00:00.03 |    4583 |          |
|*  7 |       HASH JOIN RIGHT SEMI              |      |      1 |   1096K|    105K|00:00:00.10 |    8204 | 1254K (0)|
|   8 |        COLLECTION ITERATOR PICKLER FETCH|      |      1 |   8168 |     21 |00:00:00.01 |       0 |          |
|   9 |        TABLE ACCESS FULL                | CF11 |      1 |   1096K|   1096K|00:00:00.05 |    8204 |          |
|  10 |    COLLECTION ITERATOR PICKLER FETCH    |      |      1 |   8168 |     21 |00:00:00.01 |       0 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("G"."ITM_NO"=VALUE(KOKBF$))
   5 - access("D"."SLE_NO"="H"."SLE_NO")
   6 - filter(("H"."SLE_DT">=:B1||'01' AND "H"."SLE_DT"<=:B2||'31'))
   7 - access("D"."ITM_NO"=VALUE(KOKBF$))

[포털 로그]  3종 0.15초 · 7종 0.15초 · 21종 0.16초 · SQL 실행은 호출마다 1번
[결과]  21행 · 수량 11,284 · 금액 32,113,500 · 루프 판과 MINUS 양방향 0행

모범 답안 서술

V$SQL에서 루프 안 SELECT의 Executions는 21로, 고른 상품 수와 같다. 상품마다 SELECT가 한 번씩 돈다. 그런데 한 번의 계획을 보면 ITM_NO로 시작하는 인덱스도, SLE_DT로 시작하는 인덱스도 없어서 3번 줄과 4번 줄에서 판매상품(8,204블록)과 판매(4,583블록)를 통째로 읽고 해시 조인한다. 한 번에 12,787블록, 0.11초다. 상품 하나의 272줄을 얻으려고 두 테이블을 다 읽는 일을 21번 되풀이하니 12,787블록 × 21번 = 268,527블록, 2.31초다. 상품을 하나 더 고를 때마다 0.11초씩 늘어나는 까닭이다. 개발자는 상품 수가 매번 달라서 한 문장으로 못 짠다고 했지만, 포털은 이미 상품 번호를 SQL 타입 ITM_NO_TAB 하나로 넘긴다. SQL 타입이므로 SQL 안에서 TABLE(p_itms)로 테이블처럼 펼칠 수 있고, 개수와 상관없이 문장은 하나다. 판매상품을 한 번 읽으면서 ITM_NO IN (SELECT … FROM TABLE(p_itms))로 고른 상품만 남기고, 판매와 한 번 해시 조인한 뒤 상품별로 묶는다. Buffers는 268,527 → 12,787, 21종의 경과는 2.34초 → 0.16초가 되고, 3종이든 21종이든 같은 시간이 걸린다. 루프가 없으니 임시 테이블도 필요 없다. 판매가 없는 상품은 조인하면 사라진다. 지금 판은 그런 상품에도 0을 넣으므로, 컬렉션 쪽을 기준으로 상품별 합계를 아우터 조인(g.ITM_NO(+) = t.COLUMN_VALUE)하고 NVL로 0을 채운다. 결과는 루프 판과 MINUS 양방향 0행이다. 8번 줄과 10번 줄의 E-Rows 8,168은 실제 건수가 아니다. 컬렉션에는 통계가 없어서 옵티마이저는 블록 크기 8KB일 때의 기본값 8,168행으로 본다. 이 문장은 어차피 두 테이블을 한 번씩 읽고 컬렉션은 거르는 데만 쓰므로 계획이 흔들리지 않는다. 그래서 힌트를 달지 않는다. 문제가 되는 것은 컬렉션이 드라이빙이 되어 키로 찾아가는 문장이다(예: 영수증 번호 20개로 CF10_PK를 찾는 문장). 옵티마이저가 8,168번 찾아가는 것보다 통째로 읽는 것이 싸다고 보고 해시 조인과 풀 스캔을 고른다. 그런 문장에는 CARDINALITY(t 20)이나 DYNAMIC_SAMPLING(t 2)로 흔한 건수를 알려 주고, 그 전제를 힌트 대장에 적는다. d.ITM_NO MEMBER OF p_itms로 써도 문장은 하나가 된다. 다만 MEMBER OF는 조인이 아니라 행마다 컬렉션을 뒤지는 filter라서 인덱스로 찾아가는 데 쓰지 못하고, 옵티마이저가 건수를 줄여 보지도 않는다. 이 문장은 판매상품을 어차피 통째로 읽으니 답은 되지만, 키로 찾아가야 하는 문장에서는 테이블 전체를 읽게 된다.

해설

함정: BULK COLLECT나 FORALL로 바꿔도 이 문제는 풀리지 않는다. 비싼 것은 SQL을 21번 부르는 왕복이 아니라 한 번마다 두 테이블을 통째로 읽는 일이다. 줄일 것은 반복되는 일 그 자체다(교재 15화 §5.3, FORALL은 왕복을 묶을 뿐 일을 묶지 않는다).
다른 정답과 그 대가: 고른 상품을 IN (1047, 1048, …)으로 이어 붙인 동적 SQL도 한 번만 읽는다. 하지만 상품 수와 값마다 다른 문장이 생겨 하드 파싱이 늘어난다(교재 11화 §12). 컬렉션 바인드는 문장이 하나뿐이다.
컬렉션을 SQL 안에서 펼치려면 SQL이 아는 타입이어야 한다. ITM_NO_TAB처럼 CREATE TYPE으로 만든 타입은 Java에서 배열로 바인드할 수도 있다. 프로시저 안에서만 쓰던 INDEX BY 배열이었다면 먼저 이런 타입으로 옮긴다.
같은 상품이 컬렉션에 두 번 들어와도 결과가 바뀌지 않는다. 안쪽 IN 서브쿼리는 한 번만 거르고, 바깥 컬렉션은 루프 판처럼 두 줄을 낸다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황