JOIN-025

상품을 build로 쓰려고 LEADING 맨 앞에 두었더니 상품 유형별 연 매출이 10초가 됐다

중급SELECT한빛유통175 / 700

상황

「상품 유형별 연 매출」은 한 해 주문의 주문상세를 상품 유형(4종)으로 묶는다. 원래 힌트는 LEADING(o d m) USE_HASH(d m)이었다. 조민우가 06화의 "작은 쪽을 build로"를 읽고, 상품 100행이 build가 되도록 M_ITM을 LEADING 맨 앞으로 옮겼다. 그 뒤로 0.1초이던 리포트가 10초 가까이 걸리고 TEMP 경보가 울린다. Buffers는 거의 그대로라서 조민우는 원인을 못 찾고 있다. 아래는 2024년으로 조회한 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 2024년 주문 50,000건
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 2024년 주문의 상세 100,000줄
M_ITM: 100행 · 5블록 · PK_M_ITM (ITM_ID) · ITM_TP 4종 (TP_A ~ TP_D, 25개씩)
통계 최신 · pga_aggregate_target 512M · AUTO

원본 SQL

sql

SELECT /*+ LEADING(m o d) USE_HASH(o d) */
       m.ITM_TP, COUNT(*) AS det_cnt, SUM(d.ORD_QTY * d.UNT_PRC) AS sale_amt
  FROM T_ORD o, T_ORD_DET d, M_ITM m
 WHERE o.ORD_DT >= TO_DATE(:v_yyyy || '0101', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_yyyy || '0101', 'YYYYMMDD'), 12)
   AND d.ORD_SEQ = o.ORD_SEQ
   AND m.ITM_ID  = d.ITM_ID
 GROUP BY m.ITM_TP

실행계획 학습용 예시 · 행 수는 실데이터

