SQLP 튜닝 연습장

테마

튜닝 연습조인 튜닝
JOIN-077

오픈 첫 달에 해시로 고정한 「일 매장 판매 현황」이 하루치를 보는데 주문상품 247만 줄을 읽는다

중급SELECT버거227 / 700

상황 ​

본사 상황실의 「일 매장 판매 현황」은 날짜 하나를 고르면 매장 200곳마다 그날 판매 줄 수와 판매 수량을 보여 준다. 상황판이 5분마다 오늘 날짜로 새로 고치고 영업팀이 지난 날짜를 골라 보기도 해서, 하루 400번쯤 돈다. SQL 위에는 -- 2012.02 측정: NL 0.09초 / 해시 0.03초 → 해시로 고정이라는 주석이 있다. 오픈 한 달째에 만든 화면이다. 지금은 한 번에 0.8초가 걸리고, AWR의 논리 읽기 상위 5위 안에 있다. 아래는 2013-06-14로 조회한 실행계획이다.

테이블과 인덱스 ​

  • ORD 주문: 1,000,000행 · 22,422블록 · ORD_X01 (ORD_DT, ORD_HMS) · 날짜순 적재 · 하루 약 1,370건(2013-06-14는 1,368건)
  • ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 주문당 평균 2.47줄 · 주문 순서로 적재
  • 2012년 2월에 잴 때 ORD_ITEM은 오픈 첫 달 치 약 10만 줄(약 2,300블록)이었고, 하루 주문은 그때도 1,300건 안팎이었다
  • 통계 최신

원본 SQL ​

sql
SELECT /*+ LEADING(o i) USE_HASH(i) FULL(i) */
       o.SHOP_NO, COUNT(*) AS line_cnt, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT = :dt
   AND i.ORD_NO = o.ORD_NO
 GROUP BY o.SHOP_NO

실행계획 학습용 예시 ​

xplan
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |    200 |00:00:00.82 |   55301 |          |
|   1 |  HASH GROUP BY                        |          |      1 |    200 |    200 |00:00:00.82 |   55301 | 1480K (0)|
|*  2 |   HASH JOIN                           |          |      1 |   3380 |   3379 |00:00:00.81 |   55301 | 1326K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD      |      1 |   1370 |   1368 |00:00:00.01 |      36 |          |
|*  4 |     INDEX RANGE SCAN                  | ORD_X01  |      1 |   1370 |   1368 |00:00:00.01 |       6 |          |
|   5 |    TABLE ACCESS FULL                  | ORD_ITEM |      1 |  2472K |  2472K |00:00:00.52 |   55265 |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ORD_NO"="O"."ORD_NO")
   4 - access("O"."ORD_DT"=:DT)

문제 ​

  1. 2012년 2월의 측정이 왜 옳았는지, 같은 힌트가 지금은 왜 틀렸는지 NL과 해시의 비용이 각각 무엇에 비례하는지로 설명하시오.
  2. SQL을 고치고, 힌트 유효기간 대장에 남길 한 줄의 「그때의 규모」, 「현재 규모」, 「판정」, 「다음 재검토 조건」을 숫자로 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자