SPM(SQL PLAN MANAGEMENT) 사용 방법 #1
이번 시간에는 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 버전 확인
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. 테스트 테이블, 인덱스, 통계정보 생성
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 세팅
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 파라미터 확인
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 관련 파라미터 값 변경
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE; ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE;
- - 자동 캡쳐 기능을 테스트하기 위해 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 값을 TRUE로 변경함
SELECT * FROM V$VERSION;
6. SQL을 직접 수행하여 SPB 등록
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 재수행
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 등록
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 확인
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 확인
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과 실행계획 동일할 경우 재 등록되지 않음
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 사용 확인
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 성능 비교
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일 경우
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 설정
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로 할 경우
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이 등록되는지 확인
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 등록
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 등록되는지 테스트
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이 선택되는지 확인
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. 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
'ORACLE > 튜닝' 카테고리의 다른 글
| Oracle Real-Time SQL Monitoring (0) | 2017.06.30 |
|---|---|
| SPM(SQL PLAN MANAGEMENT) 사용 방법 #2 (0) | 2017.06.15 |
| SQL 튜닝 - Query Block Name 사용 (0) | 2017.06.15 |
| SQL 튜닝- 조건절 OR 사용시.. (Predicate , Query Block Name) (0) | 2017.06.15 |
| ROWNUM 상용시 주의사항 (0) | 2017.06.15 |