OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
방법 : 오라클 19c 실행계획 비교 DBMS_XPLAN.COMPARE_PLANS
오라클 19c New feature 인 DBMS_XPLAN.COMPARE_PLANS 을 이용해서 실행계획을 비교해볼수 있음
인덱스 사용 유무도 확인을 할수 있고 본문에서 테스트할 QT(Query Transformation)에 대해서도 비교가 가능함
기본적인 사용방법
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
VARIABLE v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('db26h2x1zp29w', null),
compare_plan_list =>
plan_object_list(cursor_cache_object('2rq3vgb7sdshm', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PL/SQL procedure successfully completed.
|
reference_plan 와 compare_plan_list 에 비교할 sql의 sql_id를 입력해주면됨
동일 쿼리에 대해 플랜이 여러개인 경우
dba_sql_plan_baselines에 있는 sql_handle, plan_name (SPM_OBJECT 인자)를 넣어 비교할수도있음
type 인자는 text, html, xml로 세가지 방법으로 출력할수 있음
level 인자는 basic, typical, all 세가지가 있는데 all 이 제일 많은 정보를 출력함
basic 은 Plan은 나오지만 Plan Operation에서 rows 와 Bytes 등 값이 나오지 않음
typical 은 Plan Operation에서 rows 와 Bytes 등 값이 나옴, Predicate Information 이 나옴
all 은 typical 내용을 모두 포함하고 추가로 Hint Report도 포함됨
(https://positivemh.tistory.com/775 에서 설명한 내용)
세가지 테스트를 진행함
각각 샘플쿼리 실행 후 dbms_xplan.display_cursor으로 실행계획과 sql_id를 확인하고 dbms_xplan.compare_plans로 비교함
1. SJ(Semi Join) 테스트(where 절 서브쿼리를 Semi 조인으로 변환)
2. OE(Outer join table Elimination) 테스트(불필요한 outer 쪽 테이블 제거)
3. GBP(Group By Placement) 테스트(Group By를 먼저 수행하고 Join)
테스트1. SJ(Semi Join) 테스트
SJ 발생시
|
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
|
SQL>
select /*+ gather_plan_statistics */
deptno, dname, loc
from dept d
where exists ( select 1
from emp e
where e.deptno = d.deptno);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3qb3cd6b389qw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ deptno, dname, loc from dept d
where exists ( select 1 from emp e where
e.deptno = d.deptno)
Plan hash value: 1754319153
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 3 |00:00:00.01 | 9 | 8 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 3 | 69 | 5 (0)| 00:00:01 | 3 |00:00:00.01 | 9 | 8 | 1399K| 1399K| 1117K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | 2 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 42 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / D@SEL$1
3 - SEL$5DA710D3 / E@SEL$2
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$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "D"@"SEL$1")
FULL(@"SEL$5DA710D3" "E"@"SEL$2")
LEADING(@"SEL$5DA710D3" "D"@"SEL$1" "E"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "E"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "D"."DEPTNO"[NUMBER,22], "LOC"[VARCHAR2,13], "DNAME"[VARCHAR2,14]
2 - (rowset=256) "DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]
3 - (rowset=256) "E"."DEPTNO"[NUMBER,22]
Query Block Registry:
---------------------
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[E]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
<q o="19" f="y" h="y"><n><![CDATA[SEL$5DA710D3]]></n><p><![CDATA[SEL$1]]></p><i><o><t>SQ</t><v><![CDATA[SEL$2]]></v></o></i><f><h><t><![CDATA[D]]
></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[E]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
65 rows selected.
|
22번째 줄 : exists 서브쿼리가 unnesting 되어 semi join으로 풀림
SJ 미발생시
(_always_semi_join 파라미터를 Off로 변경하여 semi join이 발생하지 않게끔함)
|
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
|
SQL>
select /*+ gather_plan_statistics opt_param('_always_semi_join','Off') */
deptno, dname, loc
from dept d
where exists ( select 1
from emp e
where e.deptno = d.deptno);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aadj5zdnng547, child number 0
-------------------------------------
select /*+ gather_plan_statistics opt_param('_always_semi_join','Off')
*/ deptno, dname, loc from dept d where exists ( select 1
from emp e where e.deptno = d.deptno)
Plan hash value: 3547749009
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 3 |00:00:00.01 | 28 |
|* 1 | FILTER | | 1 | | | | | 3 |00:00:00.01 | 28 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 2 | 6 | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 24 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$2 / E@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_always_semi_join' 'off')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
PQ_FILTER(@"SEL$1" SERIAL)
FULL(@"SEL$2" "E"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("E"."DEPTNO"=:B1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]
2 - "DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14], "LOC"[VARCHAR2,13]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - STATEMENT
- opt_param('_always_semi_join','Off')
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[E]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
68 rows selected.
|
24번째 줄 : exists 서브쿼리가 unnesting 되지 않고 Filter로 풀림
COMPARE_PLANS 비교
reference_plan 에 기존 플랜, compare_plan_list 에 비교 대상 플랜을 넣어 비교함
|
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
|
SQL>
VARIABLE v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('3qb3cd6b389qw', null),
compare_plan_list =>
plan_object_list(cursor_cache_object('aadj5zdnng547', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PL/SQL procedure successfully completed.
SQL>
set pages 50000
set long 100000
set lines 210
col report for a200
SELECT :v_rep REPORT FROM DUAL;
REPORT
-----------------------------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : IMSI
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 3qb3cd6b389qw
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "IMSI"
SQL Text : select /*+ gather_plan_statistics */ deptno, dname,
loc from dept d where exists ( select 1 from emp e
where e.deptno = d.deptno)
Plan
-----------------------------
Plan Hash Value : 1754319153
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 | |
| * 1 | HASH JOIN SEMI | | 3 | 69 | 5 | 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 2 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("E"."DEPTNO"="D"."DEPTNO")
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : aadj5zdnng547
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "IMSI"
SQL Text : select /*+ gather_plan_statistics
opt_param('_always_semi_join','Off') */ deptno, dname,
loc from dept d where exists ( select 1 from emp e
where e.deptno = d.deptno)
Plan
-----------------------------
Plan Hash Value : 3547749009
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 | |
| * 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 2 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | EMP | 2 | 6 | 2 | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter( IS NOT NULL)
* 3 - filter("E"."DEPTNO"=:B1)
Comparison Results (1):
-----------------------------
1. Query block SEL$1: Transformation SUBQUERY UNNEST occurred only in the
reference plan (result query block: SEL$5DA710D3).
---------------------------------------------------------------------------------------------
|
97번째 줄 : Comparison Results에 reference plan(기존플랜)에서만 SUBQUERY UNNEST Transformation 이 발생했다고 나옴
테스트2. OE(Outer join table Elimination) 테스트(불필요한 outer 쪽 테이블 제거)
OE 발생시
|
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
|
SQL>
select /*+ gather_plan_statistics */
e.empno, e.ename, e.job
from emp e, dept d
where e.deptno = d.deptno(+);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5rrubfj00hhnw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.empno, e.ename, e.job from emp
e, dept d where e.deptno = d.deptno(+)
Plan hash value: 3956160932
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 14 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 252 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F7859CDE / E@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$F7859CDE")
ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
OUTLINE(@"SEL$1")
FULL(@"SEL$F7859CDE" "E"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9]
Query Block Registry:
---------------------
<q o="34" f="y" h="y"><n><![CDATA[SEL$F7859CDE]]></n><p><![CDATA[SEL$1]]></p><i><o><t>TA</t><v><![CDATA[D@SE
L$1]]></v></o></i><f><h><t><![CDATA[E]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[E]]></
t><s><![CDATA[SEL$1]]></s></h></f></q>
49 rows selected.
|
21번째 줄 : OE(Outer join table Elimination) 가 발생하여 불필요한 outer 쪽 테이블(dept)이 제거됨
OE 미발생시
(_optimizer_join_elimination_enabled 파라미터를 false로 변경하여 oe가 발생하지 않게끔함)
|
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
|
SQL>
select /*+ gather_plan_statistics opt_param('_optimizer_join_elimination_enabled','false') */
e.empno, e.ename, e.job
from emp e, dept d
where e.deptno = d.deptno(+);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5ffq63tnfmpq3, child number 0
-------------------------------------
select /*+ gather_plan_statistics opt_param('_optimizer_join_elimination
_enabled','false') */ e.empno, e.ename, e.job from emp e, dept d where
e.deptno = d.deptno(+)
Plan hash value: 438843259
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 14 |00:00:00.01 | 11 | 1 |
| 1 | NESTED LOOPS OUTER| | 1 | 14 | 336 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 11 | 1 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 294 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | 0 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 14 | 1 | 3 | 0 (0)| | 14 |00:00:00.01 | 4 | 1 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / E@SEL$1
3 - SEL$1 / D@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_join_elimination_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$1" "D"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9]
2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - STATEMENT
- opt_param('_optimizer_join_elimination_enabled','false')
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[E]]></t><s><!
[CDATA[SEL$1]]></s></h></f></q>
67 rows selected.
|
24번째 줄 : OE(Outer join table Elimination) 가 발생하지 못해 불필요한 outer 쪽 테이블(dept)이 존재함
COMPARE_PLANS 비교
reference_plan 에 기존 플랜, compare_plan_list 에 비교 대상 플랜을 넣어 비교함
|
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
|
SQL>
VARIABLE v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('5rrubfj00hhnw', null),
compare_plan_list =>
plan_object_list(cursor_cache_object('5ffq63tnfmpq3', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PL/SQL procedure successfully completed.
SQL>
set pages 50000
set long 100000
set lines 210
col report for a200
SELECT :v_rep REPORT FROM DUAL;
REPORT
---------------------------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : IMSI
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 5rrubfj00hhnw
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "IMSI"
SQL Text : select /*+ gather_plan_statistics */ e.empno,
e.ename, e.job from emp e, dept d where e.deptno =
d.deptno(+)
Plan
-----------------------------
Plan Hash Value : 3956160932
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | TABLE ACCESS FULL | EMP | 14 | 252 | 3 | 00:00:01 |
--------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 5ffq63tnfmpq3
Child Number : 0
Plan Database : 19.0.0.0
Version
Parsing Schema : "IMSI"
SQL Text : select /*+ gather_plan_statistics
opt_param('_optimizer_join_elimination_enabled','false')
*/ e.empno, e.ename, e.job from emp e, dept d where
e.deptno = d.deptno(+)
Plan
-----------------------------
Plan Hash Value : 438843259
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | NESTED LOOPS OUTER | | 14 | 336 | 3 | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 | 00:00:01 |
| * 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 | |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("E"."DEPTNO"="D"."DEPTNO")
Comparison Results (1):
-----------------------------
1. Query block SEL$1: Transformation JOIN REMOVED FROM QUERY BLOCK occurred
only in the reference plan (result query block: SEL$F7859CDE).
---------------------------------------------------------------------------------------------
|
89번째 줄 : Comparison Results에 reference plan(기존플랜)에서만 JOIN REMOVED FROM QUERY BLOCK Transformation 이 발생했다고 나옴
테스트3. GBP(Group By Placement) 테스트(Group By를 먼저 수행하고 Join)
GBP 발생시
(GBP를 발생시키기 위해 place_group_by 힌트를 사용함)
|
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
|
SQL>
select /*+ gather_plan_statistics qb_name(main) place_group_by(@main(d@main)) */
e.job, sum(sal)
from emp e, dept d
where e.deptno = d.deptno
and d.dname in('ACCOUNTING', 'SALES')
group by job;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID db26h2x1zp29w, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main)
place_group_by(@main(d@main)) */ e.job, sum(sal) from emp e, dept d
where e.deptno = d.deptno and d.dname in('ACCOUNTING', 'SALES') group
by job
Plan hash value: 2777097701
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6 (100)| | 4 |00:00:00.01 | 9 | | | |
| 1 | HASH GROUP BY | | 1 | 5 | 155 | 6 (17)| 00:00:01 | 4 |00:00:00.01 | 9 | 1116K| 1116K| 772K (0)|
|* 2 | HASH JOIN | | 1 | 9 | 279 | 5 (0)| 00:00:01 | 9 |00:00:00.01 | 9 | 2078K| 2078K| 784K (0)|
| 3 | VIEW | VW_GBF_1 | 1 | 2 | 32 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 2 | | | |
| 4 | HASH GROUP BY | | 1 | 2 | 26 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 2 | 1558K| 1558K| 618K (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 2 | 26 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 2 | | | |
| 6 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 1 | | | |
| 7 | TABLE ACCESS FULL | EMP | 1 | 14 | 210 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$926C320A
3 - SEL$F8F0E2FD / VW_GBF_1@SEL$6DFE8F9D
4 - SEL$F8F0E2FD
5 - SEL$F8F0E2FD / D@MAIN
6 - SEL$F8F0E2FD / D@MAIN
7 - SEL$926C320A / E@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$F8F0E2FD")
OUTLINE_LEAF(@"SEL$926C320A")
PLACE_GROUP_BY(@"MAIN" ( "D"@"MAIN" ) 1)
OUTLINE(@"SEL$6DFE8F9D")
OUTLINE(@"MAIN")
NO_ACCESS(@"SEL$926C320A" "VW_GBF_1"@"SEL$6DFE8F9D")
FULL(@"SEL$926C320A" "E"@"MAIN")
LEADING(@"SEL$926C320A" "VW_GBF_1"@"SEL$6DFE8F9D" "E"@"MAIN")
USE_HASH(@"SEL$926C320A" "E"@"MAIN")
USE_HASH_AGGREGATION(@"SEL$926C320A")
INDEX(@"SEL$F8F0E2FD" "D"@"MAIN" ("DEPT"."DEPTNO"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$F8F0E2FD" "D"@"MAIN")
USE_HASH_AGGREGATION(@"SEL$F8F0E2FD")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="ITEM_1")
5 - filter(("D"."DNAME"='ACCOUNTING' OR "D"."DNAME"='SALES'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "JOB"[VARCHAR2,9], SUM("SAL"*"ITEM_2")[22]
2 - (#keys=1; rowset=256) "ITEM_2"[NUMBER,22], "JOB"[VARCHAR2,9], "SAL"[NUMBER,22]
3 - (rowset=256) "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22]
4 - (rowset=256) "D"."DEPTNO"[NUMBER,22], COUNT(*)[22]
5 - "D"."DEPTNO"[NUMBER,22]
6 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
7 - (rowset=256) "JOB"[VARCHAR2,9], "SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$926C320A
- place_group_by(@main(d@main))
- qb_name(main)
Query Block Registry:
---------------------
<q o="3"><n><![CDATA[MAIN]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[MAIN]]></s></h><h><t><![CDATA[E]]></t><s><![CDATA[MAIN]]></s></h></f></q>
<q o="43" f="y" h="y"><n><![CDATA[SEL$926C320A]]></n><p><![CDATA[MAIN]]></p><i><o><t>TA</t><v><![CDATA[D@MAIN]]></v></o></i><f><h><t><![CDATA[E]]></t><s><![CDATA
[MAIN]]></s></h><h><t><![CDATA[VW_GBF_1]]></t><s><![CDATA[SEL$6DFE8F9D]]></s></h></f></q>
<q o="15" f="y"><n><![CDATA[SEL$F8F0E2FD]]></n><p><![CDATA[SEL$6DFE8F9D]]></p><f><h><t><![CDATA[D]]></t><s><![CDATA[MAIN]]></s></h></f></q>
<q o="26"><n><![CDATA[SEL$6DFE8F9D]]></n><p><![CDATA[MAIN]]></p><i><o><t>FA</t><v><![CDATA["D"@"MAIN"]]></v></o></i><f><h><t><![CDATA[E]]></t><s><![CDATA[MAIN]]>
</s></h><h><t><![CDATA[VW_GBF_1]]></t><s><![CDATA[SEL$6DFE8F9D]]></s></h></f></q>
93 rows selected.
|
27번째 줄 : GBP가 발생하여 VW_GBF_1 뷰가 생성됨, 이로 인해 group by 가 총 2번 수행됨
GBP 미발생시
(GBP를 발생시키지 않기 위해 no_place_group_by 힌트를 사용함)
|
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
|
SQL>
select /*+ gather_plan_statistics qb_name(main) no_place_group_by(@main(d@main)) */
e.job, sum(sal)
from emp e, dept d
where e.deptno = d.deptno
and d.dname in('ACCOUNTING', 'SALES')
group by job;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2rq3vgb7sdshm, child number 0
-------------------------------------
select /*+ gather_plan_statistics qb_name(main)
no_place_group_by(@main(d@main)) */ e.job, sum(sal) from emp e, dept d
where e.deptno = d.deptno and d.dname in('ACCOUNTING', 'SALES') group
by job
Plan hash value: 2006461124
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6 (100)| | 4 |00:00:00.01 | 9 | | | |
| 1 | HASH GROUP BY | | 1 | 5 | 140 | 6 (17)| 00:00:01 | 4 |00:00:00.01 | 9 | 1116K| 1116K| 772K (0)|
|* 2 | HASH JOIN | | 1 | 9 | 252 | 5 (0)| 00:00:01 | 9 |00:00:00.01 | 9 | 2546K| 2546K| 866K (0)|
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 2 | 26 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 2 | | | |
| 4 | TABLE ACCESS FULL| EMP | 1 | 14 | 210 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MAIN
3 - MAIN / D@MAIN
4 - MAIN / E@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(@"MAIN")
FULL(@"MAIN" "D"@"MAIN")
FULL(@"MAIN" "E"@"MAIN")
LEADING(@"MAIN" "D"@"MAIN" "E"@"MAIN")
USE_HASH(@"MAIN" "E"@"MAIN")
USE_HASH_AGGREGATION(@"MAIN")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")
3 - filter(("D"."DNAME"='ACCOUNTING' OR "D"."DNAME"='SALES'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "JOB"[VARCHAR2,9], SUM("SAL")[22]
2 - (#keys=1; rowset=256) "JOB"[VARCHAR2,9], "SAL"[NUMBER,22]
3 - (rowset=256) "D"."DEPTNO"[NUMBER,22]
4 - (rowset=256) "JOB"[VARCHAR2,9], "SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - MAIN
- no_place_group_by(@main(d@main))
- qb_name(main)
Query Block Registry:
---------------------
<q o="3" f="y"><n><![CDATA[MAIN]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[MAIN]]></s></h><h><t><![CDATA[E]]></t><s><![CDATA[MAIN]]></s>
</h></f></q>
73 rows selected.
|
25번째 줄 : GBP가 발생하지 않아서 하여 group by 가 한번만 수행됨
COMPARE_PLANS 비교
reference_plan 에 기존 플랜, compare_plan_list 에 비교 대상 플랜을 넣어 비교함
|
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
|
SQL>
VARIABLE v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('db26h2x1zp29w', null),
compare_plan_list =>
plan_object_list(cursor_cache_object('2rq3vgb7sdshm', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PL/SQL procedure successfully completed.
SQL>
set pages 50000
set long 100000
set lines 210
col report for a200
SELECT :v_rep REPORT FROM DUAL;
REPORT
---------------------------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : IMSI
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : db26h2x1zp29w
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "IMSI"
SQL Text : select /*+ gather_plan_statistics qb_name(main)
place_group_by(@main(d@main)) */ e.job, sum(sal) from
emp e, dept d where e.deptno = d.deptno and d.dname
in('ACCOUNTING', 'SALES') group by job
Plan
-----------------------------
Plan Hash Value : 2777097701
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | HASH GROUP BY | | 5 | 155 | 6 | 00:00:01 |
| * 2 | HASH JOIN | | 9 | 279 | 5 | 00:00:01 |
| 3 | VIEW | VW_GBF_1 | 2 | 32 | 2 | 00:00:01 |
| 4 | HASH GROUP BY | | 2 | 26 | 2 | 00:00:01 |
| * 5 | TABLE ACCESS BY INDEX ROWID BATCHED | DEPT | 2 | 26 | 2 | 00:00:01 |
| 6 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 | 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 | 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("E"."DEPTNO"="ITEM_1")
* 5 - filter(("D"."DNAME"='ACCOUNTING' OR "D"."DNAME"='SALES'))
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 2rq3vgb7sdshm
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "IMSI"
SQL Text : select /*+ gather_plan_statistics qb_name(main)
no_place_group_by(@main(d@main)) */ e.job, sum(sal)
from emp e, dept d where e.deptno = d.deptno and
d.dname in('ACCOUNTING', 'SALES') group by job
Plan
-----------------------------
Plan Hash Value : 2006461124
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | HASH GROUP BY | | 5 | 140 | 6 | 00:00:01 |
| * 2 | HASH JOIN | | 9 | 252 | 5 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | DEPT | 2 | 26 | 2 | 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 | 00:00:01 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("E"."DEPTNO"="D"."DEPTNO")
* 3 - filter(("D"."DNAME"='ACCOUNTING' OR "D"."DNAME"='SALES'))
Comparison Results (1):
-----------------------------
1. Query block MAIN: Transformation PLACE GROUP BY occurred only in the
reference plan (result query block: SEL$926C320A).
---------------------------------------------------------------------------------------------
|
103번째 줄 : Comparison Results에 reference plan(기존플랜)에서만 PLACE GROUP BY Transformation 이 발생했다고 나옴
위와 같이 DBMS_XPLAN.COMPARE_PLANS를 이용해 실행계획을 oracle 내에서 비교해볼수있음
참조 :
2736319.1, 2735151.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/generating-and-displaying-execution-plans.html#GUID-0023D232-5695-4BA8-89C5-88672B7647E2
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html#GUID-5A80E12C-F126-413D-8FF7-FC04B382FA92
https://hrjeong.tistory.com/175
https://smarttechways.com/2019/08/08/compare-execution-plan-of-two-sql-statements-in-oracle-19c/
The Logical Optimizer 서적(SJ, OE, GBP 부분)
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 11gR2 파티션 변경(split, drop, add) 시 커서 상태 변화 확인 (0) | 2022.07.18 |
|---|---|
| 오라클 19c itas 시 full scan과 index scan 속도 비교 테스트 (2) | 2022.04.13 |
| sqlp 실기튜닝실습 17-1 PUSH PREDICATE 문제 devDo (2) | 2022.03.06 |
| sqlp 실기튜닝실습 20-1 인덱스 최적화 구성 devDo (0) | 2022.03.06 |
| 오라클 11gR1 이상버전에서 보이는 Nested Loop 조인 실행계획(Prefetch, vector I/O) (0) | 2021.11.16 |