영수증 합계 뷰를 쓰는 화면 셋 가운데 월간 객단가 리포트만 TEMP를 쓰며 1.4초 걸린다

실전SELECT유통290 / 700

상황

지난달 영수증 합계 함수 사건(뷰가 합쳐지면서 함수가 세 번씩 호출된 일) 뒤에 오과장이 코딩 가이드에 "인라인 뷰와 집계 뷰를 조인할 때는 NO_MERGE를 붙인다"라는 조문을 넣었다. 개발팀은 공통 뷰 V_SLE_RCPT(영수증별 품목 수와 금액)를 쓰는 SQL 세 개에 이 힌트를 붙였다. (A) 「영수증 상세」는 영수증 번호로 하루 3,000번, (B) 「매장 일별 영수증」은 매장과 날짜로 하루 2,000번, (C) 「월간 매장 객단가」 리포트는 하루 30번 돈다. (A)와 (B)는 그대로인데 (C)만 0.2초에서 1.4초로 늘고 TEMP를 쓰기 시작했다. 오과장은 조문을 지킬지, 고칠지 숫자로 판단해 달라고 했다.

테이블과 인덱스

CF10 판매(영수증): 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 하루 약 700건, 2023년 12월 20,864건 · 매장 6곳
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · 영수증 한 장에 평균 2.2줄(한 블록 안) · 12월 판매상품 45,935줄
영수증 50만 장 모두 판매상품이 한 줄 이상 있다 · 세션 PGA 작업 영역은 자동 관리이고, 집계 하나에 쓸 수 있는 메모리는 약 20MB다 · 통계 최신

원본 SQL

sql

-- 공통 뷰: 영수증별 품목 수와 금액
CREATE OR REPLACE VIEW V_SLE_RCPT AS
SELECT SLE_NO, COUNT(*) AS ITM_CNT, SUM(SLE_QTY * SLE_UPRC) AS RCPT_AMT
  FROM CF11
 GROUP BY SLE_NO;

-- (A) 영수증 상세 (하루 3,000번)
SELECT /*+ NO_MERGE(v) */ h.SLE_NO, h.SLE_DT, h.SLE_HMS, v.ITM_CNT, v.RCPT_AMT
  FROM CF10 h, V_SLE_RCPT v
 WHERE h.SLE_NO = :sle_no
   AND v.SLE_NO = h.SLE_NO;

-- (B) 매장 일별 영수증 (하루 2,000번)
SELECT /*+ NO_MERGE(v) */ h.SLE_NO, h.SLE_HMS, v.ITM_CNT, v.RCPT_AMT
  FROM CF10 h, V_SLE_RCPT v
 WHERE h.SHP_NO = :shp
   AND h.SLE_DT = :dt
   AND v.SLE_NO = h.SLE_NO;

-- (C) 월간 매장 객단가 (하루 30번)
SELECT /*+ NO_MERGE(v) */ h.SHP_NO, COUNT(*) AS RCPT_CNT, ROUND(AVG(v.RCPT_AMT)) AS AVG_AMT
  FROM CF10 h, V_SLE_RCPT v
 WHERE h.SLE_DT BETWEEN :m1 AND :m2
   AND v.SLE_NO = h.SLE_NO
 GROUP BY h.SHP_NO;

실행계획 학습용 예시

xplan

[C · :m1 = '20231201', :m2 = '20231231']
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      6 |00:00:01.42 |    8494 |  3318 |   3318 |          |         |
|   1 |  HASH GROUP BY                       |                |      1 |      6 |      6 |00:00:01.42 |    8494 |  3318 |   3318 | 1036K (0)|         |
|*  2 |   HASH JOIN                          |                |      1 |  20833 |  20864 |00:00:01.42 |    8494 |  3318 |   3318 | 2147K (0)|         |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |  20833 |  20864 |00:00:00.01 |     290 |     0 |      0 |          |         |
|*  4 |     INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |     0 |      0 |          |         |
|   5 |    VIEW                              | V_SLE_RCPT     |      1 |    500K|    500K|00:00:01.35 |    8204 |  3318 |   3318 |          |         |
|   6 |     HASH GROUP BY                    |                |      1 |    500K|    500K|00:00:01.30 |    8204 |  3318 |   3318 |   20M (1)|     27M |
|   7 |      TABLE ACCESS FULL               | CF11           |      1 |   1096K|   1096K|00:00:00.06 |    8204 |     0 |      0 |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V"."SLE_NO"="H"."SLE_NO")
   4 - access("H"."SLE_DT">=:M1 AND "H"."SLE_DT"<=:M2)

