JOIN-069

「신선 상품 월간 판매 상세」가 월초에는 바로 뜨고 월말로 갈수록 느려진다

중급SELECT한빛유통219 / 700

상황

상품팀의 「신선 상품 월간 판매 상세」는 신선 상품 가운데 상품명에 키워드가 든 상품의 이번 달 주문 상세를 보여 준다. 매주 금요일 오후 회의 전에 많이 열린다. 1일 오전에는 0.01초인데 날이 갈수록 느려져서 월말이면 0.5초 가까이 걸린다고 한다. SQL은 바뀐 적이 없다. T_ORD의 통계는 매달 1일 02:00 월초 배치가 모은다. 아래는 2024년 12월 28일 오후에 키워드 '유기농'으로 조회한 실행계획이다.

테이블과 인덱스

T_ORD: 2024-12-28 오후 기준 약 99,600행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT) · ORD_DT는 시분초까지 있어 값이 거의 겹치지 않는다
T_ORD 통계: 12월 1일 02:00 수집 · NUM_ROWS 95,800 · ORD_DT 최댓값 2024-11-30 23:58:41 · NDV 95,640
12월 1일부터 28일 오후까지 들어온 주문 3,800건(테이블 전체에 흩어져 있다)
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 평균 2줄 / M_ITM: 100행 · 5블록 · ITM_TP 4종(신선 'FRESH' 25개) · 상품명에 '유기농'이 든 신선 상품 8개
12월 주문 가운데 이 상품 8개의 상세는 608줄이다

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, i.ITM_NM, d.ORD_QTY, d.ORD_QTY * d.UNT_PRC AS amt
  FROM T_ORD o, T_ORD_DET d, M_ITM i
 WHERE o.ORD_DT >= TRUNC(SYSDATE, 'MM')
   AND d.ORD_SEQ = o.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND i.ITM_TP  = 'FRESH'
   AND i.ITM_NM LIKE '%' || :v_kw || '%'
 ORDER BY o.ORD_DT DESC, o.ORD_SEQ

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |    608 |00:00:00.47 |   93701 |           |
|   1 |  SORT ORDER BY                           |              |      1 |      1 |    608 |00:00:00.47 |   93701 | 59392 (0) |
|   2 |   NESTED LOOPS                           |              |      1 |      1 |    608 |00:00:00.47 |   93701 |           |
|   3 |    MERGE JOIN CARTESIAN                  |              |      1 |      1 |  30400 |00:00:00.03 |    2501 |           |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED  | T_ORD        |      1 |      1 |   3800 |00:00:00.01 |    2496 |           |
|*  5 |      INDEX RANGE SCAN                    | IX_T_ORD_DT  |      1 |      1 |   3800 |00:00:00.01 |      14 |           |
|   6 |     BUFFER SORT                          |              |   3800 |      1 |  30400 |00:00:00.02 |       5 |  2048 (0) |
|*  7 |      TABLE ACCESS FULL                   | M_ITM        |      1 |      1 |      8 |00:00:00.01 |       5 |           |
|*  8 |    TABLE ACCESS BY INDEX ROWID BATCHED   | T_ORD_DET    |  30400 |      1 |    608 |00:00:00.41 |   91200 |           |
|*  9 |     INDEX RANGE SCAN                     | PK_T_ORD_DET |  30400 |      2 |  60800 |00:00:00.17 |   60800 |           |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("O"."ORD_DT">=TRUNC(SYSDATE@!,'fmmm'))
   7 - filter(("I"."ITM_TP"='FRESH' AND "I"."ITM_NM" LIKE '%'||:V_KW||'%'))
   8 - filter("D"."ITM_ID"="I"."ITM_ID")
   9 - access("D"."ORD_SEQ"="O"."ORD_SEQ")

문제

3번 줄 MERGE JOIN CARTESIAN이 왜 골라졌는지 4번 줄과 7번 줄의 E-Rows로 설명하고, 두 E-Rows가 각각 왜 1이 되었는지 쓰시오. 월초에는 빠른 이유도 설명하시오.
이 계획이 다시 나오지 않도록 고치시오. 고친 뒤의 일량과, 다른 고치는 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 날짜가 계속 자라는 T_ORD 는 통계를 매일 밤(03:00, 주문이 가장 적을 때) 모은다 : '이번 달'이 통계 범위 안으로 들어온다
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'T_ORD', no_invalidate => FALSE);
END;
/

