참고소스
https://gitlab.com/kimdongy1000/sqltuningproject EX21_Pushing 참고
서브쿼리 Pushing
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
********************************************************************************
SELECT /*+leading(p) use_nl(t) */*
FROM EX_21_PRODUCT P, EX_21_PRODUCT_ORDER O
WHERE P.PRODUCT_ID = O.PRODUCT_ID
AND P.REG_DATE >= TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
AND O.ORDER_DATE >= TRUNC(SYSDATE - 7)
AND EXISTS (
SELECT /*+no_unnest */ 1 FROM EX_21_PRODUCT_CATEGORY C
WHERE C.CATEGORY_CODE = P.CATEGORY_CODE
AND C.PARENT_CATEGORY_CODE = 'AK'
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 957 0.03 0.06 0 1196 0 14339
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 959 0.04 0.07 0 1196 0 14339
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14339 14339 14339 FILTER (cr=1196 pr=0 pw=0 time=153476 us starts=1)
28576 28576 28576 HASH JOIN (cr=1176 pr=0 pw=0 time=246216 us starts=1 cost=79 size=923328 card=28854)
8160 8160 8160 TABLE ACCESS FULL EX_21_PRODUCT (cr=31 pr=0 pw=0 time=1623 us starts=1 cost=9 size=122400 card=8160)
35007 35007 35007 TABLE ACCESS FULL EX_21_PRODUCT_ORDER (cr=1145 pr=0 pw=0 time=263992 us starts=1 cost=70 size=595102 card=35006)
5 5 5 TABLE ACCESS BY INDEX ROWID EX_21_PRODUCT_CATEGORY (cr=20 pr=0 pw=0 time=626 us starts=10 cost=1 size=6 card=1)
10 10 10 INDEX UNIQUE SCAN SYS_C008206 (cr=10 pr=0 pw=0 time=239 us starts=10 cost=0 size=0 card=1)(object id 77306)
********************************************************************************
SELECT /*+leading(p) use_nl(t) */*
FROM EX_21_PRODUCT P, EX_21_PRODUCT_ORDER O
WHERE P.PRODUCT_ID = O.PRODUCT_ID
AND P.REG_DATE >= TRUNC(ADD_MONTHS(SYSDATE, -3), 'MM')
AND O.ORDER_DATE >= TRUNC(SYSDATE - 7)
AND EXISTS (
SELECT /*+no_unnest push_subq*/ 1 FROM EX_21_PRODUCT_CATEGORY C
WHERE C.CATEGORY_CODE = P.CATEGORY_CODE
AND C.PARENT_CATEGORY_CODE = 'AK'
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 957 0.02 0.05 0 1196 0 14339
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 959 0.02 0.05 0 1196 0 14339
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14339 14339 14339 HASH JOIN (cr=1197 pr=0 pw=0 time=60227 us starts=1 cost=79 size=46176 card=1443)
4065 4065 4065 TABLE ACCESS FULL EX_21_PRODUCT (cr=51 pr=0 pw=0 time=1584 us starts=1 cost=9 size=6120 card=408)
5 5 5 TABLE ACCESS BY INDEX ROWID EX_21_PRODUCT_CATEGORY (cr=20 pr=0 pw=0 time=487 us starts=10 cost=1 size=6 card=1)
10 10 10 INDEX UNIQUE SCAN SYS_C008206 (cr=10 pr=0 pw=0 time=206 us starts=10 cost=0 size=0 card=1)(object id 77306)
35007 35007 35007 TABLE ACCESS FULL EX_21_PRODUCT_ORDER (cr=1145 pr=0 pw=0 time=55916 us starts=1 cost=70 size=595102 card=35006)
********************************************************************************
총 2개의 쿼리를 가지고 왔다 같은 쿼리에 유일하게 다른 힌트 push_subq 가 있다는 것이 차이점이다 먼저 위에 있는 트레이스 로그를 먼저 분석을 해보자
1
2
3
4
5
6
7
8
9
10
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14339 14339 14339 FILTER (cr=1196 pr=0 pw=0 time=153476 us starts=1)
28576 28576 28576 HASH JOIN (cr=1176 pr=0 pw=0 time=246216 us starts=1 cost=79 size=923328 card=28854)
8160 8160 8160 TABLE ACCESS FULL EX_21_PRODUCT (cr=31 pr=0 pw=0 time=1623 us starts=1 cost=9 size=122400 card=8160)
35007 35007 35007 TABLE ACCESS FULL EX_21_PRODUCT_ORDER (cr=1145 pr=0 pw=0 time=263992 us starts=1 cost=70 size=595102 card=35006)
5 5 5 TABLE ACCESS BY INDEX ROWID EX_21_PRODUCT_CATEGORY (cr=20 pr=0 pw=0 time=626 us starts=10 cost=1 size=6 card=1)
10 10 10 INDEX UNIQUE SCAN SYS_C008206 (cr=10 pr=0 pw=0 time=239 us starts=10 cost=0 size=0 card=1)(object id 77306)
SYS_C008206 인덱스 (EX_21_PRODUCT_CATEGORY)에서 유니크 스캔을 먼저 해 그곳에서 10건을 가져옴
1의 결과로 EX_21_PRODUCT_CATEGORY 테이블의 인덱스를 조회함 이때 결과는 5건 조회
EX_21_PRODUCT_ORDER 테이블 FULL SCAN 총 35007 건
EX_21_PRODUCT 테이블 FULL SCAN으로 총 8160 건 조회
HASH JOIN 3번과 4번을 조인해서 총 28576 건으로 조인 결과 조회
FILTER로 최종적으로 14339개를 출력
만약 FILTER 가 제일 먼저 실행이 되어서 미리 조건에서 제거되었다면 쿼리의 성능은 더 좋았을 것이다 그래서 다음 트레이서 로그를 살펴보면
1
2
3
4
5
6
7
8
9
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
14339 14339 14339 HASH JOIN (cr=1197 pr=0 pw=0 time=60227 us starts=1 cost=79 size=46176 card=1443)
4065 4065 4065 TABLE ACCESS FULL EX_21_PRODUCT (cr=51 pr=0 pw=0 time=1584 us starts=1 cost=9 size=6120 card=408)
5 5 5 TABLE ACCESS BY INDEX ROWID EX_21_PRODUCT_CATEGORY (cr=20 pr=0 pw=0 time=487 us starts=10 cost=1 size=6 card=1)
10 10 10 INDEX UNIQUE SCAN SYS_C008206 (cr=10 pr=0 pw=0 time=206 us starts=10 cost=0 size=0 card=1)(object id 77306)
35007 35007 35007 TABLE ACCESS FULL EX_21_PRODUCT_ORDER (cr=1145 pr=0 pw=0 time=55916 us starts=1 cost=70 size=595102 card=35006)
테이블 EX_21_PRODUCT_ORDER FULL 스캔 총 35007건
sYS_C008206 인덱스 (EX_21_PRODUCT_CATEGORY)에서 유니크 스캔을 먼저 해 그곳에서 10건을 가져옴
2번의 결과로 EX_21_PRODUCT_CATEGORY 테이블 조회 시 5건을 가져옴
3번의 결과의 FILTER로 인해서 EX_21_PRODUCT 테이블을 FULL 스캔하지만 이때 필터 된 개수만큼 줄어들어서 4065개 가져옴
1번화 4번을 해시 조인하게 되어서 나오는 결과 14339건을 가져옴
PUSH_SUBQ 힌트
지금 보면 같은 쿼리일지라도 현재 하나의 힌트로 성능이 달라지는 것을 볼 수 있습니다 이 힌트에 대해서 공부를 하겠습니다 이 힌트는 서브쿼리를 가능한 한 빨리 실행을 요청하는 힌트입니다 보통 WHERE 절에 있는 스칼라 서브 쿼리 또는 IN/EXISTS 서브 쿼리가 있는 경우 오라클은 기본적으로 최적의 타이밍에서 서브 쿼리를 실행하지만 어떤 경우에 따라서 이 실행 순서가 성능에 영향을 미칠 수 있습니다 (지금 같은 예제) 지금처럼 힌트를 사용하게 되면 옵티마이저가 서브 쿼리를 조인 전에 미리 실행하게 강제할 수 있습니다
주의사항
다만 PUSH_SUBQ 힌트는 Unnesting 되지 않은 서브 쿼리에서만 동작하게 됩니다 서브 쿼리가 Unnesting 되면 필터가 아닌 다양한 조인 방식으로 실행이 되는데 그 순간 이 힌트는 무용지물이 되게 됩니다 그래서 PUSH_SUBQ 힌트를 사용할 때는 반드시 no_unnest 힌트와 같이 사용이 되어야 합니다