OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 튜닝시 qb_name 힌트 사용 방법
본문에서는 오라클에서 qb_name 힌트를 이용해 leading 및 index, full 힌트 등을 사용하는 방법을 설명함
메인쿼리에서 쿼리블록명을 이용해서 힌트를 사용할수도 있고 서브쿼리 내에 힌트를 넣어줄 수도 있음
편한 방식으로 사용하면됨
테스트
샘플 테이블 및 인덱스 생성
from절 인라인뷰 예제
from절 인라인뷰 예제(힌트 다르게 사용)
where절 서브쿼리 예제
where절 서브쿼리 예제(힌트 다르게 사용)
where절 서브쿼리 push_subq 예제
where절 서브쿼리 push_subq 예제(힌트 다르게 사용)
테스트
샘플 테이블 및 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
create table users (
user_id number,
user_name varchar2(50)
);
create table orders (
order_id number,
user_id number,
order_date date
);
create table items (
item_id number,
order_id number,
item_name varchar2(50)
);
create index users_ix1 on users(user_id);
create index items_ix1 on items(order_id);
create index orders_ix1 on orders(user_id);
|
from절 인라인뷰 예제
먼저 from절 인라인뷰의 경우 from절에 ()로 서브쿼리가 감싸지고 v와 같은 alias를 달수 있기때문에
qb_name을 사용하기 보다 인라인뷰 alias를 사용하면 됨
먼저 v 인라인뷰를 읽고 이 안에서 orders 테이블을 먼저 읽고 items 테이블로 nl join함
이후 이 집합과 users 테이블(메인쿼리)를 hash join함
이때 orders 테이블은 full로 읽고, items 테이블과 users 테이블은 index를 이용해 읽음
|
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
SQL>
alter session set statistics_level = all;
select /*+ qb_name(main)
leading(v@main)
use_hash(u)
index(u users_ix1)
no_merge(v@main)
leading(v.o)
use_nl(v.i)
full(v.o)
index(v.i items_ix1)
*/
u.user_id,
u.user_name,
v.item_count
from users u,
(select /*+ qb_name(sub) */
o.user_id,
count(i.item_id) as item_count
from orders o,
items i
where o.order_id = i.order_id
group by o.user_id) v
where u.user_id = v.user_id
and u.user_id <= 100;
select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry'));
Plan hash value: 278655277
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 66 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 1209K| 1209K| 452K (0)|
| 2 | VIEW | | 1 | 1 | 26 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | | | |
| 3 | HASH GROUP BY | | 1 | 1 | 52 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 952K| 952K| |
| 4 | NESTED LOOPS | | 1 | 1 | 52 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 52 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 6 | TABLE ACCESS FULL | ORDERS | 1 | 1 | 26 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 7 | INDEX RANGE SCAN | ITEMS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | ITEMS | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| USERS | 0 | 1 | 40 | 0 (0)| | 0 |00:00:00.01 | | | |
|* 10 | INDEX RANGE SCAN | USERS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MAIN
2 - SUB / V@MAIN
3 - SUB
6 - SUB / O@SUB
7 - SUB / I@SUB
8 - SUB / I@SUB
9 - MAIN / U@MAIN
10 - MAIN / U@MAIN
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SUB")
OUTLINE_LEAF(@"MAIN")
NO_ACCESS(@"MAIN" "V"@"MAIN")
INDEX_RS_ASC(@"MAIN" "U"@"MAIN" ("USERS"."USER_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"MAIN" "U"@"MAIN")
LEADING(@"MAIN" "V"@"MAIN" "U"@"MAIN")
USE_HASH(@"MAIN" "U"@"MAIN")
FULL(@"SUB" "O"@"SUB")
INDEX(@"SUB" "I"@"SUB" ("ITEMS"."ORDER_ID"))
LEADING(@"SUB" "O"@"SUB" "I"@"SUB")
USE_NL(@"SUB" "I"@"SUB")
NLJ_BATCHING(@"SUB" "I"@"SUB")
USE_HASH_AGGREGATION(@"SUB")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("U"."USER_ID"="V"."USER_ID")
6 - filter("O"."USER_ID"<=100)
7 - access("O"."ORDER_ID"="I"."ORDER_ID")
10 - access("U"."USER_ID"<=100)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 10
---------------------------------------------------------------------------
1 - MAIN
- leading(v@main)
- qb_name(main)
1 - MAIN / V@MAIN
- no_merge(v@main)
3 - SUB
- leading(v.o)
- qb_name(sub)
6 - SUB / O@SUB
- full(v.o)
7 - SUB / I@SUB
- index(v.i items_ix1)
- use_nl(v.i)
9 - MAIN / U@MAIN
- index(u users_ix1)
- use_hash(u)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
104 rows selected.
|
from절 인라인뷰 예제(힌트 다르게 사용)
위 쿼리는 아래와 같이 사용할 수도 있음
|
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
SQL>
select /*+ qb_name(main)
leading(v)
use_hash(u)
index(u users_ix1)
*/
u.user_id,
u.user_name,
v.item_count
from users u,
(select /*+ qb_name(sub) no_merge leading(o) use_nl(i) full(o) index(i items_ix1) */
o.user_id,
count(i.item_id) as item_count
from orders o,
items i
where o.order_id = i.order_id
group by o.user_id) v
where u.user_id = v.user_id
and u.user_id <= 100;
select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry'));
Plan hash value: 278655277
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 66 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 1209K| 1209K| 467K (0)|
| 2 | VIEW | | 1 | 1 | 26 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | | | |
| 3 | HASH GROUP BY | | 1 | 1 | 52 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 952K| 952K| |
| 4 | NESTED LOOPS | | 1 | 1 | 52 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 52 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 6 | TABLE ACCESS FULL | ORDERS | 1 | 1 | 26 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 7 | INDEX RANGE SCAN | ITEMS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | ITEMS | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| USERS | 0 | 1 | 40 | 0 (0)| | 0 |00:00:00.01 | | | |
|* 10 | INDEX RANGE SCAN | USERS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MAIN
2 - SUB / V@MAIN
3 - SUB
6 - SUB / O@SUB
7 - SUB / I@SUB
8 - SUB / I@SUB
9 - MAIN / U@MAIN
10 - MAIN / U@MAIN
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SUB")
OUTLINE_LEAF(@"MAIN")
NO_ACCESS(@"MAIN" "V"@"MAIN")
INDEX_RS_ASC(@"MAIN" "U"@"MAIN" ("USERS"."USER_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"MAIN" "U"@"MAIN")
LEADING(@"MAIN" "V"@"MAIN" "U"@"MAIN")
USE_HASH(@"MAIN" "U"@"MAIN")
FULL(@"SUB" "O"@"SUB")
INDEX(@"SUB" "I"@"SUB" ("ITEMS"."ORDER_ID"))
LEADING(@"SUB" "O"@"SUB" "I"@"SUB")
USE_NL(@"SUB" "I"@"SUB")
NLJ_BATCHING(@"SUB" "I"@"SUB")
USE_HASH_AGGREGATION(@"SUB")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("U"."USER_ID"="V"."USER_ID")
6 - filter("O"."USER_ID"<=100)
7 - access("O"."ORDER_ID"="I"."ORDER_ID")
10 - access("U"."USER_ID"<=100)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 10
---------------------------------------------------------------------------
1 - MAIN
- leading(v)
- qb_name(main)
3 - SUB
- leading(o)
- no_merge
- qb_name(sub)
6 - SUB / O@SUB
- full(o)
7 - SUB / I@SUB
- index(i items_ix1)
- use_nl(i)
9 - MAIN / U@MAIN
- index(u users_ix1)
- use_hash(u)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
102 rows selected.
|
플랜이 동일한것을 확인할 수 있음
where절 서브쿼리 예제
where절 서브쿼리의 경우 ()이후 alias 를 달수 없기 때문에 qb_name을 사용해서 조작을 해줘야함
아래 예제는 unnest된 서브쿼리를 먼저 읽되 이 안에서 orders 테이블을 먼저 읽고 items 테이블로 hash join함
그리고 이 서브쿼리와 메인쿼리를 nl join함
이때 orders 테이블은 full로 읽고, items 테이블과 users 테이블은 index를 이용해 읽음
|
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
SQL>
alter session set statistics_level = all;
select /*+ qb_name(main)
leading(@sub o@sub i@sub)
unnest(@sub)
use_nl(u@main)
full(@sub o)
index(@sub i items_ix1)
index(@main u users_ix1)
*/
u.user_id,
u.user_name
from users u
where u.user_id in (select /*+ qb_name(sub) use_hash(i) */
o.user_id
from orders o,
items i
where o.order_id = i.order_id
and i.item_name = 'tuning book');
select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry'));
Plan hash value: 3753165389
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 53 | 4 (25)| 00:00:01 | 0 |00:00:00.01 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 53 | 4 (25)| 00:00:01 | 0 |00:00:00.01 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 1 | 13 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 66 | | | 0 |00:00:00.01 | 906K| 906K| |
|* 5 | HASH JOIN | | 1 | 1 | 66 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 1209K| 1209K| 454K (0)|
| 6 | TABLE ACCESS FULL | ORDERS | 1 | 1 | 26 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEMS | 0 | 1 | 40 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
| 8 | INDEX FULL SCAN | ITEMS_IX1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 9 | INDEX RANGE SCAN | USERS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | USERS | 0 | 1 | 40 | 0 (0)| | 0 |00:00:00.01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$526A7031
3 - SEL$75CB63B7 / VW_NSO_1@SEL$526A7031
4 - SEL$75CB63B7
6 - SEL$75CB63B7 / O@SUB
7 - SEL$75CB63B7 / I@SUB
8 - SEL$75CB63B7 / I@SUB
9 - SEL$526A7031 / U@MAIN
10 - SEL$526A7031 / U@MAIN
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$75CB63B7")
OUTLINE_LEAF(@"SEL$526A7031")
UNNEST(@"SUB" UNNEST_SEMIJ_VIEW)
OUTLINE(@"SUB")
OUTLINE(@"MAIN")
NO_ACCESS(@"SEL$526A7031" "VW_NSO_1"@"SEL$526A7031")
INDEX(@"SEL$526A7031" "U"@"MAIN" ("USERS"."USER_ID"))
LEADING(@"SEL$526A7031" "VW_NSO_1"@"SEL$526A7031" "U"@"MAIN")
USE_NL(@"SEL$526A7031" "U"@"MAIN")
NLJ_BATCHING(@"SEL$526A7031" "U"@"MAIN")
SEMI_TO_INNER(@"SEL$526A7031" "VW_NSO_1"@"SEL$526A7031")
FULL(@"SEL$75CB63B7" "O"@"SUB")
INDEX(@"SEL$75CB63B7" "I"@"SUB" ("ITEMS"."ORDER_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$75CB63B7" "I"@"SUB")
LEADING(@"SEL$75CB63B7" "O"@"SUB" "I"@"SUB")
USE_HASH(@"SEL$75CB63B7" "I"@"SUB")
USE_HASH_AGGREGATION(@"SEL$75CB63B7")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."ORDER_ID"="I"."ORDER_ID")
7 - filter("I"."ITEM_NAME"='tuning book')
9 - access("U"."USER_ID"="USER_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 9
---------------------------------------------------------------------------
1 - SEL$526A7031
- qb_name(main)
4 - SEL$75CB63B7
- leading(@sub o@sub i@sub)
- qb_name(sub)
- unnest(@sub)
6 - SEL$75CB63B7 / O@SUB
- full(@sub o)
7 - SEL$75CB63B7 / I@SUB
- index(@sub i items_ix1)
- use_hash(i)
9 - SEL$526A7031 / U@MAIN
- index(@main u users_ix1)
- use_nl(u@main)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
104 rows selected.
|
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
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
SQL>
select /*+ qb_name(main)
leading(vw_nso_1)
use_nl(u@main)
index(u users_ix1)
*/
u.user_id,
u.user_name
from users u
where u.user_id in (select /*+ qb_name(sub) unnest leading(o) use_hash(i) full(o) index(i items_ix1) */
o.user_id
from orders o,
items i
where o.order_id = i.order_id
and i.item_name = 'tuning book');
select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry'));
Plan hash value: 3753165389
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 53 | 4 (25)| 00:00:01 | 0 |00:00:00.01 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 53 | 4 (25)| 00:00:01 | 0 |00:00:00.01 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 1 | 13 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 66 | | | 0 |00:00:00.01 | 906K| 906K| |
|* 5 | HASH JOIN | | 1 | 1 | 66 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 1209K| 1209K| 423K (0)|
| 6 | TABLE ACCESS FULL | ORDERS | 1 | 1 | 26 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ITEMS | 0 | 1 | 40 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
| 8 | INDEX FULL SCAN | ITEMS_IX1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | | | |
|* 9 | INDEX RANGE SCAN | USERS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | USERS | 0 | 1 | 40 | 0 (0)| | 0 |00:00:00.01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$526A7031
3 - SEL$75CB63B7 / VW_NSO_1@SEL$526A7031
4 - SEL$75CB63B7
6 - SEL$75CB63B7 / O@SUB
7 - SEL$75CB63B7 / I@SUB
8 - SEL$75CB63B7 / I@SUB
9 - SEL$526A7031 / U@MAIN
10 - SEL$526A7031 / U@MAIN
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$75CB63B7")
OUTLINE_LEAF(@"SEL$526A7031")
UNNEST(@"SUB" UNNEST_SEMIJ_VIEW)
OUTLINE(@"SUB")
OUTLINE(@"MAIN")
NO_ACCESS(@"SEL$526A7031" "VW_NSO_1"@"SEL$526A7031")
INDEX(@"SEL$526A7031" "U"@"MAIN" ("USERS"."USER_ID"))
LEADING(@"SEL$526A7031" "VW_NSO_1"@"SEL$526A7031" "U"@"MAIN")
USE_NL(@"SEL$526A7031" "U"@"MAIN")
NLJ_BATCHING(@"SEL$526A7031" "U"@"MAIN")
SEMI_TO_INNER(@"SEL$526A7031" "VW_NSO_1"@"SEL$526A7031")
FULL(@"SEL$75CB63B7" "O"@"SUB")
INDEX(@"SEL$75CB63B7" "I"@"SUB" ("ITEMS"."ORDER_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$75CB63B7" "I"@"SUB")
LEADING(@"SEL$75CB63B7" "O"@"SUB" "I"@"SUB")
USE_HASH(@"SEL$75CB63B7" "I"@"SUB")
USE_HASH_AGGREGATION(@"SEL$75CB63B7")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."ORDER_ID"="I"."ORDER_ID")
7 - filter("I"."ITEM_NAME"='tuning book')
9 - access("U"."USER_ID"="USER_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 10
---------------------------------------------------------------------------
1 - SEL$526A7031
- leading(vw_nso_1)
- qb_name(main)
4 - SEL$75CB63B7
- leading(o)
- qb_name(sub)
- unnest
6 - SEL$75CB63B7 / O@SUB
- full(o)
7 - SEL$75CB63B7 / I@SUB
- index(i items_ix1)
- use_hash(i)
9 - SEL$526A7031 / U@MAIN
- index(u users_ix1)
- use_nl(u@main)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
104 rows selected.
|
플랜이 동일한것을 확인할 수 있음
where절 서브쿼리 push_subq 예제
아래 예제는 서브쿼리를 no_unnest 하고 서브쿼리를 가능한 먼저 읽되(push_subq) 이 안에서 orders 테이블을 먼저 읽고 items 테이블로 nl join함
이때 orders 테이블과 users 테이블은 full로 읽고, items 테이블은 index를 이용해 읽음
|
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
SQL>
alter session set statistics_level = all;
select /*+ qb_name(main)
leading(@sub o@sub i@sub)
use_nl(@sub i@sub)
full(@sub o)
index(@sub i items_ix1)
no_unnest(@sub)
push_subq(@sub)
full(@main u)
*/
u.user_id,
u.user_name
from users u
where u.user_id in (select /*+ qb_name(sub) */
o.user_id
from orders o,
items i
where o.order_id = i.order_id
and i.item_name = 'tuning book');
select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry'));
Plan hash value: 4209390604
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 |
|* 1 | TABLE ACCESS FULL | USERS | 1 | 1 | 40 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 0 | 1 | 66 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | NESTED LOOPS | | 0 | 1 | 66 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 4 | TABLE ACCESS FULL | ORDERS | 0 | 1 | 26 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 5 | INDEX RANGE SCAN | ITEMS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| ITEMS | 0 | 1 | 40 | 0 (0)| | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MAIN / U@MAIN
2 - SUB
4 - SUB / O@SUB
5 - SUB / I@SUB
6 - SUB / I@SUB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SUB")
OUTLINE_LEAF(@"MAIN")
FULL(@"MAIN" "U"@"MAIN")
PUSH_SUBQ(@"SUB")
FULL(@"SUB" "O"@"SUB")
INDEX(@"SUB" "I"@"SUB" ("ITEMS"."ORDER_ID"))
LEADING(@"SUB" "O"@"SUB" "I"@"SUB")
USE_NL(@"SUB" "I"@"SUB")
NLJ_BATCHING(@"SUB" "I"@"SUB")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
4 - filter("O"."USER_ID"=:B1)
5 - access("O"."ORDER_ID"="I"."ORDER_ID")
6 - filter("I"."ITEM_NAME"='tuning book')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 9
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / U@MAIN
- full(@main u)
2 - SUB
- leading(@sub o@sub i@sub)
- no_unnest(@sub)
- push_subq(@sub)
- qb_name(sub)
4 - SUB / O@SUB
- full(@sub o)
5 - SUB / I@SUB
- index(@sub i items_ix1)
- use_nl(@sub i@sub)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
90 rows selected.
|
where절 서브쿼리 push_subq 예제(힌트 다르게 사용)
위 쿼리는 아래와 같이 사용할 수도 있음
|
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
SQL>
select /*+ qb_name(main)
full(u)
*/
u.user_id,
u.user_name
from users u
where u.user_id in (select /*+ qb_name(sub) no_unnest push_subq leading(o) use_nl(i) full(o) index(i items_ix1)*/
o.user_id
from orders o,
items i
where o.order_id = i.order_id
and i.item_name = 'tuning book');
select * from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last -Projection -qbregistry'));
Plan hash value: 4209390604
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 |
|* 1 | TABLE ACCESS FULL | USERS | 1 | 1 | 40 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 0 | 1 | 66 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | NESTED LOOPS | | 0 | 1 | 66 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 4 | TABLE ACCESS FULL | ORDERS | 0 | 1 | 26 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
|* 5 | INDEX RANGE SCAN | ITEMS_IX1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| ITEMS | 0 | 1 | 40 | 0 (0)| | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MAIN / U@MAIN
2 - SUB
4 - SUB / O@SUB
5 - SUB / I@SUB
6 - SUB / I@SUB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SUB")
OUTLINE_LEAF(@"MAIN")
FULL(@"MAIN" "U"@"MAIN")
PUSH_SUBQ(@"SUB")
FULL(@"SUB" "O"@"SUB")
INDEX(@"SUB" "I"@"SUB" ("ITEMS"."ORDER_ID"))
LEADING(@"SUB" "O"@"SUB" "I"@"SUB")
USE_NL(@"SUB" "I"@"SUB")
NLJ_BATCHING(@"SUB" "I"@"SUB")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
4 - filter("O"."USER_ID"=:B1)
5 - access("O"."ORDER_ID"="I"."ORDER_ID")
6 - filter("I"."ITEM_NAME"='tuning book')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 9
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / U@MAIN
- full(u)
2 - SUB
- leading(o)
- no_unnest
- push_subq
- qb_name(sub)
4 - SUB / O@SUB
- full(o)
5 - SUB / I@SUB
- index(i items_ix1)
- use_nl(i)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
89 rows selected.
|
플랜이 동일한것을 확인할 수 있음
결론 :
여러 방식으로 힌트를 주고 쿼리를 조작할 수 있음
쿼리변환이 많이 되는 쿼리의 경우 qb_name이 변경될수 있으니 Query Block Name 부분을 잘 보고 힌트를 사용해야함
참조 :
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선2 (1) | 2025.08.21 |
|---|---|
| 오라클 19c 인덱스 선두컬럼 값이 null인 경우 성능 테스트 (0) | 2025.08.14 |
| 오라클 19c 오브젝트 100만개 생성 및 shared pool 확인 테스트 (0) | 2025.05.26 |
| 오라클 19c Insert 시 Undo TS Autoextend 옵션에 따른 성능 차이 분석 (2) | 2025.05.05 |
| 오라클 19c 인덱스 nologging 사용시 append insert 속도 차이 확인 (0) | 2025.04.27 |