EXISTS 서브쿼리에 NO_UNNEST + PUSH_SUBQ 적용
시간대별 매출현황 화면 조회 쿼리 튜닝 기록입니다. 결론은
EXISTS서브쿼리에NO_UNNEST + PUSH_SUBQ두 줄을 얹어 응답시간 18.98초 (-75.3%), 논리 읽기 8,124K → 2,327K blocks (-71.4%), 옵티마이저 cost 5,560K → 95,963 (≈ 58배 감소) 로 마무리했습니다.
본 케이스는 2025년 7월 31일에 진행한 내용입니다. 모든 식별자는 마스킹했고, plan 내 수치들은 그대로 인용했습니다.
들어가며
대상은 프랜차이즈 기업 내 시간대별 매출현황 조회 쿼리였습니다. 한 달치 매출 데이터를 15분 단위 96개 시간대로 PIVOT 해서 메뉴별로 보여주는, 쉽게 말해 한 화면에 행 200개 × 열 96 × 3 (금액·수량·단가) 정도의 격자를 그려주는 쿼리입니다. 한 번 클릭에 76.77초. 사용자 인내 한계를 한참 넘어선 응답이라 옵션이 아니라 의무로 잡아야 했습니다.
전체 쿼리 구조는 Fact 테이블(일자/매장/메뉴/시간대 단위 일별 집계) 한 개에 Dimension 테이블 몇 개(매장·메뉴·코드)를 join 한 후 PIVOT, 그리고 사용자 권한 체크용 EXISTS 서브쿼리 한 개를 추가한 것입니다.
Buffers 비효율
저의 첫 가설은 단순했습니다. plan 의 Id 9 A-Rows 가 보여주듯 한 달 fact 행수는 결과적으로 8,203,164 rows 였습니다. 이 8.2M 행이 hash join 으로 한 번 만들어지고 그 위 단계로 그대로 흘러간다면, buffer 가 8M 블록을 넘는 것은 이상한 일이 아닙니다. 결국 누군가가 이 8M 행을 더 일찍 잘라줘야 비용이 떨어진다는 뜻이 됩니다.
자르는 칼이 될 수 있는 조건은 두 가지였습니다.
- fact 자체의
WHERE: 회사·매출조직·기간(SALE_DT BETWEEN '20250101' AND '20250131') 이 이미 걸려 있고, 인덱스(PK_TBL_FACT_SALES_TMZN) RANGE SCAN 으로 잘 풀리고 있는 상태였습니다. 즉 더 줄일 여지가 거의 없습니다. 한 달 = 8.2M 행이라는 fact 의 절대 크기 자체가 한계였습니다. - 사용자 매장 권한
EXISTS:EXISTS (SELECT 'X' FROM V_USER_DATA_AUTH WHERE ... AND USER_ID = 'USER_X' AND ROWNUM = 1)형태로, USER_X 가 볼 수 있는 매장이 전사 1,693개 중 얼마나 되느냐 에 따라 fact 를 크게 좁힐 수 있는지에 대한 여부였습니다.
저는 두 번째에 무게를 실었습니다. 이유는 두 가지였습니다. 첫째, 권한 뷰가 의미 있게 작동한다면 driving 행수를 한참 줄여줄 잠재력이 있어 효과가 좋을 것이라 판단했습니다. 둘째, 이미 옵티마이저가 어떻게 권한 체크를 끼워 넣었는지는 plan 에 그대로 적혀 있을 테니, 답을 찾는 데 추가 정보가 필요 없었습니다.
원본 쿼리 일부
전체 쿼리는 길어서 EXISTS 서브쿼리가 박혀 있는 inner SELECT 의 WHERE 절 영역만 발췌합니다.
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
SELECT ... -- ... 중략 ...
FROM (
SELECT ... -- ... 중략 ...
FROM (
SELECT ... -- ... 중략 ...
FROM TBL_FACT_SALES_TMZN SM
INNER JOIN TBL_DIM_STORE MS ON ... -- ... 중략 ...
-- ... 중략 ... (다른 dimension join 들)
WHERE SM.CMP_CD = 'CMP_X'
AND SM.SALES_ORG_CD = 'ORG_X'
AND SM.SALE_DT BETWEEN '20250101' AND '20250131'
AND EXISTS ( SELECT 'X'
FROM V_USER_DATA_AUTH
WHERE CMP_CD = MS.CMP_CD
AND SALES_ORG_CD = MS.SALES_ORG_CD
AND AUTH_YN = '1'
AND USER_ID = 'USER_X'
AND STOR_CD = MS.STOR_CD
AND ROWNUM = 1
)
GROUP BY ... -- ... 중략 ...
) A
)
PIVOT( ... ) -- ... 중략 ... (96개 시간대 PIVOT)
ORDER BY MENU_NM;
권한 체크 EXISTS 가 fact 와 차원의 join 한가운데 박혀 있다는 것이 한눈에 보입니다. 이 한 덩어리를 옵티마이저가 어떻게 풀었느냐가 plan 의 형태를 좌우합니다.
원본 실행계획
전체 plan 은 길어서 핵심만 발췌합니다. Id 8 의 FILTER 와 Id 9 의 HASH JOIN RIGHT OUTER 가 본 plan 의 모든 비용이 모이는 자리 입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 | 8124K | ← 76.77s
| 1 | SORT ORDER BY | | 1 | 2432 | 200 | 8124K |
| 2 | VIEW | | 1 | 2432 | 524 | 8124K |
| 3 | TRANSPOSE | | 1 | | 524 | 8124K |
| 4 | SORT GROUP BY PIVOT | | 1 | 2432 | 25859 | 8124K |
| 5 | VIEW | | 1 | 13758 | 25859 | 8124K |
| 6 | WINDOW BUFFER | | 1 | 13758 | 25859 | 8124K |
| 7 | SORT GROUP BY | | 1 | 13758 | 25859 | 8124K |
|* 8 | FILTER | | 1 | | 8203K | 8124K | ← 권한 cut 이 여기에서 (너무 늦게)
|* 9 | HASH JOIN RIGHT OUTER | | 1 | 5364K| 8203K | 8102K | ← fact join 결과
|* 10 | VIEW | | 1 | 131 | 96 | 120 |
|* 22 | HASH JOIN | | 1 | 5364K| 8203K | 8102K |
|* 23 | INDEX FAST FULL SCAN | PK_TBL_DIM_STORE | 1 | 383 | 385 | 8 |
|* 24 | HASH JOIN | | 1 | 5392K| 8203K | 8102K |
|* 25 | TABLE ACCESS FULL | TBL_DIM_MENU | 1 | 3124 | 3143 | 269 |
| 26 | TABLE ACCESS BY INDEX ROWID BATCHED | TBL_FACT_SALES_TMZN | 1 | 5425K| 8203K | 8102K | ← 8.2M 행을 그대로 들고 올라옴
|* 27 | INDEX RANGE SCAN | PK_TBL_FACT_SALES_TMZN | 1 | 5425K| 8203K | 75866 |
|* 28 | COUNT STOPKEY | | 1693 | | 1693 | 22248 | ← V_USER_DATA_AUTH 를 1,693회 호출
-----------------------------------------------------------------------------------------------------------------------------------
plan 의 흐름을 따라가 보면 시나리오가 한눈에 잡힙니다. 옵티마이저는 EXISTS 서브쿼리를 unnest 하지 않은 채 Id 8 FILTER 자리에 두었습니다. 그런데 그 직전 단계 가 바로 Id 9 HASH JOIN RIGHT OUTER 이고, 이 hash join 이 fact 와 차원 테이블을 모두 묶어 8,203K rows 를 먼저 다 만들어낸 후에야 그 결과 한 행 한 행에 권한 체크를 적용하는 구조였습니다.
다행히 한 가지 안전장치는 작동하고 있었습니다. STOR_CD 단위 FILTER caching 입니다. 같은 매장 ID 가 여러 번 들어와도 권한 체크는 한 번만 수행되도록 옵티마이저가 결과를 캐싱해 줍니다. 그 덕분에 Id 28 COUNT STOPKEY 의 Starts 가 전사 매장 수인 1,693회 로 끊겼습니다. 만약 이 캐싱이 없었다면 8.2M × 권한 체크 비용이 그대로 곱해졌을 자리입니다.
그러나 진짜 문제는 다른 데 있었습니다. 너무 늦게 자른다 는 점입니다. 옵티마이저는 fact 8.2M 행을 join 으로 다 만든 후 에 권한 cut 을 적용하기 때문에, 그 8.2M 행을 만드는 데 이미 들어간 buffer 8,124K 와 elapsed 76.77초는 fact join 자체의 비용으로 굳어버립니다. 권한이 조건을 아무리 강하게 걸어주더라도 그 앞에서 이미 쓴 비용은 돌려받지 못합니다.
현황 파악
원본 plan 의 Outline Data 를 보면 EXISTS 서브쿼리에 대해 옵티마이저가 어떤 결정을 내렸는지가 그대로 적혀 있었습니다.
1
2
3
OLD_PUSH_PRED(@"SEL$1CF66C63" "AA"@"SEL$14" ("TBL_SYS_AUTH_ORG"."CMP_CD"))
OUTLINE_LEAF(@"SEL$1CF66C63")
MERGE(@"SEL$14" >"SEL$13")
위 세 줄을 풀어 읽어보면, 옵티마이저는 EXISTS 서브쿼리를 일단 unnest 해서 outer 와 한 query block(SEL$1CF66C63)으로 머지하려고 시도했다 는 흔적이 남아 있습니다. 그런데 정작 최종 plan 은 unnest 후의 일반 join 형태가 아니라 FILTER + COUNT STOPKEY 였습니다. 즉 옵티마이저가 unnest 변환을 한 번 해 본 뒤, 비용 모델 평가에서 FILTER 위치에 EXISTS 를 그대로 두는 path 가 더 싸 보인다는 결론을 내렸다는 뜻입니다.
그렇다면 왜 옵티마이저가 그 자리를 더 싸게 봤는지 를 알아야 했습니다. 그 답이 권한 뷰 V_USER_DATA_AUTH 의 구조에 있다고 보고 뷰 자체를 살펴봤습니다.
V_USER_DATA_AUTH 의 정체 (UNION-ALL + SORT UNIQUE)
권한 뷰는 plan 의 Id 30~54 에 풀려 있는데, 핵심을 압축하면 다음과 같습니다.
1
2
3
4
5
6
7
8
9
COUNT STOPKEY ← ROWNUM = 1
NESTED LOOPS ← BB(TBL_DIM_STORE) ↔ AA(권한뷰)
INDEX UNIQUE SCAN PK_TBL_DIM_STORE
VIEW ← AA = SET$AD7CC163
SORT UNIQUE ← UNION 의 정합성 보장
UNION-ALL PARTITION ← 3 분기
[분기1] SUI(권한 type=001) NL TBL_DIM_STORE
[분기2] SUI(권한 type ≠ 001) NL TBL_STORE_USER
[분기3] SUI(권한 type=003) NL TBL_DIM_STORE NL TBL_SYS_AUTH_ORG
V_USER_DATA_AUTH 는 사용자 권한 유형(USER_ROLE_TYPE) 에 따라 매장 권한 산출 로직이 갈리는 세 갈래의 산출 로직을 UNION ALL 로 묶은 뷰 였습니다. 그 위에 정합성 보장용 SORT UNIQUE 가 한 번 더 얹혀 있고요.
이런 구조는 옵티마이저 입장에서 통째로 unnest 해서 hash semi join 하나로 풀기 에 매력적인 후보가 아닙니다. UNION ALL 분기 셋이 각자 다른 driving 을 갖고 있는 데다, 분기마다 매번 매장 ID 하나씩 들어와 필터링하는 형태라, cost 모델 입장에서는 그냥 매장 1,693개를 NL 로 한 번씩 돌리는 편 이 더 싸 보였을 것입니다.
그래서 옵티마이저는 EXISTS 를 main fact join 의 바깥, 즉 Id 8 FILTER 위치에 두는 path 를 골랐습니다. 문제는 그 자리가 fact 8M 을 다 만든 후 라는 점입니다.
NO_UNNEST + PUSH_SUBQ 를 고른 이유
- DDL 변경이 없습니다. 인덱스 추가/변경, 뷰 재설계 같은 운영 자산 변경 없이 해당 SQL 한 건 의 hint 두 줄로 끝납니다. 변경관리 부담이 가장 작고 롤백도 hint 만 빼면 즉시 됩니다.
- 옵티마이저를 유도 만 합니다.
NO_UNNEST는 변환을 막고PUSH_SUBQ는 평가 시점만 옮기는, 옵티마이저가 원래 알고 있던 한 가지 path 를 명시적으로 가리키는 가이드입니다. 옵티마이저가 모르는 새로운 path 를 강제하는 것이 아니라서 plan 안정성이 좋습니다. - 권한 selectivity 22.7% 가 명백한 이득을 보장합니다. push 로 옮긴 자리에서 평균 4.4행 중 1행만 통과시킨다는 의미이고, 이 정도 cut 강도면 NL 호출 비용을 충분히 상쇄합니다. 만약 selectivity 가 80~90% 같이 약했다면 push 가 오히려 손해였을 텐데, 본 케이스는 그 선을 한참 넘어선 강한 cut 이었습니다.
수정 쿼리
수정은 EXISTS 안의 SELECT 'X' 자리에 hint 두 줄을 얹은 것이 전부입니다.
1
2
3
4
5
6
7
8
9
AND EXISTS ( SELECT /*+ no_unnest push_subq */ 'X' --< 두 줄 추가
FROM V_USER_DATA_AUTH
WHERE CMP_CD = MS.CMP_CD
AND SALES_ORG_CD = MS.SALES_ORG_CD
AND AUTH_YN = '1'
AND USER_ID = 'USER_X'
AND STOR_CD = MS.STOR_CD
AND ROWNUM = 1
)
수정 실행계획
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 | 2327K | ← 18.98s
| 1 | SORT ORDER BY | | 1 | 2432 | 200 | 2327K |
|* 8 | HASH JOIN RIGHT OUTER | | 1 | 303K | 8203K | 2327K | ← FILTER 가 사라짐
|* 21 | HASH JOIN | | 1 | 303K | 8203K | 2327K |
|* 22 | TABLE ACCESS FULL | TBL_DIM_MENU | 1 | 3124 | 3143 | 269 |
| 23 | NESTED LOOPS | | 1 | 304K | 8203K | 2327K | ← NL 로 풀림
| 24 | NESTED LOOPS | | 1 | 333K | 8203K | 48181 |
|* 25 | INDEX FAST FULL SCAN | PK_TBL_DIM_STORE | 1 | 19 | 385 | 4858 | ← 매장 385개로 미리 cut
|* 26 | COUNT STOPKEY | | 385 | | 385 | 4850 | ← V_USER_DATA_AUTH 호출 385회 (was 1,693)
|* 53 | INDEX RANGE SCAN | IDX_TBL_FACT_SALES_TMZN01 | 385 | 17557 | 8203K | 43323 | ← 매장당 fact range scan
|* 54 | TABLE ACCESS BY INDEX ROWID | TBL_FACT_SALES_TMZN | 8203K| 15926 | 8203K | 2278K |
---------------------------------------------------------------------------------------------------------------------------------------
세 가지 변화가 한꺼번에 일어났습니다. 모두 PUSH_SUBQ(@"SEL$1CF66C63") 한 줄이 Outline Data 에 추가된 결과입니다.
- 권한 체크가 맨 앞으로 이동: 전체 매장을 훑던 자리에서 권한 통과 매장 385개만 먼저 추렸습니다. 그 결과 EXISTS 서브쿼리 호출이 1,693회 → 385회 로 줄었고, 권한 단계 buffer 도 22,248 → 4,858 로 약 ¼ 수준이 되었습니다.
- Fact join 이 HASH 에서 NL 로 전환: driving 매장이 385개로 작아지니 매장 하나씩 fact 인덱스를 훑어도 충분히 효율적이어서, 옵티마이저가 NL + NLJ_BATCHING 으로 자동 전환했습니다.
- Fact 접근 인덱스가 PK 에서 보조 인덱스로 전환: 매장이 driving 이 되자 매장 컬럼이 선두인
IDX_TBL_FACT_SALES_TMZN01 (STOR_CD, SALE_DT)가 더 적합한 path 가 되어 자동 선택되었습니다. hint 로 강제하지 않았는데 따라온 변화라는 점이 중요합니다. push 한 줄이 join 형태와 access path 까지 옳은 방향으로 함께 끌어당겼다는 뜻입니다.
여기서 한 가지 짚어둘 점이 있습니다.
- 만들어낸 행수는 똑같습니다. 수정 후에도
Id 8 HASH JOIN RIGHT OUTER와Id 54 TABLE ACCESS BY INDEX ROWID의A-Rows는 여전히 8,203K 로 원본과 같습니다. 즉 fact 최종 행수 자체는 줄지 않았습니다. - 그런데 논리 읽기는 8,124K → 2,327K 로 줄었습니다. 같은 8.2M 행을 만드는 데 훑는 블록 수 가 약 ¼ 수준이 되었다는 뜻입니다.
- 차이는 fact 를 훑는 방식에 있습니다. 원본은 fact 인덱스를 한 번에 75K 블록만큼 훑은 뒤 table access 로 8M 블록을 또 따라갔습니다. 수정 후에는 driving 매장 385개를 두고 매장 하나당 인덱스 RANGE SCAN 한 번씩만 fact 를 훑습니다. 중복으로 따라가던 블록이 사라지면서 buffer 가 ¼ 수준으로 떨어진 것입니다.