JOIN-084

인수받은 판매 마감 패키지의 SQL 넷을 점검하는데 Hint Report가 넷 다 다르게 찍힌다

실전INSERT유통234 / 700

상황

본사 레거시 유통의 판매 마감 패키지 PKG_SLE_DAY를 이번 달부터 튜닝팀이 맡았다. 박팀장은 인수 첫 주에 패키지 안의 힌트를 모두 점검하라고 했다. 힌트가 있는 SQL은 넷이고, 오과장이 운영에서 SQL마다 실행계획과 Hint Report를 떠 왔다. 박팀장의 지시는 이렇다. "SQL마다 힌트가 먹었는지, 먹었다면 맞는지 판정하고, 고칠 것은 고쳐 오세요. 같은 일이 안 생기게 팀 규칙도 같이."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 날짜순 적재 · 하루 약 685건 · 매장 6곳
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 영수증당 평균 2.19줄
SLE_DAY_ITM 일 상품 마감: S1의 적재 대상 · 매일 밤 한 번 적재하고 낮에는 읽기만 한다
패키지는 PUP 계정 소유다. S3만 다른 계정의 프로그램에서 옮겨 와서 테이블 앞에 스키마 이름을 붙여 썼다
통계 최신 · SLE_DT가 VARCHAR2(8)이라 범위 조건은 실제의 4분의 1 안팎으로 추정된다(교재 03화)

원본 SQL

sql

-- [S1] 일 상품 마감 적재 (매일 밤 한 번, 전날 하루)
INSERT /*+ APPEND LEADING(a b) USE_HASH(b) */ INTO SLE_DAY_ITM (SLE_DT, SHP_NO, ITM_NO, SLE_QTY, SLE_AMT)
SELECT a.SLE_DT, a.SHP_NO, b.ITM_NO, SUM(b.SLE_QTY), SUM(b.SLE_QTY * b.SLE_UPRC)
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :d
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SLE_DT, a.SHP_NO, b.ITM_NO;

-- [S2] 매장 일 영수증 상세 (점장 화면, 하루 2천 번)
SELECT /*+ ORDERED USE_HASH(a) LEADING(a b) USE_NL(b) */
       a.SLE_NO, a.SLE_HMS, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF11 b, CF10 a
 WHERE a.SLE_DT = :d
   AND a.SHP_NO = :shp
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_NO, b.ITM_NO;

-- [S3] 주간 상품 판매 (영업팀, 하루 수십 번)
SELECT /*+ LEADING(PUP.CF10 PUP.CF11) USE_HASH(PUP.CF11) FULL(PUP.CF11) */
       PUP.CF11.ITM_NO, SUM(PUP.CF11.SLE_QTY) AS qty
  FROM PUP.CF10, PUP.CF11
 WHERE PUP.CF10.SLE_DT BETWEEN :d1 AND :d2
   AND PUP.CF11.SLE_NO = PUP.CF10.SLE_NO
 GROUP BY PUP.CF11.ITM_NO;

-- [S4] 영수증 한 장 상세 (고객센터, 하루 3천 번)
SELECT /*+ LEADING(a b) USE_NL(b) */
       a.SLE_NO, a.SLE_DT, a.SLE_HMS, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_NO = :sle_no
   AND b.SLE_NO = a.SLE_NO;

실행계획 학습용 예시

xplan

[요약 · 운영 실측]
 SQL  Hint Report                                         계획                                        Buffers    시간
 S1   APPEND 쓰임 · LEADING(a b), USE_HASH(b)는 N         LOAD AS SELECT ← NL (CF10 하루 → CF11_PK)     3,573   0.02초
 S2   ORDERED, USE_HASH(a) 쓰임 · LEADING(a b), USE_NL(b)는 U   HASH JOIN (CF11 FULL이 build)          8,222   0.46초
 S3   LEADING, USE_HASH, FULL 셋 다 N                     NL (CF10 일주일 → CF11_PK)                  24,997   0.19초
 S4   LEADING(a b), USE_NL(b) 쓰임                        NL · 힌트를 뗀 판도 같은 plan hash value          9   0.00초
 (참고) S1의 SELECT만 떼어 USE_HASH(b)로 돌리면 8,222블록

