연간 재료 소요 분석에서 92행짜리 상품 테이블 조회가 123만 번 실행된다

기초PLSQL버거667 / 700

상황

버거 본사 구매팀은 연초마다 「연간 재료 소요 분석」을 돌려 매장 발주 기준을 정한다. 세트 메뉴는 버거, 사이드, 음료로 나눠 세야 해서 개발팀이 PL/SQL로 짰다. 나누는 규칙은 메뉴팀이 관리하는 패키지 PKG_SET_BOM에 있다. 2013년분을 돌리자 5.9초가 걸렸다. 구매팀 담당자가 물었다. "상품이 92개뿐인데 왜 6초나 걸리죠?" 조민우가 TKPROF를 떠 왔다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · 2013년분 1,236,358행 · ORD_DT VARCHAR2(8) · 인덱스 ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
ITEM 상품: 92행 · 5블록 · ITEM_PK (ITEM_ID) · ITEM_TYPE_CD는 100100 버거 · 100101 세트(17종) · 100102 치킨 · 100103 사이드 · 100104 디저트 · 100105 음료
주문상품의 ITEM_ID는 모두 ITEM에 있다(FK는 없지만 적재 프로그램이 확인한다). 통계는 최신이다.
PKG_SET_BOM.ADD_QTY(item_id, type_cd, qty): SQL 없이 패키지 배열에 수량을 더하기만 한다(세트면 구성품마다 같은 수량을 더한다). SAVE는 상품별 소요량 92행을 넣는다.

원본 SQL

sql

PROCEDURE CALC_ITEM_NEED(p_yyyy VARCHAR2) IS          -- p_yyyy = '2013'
  v_tp  ITEM.ITEM_TYPE_CD%TYPE;
BEGIN
  PKG_SET_BOM.RESET;
  FOR r IN (SELECT ITEM_ID, ORD_ITEM_QTY
              FROM ORD_ITEM
             WHERE ORD_DT BETWEEN p_yyyy || '0101' AND p_yyyy || '1231') LOOP
    SELECT ITEM_TYPE_CD INTO v_tp
      FROM ITEM
     WHERE ITEM_ID = r.ITEM_ID;                           -- 주문상품 한 줄마다
    PKG_SET_BOM.ADD_QTY(r.ITEM_ID, v_tp, r.ORD_ITEM_QTY);  -- 세트면 구성품으로 나눠 더한다
  END LOOP;
  PKG_SET_BOM.SAVE(p_yyyy);
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — CALC_ITEM_NEED('2013') 한 번]
  경과 시간                  5.86 ~ 5.94 초
  recursive calls            1,248,745
  execute count              1,236,381
  session logical reads      2,540,438

[TKPROF — 문장별 (sys=no)]
SELECT ITEM_ID, ORD_ITEM_QTY FROM ORD_ITEM WHERE ORD_DT BETWEEN :B2 || '0101' AND :B1 || '1231'

call     count       cpu    elapsed       disk      query    current        rows
------- -------  -------- ---------- ---------- ---------- ----------  ----------
Parse         1      0.00       0.00          0          0          0           0
Execute       1      0.00       0.00          0          0          0           0
Fetch     12364      0.58       0.61          0      67629          0     1236358

Rows (1st)  Row Source Operation
   1236358  TABLE ACCESS FULL ORD_ITEM (cr=67629 pr=0 pw=0)

SELECT ITEM_TYPE_CD FROM ITEM WHERE ITEM_ID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- -------  -------- ---------- ---------- ---------- ----------  ----------
Parse         1      0.00       0.00          0          0          0           0
Execute 1236358      2.94       3.02          0          0          0           0
Fetch   1236358      1.71       1.76          0    2472716          0     1236358

Rows (1st)  Row Source Operation
         1  TABLE ACCESS BY INDEX ROWID ITEM (cr=2 pr=0 pw=0)
         1   INDEX UNIQUE SCAN ITEM_PK (cr=1 pr=0 pw=0)

문제

5.9초가 어디서 나오는지 TKPROF의 두 문장으로 설명하시오. 구동 커서는 100건씩 묶여 있는데 상품 조회는 왜 묶이지 않았는지도 쓰시오.
소요량 결과가 바뀌지 않게 프로시저를 고치고, 고친 뒤의 일량을 숫자로 적으시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE CALC_ITEM_NEED(p_yyyy VARCHAR2) IS
BEGIN
  PKG_SET_BOM.RESET;
  -- 상품 구분은 구동 커서의 조인으로 한 번에 · 수량은 상품별로 먼저 묶는다 (123만 줄 → 92줄)
  FOR r IN (SELECT oi.ITEM_ID, i.ITEM_TYPE_CD, SUM(oi.ORD_ITEM_QTY) AS qty
              FROM ORD_ITEM oi, ITEM i
             WHERE oi.ORD_DT BETWEEN p_yyyy || '0101' AND p_yyyy || '1231'
               AND i.ITEM_ID = oi.ITEM_ID
             GROUP BY oi.ITEM_ID, i.ITEM_TYPE_CD) LOOP
    PKG_SET_BOM.ADD_QTY(r.ITEM_ID, r.ITEM_TYPE_CD, r.qty);   -- 더하기만 하므로 묶은 수량을 한 번에 더해도 같다
  END LOOP;
  PKG_SET_BOM.SAVE(p_yyyy);
  COMMIT;
