JOIN-082

리포트 SQL 일괄 점검을 마친 다음 날 「일 상품 판매 리포트」가 0.01초에서 1.4초가 됐다

중급SELECT한빛유통232 / 700

상황

지난주 DBA팀이 "대량 리포트는 인덱스 랜덤 액세스를 피한다"는 기준으로 리포트 SQL 40여 개를 일괄 점검했다. 주문상세를 읽는 SQL에는 FULL(d) 힌트를 더했고, 기존 힌트는 건드리지 않았다. 그다음 날부터 영업팀의 「일 상품 판매 리포트」가 1.4초씩 걸린다. 이 리포트는 날짜 하나를 고르면 그날 주문의 상품별 판매 수량과 금액을 낸다. 영업 담당 30명이 날짜를 바꿔 가며 하루 500번쯤 돌린다. 아래는 2024-06-14(주문 137건)의 실행계획이다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · IX_T_ORD_DT (ORD_DT) · 하루 약 137건(같은 날 주문은 테이블 곳곳에 흩어져 있다)
T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) (BLEVEL 1) · 주문당 1 ~ 3건(평균 2)
M_ITM 상품: 100행 · 5블록 · PK_M_ITM (ITM_ID)
통계 최신

원본 SQL

sql

SELECT /*+ LEADING(o d m) USE_NL(d) USE_NL(m) FULL(d) */
       m.ITM_ID, m.ITM_NM, SUM(d.ORD_QTY) AS qty, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM T_ORD o, T_ORD_DET d, M_ITM m
 WHERE o.ORD_DT >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_dt, 'YYYYMMDD') + 1
   AND d.ORD_SEQ = o.ORD_SEQ
   AND m.ITM_ID  = d.ITM_ID
 GROUP BY m.ITM_ID, m.ITM_NM

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |      1 |        |     96 |00:00:01.38 |  123168 |          |
|   1 |  HASH GROUP BY                         |             |      1 |    100 |     96 |00:00:01.38 |  123168 | 1290K (0)|
|   2 |   NESTED LOOPS                         |             |      1 |    274 |    275 |00:00:01.38 |  123168 |          |
|   3 |    NESTED LOOPS                        |             |      1 |    274 |    275 |00:00:01.37 |  122893 |          |
|   4 |     NESTED LOOPS                       |             |      1 |    274 |    275 |00:00:01.37 |  122891 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD      |      1 |    137 |    137 |00:00:00.01 |     139 |          |
|*  6 |       INDEX RANGE SCAN                 | IX_T_ORD_DT |      1 |    137 |    137 |00:00:00.01 |       2 |          |
|*  7 |      TABLE ACCESS FULL                 | T_ORD_DET   |    137 |      2 |    275 |00:00:01.36 |  122752 |          |
|*  8 |     INDEX UNIQUE SCAN                  | PK_M_ITM    |    275 |      1 |    275 |00:00:00.01 |       2 |          |
|   9 |    TABLE ACCESS BY INDEX ROWID         | M_ITM       |    275 |      1 |    275 |00:00:00.01 |     275 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("O"."ORD_DT">=TO_DATE(:V_DT,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_DT,'YYYYMMDD')+1)
   7 - filter("D"."ORD_SEQ"="O"."ORD_SEQ")
   8 - access("M"."ITM_ID"="D"."ITM_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------

   1 -  SEL$1
         -  LEADING(o d m)

   7 -  SEL$1 / "D"@"SEL$1"
         -  FULL(d)
         -  USE_NL(d)

   8 -  SEL$1 / "M"@"SEL$1"
         -  USE_NL(m)

문제

7번 줄의 Starts와 Predicate Information으로 지금 계획이 무엇을 하는지 설명하고, 힌트 네 개가 어떻게 겹쳐서 이 계획이 됐는지 쓰시오.
이 리포트에 맞게 SQL을 고치시오. FULL(d)를 꼭 살려야 한다면 함께 바꿔야 할 힌트는 무엇이며, 그 계획의 일량은 얼마인지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

SELECT /*+ LEADING(o d m) USE_NL(d) */
       m.ITM_ID, m.ITM_NM, SUM(d.ORD_QTY) AS qty, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM T_ORD o, T_ORD_DET d, M_ITM m
 WHERE o.ORD_DT >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_dt, 'YYYYMMDD') + 1
   AND d.ORD_SEQ = o.ORD_SEQ
   AND m.ITM_ID  = d.ITM_ID
 GROUP BY m.ITM_ID, m.ITM_NM;

-- FULL(d)를 꼭 살려야 한다면 짝을 맞춘다. FULL 은 해시와 짝이다 (Buffers 약 1,040)
SELECT /*+ LEADING(o d m) USE_HASH(d) FULL(d) */
       m.ITM_ID, m.ITM_NM, SUM(d.ORD_QTY) AS qty, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM T_ORD o, T_ORD_DET d, M_ITM m
 WHERE o.ORD_DT >= TO_DATE(:v_dt, 'YYYYMMDD')
   AND o.ORD_DT <  TO_DATE(:v_dt, 'YYYYMMDD') + 1
   AND d.ORD_SEQ = o.ORD_SEQ
   AND m.ITM_ID  = d.ITM_ID
 GROUP BY m.ITM_ID, m.ITM_NM

