SQLP 튜닝 연습장

테마

튜닝 연습파티션
PART-011

매장 주문 내역 화면이 어떤 때는 바로 뜨고 어떤 때는 한참 걸린다

중급SELECT버거386 / 700

상황 ​

매장 관리자용 「주문 내역」 화면은 매장과 기간(월)을 골라 주문 목록을 내려받는다. 기간을 비워 두면 전체 기간을 조회한다. 하루 약 3,000번 호출되고, 그중 약 40%는 기간을 비운 채 들어온다(화면의 기간 기본값이 빈칸이다). 오과장: "실행계획은 늘 똑같은데 어떤 때는 바로 뜨고 어떤 때는 한참 걸린다."

테이블과 인덱스 ​

  • ORD_RANGE 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312과 빈 P_DEFAULT (MAXVALUE)). 칸 하나를 FULL로 읽으면 약 1,980블록이다.
  • SHOP_NO는 200개다. 매장 하나의 주문은 한 달에 약 205건, 전체 기간에 약 5,000건이다.
  • LOCAL 인덱스 ORD_RANGE_PK (ORD_NO, ORD_YM)과 ORD_RANGE_X01 (ORD_DT, ORD_HMS)는 이 조회에 쓰이지 않는다. SHOP_NO 인덱스는 없다.
  • 아래는 같은 커서(같은 Plan hash value)의 두 실행과 그 커서의 하루 합계다.

원본 SQL ​

sql
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD_RANGE
 WHERE SHOP_NO = :v_shop
   AND ORD_YM BETWEEN NVL(:v_from_ym, '201201') AND NVL(:v_to_ym, '999912')
 ORDER BY ORD_DT, ORD_HMS

실행계획 학습용 예시 · 교재 14화 규모 ​

xplan
[실행 ① — :v_shop = 'SH0042', 기간 '201306' ~ '201306']
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |    207 | 00:00:00.03 |    1980 |        |       |
|   1 |  SORT ORDER BY              |           |      1 |     13 |    207 | 00:00:00.03 |    1980 |        |       |
|*  2 |   FILTER                    |           |      1 |        |    207 | 00:00:00.03 |    1980 |        |       |
|   3 |    PARTITION RANGE ITERATOR |           |      1 |     13 |    207 | 00:00:00.03 |    1980 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | ORD_RANGE |      1 |     13 |    207 | 00:00:00.03 |    1980 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

[실행 ② — 같은 매장, 기간 빈칸 (NULL, NULL)]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |   4986 | 00:00:00.52 |   47460 |        |       |
|   1 |  SORT ORDER BY              |           |      1 |     13 |   4986 | 00:00:00.52 |   47460 |        |       |
|*  2 |   FILTER                    |           |      1 |        |   4986 | 00:00:00.50 |   47460 |        |       |
|   3 |    PARTITION RANGE ITERATOR |           |      1 |     13 |   4986 | 00:00:00.49 |   47460 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL       | ORD_RANGE |     24 |     13 |   4986 | 00:00:00.48 |   47460 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (두 실행 공통):
---------------------------------------------------
   2 - filter(NVL(:V_FROM_YM,'201201')<=NVL(:V_TO_YM,'999912'))
   4 - filter(("SHOP_NO"=:V_SHOP AND "ORD_YM">=NVL(:V_FROM_YM,'201201') AND "ORD_YM"<=NVL(:V_TO_YM,'999912')))

[V$SQL — 이 커서의 하루 합계]
  EXECUTIONS 3,000 · BUFFER_GETS 약 6,052만 (실행당 평균 약 20,170)
    기간을 넣은 실행   약 1,800번 ×  1,980 = 약   356만
    기간을 비운 실행   약 1,200번 × 47,460 = 약 5,695만 (94%)

문제 ​

  1. 같은 실행계획인데 두 실행의 일량이 왜 이렇게 다른지 Pstart, Pstop과 Starts로 설명하시오.
  2. 기간을 비운 조회가 칸을 전부 열지 않도록 SQL과 화면 규칙을 고치고, 하루에 읽는 블록 수가 어떻게 바뀌는지 어림하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자