영수증 목록은 4,176줄인데 합계 함수는 12,528번 돌았다

중급SELECT유통659 / 700

상황

매장 관리자 화면 「월간 영수증 목록」은 영수증마다 합계, 부가세, 사은품 대상 여부를 보여 준다. 명동점 12월 목록이 0.4초쯤 걸려 다른 화면보다 눈에 띄게 늦다. SQL을 짠 개발 담당자는 "합계 함수는 인라인 뷰에서 한 번만 부르게 짰고, 바깥에서는 그 컬럼을 쓰기만 한다"라고 한다. 그런데 DBA가 본 함수 안 SQL의 실행 횟수는 화면 행 수의 정확히 세 배였다.

테이블과 인덱스

CF10 판매(영수증): 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 2023년 12월 20,864건 · 그중 명동점(SHP_NO 1003) 4,176건
인덱스 IX_CF10_SLE_DT (SLE_DT): 이 문제에서는 있다
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · 영수증 한 장에 평균 2.2행
영수증에는 판매상품이 한 줄 이상 있다 · 통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_SLE_AMT(p_sle_no VARCHAR2) RETURN NUMBER IS
  v_amt NUMBER;
BEGIN
  SELECT SUM(SLE_QTY * SLE_UPRC) INTO v_amt
    FROM CF11
   WHERE SLE_NO = p_sle_no;
  RETURN v_amt;
END;
/

SELECT v.SLE_NO, v.SLE_DT, v.SLE_HMS,
       v.amt,
       ROUND(v.amt / 11)                               AS vat,
       CASE WHEN v.amt >= 30000 THEN 'Y' ELSE 'N' END  AS gift_yn
  FROM (SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS,
               FN_SLE_AMT(h.SLE_NO) AS amt          -- 합계는 여기서 한 번만
          FROM CF10 h
         WHERE h.SHP_NO = :shp_no
           AND h.SLE_DT BETWEEN :fr AND :to) v
 ORDER BY v.SLE_DT, v.SLE_HMS;

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (FORMAT => 'ALLSTATS LAST +PROJECTION') · :shp_no = 1003 · 2023년 12월]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |   4176 |00:00:00.39 |     290 |          |
|   1 |  SORT ORDER BY                       |                |      1 |   4167 |   4176 |00:00:00.39 |     290 |  407K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   4167 |   4176 |00:00:00.01 |     290 |          |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("H"."SHP_NO"=:SHP_NO)
   3 - access("H"."SLE_DT">=:FR AND "H"."SLE_DT"<=:TO)

