SQLP 튜닝 연습장

테마

튜닝 연습조인 튜닝
JOIN-074

조인을 ANSI 문법으로 바꾸는 도구를 돌린 뒤 점주 앱 「오늘 주문 상세」가 한 번에 14초씩 걸린다

기초SELECT버거224 / 700

상황 ​

점주 앱의 「오늘 주문 상세」는 매장 하나의 그날 주문을 시각순으로, 주문마다 상품명과 수량을 붙여 보여 준다. 점주 200명이 수시로 새로 고쳐서 하루 2만 번쯤 호출된다. 지난주 개발팀은 SQL을 ANSI 조인 문법으로 통일하는 변환 도구를 주문 모듈 전체에 돌렸다. 도구는 WHERE 절 첫 조인 조건의 왼쪽 테이블을 FROM 맨 앞에 두고, 나머지 조인 조건을 JOIN ... ON으로 옮긴다. 힌트는 건드리지 않았다. 그 뒤로 이 화면이 한 번에 14초씩 걸린다. 아래는 변환 전후의 SQL과, 2013-11-08 매장 SH0042(주문 6건, 주문상품 15줄)로 조회한 지금의 계획이다.

테이블과 인덱스 ​

  • ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS) · 매장 하나의 하루 주문은 평균 7건
  • ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 주문당 평균 2.47줄
  • ITEM 상품: 92행 · 5블록 · ITEM_PK (ITEM_ID)
  • 통계 최신. 변환 전 계획은 ORD_X02 → ORD_ITEM_PK → ITEM_PK 순서의 NL이었고, 같은 매장과 날짜로 56블록 · 0.01초였다

원본 SQL ​

sql
-- [변환 전]
SELECT /*+ ORDERED USE_NL(o i t) */
       o.ORD_HMS, o.ORD_NO, t.ITEM_NM, i.ORD_ITEM_QTY
  FROM ORD o, ORD_ITEM i, ITEM t
 WHERE i.ORD_NO  = o.ORD_NO
   AND t.ITEM_ID = i.ITEM_ID
   AND o.SHOP_NO = :shop
   AND o.ORD_DT  = :dt
 ORDER BY o.ORD_HMS, o.ORD_NO;

-- [변환 후 · 지금 운영]
SELECT /*+ ORDERED USE_NL(o i t) */
       o.ORD_HMS, o.ORD_NO, t.ITEM_NM, i.ORD_ITEM_QTY
  FROM ORD_ITEM i
  JOIN ORD o  ON o.ORD_NO  = i.ORD_NO
  JOIN ITEM t ON t.ITEM_ID = i.ITEM_ID
 WHERE o.SHOP_NO = :shop
   AND o.ORD_DT  = :dt
 ORDER BY o.ORD_HMS, o.ORD_NO;

실행계획 학습용 예시 ​

xplan
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |     15 |00:00:14.21 |   7473K |          |
|   1 |  SORT ORDER BY                  |          |      1 |     17 |     15 |00:00:14.21 |   7473K | 2048  (0)|
|   2 |   NESTED LOOPS                  |          |      1 |     17 |     15 |00:00:14.21 |   7473K |          |
|   3 |    NESTED LOOPS                 |          |      1 |     17 |     15 |00:00:14.21 |   7473K |          |
|   4 |     NESTED LOOPS                |          |      1 |     17 |     15 |00:00:14.21 |   7473K |          |
|   5 |      TABLE ACCESS FULL          | ORD_ITEM |      1 |  2472K |  2472K |00:00:00.58 |   55265 |          |
|*  6 |      TABLE ACCESS BY INDEX ROWID| ORD      |  2472K |      1 |     15 |00:00:13.20 |   7418K |          |
|*  7 |       INDEX UNIQUE SCAN         | ORD_PK   |  2472K |      1 |  2472K |00:00:04.60 |   4945K |          |
|*  8 |     INDEX UNIQUE SCAN           | ITEM_PK  |     15 |      1 |     15 |00:00:00.01 |       2 |          |
|   9 |    TABLE ACCESS BY INDEX ROWID  | ITEM     |     15 |      1 |     15 |00:00:00.01 |      15 |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("O"."SHOP_NO"=:SHOP AND "O"."ORD_DT"=:DT))
   7 - access("O"."ORD_NO"="I"."ORD_NO")
   8 - access("T"."ITEM_ID"="I"."ITEM_ID")

문제 ​

  1. 변환 뒤 계획이 바뀐 이유를 ORDERED 힌트가 조인 순서를 정하는 방식으로 설명하시오. 6번과 7번 줄의 Starts가 무엇을 뜻하는지도 쓰시오.
  2. ANSI 문법은 그대로 두고, FROM 절의 테이블 순서가 어떻게 바뀌어도 같은 계획이 되도록 힌트를 고치시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자