xplan

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |      4 |00:00:09.71 |    1797 |       |       |          |         |
|   1 |  HASH GROUP BY         |           |      1 |      4 |      4 |00:00:09.71 |    1797 |  1116K|  1116K|  655K (0)|         |
|*  2 |   HASH JOIN            |           |      1 |    100K|    100K|00:00:09.68 |    1797 |   236M|    14M|   98M (1)|     187M|
|   3 |    MERGE JOIN CARTESIAN|           |      1 |   5000K|   5000K|00:00:01.32 |     901 |       |       |          |         |
|   4 |     TABLE ACCESS FULL  | M_ITM     |      1 |    100 |    100 |00:00:00.01 |       5 |       |       |          |         |
|   5 |     BUFFER SORT        |           |    100 |  50000 |   5000K|00:00:00.71 |     896 |  2316K|   743K| 2058K (0)|         |
|*  6 |      TABLE ACCESS FULL | T_ORD     |      1 |  50000 |  50000 |00:00:00.02 |     896 |       |       |          |         |
|   7 |    TABLE ACCESS FULL   | T_ORD_DET |      1 |    200K|    200K|00:00:00.03 |     896 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ORD_SEQ"="O"."ORD_SEQ" AND "M"."ITM_ID"="D"."ITM_ID")
   6 - filter(("O"."ORD_DT">=TO_DATE(:V_YYYY||'0101','YYYYMMDD') AND
              "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YYYY||'0101','YYYYMMDD'),12)))

문제

3번 줄 MERGE JOIN CARTESIAN이 왜 생겼는지, 그 5,000K 행이 어디서 나왔는지 설명하시오. Buffers는 거의 같은데 9.7초가 걸리는 이유를 메모리 열로 설명하시오.
조인 순서는 주문 → 주문상세로 두고 M_ITM을 build로 쓰도록 힌트를 고치시오. LEADING과 SWAP_JOIN_INPUTS가 각각 무엇을 정하는지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 조인 순서는 주문 → 주문상세 그대로, 세 번째 조인의 build 만 M_ITM 으로 바꾼다
SELECT /*+ LEADING(o d m) USE_HASH(d m) SWAP_JOIN_INPUTS(m) */
       m.ITM_TP, COUNT(*) AS det_cnt, SUM(d.ORD_QTY * d.UNT_PRC) AS sale_amt
  FROM T_ORD o, T_ORD_DET d, M_ITM m
 WHERE o.ORD_DT >= TO_DATE(:v_yyyy || '0101', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_yyyy || '0101', 'YYYYMMDD'), 12)
   AND d.ORD_SEQ = o.ORD_SEQ
   AND m.ITM_ID  = d.ITM_ID
 GROUP BY m.ITM_TP

개선 후 실행계획 학습용 예시 · 행 수는 실데이터

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |      4 |00:00:00.11 |    1797 |       |       |          |
|   1 |  HASH GROUP BY        |           |      1 |      4 |      4 |00:00:00.11 |    1797 |  1116K|  1116K|  655K (0)|
|*  2 |   HASH JOIN           |           |      1 |    100K|    100K|00:00:00.09 |    1797 |  1250K|  1250K| 1305K (0)|
|   3 |    TABLE ACCESS FULL  | M_ITM     |      1 |    100 |    100 |00:00:00.01 |       5 |       |       |          |
|*  4 |    HASH JOIN          |           |      1 |    100K|    100K|00:00:00.07 |    1792 |  3209K|  1351K| 3471K (0)|
|*  5 |     TABLE ACCESS FULL | T_ORD     |      1 |  50000 |  50000 |00:00:00.01 |     896 |       |       |          |
|   6 |     TABLE ACCESS FULL | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     896 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("M"."ITM_ID"="D"."ITM_ID")
   4 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   5 - filter(("O"."ORD_DT">=TO_DATE(:V_YYYY||'0101','YYYYMMDD') AND
              "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YYYY||'0101','YYYYMMDD'),12)))

모범 답안 서술

LEADING(m o d)는 M_ITM과 T_ORD를 먼저 조인하라는 뜻이다. 그런데 두 테이블 사이에는 조인 조건이 없다(둘은 주문상세를 거쳐서만 이어진다). 조인 조건이 없는 두 집합을 붙이면 모든 조합을 만드는 카티션 곱이 되므로 3번 줄 MERGE JOIN CARTESIAN이 생겼다. 상품 100행 × 2024년 주문 50,000건 = 5,000K 행이고, 5번 줄 BUFFER SORT는 주문 50,000건을 한 번 읽어 메모리에 두고 상품 100행마다 되풀이해 내보냈다(Starts 100). 2번 줄 해시 조인은 첫 번째 자식인 이 카티션 곱 500만 행을 build로 올렸다. OMem 236M가 work area에 들어가지 않아 98M (1), 곧 onepass로 돌았고, Used-Tmp 187M를 TEMP에 썼다가 다시 읽었다. 9.71초의 대부분이 이 TEMP 왕복에 들었다. 테이블은 여전히 한 번씩만 읽으므로 Buffers는 1,797로 거의 그대로다. 그래서 Buffers만 보고는 원인이 보이지 않았다. LEADING은 조인 순서를 정한다. 첫 조인에서는 앞에 적은 테이블이 build가 되지만, 세 번째 테이블부터는 앞의 조인 결과와 그 테이블 중 어느 쪽을 build로 쓸지를 따로 정한다. 그것을 정하는 힌트가 SWAP_JOIN_INPUTS이다. 그래서 조인 순서는 원래대로 LEADING(o d m)으로 두고, SWAP_JOIN_INPUTS(m)을 더해 세 번째 조인의 build를 M_ITM으로 바꾼다. 첫 조인은 2024년 주문 50,000건이 build(3,471K (0))이고, 둘째 조인은 상품 100행이 build(1,305K (0))이며 그 결과 100,000줄은 probe로 흘러간다. TEMP는 0이 되고 A-Time은 9.71 → 0.11초로 준다. Buffers는 1,797 그대로다.

해설

세 번째 테이블의 build: LEADING(o d m) USE_HASH(d m)만 적으면 둘째 조인에서 (o, d의 결과)와 m 중 어느 쪽을 build로 쓸지는 옵티마이저가 비용으로 고른다. 그 선택까지 고정하려면 SWAP_JOIN_INPUTS(m)이나 NO_SWAP_JOIN_INPUTS(m)을 적는다.
다른 정답: LEADING(m d o) USE_HASH(d o)도 카티션 곱이 생기지 않는다(M_ITM과 T_ORD_DET 사이에 조건이 있다). 다만 첫 조인 결과가 주문상세 전체 20만 줄이 되어 둘째 조인의 build나 probe가 커진다. 힌트를 모두 떼도 옵티마이저가 위 개선 계획을 고른다.
카티션 곱은 E-Rows에도 보인다: 3번 줄 E-Rows 5000K가 이미 경고였다. 힌트를 바꾼 뒤에는 E-Rows가 갑자기 커진 줄이 없는지 확인한다. 카디널리티 오추정이 만드는 MERGE JOIN CARTESIAN은 원인이 다르다(E-Rows 1로 잘못 본 경우).
힌트는 순서와 방식과 build를 따로 정한다: 06화의 "작은 쪽을 build로"는 옳다. 그것을 LEADING으로 하면 조인 순서까지 바뀐다는 점이 함정이었다 (교재 06화 §4, §7).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황