복사되었습니다!
OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c SQL Profile 플랜 고정 방법
본문에서는 SQL Profile을 이용해 쿼리의 플랜을 고정하는 방법을 설명함
SQL Profile은 SQL 문장 자체를 수정하지 않고 힌트(Hint) 뭉치(Outline)를 데이터 사전(Data Dictionary)에 저장하여 옵티마이저가 이를 참조하게 만드는 기능임
본문 시나리오는 아래와 같이 진행됨
1. 튜닝 대상 쿼리(full scan)의 실행계획을 확인
2. 원하는 실행계획(index scan)이 나오는 임시 SQL을 실행
3. 해당 SQL의 실행계획 정보(Outlines)를 추출
4. 3의 정보를 원래 튜닝하려는 SQL ID에 설정
테스트
샘플 테이블 및 인덱스 생성
|
1
2
3
4
5
6
|
SQL>
create table t_temp as
select level as id, rpad('a', 100, 'a') as val
from dual connect by level <= 10000;
create index ix_t_temp_id on t_temp(id);
|
기존 SQL 수행
(현재 이쿼리가 Full scan으로 동작해서 문제라고 가정함)
|
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.
|
sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
SELECT
s.sql_id,
SUBSTR(q1.sql_text, 1, 20) AS sql_text_20,
s.prev_sql_id,
SUBSTR(q2.sql_text, 1, 20) AS prev_sql_text_20
FROM v$session s,
v$sql q1,
v$sql q2
WHERE s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1)
AND s.sql_id = q1.sql_id(+)
AND s.sql_child_number = q1.child_number(+)
AND s.prev_sql_id = q2.sql_id(+)
AND 0 = q2.child_number(+);
SQL_ID SQL_TEXT_20 PREV_SQL_ID PREV_SQL_TEXT_20
------------- ---------------------------------------- ------------- ----------------------------------------
agaf9ncx78yuu SELECT s.sql_id, bpu9gmcd1uysw select /*+ target_sq
|
bpu9gmcd1uysw이 직전 sql의 sql_id임
bpu9gmcd1uysw 쿼리 플랜 확인
|
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
|
SQL> select * from dbms_xplan.display_cursor('bpu9gmcd1uysw', NULL, 'TYPICAL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------`-----------------------------
SQL_ID bpu9gmcd1uysw, child number 0
-------------------------------------
select /*+ target_sql full(t_temp) */ * from t_temp where id <= 10
Plan hash value: 2604469312
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 47 (100)| |
|* 1 | TABLE ACCESS FULL| T_TEMP | 10 | 1050 | 47 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<=10)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - target_sql
25 rows selected.
|
full scan을 하고 있음
튜닝 SQL 수행
(Index Scan을 강제한 SQL)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select /*+ index(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.
|
sql_id 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
SELECT
s.sql_id,
SUBSTR(q1.sql_text, 1, 20) AS sql_text_20,
s.prev_sql_id,
SUBSTR(q2.sql_text, 1, 20) AS prev_sql_text_20
FROM v$session s,
v$sql q1,
v$sql q2
WHERE s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1)
AND s.sql_id = q1.sql_id(+)
AND s.sql_child_number = q1.child_number(+)
AND s.prev_sql_id = q2.sql_id(+)
AND 0 = q2.child_number(+);
SQL_ID SQL_TEXT_20 PREV_SQL_ID PREV_SQL_TEXT_20
------------- ---------------------------------------- ------------- ----------------------------------------
agaf9ncx78yuu SELECT s.sql_id, fm1k8nvq6dhc8 select /*+ index(t_t
|
fm1k8nvq6dhc8이 직전 sql의 sql_id임
fm1k8nvq6dhc8 쿼리 플랜 확인
|
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
|
SQL> select * from dbms_xplan.display_cursor('fm1k8nvq6dhc8', NULL, 'TYPICAL');
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID fm1k8nvq6dhc8, child number 0
-------------------------------------
select /*+ index(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 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=10)
19 rows selected.
|
원하는대로 index scan으로 풀림
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
|
SQL>
DECLARE
l_profile SYS.SQLPROF_ATTR;
BEGIN
FOR c1 IN (SELECT a.sql_id
, a.sql_fulltext
, b.other_xml
FROM v$sql a
, v$sql_plan b
WHERE 1 = 1
AND a.sql_id = 'bpu9gmcd1uysw' -- AS-IS
AND a.child_number = 0
AND b.sql_id = 'fm1k8nvq6dhc8' -- TO-BE
AND b.child_number = 0
AND b.other_xml IS NOT NULL)
LOOP
SELECT a.hint
BULK COLLECT
INTO l_profile
FROM XMLTABLE ('other_xml/outline_data/hint'
PASSING XMLPARSE (CONTENT c1.other_xml)
COLUMNS hint VARCHAR2 (4000) PATH 'text()') a;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => c1.sql_fulltext
, profile => l_profile
, name => 'profile_' || c1.sql_id
, replace => TRUE
, force_match => TRUE);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
|
SQL profile이 등록됨
bpu9gmcd1uysw 쿼리 재수행
|
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.
|
bpu9gmcd1uysw 쿼리 플랜 재확인
|
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이 정상적으로 index scan을 하는것을 볼수 있음
그리고 hint report에도 왜 full 힌트가 무시되었는지도 표시됨
등록된 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 Profile의 상세 힌트(Outline) 확인
(profile_name에 확인하고 싶은 프로파일 명 삽입)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
col profile_name for a30
col outline_hint for a80
select profile_name, extractvalue(value(h), '.') as outline_hint
from sys.dbmshsxp_sql_profile_attr attr,
table(xmlsequence(extract(xmltype(attr.comp_data), '/outline_data/hint'))) h
where attr.profile_name = 'profile_bpu9gmcd1uysw';
PROFILE_NAME OUTLINE_HINT
------------------------------ --------------------------------------------------------------------------------
profile_bpu9gmcd1uysw IGNORE_OPTIM_EMBEDDED_HINTS
profile_bpu9gmcd1uysw OPTIMIZER_FEATURES_ENABLE('19.1.0')
profile_bpu9gmcd1uysw DB_VERSION('19.1.0')
profile_bpu9gmcd1uysw ALL_ROWS
profile_bpu9gmcd1uysw OUTLINE_LEAF(@"SEL$1")
profile_bpu9gmcd1uysw INDEX_RS_ASC(@"SEL$1" "T_TEMP"@"SEL$1" ("T_TEMP"."ID"))
profile_bpu9gmcd1uysw BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T_TEMP"@"SEL$1")
7 rows selected.
|
아웃라인 힌트들이 정상적으로 잘 보임
이 힌트들을 보면 알겠지만 이 목록이 위 xplan 결과에 Outline Data에 있는 내용임
참고. Profile 삭제
|
1
2
3
|
SQL> exec dbms_sqltune.drop_sql_profile(name => 'profile_bpu9gmcd1uysw');
PL/SQL procedure successfully completed.
|
결론 :
요즘에는 profile 보다 spm을 이용해 플랜을 많이 고정하지만
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_PROFILES.html
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c SQL Profile 등록된 플랜 SPM(SQL Plan Management)으로 변경 (0) | 2026.05.18 |
|---|---|
| 오라클 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 |