OS환경 : Oracle Linux 7.2 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 18.0.0.0.0
테스트 명 : SQL을 변경하지 않고 실행계획 변경(SPM)
참고사항
실행 전 EMP_DEPT.SQL 실행해서 테이블 만들어야함
튜닝대상의 SQL 실행 이외의 단계는
딕셔너리 접근 권한 또는 DBMS_SQL 패키지 실행 권한있는 유저로 실행해야함.
테스트
1. 성능이 나쁜 SQL의 현재 실행계획(PLAN A) SQL 계획 베이스라인에 등록
1-1. 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
|
$ sqlplus / as sysdba
SQL> conn jds/jds
Connected.
SQL> select E.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
7839 KING ACCOUNTING
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7566 JONES RESEARCH
7788 SCOTT RESEARCH
7902 FORD RESEARCH
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7654 MARTIN SALES
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7934 MILLER ACCOUNTING
14 rows selected.
|
1-2. SQL_ID 와 PLAN_HASH_VALUE 확인
|
1
2
3
4
5
6
7
|
$ sqlplus / as sysdba
SQL> select sql_id, plan_hash_value from v$sql
where sql_text like 'select E.empno, e.ename%';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
b28bvnnn551tr 3216042416
|
1-3. 커서의 실행계획 확인
|
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> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('b28bvnnn551tr'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b28bvnnn551tr, child number 0
-------------------------------------
select E.empno, e.ename, d.dname from emp e, dept d where e.deptno =
d.deptno
Plan hash value: 3216042416
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 14 | 770 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 462 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_DEPTNO | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 | 88 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 88 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
28 rows selected.
|
1-4. 현재 실행계획을 SQL Plan 베이스라인으로 등록
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
set serveroutput on
declare
ret number;
begin
ret := dbms_spm.load_plans_from_cursor_cache(
sql_id => 'b28bvnnn551tr',
plan_hash_value => 3216042416);
dbms_output.put_line('LOAD PLANS : '|| ret);
end;
/
LOAD PLANS : 1
* 등록한 SQL 계획 베이스라인 수가 리턴값으로 출력됨
PL/SQL procedure successfully completed.
|
1-5. 등록한 SQL Plan 베이스라인 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> col PLAN_NAME for a40
SQL> select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted, fixed
from dba_sql_plan_baselines
where sql_text like 'select E.empno, e.ename%';
SQL_HANDLE PLAN_NAME CREATED ACCEPTED FIXED
------------------------------ ---------------------------------------- -------------------
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ute2772e4d 2018/06/28 16:00:07 YES NO
1 row selected.
|
2. SQL에 힌트를 추가한 성능이 좋은 실행계획(PLAN B) 생성
2-1. 힌트를 추가해서 SQL 실행(어플리케이션 실행 사용자로 실행)
|
1
2
3
4
|
SQL> select /*+FULL(e) FULL(d) LEADING(d) USE_HASH(e) */
E.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;
|
2-2. SQL_ID 와 PLAN_HASH_VALUE 확인
|
1
2
3
4
5
6
|
SQL> select sql_id, plan_hash_value from v$sql
where sql_text like 'select /*+FULL(e) FULL(d) LEADING(d) USE_HASH(e) */%';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
f126r9pddmv2r 615168685
|
2-3. 커서의 실행계획 확인
|
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 table(DBMS_XPLAN.DISPLAY_CURSOR('f126r9pddmv2r'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f126r9pddmv2r, child number 0
-------------------------------------
select /*+FULL(e) FULL(d) LEADING(d) USE_HASH(e) */ E.empno, e.ename,
d.dname from emp e, dept d where e.deptno = d.deptno
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 14 | 770 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
25 rows selected.
|
3. 원래 실행계획(PLAN A)을 성능이 좋은 실행계획(PLAN B)로 교체
3-1. 원래 SQL의 SQL_HANDLE을 지정해서, 성능이 좋은 실행계획을 SQL 계획 베이스라인으로 등록
(2-2에서 확인한 SQL_ID, PLAN_HASH_VALUE와 1-5에서 확인한 SQL_HANDLE을 사용)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
set serveroutput on
declare
ret number;
begin
ret := dbms_spm.load_plans_from_cursor_cache(
sql_id => 'f126r9pddmv2r',
plan_hash_value => 615168685,
sql_handle => 'SQL_e18f9b7e02f01359');
dbms_output.put_line('LOAD PLANS : '|| ret);
end;
/
LOAD PLANS : 1
PL/SQL procedure successfully completed.
|
3-2. 등록한 SQL Plan 베이스라인 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> col PLAN_NAME for a40
SQL> select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted, fixed
from dba_sql_plan_baselines
where sql_text like 'select E.empno, e.ename%';
SQL_HANDLE PLAN_NAME CREATED ACCEPTED FIXED
------------------------------ ---------------------------------------- -------------------
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ut8447c07a 2018/06/28 16:09:17 YES NO
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ute2772e4d 2018/06/28 16:00:07 YES NO
2 rows selected.
|
3-3. 원래 실행계획(PLAN A) 제거(CREATED, PLAN_NAME으로 구분)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
set serveroutput on
declare
ret number;
begin
ret := dbms_spm.drop_sql_plan_baseline(
sql_handle => 'SQL_e18f9b7e02f01359',
plan_name => 'SQL_PLAN_f33wvgs1g04ute2772e4d');
dbms_output.put_line('DROP PLANS : '|| ret);
end;
/
DROP PLANS : 1
PL/SQL procedure successfully completed.
|
3-4. 원래 실행계획(PLAN A) 제거되었는지 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> col PLAN_NAME for a40
SQL> select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted, fixed
from dba_sql_plan_baselines
where sql_text like 'select E.empno, e.ename%';
SQL_HANDLE PLAN_NAME CREATED ACCEPTED FIXED
------------------------------ ---------------------------------------- -------------------
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ut8447c07a 2018/06/28 16:09:17 YES NO
1 row selected.
|
3-5. 이후 같은 쿼리 실행시 베이스라인등록한 실행계획으로 가는지 확인
3-5-1. 쿼리 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> select E.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
7839 KING ACCOUNTING
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7566 JONES RESEARCH
7788 SCOTT RESEARCH
7902 FORD RESEARCH
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7654 MARTIN SALES
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7934 MILLER ACCOUNTING
14 rows selected.
|
3-5-2. SQL Plan 베이스라인 확인
|
1
2
3
4
5
6
7
8
|
SQL> select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted
from dba_sql_plan_baselines
where sql_text like 'select E.empno, e.ename%';
SQL_HANDLE PLAN_NAME CREATED ACCEPTED FIXED
------------------------------ ---------------------------------------- ------------------- ---
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ut8447c07a 2018/06/28 16:09:17 YES NO
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ute2772e4d 2018/06/28 16:19:12 NO NO
|
계획이력이 하나 추가 되었지만 ACCEPTED가 YES인(승인된) 실행계획만 사용됨
정확하게는 fix되어있는 실행계획만 사용됨
YES 실행계획이 여러개인 경우 fix로 고정해주는 방법이 있음
3-5-3. SQL Plan 베이스라인 fix
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
declare
ret number;
begin
ret := dbms_spm.alter_sql_plan_baseline(
sql_handle => 'SQL_e18f9b7e02f01359',
plan_name => 'SQL_PLAN_f33wvgs1g04ut8447c07a',
attribute_name => 'fixed',
attribute_value => 'YES');
dbms_output.put_line('PLANS ALTERED: ' || ret);
END;
/
PLANS ALTERED: 1
PL/SQL procedure successfully completed.
|
alter_sql_plan_baseline 설명
함수를 사용해 baseline 속성 값을 변경할 수 있음
attribute_name 속성
enabled : accepted가 yes 일 경우에 옵티마이저가 선택할지(YES) 안할지(NO) 결정함
fixed : 플랜을 Fix할때 사용, yes일 경우 새로운 실행계획이 들어와도 사용하지 않음
fixed yes 가 2개 이상이면 optimizer가 cost 낮은플랜을 선택함
3-5-4. SQL Plan 베이스라인 확인
|
1
2
3
4
5
6
7
8
|
SQL> select sql_handle, plan_name, to_char(created, 'YYYY/MM/DD HH24:MI:SS') created, accepted
from dba_sql_plan_baselines
where sql_text like 'select E.empno, e.ename%';
SQL_HANDLE PLAN_NAME CREATED ACCEPTED FIXED
------------------------------ ---------------------------------------- ------------------- ---
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ut8447c07a 2018/06/28 16:09:17 YES YES
SQL_e18f9b7e02f01359 SQL_PLAN_f33wvgs1g04ute2772e4d 2018/06/28 16:19:12 NO NO
|
FIXED 컬럼이 YES 로 변경됨
3-5-5. AUTOTRACE ON 후 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
|
$ sqlplus / as sysdba
SQL> conn jds/jds
Connected.
SQL> set autot on
SQL> select E.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
7839 KING ACCOUNTING
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7566 JONES RESEARCH
7788 SCOTT RESEARCH
7902 FORD RESEARCH
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7654 MARTIN SALES
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7934 MILLER ACCOUNTING
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 770 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 770 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_f33wvgs1g04ut8447c07a" used for this statement
Statistics
----------------------------------------------------------
107 recursive calls
30 db block gets
64 consistent gets
0 physical reads
9188 redo size
1013 bytes sent via SQL*Net to client
623 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
14 rows processed
|
#Note에 SQL plan baseline에 있는 "SQL_PLAN_f33wvgs1g04ut8447c07a" 가 사용되었다고 표시됨
테스트 복사 붙여넣기용
참조 : 오라클레벨업 서적 13챕터
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| enq HW - contention (0) | 2018.12.05 |
|---|---|
| enq UL - contention, PLSQL lock Timer (0) | 2018.12.05 |
| QMON Coordinator 란 (0) | 2018.05.30 |
| SQL*Net message from client 란? (8) | 2018.05.30 |
| 성능테스트 자료1 (0) | 2018.05.29 |