내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
2024
10.12
14:00
10.12
14:00
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
방법 : 오라클 19c dbms_xplan.display_cursor hint report 포맷
쿼리에 문제가 생겼을 때 실행계획을 보기위해 dbms_xplan.display_cursor 을 사용함
19c에서는 hint_report라는 포맷이 생겨서 어떤 힌트가 사용되었고 어떤 힌트가 사용이 되지않았는지(사용실패), 왜 사용실패되었는지를 알려줌
힌트 리포트는 세가지로 구분됨
U(Unused, 구문은 맞지만 미사용됨),
N(Unresolved, 구문은 맞지만 쿼리에 없는 별칭사용),
E(Syntax error, 구문에러)
아래는 테스트를 통해 어떤식으로 표시되는지 보여줌
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
|
SQL>
drop table monjip purge;
create table monjip (code varchar2(10), mon varchar2(10));
insert into monjip values ('A','202001');
commit;
drop index monjip_idx2 ;
create index monjip_idx2 on monjip(mon, code);
|
쿼리 실행 및 실행계획 확인1
select 절에 use_concat full(monjip) index(test) testtest(dual)를 사용(유효한 힌트는 full(monjip))
|
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
|
SQL> select /*+ gather_plan_statistics use_concat full(monjip) index(test) testtest(dual) */
count(*) from monjip
where code like 'A%'
and mon between '202001' and '202012';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3g9dq46qvczwa, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_concat full(monjip) index(test)
testtest(dual) */ count(*) from monjip where code like 'A%' and mon
between '202001' and '202012'
Plan hash value: 2046792910
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 874 (100)| | 1 |00:00:00.10 | 3091 |
| 1 | SORT AGGREGATE | | 1 | 1 | 14 | | | 1 |00:00:00.10 | 3091 |
|* 2 | TABLE ACCESS FULL| MONJIP | 1 | 200 | 2800 | 874 (3)| 00:00:01 | 12 |00:00:00.10 | 3091 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / MONJIP@SEL$1
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$1")
FULL(@"SEL$1" "MONJIP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MON">='202001' AND "MON"<='202012' AND "CODE" LIKE 'A%'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (rowset=60)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1), N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------------------
1 - SEL$1
N - index(test)
U - use_concat
E - testtest
2 - SEL$1 / MONJIP@SEL$1
- full(monjip)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[MONJIP]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
69 rows selected.
|
32번째 줄에 이전 버전에도 존재하던 Outline Data(Oracle이 사용한 Hidden Hint 목록) 이 나오고
57번째 줄에 Hint Report가 나옴
select 절에 use_concat full(monjip) index(test) testtest(dual)를 사용하였는데
use_concat 힌트는 힌트구문은 맞지만 쿼리에 적용될수 없는 힌트라 U로 표시되었고
index(test) 힌트는 힌트구문은 맞지만 존재하지 않는 인덱스를 넣어서 N로 표시되었고
testtest(dual) 힌트는 힌트구문이 틀려서 E로 표시됨
full(monjip) 힌트는 힌트구문이 맞고 쿼리에 적용 가능하기 때문에 E,U,N 표시가 생기지 않음
DBMS_XPLAN.DISPLAY_CURSOR 포맷에 'ADVANCED ALLSTATS LAST'을 넣지않고 HINT_REPORT만 넣으면 Hint Report와 Predicate Information 정보만 나옴
쿼리 실행 및 실행계획 확인2
select 절에 full(monjip) index_ss(monjip monjip_idx2) 를 같이 사용
|
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
|
SQL> select /*+ gather_plan_statistics full(monjip) index_ss(monjip monjip_idx2) */ count(*) from monjip
where code like 'A%'
and mon between '202001' and '202012';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'HINT_REPORT'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 46n7uq59ccjva, child number 2
-------------------------------------
select /*+ gather_plan_statistics full(monjip) index_ss(monjip
monjip_idx2) */ count(*) from monjip where code like 'A%' and mon
between '202001' and '202012'
Plan hash value: 577717968
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | INDEX RANGE SCAN| MONJIP_IDX2 | 1 | 14 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MON">='202001' AND "CODE" LIKE 'A%' AND "MON"<='202012')
filter("CODE" LIKE 'A%')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
2 - SEL$1 / MONJIP@SEL$1
U - full(monjip) / hint conflicts with another in sibling query block
U - index_ss(monjip monjip_idx2) / hint conflicts with another in sibling query block
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
34 rows selected.
|
select 절에 full(monjip) index_ss(monjip monjip_idx2)를 사용하였는데
full(monjip) 힌트는 힌트구문이 맞고 쿼리에 적용가능함
index_ss(monjip monjip_idx2) 힌트도 힌트구문이 맞고 쿼리에 적용 가능하지만
두가지 힌트를 동시에 사용했기때문에(충돌) U로 표시되었고(미사용)
Hint Report 에 'hint conflicts with another in sibling query block'(sibling 쿼리 블록에서 힌트가 다른 것과 충돌합니다) 라고 표시됨
두가지 힌트가 미사용 되었기 때문에 옵티마이저가 판단해서 실행계획이 index range scan으로 풀림
쿼리 실행 및 실행계획 확인3
select 절에 존재하지 않는 인덱스를 사용한 경우
|
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
|
SQL> select /*+ gather_plan_statistics index(monjip testidx1) */
count(*) from monjip
where code like 'A%'
and mon between '202001' and '202012';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'HINT_REPORT'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7b88369gc66nx, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(monjip testidx1) */ count(*)
from monjip where code like 'A%' and mon between '202001' and '202012'
Plan hash value: 577717968
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | INDEX RANGE SCAN| MONJIP_IDX2 | 1 | 14 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MON">='202001' AND "CODE" LIKE 'A%' AND "MON"<='202012')
filter("CODE" LIKE 'A%')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$1 / MONJIP@SEL$1
U - index(monjip testidx1) / index specified in the hint doesn't exist
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
32 rows selected.
|
select 절에 존재하지 않는 인덱스를 넣은 힌트(monjip testidx1)를 사용한 경우 U로 표시되었고(미사용)
'index specified in the hint doesn't exist'(힌트에 지정된 인덱스가 존재하지 않습니다)로 표시됨
19c의 DBMS_XPLAN.DISPLAY_CURSOR Hint Report을 활용해서 어떤 쿼리블록에 어떤 힌트가 제대로 적용이 안되고 있는지 조금더 쉽게 파악할 수 있음
https://franckpachot.medium.com/oracle-19c-hint-usage-reporting-345563a461f0
2735444.1
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| sqlp 실기튜닝실습 20-1 인덱스 최적화 구성 devDo (0) | 2022.03.06 |
|---|---|
| 오라클 11gR1 이상버전에서 보이는 Nested Loop 조인 실행계획(Prefetch, vector I/O) (0) | 2021.11.16 |
| 로그스위치 과다 발생 시 확인 사항 (0) | 2020.08.03 |
| 오라클 19c Statspack 리포트 생성 가이드 (2) | 2020.07.28 |
| sql 실행계획 확인 및 cpu 등 성능확인 스크립트 (3) | 2020.03.26 |