[B · :shp = 1003, :dt = '20230601']
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    121 |00:00:00.01 |     496 |
|   1 |  NESTED LOOPS                        |                |      1 |    118 |    121 |00:00:00.01 |     496 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    118 |    121 |00:00:00.01 |      12 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    706 |    706 |00:00:00.01 |       5 |
|   4 |   VIEW PUSHED PREDICATE              | V_SLE_RCPT     |    121 |      1 |    121 |00:00:00.01 |     484 |
|   5 |    SORT GROUP BY                     |                |    121 |      1 |    121 |00:00:00.01 |     484 |
|   6 |     TABLE ACCESS BY INDEX ROWID      | CF11           |    121 |      2 |    266 |00:00:00.01 |     484 |
|*  7 |      INDEX RANGE SCAN                | CF11_PK        |    121 |      2 |    266 |00:00:00.01 |     363 |
-----------------------------------------------------------------------------------------------------------------
   2 - filter("H"."SHP_NO"=:SHP)
   3 - access("H"."SLE_DT"=:DT)
   7 - access("SLE_NO"="H"."SLE_NO")

[A]  CF10_PK와 CF11_PK로 영수증 한 장 · Buffers 8 · 7번 줄에 해당하는 조건은 access("SLE_NO"=:SLE_NO)

문제

세 SQL에서 바깥 조건이 뷰 안으로 각각 어떻게 들어갔는지(조건절 이행, JPPD, 들어가지 못함) 실행계획으로 설명하시오. 같은 NO_MERGE인데 (A)와 (B)는 멀쩡하고 (C)만 느려진 이유도 설명하시오.
(C)를 고치고, 고친 뒤의 처리 순서와 일량(Buffers, TEMP, 시간)을 쓰시오. (C)에서 JPPD를 쓰면 어떻게 되는지도 숫자로 쓰시오.
코딩 가이드의 조문을 어떻게 고칠지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (A) · (B): 힌트는 필요 없다 ((A)는 조건 이행, (B)는 JPPD 또는 뷰 머징이 저절로 된다)
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, v.ITM_CNT, v.RCPT_AMT
  FROM CF10 h, V_SLE_RCPT v
 WHERE h.SLE_NO = :sle_no
   AND v.SLE_NO = h.SLE_NO;

SELECT h.SLE_NO, h.SLE_HMS, v.ITM_CNT, v.RCPT_AMT
  FROM CF10 h, V_SLE_RCPT v
 WHERE h.SHP_NO = :shp
   AND h.SLE_DT = :dt
   AND v.SLE_NO = h.SLE_NO;

-- (C): 뷰를 풀어 12월 영수증과 먼저 조인한 뒤 묶는다 (복합 뷰 머징)
SELECT /*+ MERGE(v) USE_HASH(v) */ h.SHP_NO, COUNT(*) AS RCPT_CNT, ROUND(AVG(v.RCPT_AMT)) AS AVG_AMT
  FROM CF10 h, V_SLE_RCPT v
 WHERE h.SLE_DT BETWEEN :m1 AND :m2
   AND v.SLE_NO = h.SLE_NO
 GROUP BY h.SHP_NO;

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

xplan

[C]
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |      6 |00:00:00.21 |    8494 |          |
|   1 |  HASH GROUP BY                         |                |      1 |      6 |      6 |00:00:00.21 |    8494 | 1036K (0)|
|   2 |   VIEW                                 | VM_NWVW_1      |      1 |  20833 |  20864 |00:00:00.21 |    8494 |          |
|   3 |    HASH GROUP BY                       |                |      1 |  20833 |  20864 |00:00:00.21 |    8494 | 3125K (0)|
|*  4 |     HASH JOIN                          |                |      1 |  45833 |  45935 |00:00:00.18 |    8494 | 2147K (0)|
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |  20833 |  20864 |00:00:00.01 |     290 |          |
|*  6 |       INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |          |
|   7 |      TABLE ACCESS FULL                 | CF11           |      1 |   1096K|   1096K|00:00:00.06 |    8204 |          |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("SLE_NO"="H"."SLE_NO")
   6 - access("H"."SLE_DT">=:M1 AND "H"."SLE_DT"<=:M2)

