Oracle 노하우 및 팁

SPM(SQL PLAN MANAGEMENT) 사용 방법 #1 0 11 10,152

by 강정식 [강정식] SPM SQL Plan Management SPB SQL PLAN Baseline [2013.07.26 09:35:13]

이번 시간에는 11g에서 새롭게 소개된 SPM(SQL PLAN MANAGEMENT) 사용 방법 사용 방법에 대해 알아보고자 한다.

SPM(SQL PLAN MANAGEMENT) 사용 방법이란?

- SPM은 baseline(Plan과 Hint)를 DB내에 저장 해 놓고 검증된 실행계획만 사용할 수 있도록 하는 기능이다. 이로써 통계정보 변경이나 application의 수정, DB version upgrade 등으로부터의 영향을 최소화 하는데 그 목적이 있다.

- SQL_PROFILE은 한 Category의 특정 SQL에 대해 오직 하나의 Plan만 저장할 수 있기 때문에, 다른 Plan을 적용시키기 위해서는 Category를 변경하여 저장하거나 새로운 Plan을 Stored Outline으로 저장해야 한다.

- 하지만 SPM은 특정 SQL에 여러개의 Plan을 관리할 수 있으며, 그 중 필요한 Plan을 사용할 수 있기 때문에 보다 공격적인 Plan Fix 방법론이라 할 수 있다.

SPM과 SQL_PROFILE 차이점은?

- SPM과 SQL Profile 모두 검증된 실행계획을 사용하는데는 비슷하나, 아래와 같은 차이점이 있다.

SQL PLAN MANAGEMENT SQL PROFLIE
Execution Plan의 변경으로 발생될지 모르는 성능저하를 예방하기 위한 사전 예방적인 방식이다.(Preventative Mechanism) High-Loaded SQL의 Plan을 Tuning 한 결과로 생성되므로 사후 조치적인 방식이다.(Reactive Mechanism)
Plan Hit가 저장되고 Plan History가 관리된다. SQL이 잘 수행될 수 있도록 일반적인 통계정보 이외의 부가 정보를 Dictionary에 저장한다. Plan Hit가 저장되지 않음.
저장된 Plan Hit대로 수행되므로 외부적인 환경변화가 있더라도 Plan은 변경되지 않는다. SQL Profile이 생성되어 있더라도 외부적인 환경변화로 인해 SQL Plan이 변경될 수 있다.
SQL Plan의 이력 관리가 됨 SQL Profile이 생성되어 있더라도 외부적인 환경변화로 인해 SQL Plan이 변경될 수 있다.
SQL Baseline에 검증된 Plan이 여러 개 존재할 수 있고 실행계획은 그 중에서 선택되어진다. 한 Category의 특정 SQL에 대해 오직 하나의 Plan만 저장할 수 있기 때문에 다른 Plan을 적응시키기 위해서는 Category를 변경하여 저장하거나 새로운 Plan을 Stored Outline으로 저장해야 한다
Plan을 검증하는 기능이 있어 기존의 Plan대비 어느 정도의 Cost와 성능 향상이 기대되는지를 분석할 수 있다. 따라서 분석 결과에 따라 특정 Plan을 버릴 수도 있고 Plan으로 채택되지 않게 설정을 변경할 수도 있다.

SPM 사용 예제

1. 테스트 DB 버전 확인
?
1
2
3
4
5
6
SELECT * FROM V$VERSION;
 
BANNER 
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production

2. 테스트 테이블, 인덱스, 통계정보 생성
?
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
CREATE TABLE EMP
  (EMPNO  NUMBER(4) NOT NULL,
  ENAME  VARCHAR2(10),
  JOB   VARCHAR2(9),
  MGR   NUMBER(4),
  HIREDATE DATE,
  SAL   NUMBER(7, 2),
  COMM  NUMBER(7, 2),
  DEPTNO NUMBER(2));
 
 