Column Projection Information (1번 줄만):
---------------------------------------------------
   1 - (#keys=2) "H"."SLE_DT"[VARCHAR2,8], "H"."SLE_HMS"[VARCHAR2,6], "H"."SLE_NO"[VARCHAR2,15],
       "PUP"."FN_SLE_AMT"("H"."SLE_NO")[22], ROUND("PUP"."FN_SLE_AMT"("H"."SLE_NO")/11)[22],
       CASE WHEN "PUP"."FN_SLE_AMT"("H"."SLE_NO")>=30000 THEN 'Y' ELSE 'N' END [1]

[V$SQL: 함수 안의 문장 (dep=1) · 이 실행 한 번의 증가분]
  SELECT SUM(SLE_QTY * SLE_UPRC) FROM CF11 WHERE SLE_NO = :B1    EXECUTIONS 12,528 · BUFFER_GETS 50,112 (한 번에 4블록)
[세션 통계]  recursive calls 12,537 · session logical reads 50,415

문제

함수 안 SQL이 화면 행 수의 세 배로 실행된 이유를 계획에서 찾아 설명하시오.
함수가 영수증마다 한 번만 실행되도록 고치고, 더 줄일 방법이 있다면 그 SQL도 쓰시오. 각 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 가장 작은 수정: 인라인 뷰를 합치지 못하게 한다 (함수는 영수증마다 한 번 · 4,176번)
SELECT v.SLE_NO, v.SLE_DT, v.SLE_HMS,
       v.amt,
       ROUND(v.amt / 11)                               AS vat,
       CASE WHEN v.amt >= 30000 THEN 'Y' ELSE 'N' END  AS gift_yn
  FROM (SELECT /*+ NO_MERGE */ h.SLE_NO, h.SLE_DT, h.SLE_HMS,
               FN_SLE_AMT(h.SLE_NO) AS amt
          FROM CF10 h
         WHERE h.SHP_NO = :shp_no
           AND h.SLE_DT BETWEEN :fr AND :to) v
 ORDER BY v.SLE_DT, v.SLE_HMS;

-- ② 함수를 부르지 않는다: 판매상품과 조인해 영수증마다 묶는다 (함수 0번)
SELECT v.SLE_NO, v.SLE_DT, v.SLE_HMS,
       v.amt,
       ROUND(v.amt / 11)                               AS vat,
       CASE WHEN v.amt >= 30000 THEN 'Y' ELSE 'N' END  AS gift_yn
  FROM (SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS,
               SUM(d.SLE_QTY * d.SLE_UPRC) AS amt
          FROM CF10 h, CF11 d
         WHERE h.SHP_NO = :shp_no
           AND h.SLE_DT BETWEEN :fr AND :to
           AND d.SLE_NO(+) = h.SLE_NO
         GROUP BY h.SLE_NO, h.SLE_DT, h.SLE_HMS) v
 ORDER BY v.SLE_DT, v.SLE_HMS;

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

xplan

[② 조인 판]
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |   4176 |00:00:00.06 |   17000 |          |
|   1 |  SORT ORDER BY                         |                |      1 |   4167 |   4176 |00:00:00.06 |   17000 |  407K (0)|
|   2 |   HASH GROUP BY                        |                |      1 |   4167 |   4176 |00:00:00.05 |   17000 | 1246K (0)|
|   3 |    NESTED LOOPS OUTER                  |                |      1 |   9142 |   9188 |00:00:00.04 |   17000 |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   4167 |   4176 |00:00:00.01 |     290 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |          |
|   6 |     TABLE ACCESS BY INDEX ROWID        | CF11           |   4176 |      2 |   9188 |00:00:00.02 |   16710 |          |
|*  7 |      INDEX RANGE SCAN                  | CF11_PK        |   4176 |      2 |   9188 |00:00:00.01 |   12528 |          |
-----------------------------------------------------------------------------------------------------------------------
   4 - filter("H"."SHP_NO"=:SHP_NO)
   5 - access("H"."SLE_DT">=:FR AND "H"."SLE_DT"<=:TO)
   7 - access("D"."SLE_NO"(+)="H"."SLE_NO")

[세 판 비교]               함수 안 SQL 실행   recursive calls   논리 읽기   경과
 원래 (뷰 병합)                    12,528           12,537       50,415    0.39 초
 ① NO_MERGE                         4,176            4,185       17,000    0.14 초
 ② 조인 + GROUP BY                      0                9       17,000    0.06 초

모범 답안 서술

계획에 인라인 뷰를 뜻하는 VIEW 줄이 없다. 옵티마이저가 조건과 컬럼만 있는 단순 뷰를 바깥 쿼리와 합친 것이다(뷰 병합). 합칠 때 바깥의 v.amt는 그 정의인 FN_SLE_AMT(h.SLE_NO)로 바뀌어 들어간다. 바깥에서 amt를 세 곳(합계, 부가세, 사은품 판정)에 썼으므로 함수 식도 세 곳에 복사되고, 영수증마다 세 번 호출된다. Column Projection의 1번 줄에 FN_SLE_AMT가 세 번 나오고, V$SQL의 EXECUTIONS 12,528이 영수증 4,176건의 정확히 세 배인 것이 그 증거다. 함수 안 SQL이 한 번에 4블록이니 50,112블록을 읽었다. 인라인 뷰에서 한 번만 부르게 짰다는 의도는 뷰 병합으로 사라진다. 에러도 경고도 없다. ① 가장 작은 수정은 인라인 뷰에 NO_MERGE 힌트를 주는 것이다. 뷰가 따로 만들어지고 amt는 영수증마다 한 번 계산된 값이 된다. 함수는 4,176번 호출되고 논리 읽기 50,415 → 17,000, 경과 0.39 → 0.14초가 된다. 대가는 성능이 힌트 하나에 걸린다는 것이다. 누가 힌트를 지우거나 뷰 모양을 바꾸면 조용히 세 배로 돌아간다. 힌트 유효기간 대장에 적어 둔다. ② 함수를 부르지 않고 CF11과 아우터 조인한 뒤 영수증마다 묶으면 함수 호출이 사라진다. recursive calls가 12,537 → 9로 줄고 경과는 0.06초가 된다. 논리 읽기는 ①과 같은 17,000이다(영수증마다 CF11_PK로 찾아가는 일은 같다). 대가는 영수증 합계 규칙(수량 × 단가)이 함수와 이 SQL 두 곳에 생긴다는 것이다. 규칙이 바뀌면 두 곳을 함께 고쳐야 한다.

해설

함수가 들어간 인라인 뷰는 병합을 의심한다. 단순 뷰 병합은 비용을 따지지 않고 거의 늘 일어난다. 함수를 부른 컬럼을 바깥에서 여러 번 쓰면 그 횟수만큼 곱해진다. 교재 15화에서도 WITH 절이 본문에 합쳐져 함수 1,997번이 99,850번이 됐다.
뷰 안에 ROWNUM을 넣어도 병합이 막힌다. 힌트처럼 보이지 않아서 누가 왜 넣었는지 잊기 쉽다. 쓴다면 주석을 단다.
함수 호출을 스칼라 서브쿼리로 감싸는 방법은 여기서 소용이 적다. 영수증 번호는 행마다 달라 캐시가 듣지 않는다.
결과는 세 판이 같다(4,176행, 합계, 부가세, 사은품 대상). 영수증에 판매상품이 없을 수 있다면 ②는 아우터 조인이어야 함수와 같은 NULL이 나온다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황