판매가 점검 리포트만 6초, 같은 가격 함수를 쓰는 다른 두 화면은 멀쩡하다

실전SELECT유통665 / 700

상황

본사 유통지원팀의 현재 판매가 함수 FN_CUR_UPRC는 세 SQL이 쓴다. POS 「상품 상세」(하루 3,000번), 매장 「가격표 출력」(하루 10번), 회계팀의 「연간 판매가 점검」(한 달에 몇 번)이다. 앞의 둘은 빠른데 점검 리포트만 6초가 걸린다. 개발 담당자는 함수에 DETERMINISTIC을 붙여 세 SQL을 한꺼번에 빠르게 하자고 제안했다. 유통지원팀장은 세 SQL을 하나씩 보고 고칠 것과 그대로 둘 것을 정해 달라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 2023년 249,971건 · CF10_PK (SLE_NO)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 2023년 판매분 548,401행 · 그해 팔린 상품 88종
CF05 상품가격: 157행 · 5블록 · PK (ITM_NO, UPRC_KN_CD, APL_DT) · 판매가는 UPRC_KN_CD = '10' · 가격 개편 때 새 적용일자 행이 들어간다
CF01 상품: 109행 · 5블록 · 판매 중(SLE_YN = 'Y') 88종 · CF07 매장: 6행 · 4블록(매장 5곳과 본사)
판매된 상품은 모두 판매가가 있다 · 통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_CUR_UPRC(p_itm_no NUMBER) RETURN NUMBER IS
  v_uprc CF05.ITM_UPRC%TYPE;
BEGIN
  SELECT ITM_UPRC INTO v_uprc
    FROM CF05
   WHERE ITM_NO = p_itm_no
     AND UPRC_KN_CD = '10'
     AND APL_DT = (SELECT MAX(APL_DT) FROM CF05
                    WHERE ITM_NO = p_itm_no AND UPRC_KN_CD = '10');
  RETURN v_uprc;
END;
/

-- [Q1] POS 상품 상세 (하루 3,000번 · 한 번에 1행)
SELECT i.ITM_NO, i.ITM_NM, FN_CUR_UPRC(i.ITM_NO) AS cur_uprc
  FROM CF01 i
 WHERE i.ITM_NO = :itm_no;

-- [Q2] 매장 가격표 출력 (하루 10번 · 매장 5곳 × 판매 상품 88종 = 440행)
SELECT s.SHP_NM, i.ITM_NO, i.ITM_NM, FN_CUR_UPRC(i.ITM_NO) AS cur_uprc
  FROM CF07 s, CF01 i
 WHERE s.SHP_NO BETWEEN 1001 AND 1005
   AND i.SLE_YN = 'Y'
 ORDER BY s.SHP_NO, i.ITM_NO;

-- [Q3] 연간 판매가 점검 (상품마다 실제 판매액과 현재가로 친 금액)
SELECT d.ITM_NO,
       SUM(d.SLE_QTY)                          AS qty,
       SUM(d.SLE_QTY * d.SLE_UPRC)             AS sle_amt,
       SUM(d.SLE_QTY * FN_CUR_UPRC(d.ITM_NO))  AS cur_amt
  FROM CF10 h, CF11 d
 WHERE h.SLE_DT BETWEEN '20230101' AND '20231231'
   AND d.SLE_NO = h.SLE_NO
 GROUP BY d.ITM_NO
 ORDER BY d.ITM_NO;

실행계획 학습용 예시

xplan

