JOIN-076

해시 조인 힌트를 넣었다는 월 정산 대사가 여전히 주문을 4,170번 찾아간다

기초SELECT한빛유통226 / 700

상황

정산팀의 「월 정산 대사」는 한 달 치 정산마다 주문을 붙여 정산 금액이나 고객이 주문과 다른 건을 찾는다. 2019년에 「일 정산 대사」로 만들 때 오과장이 힌트를 달았고, 올해 5월에 화면이 한 달 단위로 바뀌었다. 조민우는 한 달이면 해시가 맞다고 보고 힌트를 고쳤다. 팀 규칙이 "옛 힌트는 지우지 말고 바로 아래에 새 힌트를 덧붙여 이력을 남긴다"여서 그대로 따랐다. 그런데 계획은 여전히 NL이다. 조민우는 "힌트를 무시하는 버그 아니냐"라고 묻는다. 아래는 2024년 6월로 돌린 실행계획이다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · 인덱스 없음 · 2024년 6월 정산 4,170행
T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) (BLEVEL 1)
정산과 주문은 ORD_SEQ로 1:1이다 · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(s o) USE_NL(o) */            -- 2019.03 일 대사 (오과장)
       /*+ LEADING(s o) USE_HASH(o) FULL(o) */  -- 2024.05 월 대사로 바뀌어 해시로 (조민우)
       s.STLM_SEQ, s.ORD_SEQ, s.STLM_DT, s.STLM_AMT, o.ORD_AMT, o.PAY_AMT
  FROM T_ORD_STLM s, T_ORD o
 WHERE s.STLM_DT >= TO_DATE(:v_ym || '01', 'YYYYMMDD')
   AND s.STLM_DT <  ADD_MONTHS(TO_DATE(:v_ym || '01', 'YYYYMMDD'), 1)
   AND o.ORD_SEQ = s.ORD_SEQ
   AND (o.ORD_AMT <> s.STLM_AMT OR o.CUS_ID <> s.CUS_ID)

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |      3 |00:00:00.04 |   13149 |
|   1 |  NESTED LOOPS                |            |      1 |   4170 |      3 |00:00:00.04 |   13149 |
|   2 |   NESTED LOOPS               |            |      1 |   4170 |   4170 |00:00:00.02 |    8979 |
|*  3 |    TABLE ACCESS FULL         | T_ORD_STLM |      1 |   4170 |   4170 |00:00:00.01 |     640 |
|*  4 |    INDEX UNIQUE SCAN         | PK_T_ORD   |   4170 |      1 |   4170 |00:00:00.01 |    8339 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T_ORD      |   4170 |      1 |      3 |00:00:00.02 |    4170 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("S"."STLM_DT">=TO_DATE(:V_YM||'01','YYYYMMDD') AND
              "S"."STLM_DT"<ADD_MONTHS(TO_DATE(:V_YM||'01','YYYYMMDD'),1)))
   4 - access("O"."ORD_SEQ"="S"."ORD_SEQ")
   5 - filter(("O"."ORD_AMT"<>"S"."STLM_AMT" OR "O"."CUS_ID"<>"S"."CUS_ID"))

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

   1 -  SEL$1
         -  LEADING(s o)

   4 -  SEL$1 / "O"@"SEL$1"
         -  USE_NL(o)

문제

새 힌트가 계획에 반영되지 않은 이유를 Hint Report로 설명하시오. 지금 계획이 4번과 5번 줄에서 하는 일을 Starts로 쓰시오.
월 대사에 맞게 SQL을 고치고, 바뀐 판단을 어디에 어떻게 기록할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT /*+ LEADING(s o) USE_HASH(o) FULL(o) */
       s.STLM_SEQ, s.ORD_SEQ, s.STLM_DT, s.STLM_AMT, o.ORD_AMT, o.PAY_AMT
  FROM T_ORD_STLM s, T_ORD o
 WHERE s.STLM_DT >= TO_DATE(:v_ym || '01', 'YYYYMMDD')
   AND s.STLM_DT <  ADD_MONTHS(TO_DATE(:v_ym || '01', 'YYYYMMDD'), 1)
   AND o.ORD_SEQ = s.ORD_SEQ
   AND (o.ORD_AMT <> s.STLM_AMT OR o.CUS_ID <> s.CUS_ID)
