SQLP 튜닝 연습장

테마

튜닝 연습조인 튜닝
JOIN-075

송장 몇 장을 묶어 다시 뽑는 API가 주문상세 20만 줄을 통째로 읽는다

기초SELECT한빛유통225 / 700

상황 ​

물류센터의 「송장 재출력」 API는 단말이 주문 번호를 최대 10건까지 묶어 보내면, 그 주문들의 상품 줄을 한 번에 돌려준다. 주문 번호 목록은 컬렉션(SYS.ODCINUMBERLIST) 하나로 바인드한다. 단말 40대가 하루 8천 번쯤 호출한다. 처음에 해시 조인이 나와서 개발자가 힌트를 달았다. 개발자는 "USE_NL(x o)로 컬렉션과 주문을 NL로 묶었으니 주문상세도 그 흐름을 따라 NL로 붙을 것"이라고 설명한다. 아래는 주문 3건을 넘긴 실행계획이다.

테이블과 인덱스 ​

  • T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) (BLEVEL 1)
  • T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) (BLEVEL 1) · 주문당 1 ~ 3건(평균 2)
  • 통계 최신. 컬렉션에는 통계가 없어서 옵티마이저는 TABLE() 컬렉션을 8,168행으로 본다(블록 크기 8KB일 때의 기본값)

원본 SQL ​

sql
SELECT /*+ LEADING(x o d) USE_NL(x o) */
       o.ORD_SEQ, o.CUS_ID, o.ORD_DT, d.ORD_DET_NO, d.ITM_ID, d.ORD_QTY
  FROM TABLE(:ord_list) x, T_ORD o, T_ORD_DET d
 WHERE o.ORD_SEQ = x.COLUMN_VALUE
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_SEQ, d.ORD_DET_NO

실행계획 학습용 예시 ​

xplan
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |           |      1 |        |      6 |00:00:00.03 |     905 |          |
|   1 |  SORT ORDER BY                        |           |      1 |  16336 |      6 |00:00:00.03 |     905 | 2048  (0)|
|*  2 |   HASH JOIN                           |           |      1 |  16336 |      6 |00:00:00.03 |     905 | 1254K (0)|
|   3 |    NESTED LOOPS                       |           |      1 |   8168 |      3 |00:00:00.01 |       9 |          |
|   4 |     NESTED LOOPS                      |           |      1 |   8168 |      3 |00:00:00.01 |       6 |          |
|   5 |      COLLECTION ITERATOR PICKLER FETCH|           |      1 |   8168 |      3 |00:00:00.01 |       0 |          |
|*  6 |      INDEX UNIQUE SCAN                | PK_T_ORD  |      3 |      1 |      3 |00:00:00.01 |       6 |          |
|   7 |     TABLE ACCESS BY INDEX ROWID       | T_ORD     |      3 |      1 |      3 |00:00:00.01 |       3 |          |
|   8 |    TABLE ACCESS FULL                  | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     896 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   6 - access("O"."ORD_SEQ"=VALUE(KOKBF$))

문제 ​

  1. 힌트를 달았는데도 2번 줄이 해시 조인이고 T_ORD_DET을 FULL로 읽는 이유를, USE_NL 힌트의 괄호 안 테이블이 무엇을 뜻하는지로 설명하시오. x를 괄호 안에 적은 것이 무슨 효과가 있었는지도 쓰시오.
  2. 세 테이블이 모두 NL로 붙도록 힌트를 고치시오. 옵티마이저가 주문상세를 해시로 붙인 까닭도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자