월 마감의 매장별 주문 집계만 다른 집계보다 열 배 오래 걸린다

기초INSERT버거657 / 700

상황

버거 본사의 월 마감 배치는 상품별, 일자별, 매장별 주문 집계를 차례로 적재한다. 다른 집계는 0.05 ~ 0.07초인데 「매장별 주문 수」만 0.6초가 넘는다. 셋 다 같은 한 달 치 주문을 ORD_X01 범위로 읽는다. 매장별 집계는 매장명으로 묶어 넣는다. 매장명은 POS 공통 함수 FN_SHOP_NM이 붙인다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 2013년 12월 41,712건 · ORD_X01 (ORD_DT, ORD_HMS) 리프 3,774블록 · CF 22,223
SHOP 매장: 200행 · 5블록 · PK(SHOP_NO) · SH0070과 SH0089는 둘 다 '여의도점'이다
T_SHOP_MON_ORD 월 매장 집계(이 배치가 채운다): ORD_YM · SHOP_NM · ORD_CNT · 인덱스 없음 · 지난달 적재 결과는 199행
통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_SHOP_NM(p_shop_no VARCHAR2) RETURN VARCHAR2 IS
  v_nm SHOP.SHOP_NM%TYPE;
BEGIN
  SELECT SHOP_NM INTO v_nm FROM SHOP WHERE SHOP_NO = p_shop_no;
  RETURN v_nm;
END;
/

INSERT INTO T_SHOP_MON_ORD (ORD_YM, SHOP_NM, ORD_CNT)
SELECT '201312', FN_SHOP_NM(SHOP_NO), COUNT(*)
  FROM ORD
 WHERE ORD_DT BETWEEN '20131201' AND '20131231'
 GROUP BY FN_SHOP_NM(SHOP_NO);

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST)]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |                |      1 |        |      0 |00:00:00.63 |    1093 |
|   1 |  LOAD TABLE CONVENTIONAL              | T_SHOP_MON_ORD |      1 |        |      0 |00:00:00.63 |    1093 |
|   2 |   HASH GROUP BY                       |                |      1 |  41667 |    199 |00:00:00.63 |    1086 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD            |      1 |  41667 |  41712 |00:00:00.02 |    1086 |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01        |      1 |  41667 |  41712 |00:00:00.01 |     159 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("ORD_DT">='20131201' AND "ORD_DT"<='20131231')

[세션 통계: 이 INSERT 한 번]
  경과 시간                 0.63 초
  recursive calls           41,734
  session logical reads     84,530

[V$SQL: 함수 안의 문장 (dep=1)]
  SELECT SHOP_NM FROM SHOP WHERE SHOP_NO = :B1    EXECUTIONS 41,712 · BUFFER_GETS 83,424

문제

같은 범위를 읽는 다른 집계보다 열 배 느린 이유를 함수가 호출된 횟수로 설명하시오.
함수가 매장 수만큼만 호출되도록 SQL을 고치시오. 고친 SQL의 결과가 원래 SQL과 같은지 확인하고, 다르다면 어떻게 할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장 번호로 먼저 묶는다 (41,712행 → 200행) · 이름은 그 뒤에 붙인다 (함수 200번)
-- 결과를 원래와 같게 두려면 이름으로 한 번 더 묶는다 (여의도점 두 곳이 한 줄 → 199행)
INSERT INTO T_SHOP_MON_ORD (ORD_YM, SHOP_NM, ORD_CNT)
SELECT '201312', x.SHOP_NM, SUM(x.ORD_CNT)
  FROM (SELECT FN_SHOP_NM(o.SHOP_NO) AS SHOP_NM, o.ORD_CNT
          FROM (SELECT SHOP_NO, COUNT(*) AS ORD_CNT
                  FROM ORD
                 WHERE ORD_DT BETWEEN '20131201' AND '20131231'
                 GROUP BY SHOP_NO) o) x
 GROUP BY x.SHOP_NM;

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

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                        |                |      1 |        |      0 |00:00:00.06 |    1093 |
|   1 |  LOAD TABLE CONVENTIONAL                | T_SHOP_MON_ORD |      1 |        |      0 |00:00:00.06 |    1093 |
|   2 |   HASH GROUP BY                         |                |      1 |    200 |    199 |00:00:00.06 |    1086 |
|   3 |    VIEW                                 |                |      1 |    200 |    200 |00:00:00.05 |    1086 |
|   4 |     HASH GROUP BY                       |                |      1 |    200 |    200 |00:00:00.05 |    1086 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD            |      1 |  41667 |  41712 |00:00:00.02 |    1086 |
|*  6 |       INDEX RANGE SCAN                  | ORD_X01        |      1 |  41667 |  41712 |00:00:00.01 |     159 |
--------------------------------------------------------------------------------------------------------------------
   6 - access("ORD_DT">='20131201' AND "ORD_DT"<='20131231')

[세션 통계]  경과 0.06 초 · recursive calls 222 · session logical reads 1,506 (함수 안 SQL 200번 × 2블록 = 400 포함)

모범 답안 서술

GROUP BY FN_SHOP_NM(SHOP_NO)는 묶을 열쇠를 함수로 만든다. 열쇠는 묶기 전에 행마다 계산해야 하므로, 함수가 주문 41,712건마다 호출됐고 그때마다 함수 안의 SELECT SHOP_NM FROM SHOP …가 재귀 SQL로 실행됐다(recursive calls 41,734, 함수 안 SQL 83,424블록). 같은 범위를 읽는 다른 집계(1,093블록)보다 열 배 느린 것은 이 4만 번의 왕복 때문이다. 이 일은 계획의 Buffers에는 보이지 않는다. 매장 번호로 먼저 묶으면 41,712행이 200행으로 줄고, 매장명은 그 200행에 붙이면 된다. 함수는 200번만 호출되어 recursive calls 41,734 → 222, 경과 0.63 → 0.06초가 된다. 다만 SHOP에는 이름이 같은 매장이 있다. SH0070과 SH0089가 둘 다 여의도점이라, 원래 SQL은 두 매장의 주문을 한 줄로 합쳐 199행을 넣었다. 매장 번호로만 묶으면 200행이 되어 결과가 달라진다. 결과를 그대로 두려면 위처럼 매장명으로 한 번 더 묶는다(199행). 그러나 두 매장의 주문 수가 한 줄에 합쳐지는 것은 원래 SQL의 숨은 버그일 가능성이 크다. 집계를 쓰는 쪽에 알리고, 집계 테이블에 SHOP_NO를 넣어 200행으로 바꾸는 안을 함께 올린다.

해설

함수의 자리가 호출 횟수를 정한다. 같은 함수라도 GROUP BY의 열쇠로 쓰면 행마다, 묶은 결과의 SELECT 목록에서 쓰면 그룹마다 호출된다. SELECT FN_SHOP_NM(SHOP_NO), COUNT(*) … GROUP BY SHOP_NO만으로도 200번이 된다.
조인도 답이다. SHOP과 조인해 매장명으로 묶으면 함수 호출이 0번이고 SHOP 5블록만 더 읽는다. 이때도 여의도점 두 곳은 한 줄로 합쳐지므로 같은 확인이 필요하다.
매장 번호로 묶은 뒤 이름을 붙이는 모양은 이름이 같은 두 매장을 따로 보여 줄 수 있는 모양이기도 하다. 성능을 고치다가 숨은 버그를 찾으면, 고치기 전에 결과를 쓰는 사람과 먼저 정한다(교재 08화 정합성 회귀 검증).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황