SQLP 튜닝 연습장

테마

튜닝 연습조인 튜닝
JOIN-078

월 매장별 상품 판매 파일에 해시 조인 힌트를 달았는데 계획은 그대로 주문상품을 4만 번 찾아간다

중급SELECT버거228 / 700

상황 ​

영업기획팀은 매달 초 「월 매장별 상품 판매」 파일을 내려받는다. 매장 200곳과 상품 92종의 조합마다 그달 판매 수량과 판매 줄 수가 한 줄씩 들어간다. 한 번에 2초 가까이 걸려서 조민우가 "한 달 치 전체를 읽으니 해시가 맞다"며 힌트를 달았다. 힌트를 단 뒤에도 계획은 그대로다. 조민우는 힌트를 SQL 맨 앞의 SELECT 바로 뒤에 적었다. 아래는 2013년 6월로 내려받을 때의 실행계획이다.

테이블과 인덱스 ​

  • ORD 주문: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · 날짜순 적재 · 2013년 6월 41,700건
  • ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 2013년 6월 주문의 상품 103,000줄
  • SHOP 200행 · 5블록 / ITEM 92행 · 5블록
  • 통계 최신 · ORD_DT가 VARCHAR2(8)이라 옵티마이저는 한 달 범위를 약 10,300건으로 본다(실제의 4분의 1, 교재 03화와 같은 현상)

원본 SQL ​

sql
SELECT /*+ LEADING(o i) USE_HASH(i) FULL(i) */
       s.SHOP_NO, s.SHOP_NM, t.ITEM_ID, t.ITEM_NM, v.qty, v.line_cnt
  FROM (SELECT o.SHOP_NO, i.ITEM_ID,
               SUM(i.ORD_ITEM_QTY) AS qty, COUNT(*) AS line_cnt
          FROM ORD o, ORD_ITEM i
         WHERE o.ORD_DT BETWEEN :d1 AND :d2
           AND i.ORD_NO = o.ORD_NO
         GROUP BY o.SHOP_NO, i.ITEM_ID) v,
       SHOP s, ITEM t
 WHERE s.SHOP_NO = v.SHOP_NO
   AND t.ITEM_ID = v.ITEM_ID
 ORDER BY s.SHOP_NO, t.ITEM_ID

실행계획 학습용 예시 ​

xplan
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |             |      1 |        |  18216 |00:00:01.84 |    229K |          |
|   1 |  SORT ORDER BY                          |             |      1 |  18400 |  18216 |00:00:01.84 |    229K | 1740K (0)|
|*  2 |   HASH JOIN                             |             |      1 |  18400 |  18216 |00:00:01.80 |    229K | 1316K (0)|
|   3 |    TABLE ACCESS FULL                    | ITEM        |      1 |     92 |     92 |00:00:00.01 |       5 |          |
|*  4 |    HASH JOIN                            |             |      1 |  18400 |  18216 |00:00:01.78 |    229K | 1402K (0)|
|   5 |     TABLE ACCESS FULL                   | SHOP        |      1 |    200 |    200 |00:00:00.01 |       5 |          |
|   6 |     VIEW                                |             |      1 |  18400 |  18216 |00:00:01.76 |    229K |          |
|   7 |      HASH GROUP BY                      |             |      1 |  18400 |  18216 |00:00:01.76 |    229K | 2810K (0)|
|   8 |       NESTED LOOPS                      |             |      1 |  25440 |   103K |00:00:01.60 |    229K |          |
|   9 |        NESTED LOOPS                     |             |      1 |  25440 |   103K |00:00:00.71 |    126K |          |
|  10 |         TABLE ACCESS BY INDEX ROWID BATCHED| ORD      |      1 |  10300 |  41700 |00:00:00.06 |    1062 |          |
|* 11 |          INDEX RANGE SCAN               | ORD_X01     |      1 |  10300 |  41700 |00:00:00.01 |     128 |          |
|* 12 |         INDEX RANGE SCAN                | ORD_ITEM_PK |  41700 |      2 |   103K |00:00:00.52 |    125K |          |
|  13 |        TABLE ACCESS BY INDEX ROWID      | ORD_ITEM    |   103K |      2 |   103K |00:00:00.78 |    103K |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."ITEM_ID"="V"."ITEM_ID")
   4 - access("S"."SHOP_NO"="V"."SHOP_NO")
  11 - access("O"."ORD_DT">=:D1 AND "O"."ORD_DT"<=:D2)
  12 - access("I"."ORD_NO"="O"."ORD_NO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (N - Unresolved (3))
---------------------------------------------------------------------------

   1 -  SEL$1
         N -  FULL(i)
         N -  LEADING(o i)
         N -  USE_HASH(i)

문제 ​

  1. 힌트가 쓰이지 않은 이유를 Hint Report와 SQL의 블록 구조로 설명하시오. 힌트가 없을 때 옵티마이저가 8번 줄에서 NL을 고른 까닭도 쓰시오.
  2. 뷰 안의 조인이 해시 조인이 되도록 힌트를 고치시오. 힌트를 바깥 블록에 모아 두어야 한다면 어떻게 쓰는지도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자