-- ② 앞 % LIKE 는 통계로 고칠 수 없다(기본 선택도 5%) : 5블록짜리 상품 테이블은 실행할 때 표본을 보게 한다
SELECT /*+ DYNAMIC_SAMPLING(i 2) */
       o.ORD_SEQ, o.ORD_DT, i.ITM_NM, d.ORD_QTY, d.ORD_QTY * d.UNT_PRC AS amt
  FROM T_ORD o, T_ORD_DET d, M_ITM i
 WHERE o.ORD_DT >= TRUNC(SYSDATE, 'MM')
   AND d.ORD_SEQ = o.ORD_SEQ
   AND i.ITM_ID  = d.ITM_ID
   AND i.ITM_TP  = 'FRESH'
   AND i.ITM_NM LIKE '%' || :v_kw || '%'
 ORDER BY o.ORD_DT DESC, o.ORD_SEQ

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |    608 |00:00:00.08 |    1797 |           |
|   1 |  SORT ORDER BY         |           |      1 |    594 |    608 |00:00:00.08 |    1797 | 59392 (0) |
|*  2 |   HASH JOIN            |           |      1 |    594 |    608 |00:00:00.08 |    1797 | 1288K (0) |
|*  3 |    TABLE ACCESS FULL   | M_ITM     |      1 |      8 |      8 |00:00:00.01 |       5 |           |
|*  4 |    HASH JOIN           |           |      1 |   7426 |   7598 |00:00:00.07 |    1792 | 1635K (0) |
|*  5 |     TABLE ACCESS FULL  | T_ORD     |      1 |   3713 |   3800 |00:00:00.01 |     896 |           |
|   6 |     TABLE ACCESS FULL  | T_ORD_DET |      1 |   200K |   200K |00:00:00.03 |     896 |           |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ITM_ID"="D"."ITM_ID")
   3 - filter(("I"."ITM_TP"='FRESH' AND "I"."ITM_NM" LIKE '%'||:V_KW||'%'))
   4 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   5 - filter("O"."ORD_DT">=TRUNC(SYSDATE@!,'fmmm'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

모범 답안 서술

3번 줄 MERGE JOIN CARTESIAN의 E-Rows는 1이고 A-Rows는 30,400이다. 아래의 두 입력이 모두 1행으로 추정됐기 때문이다. 4번 줄 T_ORD는 E-Rows 1에 A-Rows 3,800이고, 7번 줄 M_ITM은 E-Rows 1에 A-Rows 8이다. 옵티마이저는 1행 × 1행이면 곱해도 1행이므로 둘을 먼저 카티션으로 묶고 주문상세는 한 번만 찾으면 된다고 계산했다. 실제로는 3,800 × 8 = 30,400조합이 만들어졌고, 9번 줄이 조합마다 주문상세 PK를 찾아(Starts 30,400) 그 주문의 상세를 다 읽은 뒤 상품이 맞는 608줄만 남겼다. Buffers 93,701 가운데 91,200이 8번과 9번 줄이다. T_ORD가 1행으로 추정된 원인은 통계의 최댓값이다. 통계를 12월 1일 02:00에 모았으므로 ORD_DT 최댓값이 11월 30일이다. '이번 달'은 통계 범위 밖이고, 범위 밖을 물으면 옵티마이저는 값 하나만큼(NUM_ROWS / NDV ≈ 1행)만 있다고 본다. 1일 오전에는 실제로 주문이 몇 건 없어 이 추정이 맞고 화면도 빠르다. 날이 갈수록 실제 주문만 늘어 월말에 3,800건이 된다. M_ITM이 1행인 원인은 LIKE '%키워드%'다. 앞에 %가 붙은 LIKE에는 통계를 쓸 수 없어 기본 선택도 5%를 쓰고, 신선 상품 25개 × 5% ≈ 1행이 됐다. 따로 보면 작은 오차지만 카티션 곱에서는 둘이 곱해진다. 고치는 방법은 두 추정을 바로잡는 것이다. T_ORD처럼 날짜가 계속 자라는 테이블은 통계를 매일 밤 모아 '이번 달'이 통계 범위 안에 들어오게 한다. LIKE 쪽은 통계로 고칠 수 없으므로 5블록짜리 M_ITM은 실행할 때 표본을 보게 한다(DYNAMIC_SAMPLING). 두 추정이 3,700행과 8행 근처로 맞으면 옵티마이저는 해시 조인을 고른다. Buffers는 93,701 → 1,797, 시간은 0.47 → 0.08초다.

해설

힌트로 막는다면: LEADING(o d i) USE_HASH(d i)로 순서와 방식을 고정해도 이 계획은 막을 수 있다. 하지만 원인인 틀린 추정은 그대로라서 T_ORD를 쓰는 다른 SQL에서 또 터진다. 힌트는 추정을 고칠 수 없을 때 쓰는 차선이다.
카티션을 끄는 숨은 파라미터: 카티션 조인 자체를 끄는 숨은 파라미터를 쓰자는 말이 나오곤 한다. 숨은 파라미터는 지원 범위 밖이고, 1행짜리 기준 테이블과의 조인처럼 카티션이 맞는 계획까지 막는다.
매일 모아도 남는 범위 밖: 매일 밤 모아도 낮 동안 들어온 오늘 주문은 여전히 범위 밖이다. '오늘'만 묻는 실시간 화면이라면 적재나 정산 배치 직후에 그 배치에서 통계를 모으거나, 통계 수집 기준을 낮춰 둔다(교재 10화 §7).
읽는 순서: 계획에서 MERGE JOIN CARTESIAN 아래 두 입력의 E-Rows가 모두 1이면 먼저 의심한다. 한쪽이라도 실제로 여러 행이면 곱해진다. E-Rows가 맞는데 카티션이 있다면 조인 조건이 빠진 것이다(JOIN-065).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황