복사되었습니다!
OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경
이전 게시글에서 SQL Profile을 이용해 쿼리의 플랜을 고정했음
참고 : 오라클 19c SQL Profile 플랜 고정 방법 ( https://positivemh.tistory.com/1377 )
본문에서는 SQL Profile에 등록된 플랜을 지우고 SPM(SQL Plan Management)으로 재등록하는 방법을 설명함
SQL Profile 대신 SPM을 사용할 이유?
안전성 : SQL Profile은 통계 정보 변화에 따라 플랜이 미세하게 변할 가능성이 있지만 SPM Baseline은 등록된 플랜의 구조를 그대로 유지함
기능 측면 : 나중에 더 좋은 플랜이 발견되면 기존 Baseline을 지우지 않고도 새로운 플랜을 추가하여 성능을 비교(Verify)한 뒤 승인(Accept)하는 프로세스를 밟을 수 있음
관리 효율 : SPM을 통해 자동 캡처 및 관리를 병행 시 운영 리소스를 훨씬 줄여줄수 있음(자동 캡쳐를 잘 쓰진 않음)
테스트
먼저 등록된 SQL Profile 정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set lines 200 pages 1000
col name for a25
col category for a10
col status for a8
col for for a5
col created for a30
col last_modified for a30
col sql_text for a60
select name, category, status, force_matching, created, last_modified, sql_text
from dba_sql_profiles
where name = 'profile_bpu9gmcd1uysw'
order by created desc;
NAME CATEGORY STATUS FOR CREATED LAST_MODIFIED SQL_TEXT
------------------------- ---------- -------- --- ------------------------------ ------------------------------ ------------------------------------------------------------
profile_bpu9gmcd1uysw DEFAULT ENABLED YES 16-MAY-26 10.54.01.526811 AM 16-MAY-26 10.54.01.000000 AM select /*+ target_sql full(t_temp) */ * from t_temp where id
|
이 sql의 플랜 확인
|
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 * from dbms_xplan.display_cursor('bpu9gmcd1uysw', NULL, 'ADVANCED');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID bpu9gmcd1uysw, child number 0
-------------------------------------
select /*+ target_sql full(t_temp) */ * from t_temp where id <= 10
Plan hash value: 362386456
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP | 10 | 1050 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_T_TEMP_ID | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T_TEMP@SEL$1
2 - SEL$1 / T_TEMP@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")
INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T_TEMP"."VAL"[VARCHAR2,100]
2 - "T_TEMP".ROWID[ROWID,10], "ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 8 (U - Unused (1), E - Syntax error (1))
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
1 - SEL$1
E - target_sql
1 - SEL$1 / T_TEMP@SEL$1
U - full(t_temp) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
- BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
- INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
Note
-----
- SQL profile profile_bpu9gmcd1uysw used for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T_TEMP]]></t><s><![CDATA[SEL$1]]></
s></h></f></q>
75 rows selected.
|
쿼리에 full 힌트를 사용했지만 SQL Profile에 의해 index scan을 하는것을 볼수 있음
이 sql의 sql_id는 bpu9gmcd1uysw 이고 plan hash value는 362386456임
spm에 sql_id 와 plan_hash_value로 baseline 등록
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
set serveroutput on
declare
ret number;
begin
ret := dbms_spm.load_plans_from_cursor_cache(
sql_id => 'bpu9gmcd1uysw',
plan_hash_value => 362386456,
enabled => 'YES',
fixed => 'YES');
dbms_output.put_line('LOAD PLANS : '|| ret);
end;
/
LOAD PLANS : 1
PL/SQL procedure successfully completed.
|
spm에 등록됨
등록된 spm 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col plan_name for a30
select sql_handle, plan_name, executions, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted, fixed
from dba_sql_plan_baselines
where signature in ( select exact_matching_signature FROM v$sql WHERE sql_id='bpu9gmcd1uysw');
SQL_HANDLE PLAN_NAME EXECUTIONS CREATED ACC FIX
------------------------------ ------------------------------ ---------- ------------------------------ --- ---
SQL_64df3cd3ebab3cec SQL_PLAN_69rtwugpuqg7caf835ec9 1 2026/05/16 13:03:59 YES YES
|
샘플 쿼리 재수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select /*+ target_sql full(t_temp) */ * from t_temp where id <= 10;
ID VAL
---------- ----------------------------------------------------------------------------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
4 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
5 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
6 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
7 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
8 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 rows selected.
|
플랜 재확인
|
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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
SQL> select * from dbms_xplan.display_cursor('bpu9gmcd1uysw', NULL, 'ADVANCED LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bpu9gmcd1uysw, child number 0
-------------------------------------
select /*+ target_sql full(t_temp) */ * from t_temp where id <= 10
Plan hash value: 362386456
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP | 10 | 1050 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_T_TEMP_ID | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T_TEMP@SEL$1
2 - SEL$1 / T_TEMP@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")
INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T_TEMP"."VAL"[VARCHAR2,100]
2 - "T_TEMP".ROWID[ROWID,10], "ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 8 (U - Unused (1), E - Syntax error (1))
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
1 - SEL$1
E - target_sql
1 - SEL$1 / T_TEMP@SEL$1
U - full(t_temp) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
- BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
- INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
Note
-----
- SQL profile profile_bpu9gmcd1uysw used for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T_TEMP]]></t><s><![CDATA[SEL$1]]></
s></h></f></q>
SQL_ID bpu9gmcd1uysw, child number 1
-------------------------------------
select /*+ target_sql full(t_temp) */ * from t_temp where id <= 10
Plan hash value: 362386456
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP | 10 | 1050 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_T_TEMP_ID | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T_TEMP@SEL$1
2 - SEL$1 / T_TEMP@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")
INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T_TEMP"."VAL"[VARCHAR2,100]
2 - "T_TEMP".ROWID[ROWID,10], "ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 8 (U - Unused (1), E - Syntax error (1))
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
1 - SEL$1
E - target_sql
1 - SEL$1 / T_TEMP@SEL$1
U - full(t_temp) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
- BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
- INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
Note
-----
- SQL profile profile_bpu9gmcd1uysw used for this statement
- SQL plan baseline SQL_PLAN_69rtwugpuqg7caf835ec9 used for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T_TEMP]]></t><s><![CDATA[SEL$1]]></
s></h></f></q>
151 rows selected.
|
동일 sql인데 child number가 다른 플랜이 2개 존재함
Note 부분을 보면 child number 0은 SQL Profile에 의해 플랜이 고정되었고, child number 1은 SQL Profile + SPM에 의해 플랜이 고정됨
"SQL profile profile_bpu9gmcd1uysw used for this statement"
"SQL plan baseline SQL_PLAN_69rtwugpuqg7caf835ec9 used for this statement"
SQL Profile 제거
|
1
2
3
|
SQL> exec dbms_sqltune.drop_sql_profile(name => 'profile_bpu9gmcd1uysw');
PL/SQL procedure successfully completed.
|
샘플 쿼리 재수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select /*+ target_sql full(t_temp) */ * from t_temp where id <= 10;
ID VAL
---------- ----------------------------------------------------------------------------------------------------
1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
3 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
4 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
5 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
6 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
7 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
8 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 rows selected.
|
플랜 재확인
|
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
|
SQL> select * from dbms_xplan.display_cursor('bpu9gmcd1uysw', NULL, 'ADVANCED LAST');
(child number 0 plan 정보)
(child number 1 plan 정보)
이어서 child number 2 plan 정보
SQL_ID bpu9gmcd1uysw, child number 2
-------------------------------------
select /*+ target_sql full(t_temp) */ * from t_temp where id <= 10
Plan hash value: 362386456
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_TEMP | 10 | 1050 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_T_TEMP_ID | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T_TEMP@SEL$1
2 - SEL$1 / T_TEMP@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")
INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T_TEMP"."VAL"[VARCHAR2,100]
2 - "T_TEMP".ROWID[ROWID,10], "ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 8 (U - Unused (1), E - Syntax error (1))
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
1 - SEL$1
E - target_sql
1 - SEL$1 / T_TEMP@SEL$1
U - full(t_temp) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
- BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
- INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
Note
-----
- SQL plan baseline SQL_PLAN_69rtwugpuqg7caf835ec9 used for this statement
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[T_TEMP]]></t><s><![CDATA[SEL$1]]></
s></h></f></q>
150 rows selected.
|
이번엔 child number가 총 3개 나옴
Note 부분을 보면 spm에 의해 플랜이 고정 되었다고 표시됨
"SQL plan baseline SQL_PLAN_69rtwugpuqg7caf835ec9 used for this statement"
참고1. baseline 변경
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
declare
ret number;
begin
ret := dbms_spm.alter_sql_plan_baseline(
sql_handle => 'SQL_64df3cd3ebab3cec',
plan_name => 'SQL_PLAN_69rtwugpuqg7caf835ec9',
attribute_name => 'fixed',
attribute_value => 'NO');
dbms_output.put_line('PLANS ALTERED: ' || ret);
END;
/
|
참고2. baseline 제거
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set serveroutput on
declare
ret number;
begin
ret := dbms_spm.drop_sql_plan_baseline(
sql_handle => 'SQL_64df3cd3ebab3cec',
plan_name => 'SQL_PLAN_69rtwugpuqg7caf835ec9');
dbms_output.put_line('DROP PLANS : '|| ret);
end;
/
|
결론 :
SQL Profile 대신 SPM을 사용하여 더욱더 안전하게 SQL 플랜 관리가 가능함
관련 게시글 :
오라클 19c SQL Profile 플랜 고정 방법 ( https://positivemh.tistory.com/1377 )
오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 ( https://positivemh.tistory.com/1378 )
오라클 19c SQL Profile 등록된 플랜 SPM으로 변경 자동화 스크립트 ( https://positivemh.tistory.com/1379 )
참조 :
오라클 19c SQL Plan Management(SPM) 및 Automatic SPM(19.22부터) 무료 ( https://positivemh.tistory.com/1307 )
오라클 19c 하드파싱 쿼리 플랜 변경 의심시 조치방법 ( https://positivemh.tistory.com/849 )
오라클 19c 실행계획 변경 시 spm 적용 방법 ( https://positivemh.tistory.com/844 )
SPM(SQL Plan Management) SQL PLAN BASELINE 이용한 Export / Import 테스트 (실행계획 변경) ( https://positivemh.tistory.com/447 )
SPM(SQL Plan Management) SQLSET 이용한 Export / Import 테스트 (실행계획 변경) ( https://positivemh.tistory.com/446 )
https://hrjeong.tistory.com/288
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_SQL_PLAN_BASELINES.html
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c SQL Profile 플랜 고정 방법 (0) | 2026.05.16 |
|---|---|
| 오라클 19c 리터럴 쿼리와 바인드 변수 사용 쿼리 shared pool 사용량 비교 (0) | 2026.04.24 |
| 오라클 19c 튜닝시 qb_name 힌트 사용 방법 (0) | 2026.04.17 |
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선2 (1) | 2025.08.21 |
| 오라클 19c 인덱스 선두컬럼 값이 null인 경우 성능 테스트 (0) | 2025.08.14 |