OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.5.0.24.07 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 xplan Hint Report 개선
오라클 23ai를 테스트하다가 xplan 결과 중 hint report 부분이 개선됨을 확인함
기존에는 hint report 부분에 힌트가 제대로 동작하지 않은경우 U 라고만 표시되었는데, 23ai에서는 U 뒤에 설명이 일부 추가됨
(19c에서도 이런 기능이 있었지만 23ai에서 더 많은 힌트에 설명들이 추가된듯함)
19c 옵티마이저 관련 WHITE PAPER를 보면 25p에 full 힌트를 메인쿼리와 서브쿼리에 모두 사용했을때
xplan hint report에 "hint overridden by another in parent query block" 메세지가 나오는것을 보여주고 있음
실제 19c에서 테스트해보면 아래와 같이 메세지가 표시됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select /*+ full(@sel$2 t1) */ count(*) from emp2 t1
where deptno in (select /*+ full(t1) */ deptno from dept2 t1 where deptno < 20);
(결과 생략)
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
4 - SEL$5DA710D3 / T1@SEL$2
U - full(t1) / hint overridden by another in parent query block
- full(@sel$2 t1)
|
참고 :
본문에서는 이외에도 어떤 부분이 개선되었는지 쿼리를 수행하며 확인해봄
테스트
기본 테스트
추가 테스트1. Join Predicate PushDown(JPPD)
추가 테스트2. View Merging
추가 테스트3. Subquery Unnesting
테스트
기본 테스트
샘플 테이블 생성 및 데이터 삽입
|
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
|
SQL>
drop table t1;
drop table t2;
create table t1 (col1 number, col2 number, col3 varchar2(10));
create table t2 (col1 number, col2 number, col3 varchar2(10));
create table t3 (col1 number, col2 number, col3 varchar2(10));
create index t1_ix1 on t1(col1);
create index t1_ix2 on t1(col2);
create index t1_ix3 on t1(col3);
--create index t2_ix1 on t2(col1);
--create index t2_ix2 on t2(col2);
--create index t2_ix3 on t2(col3);
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO t1 (col1, col2, col3)
VALUES (i, i+i, DBMS_RANDOM.STRING('A', 10));
END LOOP;
FOR i IN 101..200 LOOP
INSERT INTO t2 (col1, col2, col3)
VALUES (i, i+i, DBMS_RANDOM.STRING('A', 10));
END LOOP;
COMMIT;
END;
/
|
샘플 쿼리 수행
|
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
select /*+ gather_plan_statistics leading(t2) index(t2) */
t1.col1, t2.col2
from t1, t2
where t1.col1 = t2.col1
and t2.col2 like 'A%'
and t1.col1 >= 1;
(결과 생략)
|
dbms_xplan.display_cursor 결과 확인(19c)
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1
- leading(t2)
2 - SEL$1 / T2@SEL$1
U - index(t2)
|
t2 테이블에는 인덱스가 없기 때문에 Hint Report 부분에 U(Unused) 라고만 표시됨
dbms_xplan.display_cursor 결과 확인(23ai)
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1
- leading(t2)
2 - SEL$1 / "T2"@"SEL$1"
U - index(t2) / index specified in the hint doesn't exist
|
t2 테이블에는 인덱스가 없기 때문에 Hint Report 부분에 U(Unused) 라고 표시됨
그리고 19c와는 다르게 추가로 "index specified in the hint doesn't exist" 라는 말까지 덧붙여줌
추가 테스트 및 Hint Report에 표시되는 사항 확인(23ai)
추가 테스트1. Join Predicate PushDown(JPPD)
t2 테이블에 인덱스 생성
|
1
2
3
4
|
SQL>
create index t2_ix1 on t2(col1);
create index t2_ix2 on t2(col2);
create index t2_ix3 on t2(col3);
|
테스트 전 statistics_level파라미터 all로 변경
|
1
2
3
|
SQL> alter session set statistics_level = all;
Session altered.
|
jppd 정상 동작
|
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
|
SQL>
select /*+ qb_name(main) no_merge(t2_view) push_pred(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, t2.col2
from t2
where t2.col2 > 10) t2_view
where
t1.col1 = t2_view.col1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 203 (100)| | 0 |00:00:00.01 | 11 |
| 1 | NESTED LOOPS | | 1 | 100 | 2800 | 203 (0)| 00:00:01 | 0 |00:00:00.01 | 11 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 100 | 2600 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 7 |
| 3 | VIEW PUSHED PREDICATE | | 100 | 1 | 2 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 4 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 100 | 1 | 26 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | T2_IX2 | 100 | 100 | | 1 (0)| 00:00:01 | 10000 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
- no_merge(t2_view)
- push_pred(t2_view)
3 - SEL$1B3DFE22
- qb_name(invw)
|
인라인뷰에서 전체 대상으로 집계함수 사용하여 jppd(push_pred 힌트)가 미동작
|
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
|
SQL>
select /*+ qb_name(main) no_merge(t2_view) push_pred(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
sum(t2.col1) sumcol1, sum(t2.col2)
from t2
where t2.col2 > 10) t2_view
where
t1.col1 = t2_view.sumcol1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 3 |
| 1 | NESTED LOOPS | | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 2 | VIEW | | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 3 | SORT AGGREGATE | | 1 | 1 | 26 | | | 1 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | T1_IX1 | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - push_pred(t2_view) / View has aggregate but no GROUP BY
- no_merge(t2_view)
3 - INVW
- qb_name(invw)
|
push_pred에 대해 U(Unused) 로 표시되고 "View has aggregate but no GROUP BY" 메세지가 함께 출력됨
인라인뷰에서 having 절 사용하여 jppd(push_pred 힌트)가 미동작
|
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
|
SQL>
select /*+ qb_name(main) no_merge(t2_view) push_pred(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, sum(t2.col2) sumcol2
from t2
where t2.col2 > 10
group by t2.col1
having sumcol2 > 100) t2_view
where
t1.col1 = t2_view.col1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | 5 |
| 1 | NESTED LOOPS | | 1 | 100 | 3900 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 5 |
| 2 | NESTED LOOPS | | 1 | 100 | 3900 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 | 100 | 1300 | 3 (34)| 00:00:01 | 100 |00:00:00.01 | 2 |
|* 4 | HASH GROUP BY | | 1 | 100 | 2600 | 3 (34)| 00:00:01 | 100 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | T1_IX1 | 100 | 1 | | 0 (0)| | 0 |00:00:00.01 | 3 |
| 8 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - push_pred(t2_view) / View has non-standard GROUP BY
- no_merge(t2_view)
4 - INVW
- qb_name(invw)
|
push_pred에 대해 U(Unused) 로 표시되고 "View has non-standard GROUP BY" 메세지가 함께 출력됨
인라인뷰에서 rownum을 사용하여 jppd(push_pred 힌트)가 미동작
|
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
|
SQL>
select /*+ qb_name(main) no_merge(t2_view) push_pred(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, t2.col2
from t2
where t2.col2 > 10
and rownum <= 1) t2_view
where
t1.col1 = t2_view.col1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 3 |
| 1 | NESTED LOOPS | | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 2 | NESTED LOOPS | | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 3 | VIEW | | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 4 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | T1_IX1 | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - push_pred(t2_view) / View contains a ROWNUM reference
- no_merge(t2_view)
4 - INVW
- qb_name(invw)
|
push_pred에 대해 U(Unused) 로 표시되고 "View contains a ROWNUM reference" 메세지가 함께 출력됨
jppd용 조인 조건절을 누락하여 jppd(push_pred 힌트)가 미동작
|
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
|
SQL>
select /*+ qb_name(main) no_merge(t2_view) push_pred(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, t2.col2
from t2
where t2.col2 > 10) t2_view
where
t1.col1 = 123;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 0 |00:00:00.01 | 2 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 1 | 26 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
| 2 | VIEW | | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
|* 3 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | 1300 | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
| 4 | BUFFER SORT | | 100 | 1 | 26 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 6 | INDEX RANGE SCAN | T1_IX1 | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - push_pred(t2_view) / No valid join condition found
- no_merge(t2_view)
2 - INVW
- qb_name(invw)
|
push_pred에 대해 U(Unused) 로 표시되고 "No valid join condition found" 메세지가 함께 출력됨
추가 테스트2. View Merging
뷰머징 정상 동작
|
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
|
SQL>
select /*+ qb_name(main) merge(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, t2.col2
from t2
where t2.col2 > 10) t2_view
where
t1.col1 = t2_view.col1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 5 |
| 1 | NESTED LOOPS | | 1 | 100 | 5200 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 5 |
| 2 | NESTED LOOPS | | 1 | 100 | 5200 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
|* 5 | INDEX RANGE SCAN | T1_IX1 | 100 | 1 | | 0 (0)| | 0 |00:00:00.01 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 |
------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
1 - SEL$0CA7B74D
- qb_name(invw)
- qb_name(main)
1 - SEL$0CA7B74D / "T2_VIEW"@"MAIN"
- merge(t2_view)
|
인라인뷰에서 rownum을 사용하여 뷰머징(merge 힌트)이 미동작
|
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
|
SQL>
select /*+ qb_name(main) merge(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, t2.col2
from t2
where t2.col2 > 10
and rownum <= 1) t2_view
where
t1.col1 = t2_view.col1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 3 |
| 1 | NESTED LOOPS | | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 2 | NESTED LOOPS | | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 3 | VIEW | | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 4 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | T1_IX1 | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - merge(t2_view) / ROWNUM column
4 - INVW
- qb_name(invw)
|
merge에 대해 U(Unused) 로 표시되고 "ROWNUM column" 메세지가 함께 출력됨
인라인뷰에서 union 사용하여 뷰머징(merge 힌트)이 미동작
|
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
|
SQL>
select /*+ qb_name(main) merge(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, t2.col2
from t2
where t2.col2 > 10
union all
select
t2.col1, t2.col2
from t2
where t2.col2 < 5
) t2_view
where
t1.col1 = t2_view.col1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | 101 | 3939 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | | 1 | 101 | 3939 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
| 3 | VIEW | | 1 | 101 | 1313 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 3 |
| 4 | UNION-ALL | | 1 | 101 | 2626 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 8 | INDEX RANGE SCAN | T2_IX2 | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 9 | INDEX RANGE SCAN | T1_IX1 | 100 | 1 | | 0 (0)| | 0 |00:00:00.01 | 3 |
| 10 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - merge(t2_view) / SET (union, intersect, etc.) operator
5 - INVW
- qb_name(invw)
|
merge에 대해 U(Unused) 로 표시되고 "SET (union, intersect, etc.) operator" 메세지가 함께 출력됨
인라인뷰에서 전체 대상으로 집계함수 사용하여 뷰머징(merge 힌트)이 미동작
|
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
|
SQL>
select /*+ qb_name(main) merge(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
sum(t2.col1) sumcol1, sum(t2.col2)
from t2
where t2.col2 > 10) t2_view
where
t1.col1 = t2_view.sumcol1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 3 |
| 1 | NESTED LOOPS | | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 2 | VIEW | | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 3 | SORT AGGREGATE | | 1 | 1 | 26 | | | 1 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | T1_IX1 | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - merge(t2_view) / Single GROUPING SET function (aggr) without GROUP BY
3 - INVW
- qb_name(invw)
|
merge에 대해 U(Unused) 로 표시되고 "Single GROUPING SET function (aggr) without GROUP BY" 메세지가 함께 출력됨
인라인뷰에서 윈도우 함수를 사용하여 뷰머징(merge 힌트)이 미동작
|
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
|
SQL>
select /*+ qb_name(main) merge(t2_view) */
t1.col1, t1.col2
from
t1,
(select /*+ qb_name(invw) */
t2.col1, row_number() over (partition by t2.col1 order by t2.col2 desc) as rn
from t2
where t2.col2 > 10) t2_view
where
t1.col1 = t2_view.col1
and t2_view.rn = 1;
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 3 (100)| | 0 |00:00:00.01 | 5 | | | |
| 1 | NESTED LOOPS | | 1 | 3 | 156 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
| 2 | NESTED LOOPS | | 1 | 3 | 156 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
|* 3 | VIEW | | 1 | 3 | 78 | 3 (34)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |
|* 4 | WINDOW SORT PUSHED RANK | | 1 | 100 | 2600 | 3 (34)| 00:00:01 | 100 |00:00:00.01 | 2 | 6144 | 6144 | 6144 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 | | | |
|* 7 | INDEX RANGE SCAN | T1_IX1 | 100 | 1 | | 0 (0)| | 0 |00:00:00.01 | 3 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
1 - MAIN / "T2_VIEW"@"MAIN"
U - merge(t2_view) / Window functions in this view
4 - INVW
- qb_name(invw)
|
merge에 대해 U(Unused) 로 표시되고 "Window functions in this view" 메세지가 함께 출력됨
추가 테스트3. Subquery Unnesting
Unnesting 정상 동작
|
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
|
SQL>
select /*+ qb_name(main) */
t1.col1, t1.col2
from
t1
where
t1.col1 in ( select /*+ qb_name(sub) unnest */t2.col1 from t2 where t2.col2 > 10 );
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 3 (100)| | 0 |00:00:00.01 | 5 | | | |
| 1 | NESTED LOOPS | | 1 | 100 | 5200 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
| 2 | NESTED LOOPS | | 1 | 100 | 5200 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 5 | | | |
| 3 | SORT UNIQUE | | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | 6144 | 6144 | 6144 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | T1_IX1 | 100 | 1 | | 0 (0)| | 0 |00:00:00.01 | 3 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------3
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - SUB
- qb_name(sub)
- unnest
1 - SEL$526A7031
- qb_name(main)
|
서브쿼리에서 union 사용하여 unnest 미동작
|
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
|
SQL>
select /*+ qb_name(main) */
t1.col1, t1.col2
from
t1
where
t1.col1 in (
select /*+ qb_name(sub) unnest */ t2.col1 from t2 where t2.col2 > 10
union all
select t2.col1 from t2 where t2.col2 <= 10
);
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | 101 | 3939 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | | 1 | 101 | 3939 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
| 3 | VIEW | VW_NSO_1 | 1 | 101 | 1313 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 3 |
| 4 | HASH UNIQUE | | 1 | 101 | 2626 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 3 |
| 5 | UNION-ALL | | 1 | 101 | 2626 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 100 | 2600 | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 2 |
|* 7 | INDEX RANGE SCAN | T2_IX2 | 1 | 100 | | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 9 | INDEX RANGE SCAN | T2_IX2 | 1 | 1 | | 0 (0)| | 0 |00:00:00.01 | 1 |
|* 10 | INDEX RANGE SCAN | T1_IX1 | 100 | 1 | | 0 (0)| | 0 |00:00:00.01 | 3 |
| 11 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 26 | 0 (0)| | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$FA7A751B
- qb_name(main)
6 - SUB
U - unnest
- qb_name(sub)
|
이때는 U(Unused)만 표시되고 별다른 추가 내용이 안나옴
단일값 비상관 서브쿼리를 사용하여 unnest 미동작
|
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
|
SQL>
select /*+ qb_name(main) */
t1.col1, t1.col2
from
t1
where
t1.col1 = (select /*+ qb_name(sub) unnest */ sum(t2.col1) from t2);
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 26 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | T1_IX1 | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
| 3 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.01 | 1 |
| 4 | INDEX FULL SCAN | T2_IX1 | 1 | 100 | 1300 | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
3 - SUB
U - unnest / Failed basic validity checks
- qb_name(sub)
|
unnest에 대해 U(Unused) 로 표시되고 "Failed basic validity checks" 메세지가 함께 출력됨
not exists 시 서브쿼리에 메인쿼리의 컬럼을 기술하여 unnest 미동작
|
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
|
SQL>
select /*+ qb_name(main) */
t1.col1, t1.col2
from
t1
where
not exists (select /*+ qb_name(sub) unnest */ t1.col1 from t2 where t1.col1 = t2.col1);
SQL> select * from dbms_xplan.display_cursor(null, null, 'advanced allstats last');
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 55 (100)| | 100 |00:00:00.01 | 113 |
|* 1 | FILTER | | 1 | | | | | 100 |00:00:00.01 | 113 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 700 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 13 |
|* 3 | INDEX RANGE SCAN | T2_IX1 | 100 | 1 | 4 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 100 |
-----------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
1 - MAIN
- qb_name(main)
3 - SUB
U - unnest / invalidated
- qb_name(sub)
|
unnest에 대해 U(Unused) 로 표시되고 "invalidated" 메세지가 함께 출력됨
결론 :
오라클 23ai의 xplan hint report 기능이 개선되어 튜닝시 쿼리 변환이나 힌트가 왜 사용되지 않았는지에 대한 분석이 기존보다 빠르게 될 수 있을것으로 보임
버전이 올라가면서 이런 메세지들이 더 많이 추가되지 않을까 싶음
그리고 이 기능은 19c에서도 일부 동작하는 기능이기 때문에 23ai에 나오는것만큼 19c에도 백포트 되지않을까 예상해봄
참조 :
https://positivemh.tistory.com/775
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/influencing-the-optimizer.html#GUID-F9F20FDC-8AC9-429C-A4F9-3FF747077182
https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_XPLAN.html#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF
https://hrjeong.tistory.com/241
https://hrjeong.tistory.com/370
http://wiki.gurubee.net/pages/viewpage.action?pageId=26746676
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 AHF Fleet Insights 구성 가이드 (0) | 2025.01.12 |
|---|---|
| Oracle 23ai New Features Part 2 (Oct 2024) (0) | 2025.01.01 |
| Oracle 23ai New Features Part 1 (June 2024) (0) | 2025.01.01 |
| 오라클 23ai 신기능 Lock Free Reservation (0) | 2024.12.24 |
| 오라클 19c undo 사용률 관련 expired, unexpired 익스텐트 조회 방법 (0) | 2024.12.08 |