잘못 박힌 Hash Join 힌트로 만든 44배 차이
한 프랜차이즈의 일본법인 POS 부팅 쿼리. USE_HASH 힌트 8개로 떡칠된 SQL 에서 단 한 줄 USE_HASH(PST PRM) 만 제거하여 옵티마이저를 NL chain + INDEX UNIQUE SCAN 으로 유도. Cost 47% 절감, 실행시간 2.23초 → 0.05초 (44.6배), Physical Reads 3,516 → 0.
한 프랜차이즈의 일본법인 POS 부팅 쿼리 실측 사례입니다. 본문 변환은 회사 코드를 전부 마스킹했습니다.
들어가며
매장이 오픈 준비를 할 때 POS 단말은 부팅 직후 그 매장의 메뉴와 진행 중인 프로모션 정보를 한 번에 끌어옵니다. 그 한 번의 쿼리가 단말당 2.23초가 걸리고 있었습니다. 단말 한 대만 보면 2초가 큰 시간은 아닙니다. 하지만 매장 하나에 단말이 5대 있고, 일본 전역의 매장이 일제히 오픈 준비를 들어가면 그 2.23초 × 수백 단말이 동시에 DB 세션을 점유하는 모양이 됩니다.
쿼리 자체는 간단하지 않습니다. 프로모션 마스터, 프로모션 메뉴 매핑, 프로모션 적용 매장, 프로모션 채널 네 테이블로 만든 inline view 가 있고, 그 위에 메뉴 가격을 합치는 또 다른 inline view, 그 위에 옵션·세트 정보를 붙이는 outer SELECT 가 얹혀 있습니다.
문제 영역만 따로 떼서 보면 답은 한 줄이었습니다. 개발자가 직접 박아둔 USE_HASH(PST PRM) 한 줄을 빼니까 끝이었습니다. 그 한 줄 제거로 cost 4,942 → 2,617 (47% ↓), 실행시간 2.23초 → 0.05초 (44.6배 향상), physical reads 3,516 → 0 으로 줄었습니다.
이 글은 그 한 줄이 어떻게 그렇게 큰 차이를 만들 수 있었는지, 옵티마이저가 무엇을 못 보고 있었는지, 그리고 왜 hint 를 “더 박는” 게 아니라 “잘못 박힌 것을 빼는” 게 정답이었는지에 대해서 기록하고자 합니다.
“Hash Join 은 대량 처리에 빠르다?”
DBA 가 처음 접하는 조인 알고리즘 교과서는 보통 이렇게 설명합니다. NL Join 은 한 쪽이 작을 때 좋고, Hash Join 은 양쪽이 클 때 좋고, Merge Join 은 양쪽이 이미 정렬돼 있을 때 좋다. 이 룰을 그대로 받아 들이면 “대량 데이터를 다루는 시스템이니까 일단 hash 를 박자” 라는 결론이 너무 쉽게 나옵니다.
원본 쿼리에 박혀 있던 hint 는 정확히 그 결론의 결과물처럼 보였습니다. VW_PROMO inline view 안에 USE_HASH(PMN PRM) 와 USE_HASH(PST PRM) 두 개, VW_MENU_UPC inline view 안에 USE_HASH(B A) 한 개, outer SELECT 에는 USE_HASH( B A) USE_HASH(C A) USE_HASH(D A) USE_HASH(E A) USE_HASH(F A) 다섯 개, 총 여덟 개의 USE_HASH 힌트가 거의 모든 조인에 박혀 있었습니다.
Hash Join 활용은 두 가지를 전제해야 한다고 생각합니다. 첫째, build 쪽(보통 작은 쪽) 이 메모리에 올라갈 수 있을 만큼 충분히 작아야 합니다. 둘째, probe 쪽(보통 큰 쪽) 이 build 쪽보다 훨씬 커서 정렬·인덱스 탐색 비용보다 hash table 직접 lookup 이 압도적으로 싸야 합니다. 이 두 전제가 깨지는 케이스를 생각해 봅시다. 예를 들어 build 쪽 row 가 한 자릿수이고, 자식 쪽에는 PK 가 잘 정의되어 있는 경우입니다. 이런 상황에서는 Hash Join 의 hash table 빌드 비용 자체가 NL 의 PK lookup 9번보다 훨씬 비쌉니다.
여덟 개 중 한 개의 hint 가 정확히 그 함정에 빠져 있었습니다. USE_HASH(PST PRM) 였습니다.
원본 쿼리 일부
쿼리 시작 부분입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
WITH VW_PROMO AS (
SELECT /*+ USE_HASH(PMN PRM) USE_HASH(PST PRM) gather_plan_statistics dev_001 */
PRM.CMP_CD, PRM.SALES_ORG_CD, PMN.MENU_CD, PMN.KIOSK_MENU_NM,
PRM.KIOSK_DCPRC_DISP_YN, PST.STOR_CD,
ROW_NUMBER() OVER (
PARTITION BY PRM.CMP_CD, PRM.SALES_ORG_CD, PMN.MENU_CD, PST.STOR_CD
ORDER BY PRM.PRRTY
) RN,
PMN.PRM_DC_TYPE, PMN.DC_RATE_AMT, PRM.UPD_DATE
FROM RIMS.PRM_MST PRM
INNER JOIN RIMS.PRM_MENU_MNG PMN
ON PMN.CMP_CD = PRM.CMP_CD
AND PMN.SALES_ORG_CD = PRM.SALES_ORG_CD
AND PMN.PRM_CD = PRM.PRM_CD
AND PMN.USE_YN = '1'
INNER JOIN PRM_APPLY_STOR PST
ON PST.CMP_CD = PRM.CMP_CD
AND PST.SALES_ORG_CD = PRM.SALES_ORG_CD
AND PST.PRM_CD = PRM.PRM_CD
AND PST.USE_YN = '1'
INNER JOIN PRM_JOIN_CHL CHL
ON CHL.CMP_CD = PRM.CMP_CD
AND CHL.SALES_ORG_CD = PRM.SALES_ORG_CD
AND CHL.PRM_CD = PRM.PRM_CD
AND CHL.JOIN_CHL_CD = '02'
WHERE PRM.USE_YN = '1'
AND PRM.PRGRS_STATUS = 'C'
AND PRM.BFPSN_CPN_YN = '0'
AND PRM.PRC_CLASS = '1'
AND TO_CHAR(RIMS.FN_GET_SYSDATE('XXX'), 'YYYYMMDD')
BETWEEN PRM.START_DT AND PRM.FNSH_DT
AND PRM.CMP_CD = 'XXX'
GROUP BY PRM.CMP_CD, PRM.SALES_ORG_CD, PMN.MENU_CD, PMN.KIOSK_MENU_NM,
PRM.KIOSK_DCPRC_DISP_YN, PST.STOR_CD, PRM.PRRTY,
PMN.PRM_DC_TYPE, PMN.DC_RATE_AMT, PRM.UPD_DATE
),
핵심은 첫 줄의 /*+ USE_HASH(PMN PRM) USE_HASH(PST PRM) ... */ 입니다. 개발자는 PRM_MST 와 PRM_MENU_MNG 사이도 hash 로, PRM_MST 와 PRM_APPLY_STOR 사이도 hash 로 강제했습니다. WHERE 절에는 PRM.PRC_CLASS = '1', PRM.PRGRS_STATUS = 'C' 같은 강력한 필터가 걸려 있어서 PRM_MST 는 단 9건으로 좁혀집니다. 그 9건짜리 부모를 hash 의 build 쪽으로 강제로 밀어 넣는 hint 였던 셈입니다.
원본 실행계획
핵심 구간만 발췌했습니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Plan hash value: 4132740607
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4942 (100)| | 200 |00:00:02.23 | 13719 | 3516 | | | |
|* 1 | VIEW | | 1 | 6126 | 10M| 4942 (1)| 00:00:01 | 200 |00:00:02.23 | 13719 | 3516 | | | |
| 2 | COUNT | | 1 | | | | | 200 |00:00:02.23 | 13719 | 3516 | | | |
|* 3 | FILTER | | 1 | | | | | 200 |00:00:02.23 | 13719 | 3516 | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 1 | 6126 | 2381K| 4942 (1)| 00:00:01 | 1318 |00:00:02.23 | 13719 | 3516 | 1356K| 1356K| 1532K (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| MST_MENU_SOLDOUT | 1 | 4229 | 173K| 1220 (0)| 00:00:01 | 5881 |00:00:00.01 | 1969 | 0 | | | |
|* 6 | INDEX RANGE SCAN | MST_MENU_SOLDOUT_IDX01 | 1 | 4238 | | 18 (0)| 00:00:01 | 5881 |00:00:00.01 | 24 | 0 | | | |
|* 7 | HASH JOIN RIGHT OUTER | | 1 | 5102 | 1773K| 3722 (1)| 00:00:01 | 1318 |00:00:02.23 | 11750 | 3516 | 1055K| 1055K| 1352K (0)|
|* 8 | VIEW | | 1 | 9 | 927 | 2414 (1)| 00:00:01 | 49 |00:00:02.19 | 7245 | 3495 | | | |
|* 9 | WINDOW NOSORT | | 1 | 9 | 1269 | 2414 (1)| 00:00:01 | 49 |00:00:02.19 | 7245 | 3495 | 73728 | 73728 | |
| 10 | SORT GROUP BY | | 1 | 9 | 1269 | 2414 (1)| 00:00:01 | 49 |00:00:02.19 | 7245 | 3495 | 6144 | 6144 | 6144 (0)|
|* 11 | HASH JOIN | | 1 | 9 | 1269 | 2413 (1)| 00:00:01 | 49 |00:00:02.19 | 7245 | 3495 | 1090K| 1090K| 679K (0)|
|* 12 | HASH JOIN | | 1 | 1 | 105 | 2345 (1)| 00:00:01 | 3 |00:00:02.19 | 6997 | 3495 | 1185K| 1185K| 989K (0)|
| 13 | NESTED LOOPS | | 1 | 1 | 77 | 17 (0)| 00:00:01 | 9 |00:00:00.01 | 1384 | 0 | | | |
|* 14 | TABLE ACCESS FULL | PRM_MST | 1 | 1 | 57 | 17 (0)| 00:00:01 | 9 |00:00:00.01 | 1373 | 0 | | | |
|* 15 | INDEX UNIQUE SCAN | PK_PRM_JOIN_CHL | 9 | 1 | 20 | 0 (0)| | 9 |00:00:00.01 | 11 | 0 | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | PRM_APPLY_STOR | 1 | 2335 | 65380 | 2327 (1)| 00:00:01 | 1994 |00:00:02.26 | 5613 | 3495 | | | |
|* 17 | INDEX SKIP SCAN | PK_PRM_APPLY_STOR | 1 | 2386 | | 2209 (1)| 00:00:01 | 2006 |00:00:02.13 | 3608 | 3495 | | | | ← 한 행이 2.13초, Buffers 3,608, Reads 3,495
|* 18 | TABLE ACCESS FULL | PRM_MENU_MNG | 1 | 14201 | 499K| 68 (0)| 00:00:01 | 14602 |00:00:00.01 | 248 | 0 | | | |
|* 19 | HASH JOIN OUTER | | 1 | 5102 | 1260K| 1308 (1)| 00:00:01 | 1318 |00:00:00.04 | 4505 | 21 | 1400K| 1067K| 2579K (0)|
⋮
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(이후 행은 외곽의 HASH JOIN OUTER, HASH JOIN RIGHT OUTER 와 MST_MENU/MST_MENU_PRC 의 full scan 들)
Predicate Information (발췌):
17 - access("PST"."CMP_CD"='XXX' AND "PST"."SALES_ORG_CD"='1000'
AND "PST"."STOR_CD"='00000286')
filter("PST"."STOR_CD"='00000286')
Before plan — Id 17 의 INDEX SKIP SCAN 한 행이 쿼리 시간의 95.5% 를 점유합니다.
한 행에 비용이 어떻게 95% 까지 몰리는가
Top SELECT STATEMENT (Id 0) 의 A-Time 이 00:00:02.23 입니다. 그 아래로 비용을 따라 내려가 보면, Id 17 의 INDEX SKIP SCAN PK_PRM_APPLY_STOR 가 단독으로 A-Time 00:00:02.13, Buffers 3,608, Reads 3,495 를 쓰고 있습니다.
- 전체 시간 대비: 2.13 / 2.23 = 95.5%
- 전체 buffers 대비: 3,608 / 13,719 = 26.3%
- 전체 reads 대비: 3,495 / 3,516 = 99.4%
쿼리가 일으킨 거의 모든 physical I/O 가 이 한 행에서 발생했습니다. 단말이 부팅 호출을 동시에 일으키면 이 SKIP SCAN 의 buffer 폭증이 DB 단의 latch contention 으로 번질 수 있는 상황이었습니다.
단일 operation (Id 17, INDEX SKIP SCAN) 이 쿼리 시간의 95.5% (2.13초 / 2.23초) 와 physical reads 의 99.4% (3,495 / 3,516) 를 차지합니다. POS 단말이 부팅 호출을 동시 발생시키면 이 한 행의 buffer 폭증이 DB 단의 latch contention 으로 번질 수 있습니다.
Hash Join 이 만든 SKIP SCAN
PRM_APPLY_STOR 테이블의 primary key 는 PK_PRM_APPLY_STOR 이고 컬럼 순서는 (CMP_CD, SALES_ORG_CD, PRM_CD, STOR_CD) 입니다. 4컬럼 복합 인덱스입니다.
이 인덱스를 UNIQUE SCAN 또는 RANGE SCAN 으로 쓰려면 옵티마이저가 선두 컬럼부터 차례대로 값을 알고 있어야 합니다. 1번째 (CMP_CD) 를 알면 1컬럼 RANGE, 1~2번째를 알면 2컬럼 RANGE, 1~3번째를 알면 3컬럼 RANGE, 1~4번째 모두 알면 UNIQUE 입니다. 중간 컬럼이 비어 있는데 뒤쪽 컬럼만 알고 있으면, 옵티마이저는 차선책으로 INDEX SKIP SCAN 을 시도합니다. 비어 있는 컬럼의 distinct 값마다 인덱스 sub-tree 를 한 번씩 타고 들어가는, 비싸지만 작동은 하는 접근 방식입니다.
원본 plan 의 Id 17 의 Predicate Information 을 보면 옵티마이저가 PRM_APPLY_STOR 에 들어갈 때 알고 있던 값이 무엇이었는지 정확히 나옵니다.
1
2
3
17 - access("PST"."CMP_CD"='XXX' AND "PST"."SALES_ORG_CD"='1000'
AND "PST"."STOR_CD"='00000286')
filter("PST"."STOR_CD"='00000286')
알고 있던 값: CMP_CD, SALES_ORG_CD, STOR_CD 세 컬럼. WHERE 절의 상수 조건들이고, STOR_CD 는 POS 단말이 자신의 매장 번호로 박은 바인드 값입니다. 그런데 PK 의 3번째 컬럼인 PRM_CD 는 알고 있지 않습니다. PRM_MST 와 join 이 일어나는 시점에 PRM_CD 가 따라 들어와야 하는데, hint USE_HASH(PST PRM) 가 그 순서를 막아 놨습니다. Hash Join 은 양쪽을 동시에 빌드/프로브 하는 구조이지 PRM_MST 의 PRM_CD 를 PRM_APPLY_STOR 의 인덱스 진입 키로 전달 하지 않습니다.
요약하면 이런 그림입니다.
1
2
3
4
5
6
7
8
9
PK_PRM_APPLY_STOR
( CMP_CD , SALES_ORG_CD , PRM_CD , STOR_CD )
↑ ↑ ↑ ↑
='XXX' ='1000' ??? 빠짐 ='00000286'
(상수) (상수) (driving 없음) (바인드)
→ 옵티마이저: 3번째 컬럼 미지수, 4번째 컬럼만 가지고 있음
→ 결정: INDEX SKIP SCAN
→ 결과: A-Rows 2,006 / Buffers 3,608 / Reads 3,495
A-Rows 가 2,006 인 게 인상적입니다. 최종적으로 이 단계에서 살아남는 row 는 본문 위쪽의 NL Join 결과(Id 13~15) 가 만들어낸 9건과 join 되어 단 3건이지만, SKIP SCAN 은 그 9건이 정해지기 전에 PRM_CD 차원으로 인덱스를 한참 휘젓고 다닙니다.
PRM_CD 를 전달 해 줄 수 있는 유일한 방법은 NL Join 입니다. 부모(PRM_MST) 의 각 row 가 자식(PRM_APPLY_STOR) 의 인덱스 진입 키로 풀려서 한 건씩 lookup 하는 구조. PRM_MST 는 WHERE 절 필터(PRC_CLASS='1', PRGRS_STATUS='C', BFPSN_CPN_YN='0', CMP_CD='XXX') 와 날짜 범위로 좁혀져서 단 9건 만 나옵니다. 9건짜리 부모로 NL driving 을 돌리는 비용은 9 × (PK lookup 3블록) = 약 27 블록 정도입니다. Hash Join 의 hash table 빌드 + SKIP SCAN probe 가 쓴 3,608 buffers 와는 100배 차이의 규모입니다.
옵티마이저는 이미 이 사실을 알고 있었습니다. cost 모델을 돌려 보면 NL + UNIQUE_SCAN 쪽이 훨씬 싸다는 결론이 나옵니다. 다만 개발자가 박아둔 USE_HASH(PST PRM) 가 그 결론을 강제로 막고 있었을 뿐입니다.
수정
원본과 수정본은 힌트 제거 뿐입니다.
1
2
- SELECT /*+ USE_HASH(PMN PRM) USE_HASH(PST PRM) */
+ SELECT /*+ USE_HASH(PMN PRM) */
USE_HASH(PMN PRM) 는 그대로 남겼습니다. 그 hint 가 막고 있던 조인은 PRM_MST 와 PRM_MENU_MNG 사이로, Hash 가 실제로 적합한 케이스이기 때문입니다 (PRM_MENU_MNG 는 14,201 rows). 외곽의 HASH JOIN RIGHT OUTER, HASH JOIN OUTER 같은 다른 큰 조인들도 손대지 않았습니다. 비효율 핵심 한 군데, USE_HASH(PST PRM) 한 줄만 제거했습니다.
USE_NL 힌트를 추가 하지도 않았습니다. INDEX 힌트로 강제하지도 않았습니다. 그저 잘못 박힌 hint 하나를 빼는 것만으로 옵티마이저가 “그러면 NL 이 더 싸다” 는 자기 결론을 따라가도록 길을 열어줬을 뿐입니다.
수정 실행계획
같은 구간(Id 0~19) 을 발췌해 비교 가능하게 두었습니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Plan hash value: 47371715
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2617 (100)| | 200 |00:00:00.05 | 8129 | | | |
|* 1 | VIEW | | 1 | 6126 | 10M| 2617 (1)| 00:00:00 | 200 |00:00:00.05 | 8129 | | | |
| 2 | COUNT | | 1 | | | | | 200 |00:00:00.05 | 8129 | | | |
|* 3 | FILTER | | 1 | | | | | 200 |00:00:00.05 | 8129 | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 1 | 6126 | 2381K| 2617 (1)| 00:00:00 | 1318 |00:00:00.05 | 8129 | 1356K| 1356K| 1468K (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| MST_MENU_SOLDOUT | 1 | 4229 | 173K| 1220 (0)| 00:00:00 | 5881 |00:00:00.01 | 1969 | | | |
|* 6 | INDEX RANGE SCAN | MST_MENU_SOLDOUT_IDX01 | 1 | 4238 | | 18 (0)| 00:00:00 | 5881 |00:00:00.01 | 24 | | | |
|* 7 | HASH JOIN RIGHT OUTER | | 1 | 5102 | 1773K| 1396 (1)| 00:00:00 | 1318 |00:00:00.04 | 6160 | 1055K| 1055K| 1324K (0)|
|* 8 | VIEW | | 1 | 6 | 618 | 88 (2)| 00:00:00 | 49 |00:00:00.01 | 1655 | | | |
|* 9 | WINDOW NOSORT | | 1 | 6 | 846 | 88 (2)| 00:00:00 | 49 |00:00:00.01 | 1655 | 73728 | 73728 | |
| 10 | SORT GROUP BY | | 1 | 6 | 846 | 88 (2)| 00:00:00 | 49 |00:00:00.01 | 1655 | 6144 | 6144 | 6144 (0)|
|* 11 | HASH JOIN | | 1 | 6 | 846 | 87 (0)| 00:00:00 | 49 |00:00:00.01 | 1655 | 1090K| 1090K| 662K (0)|
| 12 | NESTED LOOPS | | 1 | 1 | 105 | 19 (0)| 00:00:00 | 3 |00:00:00.01 | 1407 | | | | ← NL chain 시작
| 13 | NESTED LOOPS | | 1 | 1 | 105 | 19 (0)| 00:00:00 | 3 |00:00:00.01 | 1404 | | | |
| 14 | NESTED LOOPS | | 1 | 1 | 77 | 17 (0)| 00:00:00 | 9 |00:00:00.01 | 1384 | | | |
|* 15 | TABLE ACCESS FULL | PRM_MST | 1 | 1 | 57 | 17 (0)| 00:00:00 | 9 |00:00:00.01 | 1373 | | | |
|* 16 | INDEX UNIQUE SCAN | PK_PRM_JOIN_CHL | 9 | 1 | 20 | 0 (0)| | 9 |00:00:00.01 | 11 | | | |
|* 17 | INDEX UNIQUE SCAN | PK_PRM_APPLY_STOR | 9 | 1 | | 1 (0)| 00:00:00 | 3 |00:00:00.01 | 20 | | | | ← UNIQUE SCAN, Buffers 20
|* 18 | TABLE ACCESS BY INDEX ROWID | PRM_APPLY_STOR | 3 | 1 | 28 | 2 (0)| 00:00:00 | 3 |00:00:00.01 | 3 | | | |
|* 19 | TABLE ACCESS FULL | PRM_MENU_MNG | 1 | 14201 | 499K| 68 (0)| 00:00:00 | 14602 |00:00:00.01 | 248 | | | |
⋮
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(이후 행은 Before 와 동일한 외곽 구조)
Predicate Information (발췌):
17 - access("PST"."CMP_CD"='XXX' AND "PST"."SALES_ORG_CD"='1000'
AND "PST"."PRM_CD"="PRM"."PRM_CD"
AND "PST"."STOR_CD"='00000286')
After plan — Id 12, 13, 14 의 3단 NESTED LOOPS 체인이 PRM_MST → PRM_JOIN_CHL → PRM_APPLY_STOR → PRM_MENU_MNG 순으로 PK 를 타고 진행합니다.
원본의 Id 12 HASH JOIN 자리가 세 개의 NESTED LOOPS (Id 12, 13, 14) 체인으로 바뀌었습니다. 그리고 Id 17 의 INDEX SKIP SCAN 이 INDEX UNIQUE SCAN 으로 바뀌었습니다. Predicate Information 한 줄에 모든 메커니즘이 적혀 있습니다.
원본 Predicate 와 수정 Predicate 의 차이는 정확히 한 항목입니다.
1
2
3
4
17 - access("PST"."CMP_CD"='XXX' AND "PST"."SALES_ORG_CD"='1000'
+ AND "PST"."PRM_CD"="PRM"."PRM_CD"
AND "PST"."STOR_CD"='00000286')
- filter("PST"."STOR_CD"='00000286')
"PST"."PRM_CD"="PRM"."PRM_CD" 가 추가됐습니다. 옵티마이저가 PRM_MST 의 PRM_CD 를 NL 의 driving 으로 받아 PRM_APPLY_STOR 의 PK 3번째 컬럼을 채웠습니다. 4컬럼 PK 가 모두 채워졌으니 SKIP SCAN 이 아니라 UNIQUE SCAN. Starts = 9 (NL 의 9 row 가 한 번씩 호출), 한 번 호출당 buffers 약 2 블록, 총 buffers 20.
수치 비교
ALLSTATS LAST 출력 결과 비교입니다.
전체 쿼리 수준
| 지표 | Before (Hash) | After (NL) | 변화 | 변화율 |
|---|---|---|---|---|
| Plan hash value | 4132740607 | 47371715 | — | — |
| Cost (CPU%) | 4,942 (100%) | 2,617 (100%) | −2,325 | −47.04% |
| A-Time (실측) | 00:00:02.23 | 00:00:00.05 | −2.18초 | 44.6배 향상 |
| Buffers (logical reads 총) | 13,719 | 8,129 | −5,590 | −40.75% |
| Reads (physical reads) | 3,516 | 0 | −3,516 | 100% 제거 |
핵심 Operation 비교
| 항목 | Before (Id 17, SKIP SCAN) | After (Id 17, UNIQUE SCAN) | 변화 |
|---|---|---|---|
| 접근 방식 | INDEX SKIP SCAN | INDEX UNIQUE SCAN | 종류 자체가 바뀜 |
| Starts | 1 | 9 | NL 의 driving row 수와 일치 |
| Cost | 2,209 | 1 | 2,209배 ↓ |
| A-Rows | 2,006 | 3 | 668배 ↓ (불필요 스캔 제거) |
| A-Time | 00:00:02.13 | 00:00:00.01 | 213배 ↓ |
| Buffers | 3,608 | 20 | 180.4배 ↓ |
| Reads (physical) | 3,495 | 0 | 100% 제거 |
결론
변환의 본질이 hint 를 더 박는 게 아니라 hint 를 덜 박는 데 있었습니다. USE_HASH 가 8개에서 7개로 줄었을 뿐, USE_NL 도 INDEX 힌트도 추가되지 않았습니다. 옵티마이저는 처음부터 NL + UNIQUE_SCAN 이 더 싸다는 cost 결론을 내릴 수 있었지만, USE_HASH(PST PRM) 한 줄이 그 결론을 강제로 막고 있었습니다.
Hash Join 이 빠른 것이 아니라, 조인 양쪽 카디널리티가 그 모양일 때 Hash 가 빠른 것입니다. hint 가 옵티마이저의 옳은 결론을 막고 있지 않은지에 대한 확인부터가 튜닝의 출발점인 것 같습니다.