[Q3: DISPLAY_CURSOR (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     88 |00:00:06.12 |   12787 |          |
|   1 |  SORT GROUP BY      |      |      1 |     88 |     88 |00:00:06.12 |   12787 |18432  (0)|
|*  2 |   HASH JOIN         |      |      1 |    548K|    548K|00:00:00.31 |   12787 |   17M (0)|
|*  3 |    TABLE ACCESS FULL| CF10 |      1 |    250K|    249K|00:00:00.03 |    4583 |          |
|   4 |    TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.07 |    8204 |          |
---------------------------------------------------------------------------------------------------
   2 - access("D"."SLE_NO"="H"."SLE_NO")
   3 - filter(("H"."SLE_DT">='20230101' AND "H"."SLE_DT"<='20231231'))

[함수 안 문장 (dep=1) · 세 SQL을 한 번씩]
          함수 호출(= 함수 안 SQL 실행)   함수 안 SQL 블록   SQL 전체 경과
  [Q1]                              1                  3          1 ms
  [Q2]                            440              1,320         12 ms
  [Q3]                        548,401          1,645,203        6.12 초

문제

세 SQL에서 함수가 각각 몇 번 호출되는지 쓰고, 점검 리포트의 6초가 어디서 나오는지 계획과 함께 설명하시오.
SQL마다 고칠지 그대로 둘지 정하시오. 고칠 SQL은 결과가 바뀌지 않게 다시 작성하시오.
함수에 DETERMINISTIC을 붙여 세 SQL을 한꺼번에 해결하자는 제안을 평가하시오.

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

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

모범답안

모범 답안 SQL

sql

-- [Q3] 현재가는 상품마다 하나 → 상품으로 묶은 뒤에 곱한다 (함수 548,401번 → 88번)
SELECT g.ITM_NO, g.qty, g.sle_amt,
       g.qty * FN_CUR_UPRC(g.ITM_NO) AS cur_amt
  FROM (SELECT d.ITM_NO,
               SUM(d.SLE_QTY)              AS qty,
               SUM(d.SLE_QTY * d.SLE_UPRC) AS sle_amt
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN '20230101' AND '20231231'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO) g
 ORDER BY g.ITM_NO;

-- [Q1] 상품 상세: 그대로 (함수 1번)
-- [Q2] 가격표: 그대로 (함수 440번 · 하루 10번)
-- 함수: DETERMINISTIC 을 붙이지 않는다 (CF05 를 읽는다)

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

xplan

[Q3 개선 뒤]
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     88 |00:00:00.36 |   12787 |          |
|   1 |  SORT GROUP BY      |      |      1 |     88 |     88 |00:00:00.36 |   12787 |18432  (0)|
|*  2 |   HASH JOIN         |      |      1 |    548K|    548K|00:00:00.30 |   12787 |   17M (0)|
|*  3 |    TABLE ACCESS FULL| CF10 |      1 |    250K|    249K|00:00:00.03 |    4583 |          |
|   4 |    TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.07 |    8204 |          |
---------------------------------------------------------------------------------------------------
   2 - access("D"."SLE_NO"="H"."SLE_NO")
   3 - filter(("H"."SLE_DT">='20230101' AND "H"."SLE_DT"<='20231231'))

[함수 안 문장]  호출 88 · 블록 264 · 결과 88행은 원래 리포트와 MINUS 양방향 0행

모범 답안 서술

함수가 호출되는 횟수는 SQL마다 다르다. [Q1]은 상품 하나라 1번, [Q2]는 매장 5곳 × 판매 상품 88종이라 440번이다. [Q3]은 함수가 SUM 안에 있어서 조인된 판매상품 548,401행마다 호출된다. 함수 안 SQL은 한 번에 CF05 3블록을 읽으므로 1,645,203블록이고, 함수로 건너가는 일도 55만 번이다. 계획의 Buffers 12,787(판매 4,583 + 판매상품 8,204)에는 이 일이 보이지 않는다. 해시 조인까지는 0.31초에 끝나고, SORT GROUP BY 줄의 6.12초 대부분이 함수 호출이다. [Q3]만 고친다. 현재가는 상품 번호만으로 정해지고 리포트도 상품 번호로 묶으므로, 상품마다 현재가는 하나다. 판매상품 행마다 수량 × 현재가를 더하는 대신, 상품으로 먼저 묶어 수량 합계를 구한 뒤 상품마다 한 번 현재가를 곱해도 결과가 같다(곱셈을 합계 밖으로 꺼낸 것이고 반올림도 없다). 함수는 548,401 → 88번, 함수 안 SQL은 1,645,203 → 264블록, 경과는 6.12 → 0.36초가 된다. 판매와 판매상품을 읽는 12,787블록은 그대로다. 함수를 그대로 쓰므로 현재가 규칙도 함수 한 곳에 남는다. [Q1]과 [Q2]는 그대로 둔다. [Q1]은 한 번 부르는 자리라 함수가 문제가 아니고, [Q2]는 440번에 1,320블록, 하루 10번이다. 조인으로 바꾸면 현재가를 고르는 규칙(가장 늦은 적용일자)을 SQL에 다시 써야 해서 규칙이 두 곳이 되는데, 아낄 것은 하루 1만 블록 남짓이다. DETERMINISTIC 제안은 받지 않는다. 함수는 CF05를 읽고, 가격 개편 때 새 적용일자 행이 들어오면 같은 상품의 답이 바뀌므로 '같은 입력이면 같은 답'이라는 약속은 거짓이다. 효과도 한 번의 페치 안에서만 난다. [Q1]은 한 번 부르니 줄 것이 없다. [Q3]도 집계 한 번의 페치 안에서 줄기는 하지만, 칸에 들지 못한 상품은 나올 때마다 다시 부르고 행마다 함수로 건너가는 일도 남는다. 거짓 약속은 나중에 이 함수로 함수 기반 인덱스나 결과 캐시를 만들 때 틀린 답으로 돌아온다.

해설

같은 함수, 다른 처방. 함수가 느린지가 아니라 몇 번 부르는지를 SQL마다 센다. 한 번 부르는 상세 화면의 함수를 조인으로 바꾸는 것은 일만 늘린다.
[Q3]을 CF05와 조인해도 답이 된다. 상품마다 가장 늦은 적용일자의 판매가를 구한 인라인 뷰(MAX(ITM_UPRC) KEEP (DENSE_RANK LAST ORDER BY APL_DT))를 상품별 합계에 붙인다. 함수 호출은 0번이 되지만 현재가 규칙이 SQL에도 생긴다.
묶은 뒤에 곱하는 판은 바깥 쿼리가 묶음 결과 위에서 함수를 부르기 때문에 성립한다. 함수를 다시 SUM 안으로 넣거나 상품 번호가 아닌 컬럼으로 묶으면 행마다 호출로 돌아간다. 회귀 시험에서 함수 안 SQL 실행 수(88)를 함께 본다.
가격표의 함수를 스칼라 서브쿼리로 감싸면 440 → 88번 남짓으로 준다. 고칠 이유가 생기면 그것이 가장 작은 수정이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황