복사되었습니다!
OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 자동화 스크립트
이전 게시글에서는 SQL Profile에 등록된 플랜을 지우고 SPM(SQL Plan Management)으로 단건 재등록 했었음
참고 : 오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 ( https://positivemh.tistory.com/1378 )
본문에서는 여러개의 SQL Profile이 있을때 모두 SPM으로 옮기는 스크립트를 설명함
테스트
샘플 테이블 및 인덱스 생성, 통계정보 수집
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
drop table t_profile_test purge;
create table t_profile_test as
select
level as id,
mod(level, 5) as type_id,
rpad('A', 100, 'A') as val
from dual connect by level <= 10000;
create index ix_profile_test_id on t_profile_test(id);
exec dbms_stats.gather_table_stats(ownname => user, tabname => 'T_PROFILE_TEST', cascade => true);
|
샘플 쿼리 5개 수행
|
1
2
3
4
5
6
|
SQL>
select /*+ batch_q1 */ * from t_profile_test where id = 10;
select /*+ batch_q2 */ * from t_profile_test where id = 20;
select /*+ batch_q3 */ * from t_profile_test where id = 30;
select /*+ batch_q4 */ * from t_profile_test where id = 40;
select /*+ batch_q5 */ * from t_profile_test where id = 50;
|
샘플 쿼리 SQL Profile에 등록
|
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>
set serveroutput on
declare
l_profile sys.sqlprof_attr;
procedure create_profile_v2(p_sql_text clob, p_profile_name varchar2) is
begin
l_profile := sys.sqlprof_attr(
'BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''19.1.0'')',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1" "T_PROFILE_TEST"@"SEL$1" ("T_PROFILE_TEST"."ID"))',
'END_OUTLINE_DATA'
);
dbms_sqltune.import_sql_profile (
sql_text => p_sql_text
, profile => l_profile
, name => p_profile_name
, replace => true
, force_match => true
);
end;
begin
create_profile_v2('select /*+ batch_q1 */ * from t_profile_test where id = 10', 'profile_batch_01');
create_profile_v2('select /*+ batch_q2 */ * from t_profile_test where id = 20', 'profile_batch_02');
create_profile_v2('select /*+ batch_q3 */ * from t_profile_test where id = 30', 'profile_batch_03');
create_profile_v2('select /*+ batch_q4 */ * from t_profile_test where id = 40', 'profile_batch_04');
create_profile_v2('select /*+ batch_q5 */ * from t_profile_test where id = 50', 'profile_batch_05');
dbms_output.put_line('5 SQL Profile Registered!');
end;
/
5 SQL Profile Registered!
PL/SQL procedure successfully completed.
|
등록됨
등록된 SQL Profile 정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 like 'profile_batch%'
order by created desc;
NAME CATEGORY STATUS FOR CREATED LAST_MODIFIED SQL_TEXT
------------------------- ---------- -------- --- ------------------------------ ------------------------------ ------------------------------------------------------------
profile_batch_05 DEFAULT ENABLED YES 16-MAY-26 02.05.12.701736 PM 16-MAY-26 03.17.46.000000 PM select /*+ batch_q5 */ * from t_profile_test where id = 50
profile_batch_04 DEFAULT ENABLED YES 16-MAY-26 02.05.12.683075 PM 16-MAY-26 03.17.46.000000 PM select /*+ batch_q4 */ * from t_profile_test where id = 40
profile_batch_03 DEFAULT ENABLED YES 16-MAY-26 02.05.12.664284 PM 16-MAY-26 03.17.46.000000 PM select /*+ batch_q3 */ * from t_profile_test where id = 30
profile_batch_02 DEFAULT ENABLED YES 16-MAY-26 02.05.12.641311 PM 16-MAY-26 03.17.46.000000 PM select /*+ batch_q2 */ * from t_profile_test where id = 20
profile_batch_01 DEFAULT ENABLED YES 16-MAY-26 02.05.12.613368 PM 16-MAY-26 03.17.46.000000 PM select /*+ batch_q1 */ * from t_profile_test where id = 10
|
SQL Profile이 5개 등록됨
해당 sql들의 sql id 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col sql_profile for a30
select sql_id, child_number, plan_hash_value, sql_profile, substr(sql_text,1,20) sql_text
from v$sql
where sql_text like 'select /*+ batch_q%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE SQL_PROFILE SQL_TEXT
------------- ------------ --------------- ------------------------------ ------------------------------------------------------------
fdvym9drgs4f2 0 944717240 profile_batch_04 select /*+ batch_q4
1pkgru05u4928 0 944717240 profile_batch_02 select /*+ batch_q2
cpnxwckpay1bc 0 944717240 profile_batch_03 select /*+ batch_q3
f7uxknt8x21bs 0 944717240 profile_batch_01 select /*+ batch_q1
8ka0s0hsdv407 0 944717240 profile_batch_05 select /*+ batch_q5
|
현재 해당 sql들이 SQL Profile에 등록되어 있음
샘플 쿼리 5개 재수행
|
1
2
3
4
5
6
|
SQL>
select /*+ batch_q1 */ * from t_profile_test where id = 10;
select /*+ batch_q2 */ * from t_profile_test where id = 20;
select /*+ batch_q3 */ * from t_profile_test where id = 30;
select /*+ batch_q4 */ * from t_profile_test where id = 40;
select /*+ batch_q5 */ * from t_profile_test where id = 50;
|
xplan으로 확인
|
1
2
3
4
5
6
|
SQL>
select * from dbms_xplan.display_cursor('fdvym9drgs4f2', NULL, 'ADVANCED');
select * from dbms_xplan.display_cursor('1pkgru05u4928', NULL, 'ADVANCED');
select * from dbms_xplan.display_cursor('cpnxwckpay1bc', NULL, 'ADVANCED');
select * from dbms_xplan.display_cursor('f7uxknt8x21bs', NULL, 'ADVANCED');
select * from dbms_xplan.display_cursor('8ka0s0hsdv407', NULL, 'ADVANCED');
|
수행시
|
1
2
3
|
Note
-----
- SQL profile profile_batch_04 used for this statement
|
라는 부분을 모든 쿼리에서 볼수 있음
현재 5개의 쿼리가 5개의 SQL Profile로 등록되어 플랜이 고정되어 있는 상태임
이제 이 고정된 플랜을 SPM에도 동일하게 등록해주는 스크립트를 수행함
SQL Profile에 있는 목록을 SPM에 등록
|
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
|
SQL>
set serveroutput on size unlimited
declare
v_plans_loaded number;
v_sql_id varchar2(13);
v_phv number;
begin
dbms_output.put_line('-- [SQL Profile to SPM Conversion Start]');
dbms_output.put_line('------------------------------------------------------');
-- Loop through active SQL Profiles matching the target naming pattern
for rec in (
select name as profile_name, signature, sql_text
from dba_sql_profiles
where status = 'ENABLED' and name like 'profile_batch_%'
) loop
v_sql_id := null;
v_phv := null;
v_plans_loaded := 0;
-- 1. Try to find the SQL in the live memory (Shared Pool / v$sql)
-- Fixed: Changed exact_matching_signature to force_matching_signature for custom profiles
begin
select sql_id, plan_hash_value
into v_sql_id, v_phv
from v$sql
where force_matching_signature = rec.signature and child_number = 0 and rownum = 1;
exception when no_data_found then null;
end;
-- 2. If not found in memory, look up from AWR history (dba_hist_sqlstat)
if v_sql_id is null then
begin
select sql_id, plan_hash_value
into v_sql_id, v_phv
from dba_hist_sqlstat
where force_matching_signature = rec.signature and rownum = 1;
exception when no_data_found then null;
end;
end if;
-- 3. If SQL_ID and Plan Hash Value are found, migrate to SPM Baseline
if v_sql_id is not null and v_phv is not null then
begin
v_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
sql_id => v_sql_id,
plan_hash_value => v_phv,
enabled => 'YES',
fixed => 'YES'
);
if v_plans_loaded > 0 then
dbms_output.put_line('-- [SUCCESS] Profile: ' || rec.profile_name || ' -> Converted to SPM (SQL_ID: ' || v_sql_id || ')');
else
dbms_output.put_line('-- [WARNING] Failed to load plan for Profile: ' || rec.profile_name);
end if;
exception when others then
dbms_output.put_line('-- [ERROR] Exception on ' || rec.profile_name || ' - ' || sqlerrm);
end;
else
dbms_output.put_line('-- [FAILED] No SQL snapshot found in both Library Cache and AWR: ' || rec.profile_name);
end if;
end loop;
dbms_output.put_line('------------------------------------------------------');
dbms_output.put_line('-- [Conversion Task Completed]');
end;
/
-- [SQL Profile to SPM Conversion Start]
------------------------------------------------------
-- [SUCCESS] Profile: profile_batch_01 -> Converted to SPM (SQL_ID: f7uxknt8x21bs)
-- [SUCCESS] Profile: profile_batch_03 -> Converted to SPM (SQL_ID: cpnxwckpay1bc)
-- [SUCCESS] Profile: profile_batch_04 -> Converted to SPM (SQL_ID: fdvym9drgs4f2)
-- [SUCCESS] Profile: profile_batch_02 -> Converted to SPM (SQL_ID: 1pkgru05u4928)
-- [SUCCESS] Profile: profile_batch_05 -> Converted to SPM (SQL_ID: 8ka0s0hsdv407)
------------------------------------------------------
-- [Conversion Task Completed]
|
5개 sql profile이 spm에도 생성됨
등록된 spm 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 in ('fdvym9drgs4f2', '1pkgru05u4928', 'cpnxwckpay1bc', 'f7uxknt8x21bs', '8ka0s0hsdv407'));
SQL_HANDLE PLAN_NAME EXECUTIONS CREATED ACC FIX
------------------------------ ------------------------------ ---------- ------------------------------ --- ---
SQL_47add675a88c9a16 SQL_PLAN_4gbfqfqn8t6hq2589b4ea 1 2026/05/16 15:21:10 YES YES
SQL_51f8fc5763ebb70d SQL_PLAN_53y7waxjyrdsd2589b4ea 1 2026/05/16 15:21:10 YES YES
SQL_858b628549109e2b SQL_PLAN_8b2v2hp4j17jb2589b4ea 1 2026/05/16 15:21:10 YES YES
SQL_f7812053c17ce6f9 SQL_PLAN_gg090ag0rttrt2589b4ea 1 2026/05/16 15:21:10 YES YES
SQL_fd98ff1a53002602 SQL_PLAN_gv67z399h09h22589b4ea 1 2026/05/16 15:21:10 YES YES
|
정상적으로 등록됨
실제 플랜 확인을 위해 세션 레벨로 statistics_level 변경
|
1
2
3
|
SQL> alter session set statistics_level = all;
Session altered.
|
샘플 쿼리 5개 재수행
|
1
2
3
4
5
6
|
SQL>
select /*+ batch_q1 */ * from t_profile_test where id = 10;
select /*+ batch_q2 */ * from t_profile_test where id = 20;
select /*+ batch_q3 */ * from t_profile_test where id = 30;
select /*+ batch_q4 */ * from t_profile_test where id = 40;
select /*+ batch_q5 */ * from t_profile_test where id = 50;
|
xplan으로 확인
|
1
2
3
4
5
6
|
SQL>
select * from dbms_xplan.display_cursor('fdvym9drgs4f2', NULL, 'ADVANCED LAST');
select * from dbms_xplan.display_cursor('1pkgru05u4928', NULL, 'ADVANCED LAST');
select * from dbms_xplan.display_cursor('cpnxwckpay1bc', NULL, 'ADVANCED LAST');
select * from dbms_xplan.display_cursor('f7uxknt8x21bs', NULL, 'ADVANCED LAST');
select * from dbms_xplan.display_cursor('8ka0s0hsdv407', NULL, 'ADVANCED LAST');
|
수행시
|
1
2
3
4
|
Note
-----
- SQL profile profile_batch_04 used for this statement
- SQL plan baseline SQL_PLAN_8b2v2hp4j17jb2589b4ea used for this statement
|
라는 부분을 모든 쿼리에서 볼수 있음
SPM을 이용해 플랜이 고정되고 있음
이제 SQL Profile을 삭제 가능한 상태임
sql profile 제거 명령어 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
col cmd for a80
select 'exec dbms_sqltune.drop_sql_profile(name => '''||name||''');' cmd
from dba_sql_profiles
where name like 'profile_batch_%';
CMD
--------------------------------------------------------------------------------
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_01');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_03');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_04');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_02');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_05');
|
실제 drop 명령어 수행
|
1
2
3
4
5
6
|
SQL>
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_01');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_03');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_04');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_02');
exec dbms_sqltune.drop_sql_profile(name => 'profile_batch_05');
|
SQL Profile 정보 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 like 'profile_batch%'
order by created desc;
no rows selected
|
모두 제거됨
참고. baseline 일괄제거
|
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>
set serveroutput on
select
'declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => '''
|| sql_handle || ''', plan_name => ''' || plan_name || '''); dbms_output.put_line(''DROP PLANS : ''|| ret); end; /' as cmd
from
dba_sql_plan_baselines
where
sql_text like 'select /*+ batch_q%';
CMD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_47add675a88c9a16', plan_name => 'SQL_PLAN_4gbfqfqn8t6hq2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_51f8fc5763ebb70d', plan_name => 'SQL_PLAN_53y7waxjyrdsd2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_858b628549109e2b', plan_name => 'SQL_PLAN_8b2v2hp4j17jb2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_f7812053c17ce6f9', plan_name => 'SQL_PLAN_gg090ag0rttrt2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_fd98ff1a53002602', plan_name => 'SQL_PLAN_gv67z399h09h22589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
|
실제 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_47add675a88c9a16', plan_name => 'SQL_PLAN_4gbfqfqn8t6hq2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_51f8fc5763ebb70d', plan_name => 'SQL_PLAN_53y7waxjyrdsd2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_858b628549109e2b', plan_name => 'SQL_PLAN_8b2v2hp4j17jb2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_f7812053c17ce6f9', plan_name => 'SQL_PLAN_gg090ag0rttrt2589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
declare ret number; begin ret := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_fd98ff1a53002602', plan_name => 'SQL_PLAN_gv67z399h09h22589b4ea'); dbms_output.put_line('DROP PLANS : '|| ret); end;
/
|
등록된 spm 확인
|
1
2
3
4
5
6
7
8
|
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 in ('fdvym9drgs4f2', '1pkgru05u4928', 'cpnxwckpay1bc', 'f7uxknt8x21bs', '8ka0s0hsdv407'));
no rows selected
|
정상적으로 제거됨
결론 :
본문 스크립트를 이용하여 SQL Profile로 고정된 플랜을 SPM으로 빠르게 옮길 수 있음
관련 게시글 :
오라클 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 AWR 스냅샷의 플랜을 SPM(SQL Plan Management)으로 고정 (0) | 2026.05.24 |
|---|---|
| 오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 (0) | 2026.05.18 |
| 오라클 19c SQL Profile 플랜 고정 방법 (0) | 2026.05.16 |
| 오라클 19c 리터럴 쿼리와 바인드 변수 사용 쿼리 shared pool 사용량 비교 (0) | 2026.04.24 |
| 오라클 19c 튜닝시 qb_name 힌트 사용 방법 (0) | 2026.04.17 |