SQLP 튜닝 연습장

테마

튜닝 연습조인 튜닝
JOIN-052

메뉴별 월 매출이 2,785블록만 읽는데 해시 조인 한 줄에서 2초 가까이 쓴다

중급SELECT버거202 / 700

상황 ​

POS는 주문상품의 수량만 보내므로, 본사 재무팀의 「메뉴별 월 매출」은 주문일에 적용되던 주간 가격을 곱해 금액을 낸다. 월 마감 때 달마다 돌리고, 연간 리포트는 같은 SQL을 열두 번 돌려 붙인다. 한 달에 2초 가까이 걸린다. 오과장은 "Buffers는 주문상품 한 달 치와 가격표를 한 번씩 읽은 만큼이라 정상이다. 해시 조인인데 왜 느린가"라고 묻는다. 아래는 2013년 7월로 돌린 실행계획이다.

테이블과 인덱스 ​

  • ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 날짜순 적재 · 2013년 7월 105,012줄이 약 2,350블록에 모여 있다(ORD_ITEM_X01 리프 약 375블록)
  • ITEM_PRC_HIS 메뉴 가격 이력 (이 문제에서 쓰는 파생 테이블, 선분이력): ITEM_ID · STA_DT · END_DT (둘 다 VARCHAR2(8), 그날까지 포함) · SALE_PRC · 본사가 2012년부터 매주 월요일 전 메뉴의 그 주 가격을 한 줄씩 올린다 · 9,568행(92개 × 104주) · 60블록 · ITEM_PRC_HIS_PK (ITEM_ID, STA_DT)
  • 구간은 겹치거나 끊기지 않는다 · 2013년 7월에 걸친 구간은 메뉴마다 5개(7월 1일, 8일, 15일, 22일, 29일에 시작한 주) · 통계 최신 · pga_aggregate_target 512M
  • 바인드 :v_from = '20130701' · :v_to = '20130731'

원본 SQL ​

sql
SELECT i.ITEM_ID,
       SUM(i.ORD_ITEM_QTY)              AS qty,
       SUM(i.ORD_ITEM_QTY * h.SALE_PRC) AS sale_amt
  FROM ORD_ITEM i, ITEM_PRC_HIS h
 WHERE i.ORD_DT BETWEEN :v_from AND :v_to
   AND h.ITEM_ID = i.ITEM_ID
   AND i.ORD_DT BETWEEN h.STA_DT AND h.END_DT
 GROUP BY i.ITEM_ID

실행계획 학습용 예시 ​

xplan
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |     92 |00:00:01.97 |    2785 |           |
|   1 |  HASH GROUP BY                        |              |      1 |     92 |     92 |00:00:01.97 |    2785 | 1201K (0) |
|*  2 |   HASH JOIN                           |              |      1 |   2625 |    105K|00:00:01.92 |    2785 | 1786K (0) |
|   3 |    TABLE ACCESS FULL                  | ITEM_PRC_HIS |      1 |   9568 |   9568 |00:00:00.01 |      60 |           |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |    105K|    105K|00:00:00.05 |    2725 |           |
|*  5 |     INDEX RANGE SCAN                  | ORD_ITEM_X01 |      1 |    105K|    105K|00:00:00.01 |     375 |           |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."ITEM_ID"="I"."ITEM_ID")
       filter(("I"."ORD_DT">="H"."STA_DT" AND "I"."ORD_DT"<="H"."END_DT"))
   5 - access("I"."ORD_DT">=:V_FROM AND "I"."ORD_DT"<=:V_TO)

문제 ​

  1. 두 테이블을 한 번씩만 읽었는데 2번 줄 HASH JOIN에서 2초 가까이 쓰는 까닭을 build 입력의 모양으로 설명하시오.
  2. 결과는 그대로 두고 해시 조인의 일을 줄이도록 SQL을 고치시오. 연간 리포트처럼 기간이 길 때는 어떻게 할지도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자