END;

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

xplan

[구동 커서 — DISPLAY_CURSOR (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |        |     92 |00:00:00.61 |   55270 |          |
|   1 |  HASH GROUP BY       |          |      1 |     92 |     92 |00:00:00.61 |   55270 | 1180K (0)|
|*  2 |   HASH JOIN          |          |      1 |   1236K|   1236K|00:00:00.52 |   55270 | 1264K (0)|
|   3 |    TABLE ACCESS FULL | ITEM     |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|*  4 |    TABLE ACCESS FULL | ORD_ITEM |      1 |   1236K|   1236K|00:00:00.27 |   55265 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITEM_ID"="OI"."ITEM_ID")
   4 - filter(("OI"."ORD_DT">=:B1||'0101' AND "OI"."ORD_DT"<=:B2||'1231'))

[세션 통계]  경과 0.64초 · recursive calls 14 · session logical reads 55,312 · ADD_QTY 호출 92번
             (상품별 소요량 92행이 원래 판과 같다: MINUS 0 / 0)

모범 답안 서술

TKPROF에서 SELECT ITEM_TYPE_CD FROM ITEM WHERE ITEM_ID = :B1의 Execute와 Fetch가 둘 다 1,236,358로, 구동 커서의 rows와 같다. 주문상품 한 줄마다 상품을 한 번씩 조회했다는 뜻이다. 한 번은 인덱스 1블록과 테이블 1블록, 합쳐 2블록이라 가볍지만 123만 번 곱해지면 2,472,716블록이 되고, Execute와 Fetch의 elapsed만 4.8초다. 한 줄마다 PL/SQL에서 SQL로 건너가는 비용도 붙는다(recursive calls 1,248,745). 구동 커서는 PLSQL_OPTIMIZE_LEVEL 2의 자동 배열 페치로 100건씩 가져왔지만(Fetch 12,364), 자동으로 묶이는 것은 구동 커서뿐이고 루프 안의 SELECT INTO는 레벨과 상관없이 행마다 나간다. 상품 구분은 구동 커서에서 ITEM과 조인해 한 번에 가져온다. 92행짜리 ITEM을 해시 조인의 빌드로 한 번 읽으면 된다. 더 나아가 ADD_QTY는 수량을 더하기만 하므로 상품별로 먼저 묶어도 결과가 같다. 구동 커서에서 GROUP BY로 123만 줄을 92줄로 줄이면 PL/SQL 호출도 123만 번에서 92번이 된다. 경과는 5.9초 → 0.64초, recursive calls는 1,248,745 → 14, 논리 읽기는 2,540,438 → 55,312로 준다. 모든 ITEM_ID가 ITEM에 있으므로 조인으로 빠지는 줄이 없고, ITEM_ID가 PK라 늘어나는 줄도 없다. 그래서 소요량 92행이 원래 판과 같다.

해설

다른 정답: ITEM 92행을 루프 전에 BULK COLLECT로 읽어 INDEX BY PLS_INTEGER 배열에 넣고, 루프 안에서는 배열에서 찾는 방법도 있다. SQL은 한 번으로 주지만 ADD_QTY 123만 번은 남는다(약 1.2초).
함정: ITEM에 없는 ITEM_ID가 있으면 원래 판은 NO_DATA_FOUND로 멈추고, 조인 판은 그 줄을 조용히 빼 버린다. 적재 프로그램이 확인해 주지 않는 데이터라면 아우터 조인으로 받고 ITEM_TYPE_CD IS NULL인 줄이 있으면 멈추게 한다.
구동 커서의 Fetch 12,364는 레벨 2의 자동 배열 페치 덕분이다. 같은 프로시저를 레벨 1로 컴파일하면 Fetch가 1,236,359로 늘어난다(교재 15화 §2.1).
묶을 수 있는지는 PL/SQL 쪽 처리의 성질이 정한다. 더하기처럼 순서와 상관없이 합칠 수 있으면 SQL에서 먼저 묶고, 행마다 판단이 달라지는 규칙이면 묶지 말고 조인까지만 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황