[S1 · Hint Report]
Total hints for statement: 3 (N - Unresolved (2))
   1 -  INS$1
         N -  LEADING(a b)
         N -  USE_HASH(b)
   1 -  INS$1 / SLE_DAY_ITM@INS$1
           -  APPEND

[S2 · Hint Report]
Total hints for statement: 4 (U - Unused (2))
   1 -  SEL$1
         U -  LEADING(a b)
           -  ORDERED
   3 -  SEL$1 / "B"@"SEL$1"
         U -  USE_NL(b)
   4 -  SEL$1 / "A"@"SEL$1"
           -  USE_HASH(a)

[S3 · Hint Report]
Total hints for statement: 3 (N - Unresolved (3))
   1 -  SEL$1
         N -  FULL(PUP.CF11)
         N -  LEADING(PUP.CF10 PUP.CF11)
         N -  USE_HASH(PUP.CF11)

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
[S2 · 매장 1003, 2023-12-24]
|   0 | SELECT STATEMENT                       |                |      1 |        |    251 |00:00:00.46 |    8222 |          |
|   1 |  SORT ORDER BY                         |                |      1 |    251 |    251 |00:00:00.46 |    8222 | 2048  (0)|
|*  2 |   HASH JOIN                            |                |      1 |    251 |    251 |00:00:00.46 |    8222 |   38M (0)|
|   3 |    TABLE ACCESS FULL                   | CF11           |      1 |  1096K |  1096K |00:00:00.11 |    8204 |          |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    114 |    114 |00:00:00.01 |      18 |          |
|*  5 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |    685 |    685 |00:00:00.01 |       4 |          |
[S3 · 2023-12-18 ~ 2023-12-24]
|   0 | SELECT STATEMENT                       |                |      1 |        |    109 |00:00:00.19 |   24997 |          |
|   1 |  HASH GROUP BY                         |                |      1 |    109 |    109 |00:00:00.19 |   24997 | 1210K (0)|
|   2 |   NESTED LOOPS                         |                |      1 |   2626 |  10501 |00:00:00.18 |   24997 |          |
|   3 |    NESTED LOOPS                        |                |      1 |   2626 |  10501 |00:00:00.09 |   14497 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   1199 |   4795 |00:00:00.01 |     112 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |   1199 |   4795 |00:00:00.01 |      14 |          |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |   4795 |      2 |  10501 |00:00:00.06 |   14385 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID         | CF11           |  10501 |      2 |  10501 |00:00:00.08 |   10500 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  [S2]  2 - access("B"."SLE_NO"="A"."SLE_NO")
        4 - filter("A"."SHP_NO"=:SHP)
        5 - access("A"."SLE_DT"=:D)
  [S3]  5 - access("CF10"."SLE_DT">=:D1 AND "CF10"."SLE_DT"<=:D2)
        6 - access("CF11"."SLE_NO"="CF10"."SLE_NO")

문제

네 SQL의 힌트를 Hint Report와 실행계획으로 각각 판정하시오(대상을 못 찾음, 찾았지만 못 씀, 쓰였지만 틀림, 쓰였고 맞음). 판정마다 그 원인을 쓰시오.
고칠 SQL은 고쳐 쓰고, 그대로 두거나 지울 힌트는 그 근거를 숫자로 쓰시오.
같은 일이 되풀이되지 않도록 팀의 힌트 작성 규칙을 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- [S1] 조인 힌트는 지운다 (하루 685건이면 옵티마이저의 NL 이 맞다). APPEND 는 INSERT 뒤가 제자리다
INSERT /*+ APPEND */ INTO SLE_DAY_ITM (SLE_DT, SHP_NO, ITM_NO, SLE_QTY, SLE_AMT)
SELECT a.SLE_DT, a.SHP_NO, b.ITM_NO, SUM(b.SLE_QTY), SUM(b.SLE_QTY * b.SLE_UPRC)
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :d
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SLE_DT, a.SHP_NO, b.ITM_NO;