모범 답안 서술

(A)는 바깥 조건이 상수다. h.SLE_NO = :sle_no와 v.SLE_NO = h.SLE_NO에서 옵티마이저가 v.SLE_NO = :sle_no를 끌어내(조건절 이행) 뷰 안으로 밀어 넣으므로, 뷰가 따로 있어도 CF11_PK로 영수증 한 장만 묶는다(8블록). NO_MERGE와 상관이 없다. (B)는 4번 줄 VIEW PUSHED PREDICATE다. NO_MERGE는 뷰를 합치지 말라는 지시일 뿐 조인 조건을 뷰 안으로 밀어 넣는 것(JPPD)은 막지 않는다. 그래서 명동점 영수증 121장마다 뷰를 열어 그 영수증의 상품만 묶었고(Starts 121, 484블록), 합계 496블록으로 예전과 같다. (C)는 바깥 영수증이 20,864장이다. JPPD를 쓰면 20,864번 × 4블록 = 약 83,000블록이라 옵티마이저가 고르지 않았고, 뷰를 합칠 수도 없으니 남은 길은 뷰를 통째로 만드는 것이었다. 6번 줄이 판매상품 110만 줄을 영수증 50만 장으로 모두 묶는데, 그 결과(약 20MB)가 작업 영역을 넘어 TEMP에 27MB를 쓰고 3,318블록을 다시 읽었다(1.42초). 실제로 필요한 것은 12월 영수증 20,864장뿐이다. (C)에서 NO_MERGE를 떼고 MERGE(v)로 뷰를 풀면(복합 뷰 머징, VM_NWVW_1) 12월 영수증 20,864장과 판매상품을 먼저 해시 조인해 45,935줄만 남기고, 그다음에 영수증별로 묶는다. Buffers는 8,494로 같지만 묶는 그룹이 50만 → 20,864개로 줄어 메모리 3MB 안에서 끝나고 TEMP가 사라진다. 1.42초 → 0.21초다. 힌트 없이 두어도 옵티마이저는 이 모양을 고른다. 조문은 "행마다 비싼 계산(사용자 함수, 스칼라 서브쿼리)이 든 뷰를 조인할 때만 NO_MERGE를 쓴다"로 좁힌다. 집계 뷰는 바깥 집합의 크기에 따라 조건 이행, JPPD, 복합 뷰 머징 중 맞는 길이 다르므로 일괄 힌트를 붙이지 않고 화면마다 계획을 보고 정한다. 이번 조문은 (A)와 (B)에서는 아무것도 얻지 못하고 (C)에서만 TEMP를 만들었다.

해설

세 길의 손익분기: 바깥 집합이 영수증 1장이면 조건 이행, 121장이면 JPPD(약 500블록), 2만 장이면 뷰를 풀어 조인 뒤 묶기(약 8,500블록)가 맞다. JPPD는 바깥 행 수 × 4블록이 판매상품 전체 스캔 8,204블록을 넘는 지점(약 2,000장)부터 불리하다.
(C)를 더 줄이려면: 판매번호가 판매일자 8자리로 시작하므로('20231201-0001') 인라인 뷰 안에 SLE_NO >= :m1 AND SLE_NO < :next_m1 범위를 직접 주면 CF11_PK로 12월 판매상품만 읽는다(약 1,500블록, 합계 약 1,800블록). 저장된 뷰에는 바인드를 넣을 수 없으므로 리포트 SQL을 인라인 뷰로 바꿔야 한다(SUBQ-050과 같은 원리).
함수 사건과 무엇이 다른가: 그 사건은 뷰가 합쳐지면서 함수 식이 바깥의 컬럼 자리마다 복사된 것이 원인이었다(PLSQL-009). 이 뷰에는 함수가 없으므로 합쳐져도 같은 계산을 되풀이하지 않는다.
조문을 고칠 때는 근거 숫자를 함께 적는다. "(C) 1.42초 TEMP 27MB → 0.21초"처럼 조문이 만든 손해와 그 조문이 막으려던 손해를 나란히 적어 두면 다음 사람이 조문의 범위를 오해하지 않는다(교재 06화 힌트 유효기간 대장).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황