SQLP 튜닝 연습장

테마

튜닝 연습서브쿼리와 쿼리 변환
SUBQ-024

사내 템플릿대로 힌트를 붙였더니 VIP 고가 주문 조회가 열두 배 느려졌다

중급SELECT한빛유통259 / 700

상황 ​

영업팀의 「VIP 고가 주문」 화면은 A 등급 고객의 한 달 주문 가운데 9만 원 이상 상품이 든 주문을 보여 준다. 지난달 사내 SQL 템플릿에 "필터 서브쿼리에는 NO_UNNEST PUSH_SUBQ를 붙여 먼저 거른다"라는 줄이 들어갔다. 다른 팀의 지역 매장 화면이 이 힌트로 일곱 배 빨라진 뒤에 생긴 규칙이다. 이 화면에도 그대로 붙였더니 0.004초이던 조회가 0.05초가 되었다.

테이블과 인덱스 ​

  • T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_DT에는 인덱스가 없다(한 달 주문이 테이블 전체에 흩어져 있어 전체 스캔이 낫다) · 2024년 6월 주문 4,110건
  • T_ORD_DET 주문상세: 200,000행 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) (높이 2) · 주문당 1~3줄 · 한 주문의 상세는 한 블록에 모여 있다
  • M_CUS 고객: 2,000행 · 30블록 · A 등급 20명 · A 등급 고객의 6월 주문 41건
  • 9만 원 이상 상품이 든 주문은 6월 전체 781건, 그중 A 등급 고객의 주문 8건 · 통계 최신

원본 SQL ​

sql
SELECT o.ORD_SEQ, o.ORD_DT, c.CUS_NM, o.ORD_AMT
  FROM T_ORD o, M_CUS c
 WHERE o.ORD_DT >= :d1
   AND o.ORD_DT <  :d2
   AND c.CUS_ID = o.CUS_ID
   AND c.CUS_GD = 'A'
   AND EXISTS (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 1     -- 사내 템플릿
                 FROM T_ORD_DET d
                WHERE d.ORD_SEQ = o.ORD_SEQ
                  AND d.UNT_PRC >= :prc)

실행계획 학습용 예시 ​

xplan
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |      8 |00:00:00.05 |   13256 |          |
|*  1 |  HASH JOIN                    |              |      1 |      2 |      8 |00:00:00.05 |   13256 | 1214K (0)|
|*  2 |   TABLE ACCESS FULL           | M_CUS        |      1 |     20 |     20 |00:00:00.01 |      30 |          |
|*  3 |   TABLE ACCESS FULL           | T_ORD        |      1 |    206 |    781 |00:00:00.05 |   13226 |          |
|*  4 |    TABLE ACCESS BY INDEX ROWID| T_ORD_DET    |   4110 |      1 |    781 |00:00:00.04 |   12330 |          |
|*  5 |     INDEX RANGE SCAN          | PK_T_ORD_DET |   4110 |      2 |   7809 |00:00:00.01 |    8220 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."CUS_ID"="O"."CUS_ID")
   2 - filter("C"."CUS_GD"='A')
   3 - filter(("O"."ORD_DT">=:D1 AND "O"."ORD_DT"<:D2 AND  EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0
              FROM "T_ORD_DET" "D" WHERE "D"."ORD_SEQ"=:B1 AND "D"."UNT_PRC">=:PRC)))
   4 - filter("D"."UNT_PRC">=:PRC)
   5 - access("D"."ORD_SEQ"=:B1)

문제 ​

  1. 3번 줄의 Buffers 13,226이 어디서 나오는지 4번과 5번 줄의 Starts로 설명하시오. 같은 힌트가 다른 팀 화면에서는 빨라지고 이 화면에서는 느려진 이유도 설명하시오.
  2. 이 화면에 맞게 SQL을 고치고, 서브쿼리를 조인보다 먼저 처리할지 나중에 처리할지 정하는 기준을 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자