INSERT INTO EMP VALUES
  (7369, 'SMITH', 'CLERK', 7902,
  TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
  (7499, 'ALLEN', 'SALESMAN', 7698,
  TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
  (7521, 'WARD',  'SALESMAN', 7698,
  TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
  (7566, 'JONES', 'MANAGER',  7839,
  TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
  (7654, 'MARTIN', 'SALESMAN', 7698,
  TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
  (7698, 'BLAKE', 'MANAGER',  7839,
  TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
  (7782, 'CLARK', 'MANAGER',  7839,
  TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
  (7788, 'SCOTT', 'ANALYST',  7566,
  TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
  (7839, 'KING',  'PRESIDENT', NULL,
  TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
  (7844, 'TURNER', 'SALESMAN', 7698,
  TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500,  0, 30);
INSERT INTO EMP VALUES
  (7876, 'ADAMS', 'CLERK', 7788,
  TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
  (7900, 'JAMES', 'CLERK', 7698,
  TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  950, NULL, 30);
INSERT INTO EMP VALUES
  (7902, 'FORD',  'ANALYST',  7566,
  TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
  (7934, 'MILLER', 'CLERK', 7782,
  TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
 
CREATE TABLE DEPT
  (DEPTNO NUMBER(2),
  DNAME  VARCHAR2(14),
  LOC   VARCHAR2(13) );
 
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',  'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',  'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
 
COMMIT;
/
 
CREATE UNIQUE INDEX APPS.EMP_U1 ON APPS.EMP (EMPNO);
 
CREATE INDEX APPS.EMP_N1 ON APPS.EMP (DEPTNO);
 
CREATE UNIQUE INDEX APPS.DEPT_U1 ON APPS.DEPT (DEPTNO);
 
CREATE INDEX APPS.DEPT_N1 ON APPS.DEPT (LOC);
 
BEGIN
 DBMS_STATS.GATHER_TABLE_STATS(USER,
  'EMP',
  CASCADE => TRUE);
END;
/
 
BEGIN
 DBMS_STATS.GATHER_TABLE_STATS(USER,
  'DEPT',
  CASCADE => TRUE);
END;
/

3. Format 세팅
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SET LINESIZE 200 PAGESIZE 1000 ECHO ON FEEDBACK OFF TRIMSPOOL ON
COL SQL_HANDLE FORMAT A20
COL PLAN_NAME FORMAT A30
COL ORIGIN FORMAT A12
COL OCO FORMAT 9999
COL LAST_MODIFIED FORMAT A17
COL LAST_EXECUTED FORMAT A17
COL LAST_VERIFIED FORMAT A17
COL REPRODUCED FORMAT A3
COL SQL_TEXT FORMAT A65
COL PLAN_TABLE_OUTPUT FORMAT A130
 
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YY/MM/DD HH24:MI:SS';

4. 테스트 DB에서 SQL_PLAN_BASELINES 파라미터 확인
?
1
2
3
4
5
6
SHOW PARAMETER SQL_PLAN_BASELINES
 
NAME TYPE  VALUE 
------------------------------------ -------- --------
optimizer_capture_sql_plan_baselines boolean FALSE  
optimizer_use_sql_plan_baselines   boolean TRUE 
  • - optimizer_capture_sql_plan_baselines : SPB를 통해 실행계획을 자동으로 캡쳐하도록 활성화하는 파라미터
  • - optimizer_use_sql_plan_baselines : SPB를 사용토록 활성화하는 파라미터

5. 세션 레벨에서 SPB 관련 파라미터 값 변경
?
1
2
3
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
 
ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE; 
  • - 자동 캡쳐 기능을 테스트하기 위해 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 값을 TRUE로 변경함
?
1
SELECT * FROM V$VERSION;

6. SQL을 직접 수행하여 SPB 등록
?
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
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=300;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO),
    COUNT(EMPNO)
 FROM EMP
 WHERE DEPTNO = 97;
 
MAX(EMPNO) COUNT(EMPNO)
---------- ------------
 0
 
1 row selected.
 
Elapsed: 00:00:00.06
 
Execution Plan
---------------------------------------------------------- 
Plan hash value: 1849991560
   
---------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |   | 1   | 7   | 5  (0)| 00:00:01 |
|  1 | SORT AGGREGATE   |   | 1   | 7   |    |     |
|* 2 |  TABLE ACCESS FULL | EMP | 1   | 7   | 5  (0)| 00:00:01 |
---------------------------------------------------------------------------
   
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("DEPTNO"=97)
 
SET AUTOT OFF 
  • - OPTIMIZER_INDEX_COST_ADJ : 이 파라미터는 값이 높을수록 FULL SCAN을 선호하고, 낮을수록 인덱스 사용을 선호함
  • - 1회 수행된 SQL은 SPB에 캡쳐가 안되고, 2회 이상부터 캡쳐됨

7. 동일 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
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO),
    COUNT(EMPNO)
 FROM EMP
 WHERE DEPTNO = 97;
  
SET AUTOT OFF
 
SELECT SQL_HANDLE,
    PLAN_NAME,
    ORIGIN,
    ENABLED,
    ACCEPTED,
    FIXED,
    OPTIMIZER_COST AS OCO,
    --LAST_MODIFIED,
    --LAST_EXECUTED,
    --LAST_VERIFIED,
    SQL_TEXT
 FROM DBA_SQL_PLAN_BASELINES
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
 
SQL_HANDLE  PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
   
ORIGIN    ENABLE ACCEPT FIXED OCO  SQL_TEXT 
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  YES  NO   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                     FROM EMP
                     WHERE DEPTNO = 97
  • - SQL_HANDLE : SQL_ID에 대한 SPB 대표이름
  • - PLAN_NAME : SQL_HANDLE에 종속된 Plan 이름
  • - ORIGIN : SPB 등록 방식(AUTO-CAPTURE 또는 MANUAL)
  • - ACCEPT : PLAN_NAME 중 ACCEPT가 YES인 경우에만 SPB로 사용됨
  • - FIXED : SQL_HANDLE 내에 여러 PLAN_NAME이 있을 경우 FIXED가 YES인 PLAN_NAME만 사용됨

8. 동일 SQL에 대해 새로운 PLAN_NAME 등록
?
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
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
MAX(EMPNO) COUNT(EMPNO)
---------- ------------
 0
Elapsed: 00:00:00.10
 
Execution Plan
---------------------------------------------------------- 
Plan hash value: 2854672349
   
---------------------------------------------------------------------------------
| Id | Operation        | Name  | Rows | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |    | 1  | 7   | 1  (0)  | 00:00:01 |
|  1 | SORT AGGREGATE     |    | 1  | 7   |      |     |
|  2 |  TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7   | 1  (0)  | 00:00:01 |
|* 3 |  INDEX RANGE SCAN   | EMP_N1 | 1  |    | 1  (0)  | 00:00:01 |
---------------------------------------------------------------------------------
   
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("DEPTNO"=97)  
  • - 세션 레벨에서 OPTIMIZER_INDEX_COST_ADJ 파라미터를 1로 주어 인덱스를 사용토록 유도함.
  • - OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, OPTIMIZER_USE_SQL_PLAN_BASELINES 2개 파라미터 중 하나라도 TURE일 경우 새로운 PLAN_NAME 등록됨

9. SPB 확인
?
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
SELECT SQL_HANDLE,
    PLAN_NAME,
    ORIGIN,
    ENABLED,
    ACCEPTED,
    FIXED,
    OPTIMIZER_COST AS OCO,
    -- LAST_MODIFIED,
    -- LAST_EXECUTED,
    -- LAST_VERIFIED
    SQL_TEXT
 FROM DBA_SQL_PLAN_BASELINES
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
SQL_HANDLE      PLAN_NAME  
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9 
  
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  NO   NO   1   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
SQL_HANDLE      PLAN_NAME  
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a 
  
ORIGIN    ENABLE ACCEPT FIXED OCO  SQL_TEXT 
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  YES  NO   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97

10. XPLAN으로 SPB 확인
?
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
SELECT T.*
 FROM
   ( SELECT DISTINCT SQL_HANDLE
     FROM DBA_SQL_PLAN_BASELINES
    WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
   ) PB,
    TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PB.SQL_HANDLE, NULL, 'TYPICAL')) T
;
 
 
---------------------------------------------------------------------------
SQL handle: SQL_5009a0e70c6d5325  
SQL text: SELECT MAX(EMPNO),   COUNT(EMPNO) FROM  EMP WHERE DEPTNO = 97 
---------------------------------------------------------------------------
   
----------------------------------------------------------------------------
Plan name: SQL_PLAN_502d0ww66unt59995a0e9 Plan id: 2576720105 
Enabled: YES Fixed: NO  Accepted: NO  Origin: AUTO-CAPTURE 
----------------------------------------------------------------------------
   
Plan hash value: 2854672349
   
-----------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |    | 1   | 7   | 1  (0)| 00:00:01 |
|  1 | SORT AGGREGATE     |    | 1   | 7   |    |     |
|  2 |  TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7   | 1  (0)| 00:00:01 |
|* 3 |  INDEX RANGE SCAN   | EMP_N1| 1   |    | 1  (0)| 00:00:01 |
-----------------------------------------------------------------------------
   
Predicate Information (identified by operation id):
---------------------------------------------------
   
  3 - access("DEPTNO"=97) 
   
----------------------------------------------------------------------------
Plan name: SQL_PLAN_502d0ww66unt5c392520a Plan id: 3281146378 
Enabled: YES Fixed: NO  Accepted: YES Origin: AUTO-CAPTURE 
----------------------------------------------------------------------------
   
Plan hash value: 1849991560
   
--------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |    | 1   | 7   | 5  (0)| 00:00:01 |
|  1 | SORT AGGREGATE   |    | 1   | 7   |    |     |
|* 2 |  TABLE ACCESS FULL | EMP  | 1   | 7   | 5  (0)| 00:00:01 |
--------------------------------------------------------------------------
   
Predicate Information (identified by operation id):
---------------------------------------------------  
  2 - filter("DEPTNO"=97)
  • - SQL_PLAN_502d0ww66unt59995a0e9 : INDEX SCAN 플랜
  • - SQL_PLAN_502d0ww66unt5c392520a : FULL SCAN 플랜

11. 새로운 PLAN_NAME은 등록되나, 기존에 등록된 PLAN_NAME과 실행계획 동일할 경우 재 등록되지 않음
?
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
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=300;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
SELECT SQL_HANDLE,
    PLAN_NAME,
    ORIGIN,
    ENABLED,
    ACCEPTED,
    FIXED,
    OPTIMIZER_COST AS OCO,
    -- LAST_MODIFIED,
    -- LAST_EXECUTED,
    -- LAST_VERIFIED
    SQL_TEXT
 FROM DBA_SQL_PLAN_BASELINES
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
SQL_HANDLE      PLAN_NAME  
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9 
  
ORIGIN    ENABLE ACCEPT FIXED OCO  SQL_TEXT  
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  NO   NO   1   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
SQL_HANDLE      PLAN_NAME  
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a 
  
ORIGIN    ENABLE ACCEPT FIXED OCO  SQL_TEXT 
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  YES  NO   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97

12. ACCEPT PLAN_NAME 사용 확인
?
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
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
 
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
Execution Plan
----------------------------------------------------------  
Plan hash value: 1849991560 
  
--------------------------------------------------------------------------- 
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time | 
--------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT  |    | 1   | 7   | 5  (0)| 00:00:01 | 
|  1 | SORT AGGREGATE   |    | 1   | 7   |    |     | 
|* 2 |  TABLE ACCESS FULL | EMP  | 1   | 7   | 5  (0)| 00:00:01 | 
--------------------------------------------------------------------------- 
  
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  
  2 - filter("DEPTNO"=97)  
  
Note 
-----
  - SQL plan baseline "SQL_PLAN_502d0ww66unt5c392520a" used for this statement 
 
SELECT SQL_HANDLE,
    PLAN_NAME,
    ORIGIN,
    ENABLED,
    ACCEPTED,
    FIXED,
    OPTIMIZER_COST AS OCO,
    -- LAST_MODIFIED,
    -- LAST_EXECUTED,
    -- LAST_VERIFIED
    SQL_TEXT
 FROM DBA_SQL_PLAN_BASELINES
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
SQL_HANDLE      PLAN_NAME  
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9 
 
ORIGIN    ENABLE ACCEPT FIXED OCO  SQL_TEXT  
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  NO   NO   1   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
   
ORIGIN    ENABLE ACCEPT FIXED  OCO  SQL_TEXT 
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  YES  NO   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97

- OPTIMIZER_INDEX_COST_ADJ 값을 1로 주어 인덱스를 사용토록 유도했지만 SQL_PLAN_502d0ww66unt5c392520a PLAN_NAME의 ACCEPT 값이 YES이기 때문에 FULL SCAN을 함

13. DBMS_SPM 패키지를 통해 등록된 PLAN_NAME 성능 비교
?
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
SET SERVEROUTPUT ON LONG 10000
 
DECLARE
  REPORT CLOB;
BEGIN
  REPORT := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE => 'SQL_5009a0e70c6d5325');
  DBMS_OUTPUT.PUT_LINE(REPORT);
END;
/
 
------------------------------------------------
  Evolve SQL Plan Baseline Report
------------------------------------------------
 
 
Inputs:
-------
 SQL_HANDLE = SQL_5009a0e70c6d5325
 PLAN_NAME =
 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
 VERIFY = YES
 COMMIT = YES
 
Plan: SQL_PLAN_502d0ww66unt59995a0e9
------------------------------------
 Plan was verified: Time used .116 seconds.
 Plan passed performance criterion: 14 times better than baseline plan.
 Plan was changed to an accepted plan.
 
Baseline Plan  Test Plan  Stats Ratio
-------------  ---------  -----------
 Execution Status:  COMPLETE  COMPLETE
 Rows Processed:   1  1
 Elapsed Time(ms):  .074   .05  1.48
 CPU Time(ms):  0  0
 Buffer Gets:  14  1   14
 Physical Read Requests:   0  0
 Physical Write Requests:  0  0
 Physical Read Bytes:  0  0
 Physical Write Bytes:  0  0
 Executions:   1  1
--------------------------------------------
Report Summary
--------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1 
;
 
SELECT SQL_HANDLE,
    PLAN_NAME,
    ORIGIN,
    ENABLED,
    ACCEPTED,
    FIXED,
    OPTIMIZER_COST AS OCO,
    -- LAST_MODIFIED,
    -- LAST_EXECUTED,
    -- LAST_VERIFIED
    SQL_TEXT
 FROM DBA_SQL_PLAN_BASELINES
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9
   
ORIGIN    ENABLE ACCEPT FIXED OCO  SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  NO  1   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
   
ORIGIN    ENABLE ACCEPT FIXED OCO  SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES   NO   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97

- DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 펑션을 통해, SQL_HANDLE에 있는 PLAN_NAME을 비교할 수 있으며, 위의 결과에서는 인덱스를 사용하는 SQL_5009a0e70c6d5325 PLAN_NAME이 FULL SCAN하는 SQL_PLAN_502d0ww66unt5c392520a 보다 더 좋은것으로 평가됨

- 이 평가로 인해 인덱스를 사용하는 SQL_PLAN_502d0ww66unt59995a0e9 PLAN_NAME의 ACCEPT 값이 NO에서 YES로 변경됨

14. 2개 PLAN_NAME의 ACCEPT 값이 모두 YES일 경우
?
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
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
Execution Plan
---------------------------------------------------------- 
Plan hash value: 2854672349 
 
--------------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |    | 1   | 7   | 1  (0)  | 00:00:01 |
|  1 | SORT AGGREGATE     |    | 1   | 7   |      |     |
|  2 |  TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7   | 1  (0)  | 00:00:01 |
|* 3 |  INDEX RANGE SCAN   | EMP_N1| 1   |    | 1  (0)  | 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  3 - access("DEPTNO"=97) 
 
Note
-----  
  - SQL plan baseline "SQL_PLAN_502d0ww66unt59995a0e9" used for this statement 
 
 
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=300;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
Execution Plan
----------------------------------------------------------  
Plan hash value: 1849991560 
  
--------------------------------------------------------------------------- 
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time  | 
--------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT  |    | 1   | 7   | 5  (0)  | 00:00:01 | 
|  1 | SORT AGGREGATE   |    | 1   | 7   |      |     | 
|* 2 |  TABLE ACCESS FULL | EMP  | 1   | 7   | 5  (0)  | 00:00:01 | 
--------------------------------------------------------------------------- 
  
Predicate Information (identified by operation id): 
---------------------------------------------------  
  2 - filter("DEPTNO"=97)  
  
Note 
-----
  - SQL plan baseline "SQL_PLAN_502d0ww66unt5c392520a" used for this statement

- 2개 PLAN_NAME의 ACCPECT 값이 모두 YES일 경우, 기존의 옵티마이저 Cost에 따라 Plan 결정됨

15. PLAN_NAME 중 하나를 FIXED 설정
?
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
VAR PBSTS VARCHAR2(30);
EXEC :PBSTS :=
   DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_5009a0e70c6d5325', 'SQL_PLAN_502d0ww66unt5c392520a', 'FIXED', 'YES');
 
SELECT SQL_HANDLE,
    PLAN_NAME,
    ORIGIN,
    ENABLED,
    ACCEPTED,
    FIXED,
    OPTIMIZER_COST AS OCO,
    -- LAST_MODIFIED,
    -- LAST_EXECUTED,
    -- LAST_VERIFIED
    SQL_TEXT
 FROM DBA_SQL_PLAN_BASELINES
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9
   
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  NO   1   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
 
SQL_HANDLE  PLAN_NAME 
-------------------- -----------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
   
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  YES   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
 
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
Execution Plan
---------------------------------------------------------- 
Plan hash value: 1849991560
   
---------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |    | 1   | 7   | 5  (0)  | 00:00:01 |
|  1 | SORT AGGREGATE   |    | 1   | 7   |      |     |
|* 2 |  TABLE ACCESS FULL | EMP  | 1   | 7   | 5  (0)  | 00:00:01 |
---------------------------------------------------------------------------
   
Predicate Information (identified by operation id):
---------------------------------------------------  
  2 - filter("DEPTNO"=97) 
   
Note  
----- 
  - SQL plan baseline "SQL_PLAN_502d0ww66unt5c392520a" used for this statement

- DBMS_SPM.ALTER_SQL_PLAN_BASELINE 펑션을 통해 FULL SCAN 하는 SQL_PLAN_502d0ww66unt5c392520a PLAN_NAME을 FIXED함.

- 이후, OPTIMIZER_INDEX_COST_ADJ 값을 1로 주어 인덱스 사용을 유도하여도 FULL SCAN하는 SQL_PLAN_502d0ww66unt5c392520a PLAN_NAME 사용함.

16. 양쪽 모두 FIXED 값을 YES로 할 경우
?
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
VAR PBSTS VARCHAR2(30);
EXEC :PBSTS :=
  DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_5009a0e70c6d5325', 'SQL_PLAN_502d0ww66unt59995a0e9', 'FIXED', 'YES');
 
SELECT SQL_HANDLE,
    PLAN_NAME,
    ORIGIN,
    ENABLED,
    ACCEPTED,
    FIXED,
    OPTIMIZER_COST AS OCO,
    -- LAST_MODIFIED,
    -- LAST_EXECUTED,
    -- LAST_VERIFIED
    SQL_TEXT
 FROM DBA_SQL_PLAN_BASELINES
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9
   
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  YES   1 SELECT MAX(EMPNO), 
  COUNT(EMPNO)
  FROM  EMP 
  WHERE DEPTNO = 97
 
 
SQL_HANDLE  PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
   
ORIGIN  ENABLE ACCEPT FIXED  OCO  SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  YES   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
 
 
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
Execution Plan
---------------------------------------------------------- 
Plan hash value: 2854672349
   
------------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |    | 1   | 7   | 1  (0)| 00:00:01 |
|  1 | SORT AGGREGATE     |    | 1   | 7   |    |     |
|  2 |  TABLE ACCESS BY INDEX ROWID | EMP | 1 | 7   | 1  (0)| 00:00:01 |
|* 3 |  INDEX RANGE SCAN   | EMP_N1 | 1  |    | 1  (0)| 00:00:01 |
------------------------------------------------------------------------------
   
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("DEPTNO"=97) 
   
Note  
----- 
  - SQL plan baseline "SQL_PLAN_502d0ww66unt59995a0e9" used for this statement

- 2개 모두 FIXED일 경우 ACCEPT 값이 모두 YES인 경우와 동일하게 옵티마이저 Cost에 의해 Plan 선택됨

17. 인덱스 사용하는 PLAN_NAME 삭제 후, FIXED 설정된 SQL_HANDLE에 추가로 PLAN_NAME이 등록되는지 확인
?
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
var v_num NUMBER
EXEC :V_NUM :=
  DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_5009a0e70c6d5325', PLAN_NAME=>'SQL_PLAN_502d0ww66unt59995a0e9');
 
SELECT SQL_HANDLE,
  PLAN_NAME,
  ORIGIN,
  ENABLED,
  ACCEPTED,
  FIXED,
  OPTIMIZER_COST AS OCO,
  -- LAST_MODIFIED,
  -- LAST_EXECUTED,
  -- LAST_VERIFIED
  SQL_TEXT
FROM  DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
;
 
SQL_HANDLE  PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
   
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  YES   5  SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
 
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
Execution Plan
---------------------------------------------------------- 
Plan hash value: 1849991560
   
---------------------------------------------------------------------------
| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |    | 1   | 7   | 5  (0)  | 00:00:01 |
|  1 | SORT AGGREGATE   |    | 1   | 7   |      |     |
|* 2 |  TABLE ACCESS FULL | EMP  | 1   | 7   | 5  (0)  | 00:00:01 |
---------------------------------------------------------------------------
   
Predicate Information (identified by operation id):
---------------------------------------------------
   
  2 - filter("DEPTNO"=97) 
   
Note  
----- 
  - SQL plan baseline "SQL_PLAN_502d0ww66unt5c392520a" used for this statement 
;
 
SELECT SQL_HANDLE,
  PLAN_NAME,
  ORIGIN,
  ENABLED,
  ACCEPTED,
  FIXED,
  OPTIMIZER_COST AS OCO,
  -- LAST_MODIFIED,
  -- LAST_EXECUTED,
  -- LAST_VERIFIED
  SQL_TEXT
FROM  DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
;
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
   
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  YES   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97

- 인덱스 사용하는 SQL_PLAN_502d0ww66unt59995a0e9 PLAN_NAME 삭제

- SPB 캡쳐가 되도록 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 파라미터 활성화

- 이후, OPTIMIZER_INDEX_COST_ADJ 값을 1로 주어 인덱스를 사용토록 유도했으나, FULL SCAN하는 PLAN_NAME 선택됨

- SPB 확인 결과, 새로운 PLAN_NAME 추가 안됨

18. AUTO-CAPTURE가 아닌 MANUAL로 SPB 등록
?
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
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
;
 
Execution Plan
----------------------------------------------------------  
Plan hash value: 2854672349 
  
----------------------------------------------------------------------------- 
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time | 
----------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT    |    | 1   | 7   | 2  (0)| 00:00:01 | 
|  1 | SORT AGGREGATE     |    | 1   | 7   |    |     | 
|  2 |  TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7   | 2  (0)| 00:00:01 | 
|* 3 |  INDEX RANGE SCAN  | EMP_N1 | 1   |    | 1  (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
  
Predicate Information (identified by operation id): 
--------------------------------------------------- 
  3 - access("DEPTNO"=97)  
;
 
SELECT SQL_ID,
    CHILD_NUMBER,
    PLAN_HASH_VALUE
 FROM V$SQL
 WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%';
 
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
-------------------------- ------------ ---------------
5d3txdaq22gts 0  1849991560
5d3txdaq22gts 1  2854672349 --> Target
5d3txdaq22gts 2  1849991560
;
 
DECLARE
  MY_PLANS PLS_INTEGER;
BEGIN
  MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( SQL_ID => '5d3txdaq22gts', PLAN_HASH_VALUE => '2854672349', FIXED => 'NO', ENABLED => 'YES');
END;
/
 
SELECT SQL_HANDLE,
  PLAN_NAME,
  ORIGIN,
  ENABLED,
  ACCEPTED,
  FIXED,
  OPTIMIZER_COST AS OCO,
  -- LAST_MODIFIED,
  -- LAST_EXECUTED,
  -- LAST_VERIFIED
  SQL_TEXT
FROM  DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
;
 
SQL_HANDLE      PLAN_NAME
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9 
   
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
MANUAL-LOAD YES  YES  NO   1   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
   
SQL_HANDLE      PLAN_NAME
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a
 
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  YES   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97

- OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 파라미터 비활성화 시킴

- 인덱스 사용하는 PLAN을 Shared Pool에 등록

- 해당 PLAN의 SQL_ID, PLAN_HASH_VALUE 값을 이용하여 MANUAL하게 SPB 등록

19. FIXED를 NO로 변경할 경우 새로운 PLAN_NAME 등록되는지 테스트
?
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
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES  = TRUE;
 
  
 
var v_num NUMBER
EXEC :V_NUM :=
  DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_5009a0e70c6d5325', PLAN_NAME=>'SQL_PLAN_502d0ww66unt59995a0e9');
 
  
 
SELECT SQL_HANDLE,
  PLAN_NAME,
  ORIGIN,
  ENABLED,
  ACCEPTED,
  FIXED,
  OPTIMIZER_COST AS OCO,
  -- LAST_MODIFIED,
  -- LAST_EXECUTED,
  -- LAST_VERIFIED
  SQL_TEXT
FROM  DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
;
 
  
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a 
 
  
 
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT   
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  YES   5  SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
 
 
VAR PBSTS VARCHAR2(30);
EXEC :PBSTS :=
  DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_5009a0e70c6d5325', 'SQL_PLAN_502d0ww66unt5c392520a', 'FIXED', 'NO');
 
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
SELECT SQL_HANDLE,
  PLAN_NAME,
  ORIGIN,
  ENABLED,
  ACCEPTED,
  FIXED,
  OPTIMIZER_COST AS OCO,
  -- LAST_MODIFIED,
  -- LAST_EXECUTED,
  -- LAST_VERIFIED
  SQL_TEXT
FROM  DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
;
 
SQL_HANDLE      PLAN_NAME  
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9 
 
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT 
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  NO   NO   1   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
   
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------ 
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a 
 
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- --------------------
AUTO-CAPTURE YES  YES  NO   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97

- 인덱스 사용하는 SQL_PLAN_502d0ww66unt59995a0e9 PLAN_NAME 삭제

- AUTO-CAPTURE 사용토록 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 파라미터 활성화

- 인덱스 사용하는 Plan 등록하기 위해 OPTIMIZER_INDEX_COST_ADJ 파라미터 1로 주고 실행

- SQL_PLAN_502d0ww66unt5c392520a PLAN_NAME의 FIXED를 NO로 하고, OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 파라미터를 활성화 했기 때문에 새로운 PLAN_NAME 등록됨

20. ACCEPT 값이 NO이고 FIXED만 YES일 경우 Plan이 선택되는지 확인
?
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
VAR PBSTS VARCHAR2(30);
EXEC :PBSTS := DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_5009a0e70c6d5325', 'SQL_PLAN_502d0ww66unt59995a0e9', 'FIXED', 'YES');
 
SELECT SQL_HANDLE,
  PLAN_NAME,
  ORIGIN,
  ENABLED,
  ACCEPTED,
  FIXED,
  OPTIMIZER_COST AS OCO,
  -- LAST_MODIFIED,
  -- LAST_EXECUTED,
  -- LAST_VERIFIED
  SQL_TEXT
FROM  DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
;
 
SQL_HANDLE      PLAN_NAME 
-------------------- ------------------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt59995a0e9
 
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT  
------------ ------ ------ ------ ----- -------------------
AUTO-CAPTURE YES  NO   YES   1  SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
SQL_HANDLE      PLAN_NAME
-------------------- ------------------------------ 
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a  
   
ORIGIN    ENABLE ACCEPT FIXED  OCO SQL_TEXT
------------ ------ ------ ------ ----- ---------------------
AUTO-CAPTURE YES  YES  NO   5   SELECT MAX(EMPNO),
                        COUNT(EMPNO)
                      FROM EMP
                     WHERE DEPTNO = 97
 
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;
 
SET AUTOT ON EXPLAIN
 
SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97;
 
SET AUTOT OFF
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1849991560  
   
----------------------------------------------------------------------  
| Id | Operation     | Name | Rows| Bytes | Cost (%CPU)| Time |  
----------------------------------------------------------------------  
|  0 | SELECT STATEMENT  |   | 1  | 7   | 5  (0)| 00:00:01 |  
|  1 | SORT AGGREGATE  |   | 1  | 7   |    |     |  
|* 2 |  TABLE ACCESS FULL| EMP | 1  | 7   | 5  (0)| 00:00:01 |  
----------------------------------------------------------------------  
   
Predicate Information (identified by operation id):  
---------------------------------------------------
  2 - filter("DEPTNO"=97)
   
Note 
----- 
  - SQL plan baseline "SQL_PLAN_502d0ww66unt5c392520a" used for this statement

- 인덱스 사용하는 SQL_PLAN_502d0ww66unt59995a0e9 PLAN_NAME의 FIXED를 YES로 변경함

- 세션 레벨에서 인덱스 사용을 유도하는 OPTIMIZER_INDEX_COST_ADJ 파라미터 값을 1로 설정 후 Plan 확인

- 그러나, 해당 Plan은 인덱스를 사용하지 않고 FULL SCAN을 하는 SQL_PLAN_502d0ww66unt5c392520a 사용함

- 이유는, SQL_PLAN_502d0ww66unt59995a0e9 PLAN_NAME의 FIXED가 YES여도 ACCEPT값이 NO이기 때문에 선택 안됨

이상으로 SPM에 대한 간략한 테스트를 마쳤다. 다음 시간에는 SPM을 Export/Import 하는 기능과 Shared Pool에서 SPB를 등록하는 상세 방법에 대해 소개해 보고자 한다. 내용을 보고 궁금한 점 있으면 리플을 달아주기 바라며, 아래에는 위에서 소개된 기능에 대해 요약한 내용이다.

요약

1. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, OPTIMIZER_USE_SQL_PLAN_BASELINES 2개 파라미터 중 하나가 TRUE이면 New Plan 추가됨

2. SPM은 2번 이상 Shared Pool에 Hit 될 경우에만 등록됨

3. 같은 Plan은 SPM에 추가 등록되지 않음

4. DBA_SQL_PLAN_BASELINES 뷰에서 ACCEPT, FIXED 2가지 값에 의해 SPM 선택됨

  • - ACCEPT가 YES인 Plan만 선택됨
  • - 2개 이상 Plan이 YES일 경우 FIXED가 YES인 Plan만 선택됨
  • - FIXED가 YES여도 ACCEPT가 NO이면 Plan 선택 안됨
  • - APPEPT, FIXED 양쪽 모두 YES일 경우, 옵타마이저 Cost에 의해 플랜 선택됨

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
-- 1. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(RETUEN : CLOB)
 - 기능  : SPM에 등록된 PLAN 비교하여 더 나은 PLAN을 ACCEPT 하는 펑션
 - 사용 샘플
DECLARE
REPORT CLOB;
BEGIN
REPORT := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE => 'SQL_5009a0e70c6d5325');
DBMS_OUTPUT.PUT_LINE(REPORT);
END;
/
  
-- 2. DBMS_SPM.ALTER_SQL_PLAN_BASELINE(RETURN : PLS_INTEGER)
 - 기능  : SPM에 등록된 PLAN 중, 더 나은 PLAN을 Fix하는 펑션
 - 사용 샘플
VAR PBSTS VARCHAR2(30);
EXEC :PBSTS := DBMS_SPM.ALTER_SQL_PLAN_BASELINE('SQL_5009a0e70c6d5325', 'SQL_PLAN_502d0ww66unt5c392520a', 'FIXED', 'YES');
 
-- 3. DBMS_SPM.DROP_SQL_PLAN_BASELINE(RETURN : PLS_INTEGER)
 - 기능  : SPM에 등록된 PLAN 중, 삭제하고자 하는 PLAN 선정하는 펑션
 - 사용 샘플
var v_num NUMBER
EXEC :V_NUM := DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_5009a0e70c6d5325', PLAN_NAME=>'SQL_PLAN_502d0ww66unt59995a0e9');
 
-- 4. DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(RETURN : PLS_INTEGER)
 - 기능  : Shared pool에 등록된 SQL_ID를 SPM에 등록하는 펑션
 - 사용 샘플
DECLARE
MY_PLANS PLS_INTEGER;
BEGIN
MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( SQL_ID => '5d3txdaq22gts', PLAN_HASH_VALUE => '2854672349', FIXED => 'NO', ENABLED => 'YES');
END;
/

참고

  • - Oracle Document
  • - http://wiki.gurubee.net/display/CORE/3.+SQL+PLAN+MANAGEMENT

Blog

  • - http://blog.naver.com/xsoft
by 부쉬맨 [2013.07.26 09:42:31]
일단 우왕
by 부쉬맨 [2013.07.26 09:59:56]
스크롤내리다가
너무길어서
시간날때..
나중에봐야겠슴
by DIIIN [2013.07.26 10:31:46]
좋은 자료 감사합니다 핸드폰이라 보기가 조금 힘들군요 나중에 다시 살펴봐야겠네요
by 타락천사 [2013.07.26 10:57:52]
좋은 정보 감사합니다.
테스트 해보려면 시간 좀 걸릴 듯 ㅇ.ㅇ
by 강정식 [2013.07.26 11:34:36]
편집하는데 시간 많이 들였는데... 폰트 정렬이 잘 안되네요..
혹시 보시기 힘드신 분들은 제 블로그로 와주세요..
by 김정식 [2013.07.26 14:13:48]
이거 내가 시간날때 깔끔하게 수정하고, 깔끔하게 올릴 수 있는 템플릿 하나 등록해 놓을께..
by 강정식 [2013.07.26 14:20:38]
오오.. 형 땡큐요 ^^
by 임상준 [2013.07.26 17:21:46]

깔끔하게 잘 보이는데요...감사합니다

by 김정식 [2013.07.27 22:34:16]
내용에는 간략하게 소개했다는데.. 전혀 간략하지 않은데..ㅎㅎ
내가 강좌 스타일로 변경했는데.. 워낙에 긴 내용이라 가독성이 좋아졌는지는 모르겠네..ㅎㅎ
by 강정식 [2013.07.29 09:06:54]
아.. 저 방법이 있었군요.. 
앞으로는 이 방식으로 작성해야겠네요 ^^
고마워요 형~
by 우리집아찌 [2022.10.13 13:44:30]

SPM 쓸일이 생겼는데.. 이제 보네요~

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입