-- 힌트로 읽히는 주석은 SELECT 바로 뒤의 하나뿐이다. 옛 힌트의 이력은 소스가 아니라 힌트 유효기간 대장에 남긴다

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

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |      3 |00:00:00.01 |    1536 |          |
|*  1 |  HASH JOIN         |            |      1 |   4170 |      3 |00:00:00.01 |    1536 | 1390K (0)|
|*  2 |   TABLE ACCESS FULL| T_ORD_STLM |      1 |   4170 |   4170 |00:00:00.01 |     640 |          |
|   3 |   TABLE ACCESS FULL| T_ORD      |      1 |    100K|    100K|00:00:00.01 |     896 |          |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("O"."ORD_SEQ"="S"."ORD_SEQ")
       filter(("O"."ORD_AMT"<>"S"."STLM_AMT" OR "O"."CUS_ID"<>"S"."CUS_ID"))
   2 - filter(("S"."STLM_DT">=TO_DATE(:V_YM||'01','YYYYMMDD') AND
              "S"."STLM_DT"<ADD_MONTHS(TO_DATE(:V_YM||'01','YYYYMMDD'),1)))

모범 답안 서술

Hint Report의 Total hints for statement: 2가 증거다. 오라클이 힌트로 읽은 것은 첫 주석의 LEADING(s o)와 USE_NL(o) 두 개뿐이고, 둘 다 쓰였다(앞에 코드 없이 찍힌 줄). 한 문장 블록에서 힌트로 읽히는 주석은 SELECT 바로 뒤의 첫 /*+ ... */ 하나뿐이다. 그 뒤에 덧붙인 두 번째 /*+ ... */는 힌트가 아니라 평범한 주석으로 버려지므로, USE_HASH(o)와 FULL(o)는 Hint Report에 U로도 찍히지 않는다. 버그가 아니라 정해진 동작이다. 그래서 2019년 일 대사의 힌트가 그대로 돌았다. 3번 줄에서 6월 정산 4,170건을 읽고, 4번 줄에서 정산 한 건마다 PK_T_ORD를 찾아가고(Starts 4,170), 5번 줄에서 주문 행을 읽어 금액과 고객을 비교한다. 하루 137건이던 일 대사에서는 문제없던 왕복이 한 달 4,170건으로 30배 늘었다. 힌트를 주석 하나로 합쳐 LEADING(s o) USE_HASH(o) FULL(o)로 고친다. 정산 4,170건으로 해시 테이블을 만들고 주문을 한 번 FULL로 읽으며 대어 본다. Buffers는 13,149 → 1,536으로 준다. 힌트를 모두 떼도 옵티마이저는 같은 해시를 고른다. 옛 힌트를 소스에 남기는 규칙은 이번처럼 소스와 실제 동작이 어긋나게 한다. 이력은 힌트 유효기간 대장에 남긴다. 2019년 줄은 판정을 「폐기」로 닫고(그때 규모 하루 137건), 새 줄에는 그때 규모(한 달 4,170건)와 재검토 조건(정산이 한 달 1만 건을 넘거나 대사 기간이 다시 바뀔 때)을 숫자로 적는다.

해설

힌트 주석의 자리: 힌트는 SELECT, INSERT, UPDATE, DELETE, MERGE 키워드 바로 뒤의 첫 주석에만 쓸 수 있다. /*와 + 사이에 공백이 들어간 /* + ... */도 힌트가 아니다. 둘 다 오류 없이 무시된다.
고친 사람이 확인할 것: 힌트를 바꾼 뒤에는 Hint Report의 Total hints 수가 넣은 힌트 수와 같은지 본다. 이번처럼 수가 모자라면 힌트가 아예 읽히지 않은 것이다.
NL이 맞는 기간: 해시의 상수항은 T_ORD 896블록이고 NL은 정산 한 건에 약 3블록이다. 대략 300건 아래(이틀 치 안팎)라면 NL이 이긴다. 일 대사를 따로 남긴다면 그 SQL에는 NL이 맞다 (교재 06화 §5).
대장에 올리는 줄은 힌트 하나당 한 줄이다. LEADING(s o)는 유지, USE_NL(o)는 폐기, USE_HASH(o)와 FULL(o)는 새로 올린다 (교재 06화 §8).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황