-- [S2] ORDERED 와 USE_HASH(a) 를 지우고 LEADING 만 남긴다
SELECT /*+ LEADING(a b) USE_NL(b) */
       a.SLE_NO, a.SLE_HMS, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF11 b, CF10 a
 WHERE a.SLE_DT = :d
   AND a.SHP_NO = :shp
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_NO, b.ITM_NO;

-- [S3] 별칭을 붙이고, 힌트에는 스키마 이름을 쓰지 않는다
SELECT /*+ LEADING(a b) USE_HASH(b) FULL(b) */
       b.ITM_NO, SUM(b.SLE_QTY) AS qty
  FROM PUP.CF10 a, PUP.CF11 b
 WHERE a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO;

-- [S4] 힌트를 뗀다 (뗀 판도 같은 plan hash value)
SELECT a.SLE_NO, a.SLE_DT, a.SLE_HMS, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_NO = :sle_no
   AND b.SLE_NO = a.SLE_NO

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

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
[S2 · 고친 뒤]
|   0 | SELECT STATEMENT                       |                |      1 |        |    251 |00:00:00.01 |     611 |          |
|   1 |  SORT ORDER BY                         |                |      1 |    251 |    251 |00:00:00.01 |     611 | 2048  (0)|
|   2 |   NESTED LOOPS                         |                |      1 |    251 |    251 |00:00:00.01 |     611 |          |
|   3 |    NESTED LOOPS                        |                |      1 |    251 |    251 |00:00:00.01 |     360 |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    114 |    114 |00:00:00.01 |      18 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    685 |    685 |00:00:00.01 |       4 |          |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |    114 |      2 |    251 |00:00:00.01 |     342 |          |
|   7 |    TABLE ACCESS BY INDEX ROWID         | CF11           |    251 |      2 |    251 |00:00:00.01 |     251 |          |
[S3 · 고친 뒤]
|   0 | SELECT STATEMENT                       |                |      1 |        |    109 |00:00:00.11 |    8316 |          |
|   1 |  HASH GROUP BY                         |                |      1 |    109 |    109 |00:00:00.11 |    8316 | 1210K (0)|
|*  2 |   HASH JOIN                            |                |      1 |   2626 |  10501 |00:00:00.10 |    8316 | 1390K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |   1199 |   4795 |00:00:00.01 |     112 |          |
|*  4 |     INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |   1199 |   4795 |00:00:00.01 |      14 |          |
|   5 |    TABLE ACCESS FULL                   | CF11           |      1 |  1096K |  1096K |00:00:00.08 |    8204 |          |
------------------------------------------------------------------------------------------------------------------------

모범 답안 서술