개선 후 실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |      1 |        |     96 |00:00:00.01 |     693 |          |
|   1 |  HASH GROUP BY                         |              |      1 |    100 |     96 |00:00:00.01 |     693 | 1290K (0)|
|*  2 |   HASH JOIN                            |              |      1 |    274 |    275 |00:00:00.01 |     693 | 1380K (0)|
|   3 |    TABLE ACCESS FULL                   | M_ITM        |      1 |    100 |    100 |00:00:00.01 |       5 |          |
|   4 |    NESTED LOOPS                        |              |      1 |    274 |    275 |00:00:00.01 |     688 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD        |      1 |    137 |    137 |00:00:00.01 |     139 |          |
|*  6 |      INDEX RANGE SCAN                  | IX_T_ORD_DT  |      1 |    137 |    137 |00:00:00.01 |       2 |          |
|   7 |     TABLE ACCESS BY INDEX ROWID        | T_ORD_DET    |    137 |      2 |    275 |00:00:00.01 |     549 |          |
|*  8 |      INDEX RANGE SCAN                  | PK_T_ORD_DET |    137 |      2 |    275 |00:00:00.01 |     274 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("M"."ITM_ID"="D"."ITM_ID")
   6 - access("O"."ORD_DT">=TO_DATE(:V_DT,'YYYYMMDD') AND "O"."ORD_DT"<TO_DATE(:V_DT,'YYYYMMDD')+1)
   8 - access("D"."ORD_SEQ"="O"."ORD_SEQ")

모범 답안 서술

7번 줄은 T_ORD_DET 전체 스캔인데 Starts가 137이다. 그날 주문 137건마다 주문상세 20만 행(896블록)을 처음부터 끝까지 읽은 것이다. Predicate Information에서도 7번 줄의 조인 조건 "D"."ORD_SEQ"="O"."ORD_SEQ"가 access가 아니라 filter다. 인덱스로 찾지 않고 다 읽으면서 맞는 행을 골랐다는 뜻이다. 137 × 896 = 약 12만 3천 블록이다. 힌트는 계획 후보에서 나머지를 지우는 지시라서, 여러 개를 주면 모두를 만족하는 계획만 남는다. LEADING(o d m)이 순서를, USE_NL(d)가 주문상세를 NL의 안쪽으로 붙이라는 것을, FULL(d)가 주문상세를 전체 스캔으로 읽으라는 것을 정했다. 셋을 함께 만족하는 계획은 "주문 한 건마다 주문상세를 FULL로 읽는 NL" 하나뿐이다. DBA팀은 FULL(d)만 더했지만 기존의 USE_NL(d)와 겹치면서 이 계획이 남았다. Hint Report에도 네 힌트가 모두 쓰인 것으로 찍혀 있다. 하루 137건짜리 리포트에는 NL이 맞다. FULL(d)를 떼면 주문 한 건마다 PK_T_ORD_DET로 주문상세 2건 남짓을 찾는다. USE_NL(m)도 뗐다. 상품은 5블록이라 옵티마이저가 해시로 붙인다. Buffers는 123,168 → 693, 시간은 1.38 → 0.01초로 준다. 하루 500번이면 약 6,158만 블록이 약 35만 블록이 된다. FULL(d)를 꼭 살려야 한다면 짝을 맞춰 USE_NL(d)를 USE_HASH(d)로 바꾼다. 그날 주문 137건으로 해시 테이블을 만들고 주문상세를 한 번만 FULL로 읽으므로 약 1,040블록이다. 다만 하루치에는 NL(693블록)이 더 싸다. FULL과 해시는 한 달 이상을 읽는 리포트에 맞는 짝이다.

해설

힌트는 짝이 맞아야 한다: USE_NL은 안쪽 테이블을 인덱스로 찾을 때 의미가 있고, FULL은 한 번만 읽는 해시 조인이나 소트 머지와 짝이다. 일괄로 힌트를 더할 때는 같은 테이블에 걸린 기존 힌트와 겹쳐서 무엇이 남는지 봐야 한다 (교재 06화 §7).
일괄 기준의 함정: "대량 리포트는 FULL"은 한 달 이상을 읽는 리포트에는 맞다. 하루치처럼 작은 범위를 읽는 리포트는 이름만 리포트일 뿐 화면에 가깝다. 기준을 적용하기 전에 SQL마다 읽는 범위를 본다 (JOIN-014와 같은 함정).
분기점: 이 조인에서 해시의 상수항은 주문상세 896블록이고 NL은 주문 한 건에 약 4블록이다. 주문 약 220건(이틀 치 안팎) 아래면 NL이 이긴다. 기간을 고를 수 있게 바뀌면 다시 잰다.
일괄 점검 뒤에는 바뀐 SQL마다 Starts가 1이 아닌 TABLE ACCESS FULL 줄이 생겼는지 찾아본다. 이 모양은 거의 언제나 사고다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황