S1 (대상을 못 찾음, 그런데 계획은 맞다): Hint Report에서 APPEND만 쓰였고 LEADING(a b)와 USE_HASH(b)는 N(Unresolved)이다. INSERT ... SELECT는 INSERT 문 블록(INS$1)과 SELECT 블록(SEL$1)으로 나뉜다. INSERT 바로 뒤의 힌트는 INS$1에 속하는데, 테이블 a와 b는 SEL$1에 있으므로 INS$1에서는 찾지 못했다. APPEND는 적재 대상에 거는 힌트라 그 자리가 맞다. 그런데 조인 힌트가 버려진 덕분에 계획은 오히려 맞다. 하루 685건이면 옵티마이저가 고른 NL(3,573블록)이 작성자가 의도한 해시(8,222블록)보다 싸다. 힌트를 SELECT 뒤로 옮겨 살리면 2.3배 나빠지므로, 죽은 조인 힌트는 지우고 APPEND만 남긴다. S2 (찾았지만 못 씀, 그래서 틀린 계획): ORDERED와 USE_HASH(a)는 쓰였고 LEADING(a b)와 USE_NL(b)는 U(Unused)다. 두 힌트가 함께 있으면 ORDERED가 이기고 LEADING은 무시된다. 그래서 FROM 절 순서대로 CF11이 먼저 와서 2번 줄 해시 조인의 build가 됐고(110만 줄, 38M), CF11이 첫 테이블이라 NL의 안쪽이 될 수 없으니 USE_NL(b)도 적용될 자리가 없었다. 작년에 LEADING을 더한 개발자가 ORDERED를 지우지 않은 탓이다. ORDERED와 USE_HASH(a)를 지우고 LEADING(a b) USE_NL(b)만 남기면 매장의 그날 영수증 114건마다 CF11_PK를 찾는다. Buffers는 8,222 → 611, 시간은 0.46 → 0.01초이고, 하루 2천 번이면 약 1,644만 블록이 약 122만 블록이 된다. S3 (대상을 못 찾음, 그래서 틀린 계획): 힌트 세 개가 모두 N이다. FROM 절에 PUP.CF10처럼 스키마 이름을 붙여 썼더라도 힌트에는 스키마 이름을 쓰지 않는다. 힌트 안의 PUP.CF11은 "뷰 PUP 안의 테이블 CF11"을 가리키는 표기로 읽혀 대상을 찾지 못했다. 힌트가 버려지자 옵티마이저는 일주일을 1,199건(실제 4,795건)으로 적게 보고 NL을 골라, 6번 줄에서 CF11_PK를 4,795번 찾아갔다(24,997블록). FROM 절에 별칭(a, b)을 붙이고 힌트를 LEADING(a b) USE_HASH(b) FULL(b)로 고치면 해시로 CF11을 한 번 읽는다. Buffers는 24,997 → 8,316이다. S4 (쓰였고 맞음, 하지만 하는 일이 없다): 힌트가 쓰였고 계획도 맞다. 하지만 힌트를 뗀 판도 같은 plan hash value였다. 영수증 한 장이면 옵티마이저도 언제나 NL을 고르므로 이 힌트는 아무 일도 하지 않는다. 지금은 해가 없지만, 쓸모없는 힌트도 SQL이 바뀌면 해로운 힌트가 될 수 있다(JOIN-079). 떼고, 대장에 「힌트 없음, 영수증 한 장」으로 한 줄 올린다. 남겨 두어도 틀린 답은 아니지만 그때도 대장에는 올린다. 팀 규칙: 첫째, 힌트는 FROM 절의 별칭으로 쓴다. 별칭이 없으면 스키마 이름 없이 테이블 이름만 쓴다. 둘째, 조인 순서는 LEADING으로만 적고 ORDERED는 쓰지 않는다. 셋째, INSERT ... SELECT의 조인 힌트는 SELECT 바로 뒤에 쓰고, INSERT 뒤에는 APPEND 같은 적재 힌트만 둔다. 넷째, 힌트를 넣거나 고친 뒤에는 Hint Report에 N이나 U가 없는지, Total hints 수가 넣은 수와 같은지 확인한다. 다섯째, 힌트를 넣기 전에 힌트 없는 판과 plan hash value와 Buffers를 비교하고, 넣는다면 그때 규모와 재검토 조건을 힌트 유효기간 대장에 적는다.

해설

판정의 네 갈래: Hint Report로 "대상을 못 찾음(N)"과 "찾았지만 못 씀(U)"을 먼저 가른다. 쓰인 힌트는 힌트를 뗀 판과 숫자로 비교해야 "쓰였지만 틀림"과 "쓰였고 맞음"이 갈린다. 이 비교 없이 Hint Report만 보면 S1을 고치려다 2.3배 나빠진다.
@블록으로 살리는 길: S1의 조인 힌트를 INSERT 뒤에 둔 채 LEADING(@SEL$1 a b)처럼 블록을 지정해 살릴 수도 있다. 하지만 이 SQL에서는 살릴 이유가 없고, 시스템 블록 이름은 SQL이 바뀌면 밀릴 수 있다(JOIN-079).
APPEND의 대가: 직접 경로 적재는 적재하는 동안 테이블에 배타 잠금을 걸고 HWM 위에 블록을 새로 쓴다. 낮에는 읽기만 하는 마감 테이블이라 괜찮다 (교재 14화).
S3의 일주일: 문자 날짜 범위의 추정이 흐려서 힌트가 필요해진 경우다. 날짜 가상 컬럼으로 추정을 바로잡으면 힌트를 아예 뗄 수 있다 (교재 03화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황