Recent Comments
Toobit Exchange: https://toobitexchange...
야튜브: What's up!
What a nice..
한국야동: Hi,
What a nice post!
..
야유브: Hi,
What an good blog!..
Stanmorenhq: from lat. manus - "hand..
Pouringkhu: Century to a kind of de..이번 칼럼은 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)
*. Plan Hit가 저장되고 Plan History가 관리된다. *. 저장된 Plan Hit대로 수행되므로 외부적인 환경변화가 있더라도 Plan은 변경되지 않는다. *. SQL Plan의 이력 관리가 됨. *. SQL Baseline에 검증된 Plan이 여러 개 존재할 수 있고 실행계획은 그 중에서 선택되어진다. *. Plan을 검증하는 기능이 있어 기존의 Plan대비 어느 정도의 Cost와 성능 향상이 기대되는지를 분석할 수 있다. 따라서 분석 결과에 따라 특정 Plan을 버릴 수도 있고 Plan으로 채택되지 않게 설정을 변경할 수도 있다. |
*. High-Loaded SQL의 Plan을 Tuning 한 결과로 생성되므로 사후 조치적인 방식이다.(Reactive Mechanism).
*. SQL이 잘 수행될 수 있도록 일반적인 통계정보 이외의 부가 정보를 Dictionary에 저장한다. Plan Hit가 저장되지 않음. *. SQL Profile이 생성되어 있더라도 외부적인 환경변화로 인해 SQL Plan이 변경될 수 있다. *. SQL Plan의 이력관리가 안 됨. *. 한 Category의 특정 SQL에 대해 오직 하나의 Plan만 저장할 수 있기 때문에 다른 Plan을 적응시키기 위해서는 Category를 변경하여 저장하거나 새로운 Plan을 Stored Outline으로 저장해야 한다 |
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 |
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 EMP_U1 ON EMP (EMPNO); CREATE INDEX EMP_N1 ON EMP (DEPTNO); CREATE UNIQUE INDEX DEPT_U1 ON DEPT (DEPTNO); CREATE INDEX DEPT_N1 ON DEPT (LOC); BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE); END; / BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE); END; / |
테스트로 많이 사용되는 EMP, DEPT 테이블을 생성하고, 통계정보를 만들었습니다.
3. Format 세팅
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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'; |
SQL-PLUS에서 테스트를 진행하기 때문에 출력 포멧을 지정합니다.
4. 테스트 DB에서 SQL_PLAN_BASELINES 파라미터 확인
|
1 2 3 4 |
NAME TYPE VALUE ------------------------------------ ---------------------- ------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE |
SPM에서 사용되는 2개 hidden parameters는 위와 같이 2개이며, 그 기능은 아래와 같습니다.
- optimizer_capture_sql_plan_baselines : SPB를 통해 실행계획을 자동으로 캡쳐하도록 활성화하는 파라미터
- optimizer_use_sql_plan_baselines : SPB를 사용토록 활성화하는 파라미터
5. 세션 레벨에서 SPB 관련 파라미터 값 변경
|
1 2 |
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE; ALTER SESSION SET OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE; |
여기서 자동 캡쳐 기능을 테스트하기 위해 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 값을 TRUE로 변경하겠습니다.
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 |
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 |
DEPT 테이블에 DEPTNO 컬럼으로 구성된 인덱스가 있어서 INDEX RANGE SCAN 실행계획이 나오겠지만, FULL SCAN을 유도하기 위해 세션레벨로 OPTIMIZER_INDEX_COST_ADJ 파라미터값을 변경해 보겠습니다. 이 파라미터는 값이 높을수록 FULL SCAN을 선호하고, 낮을수록 인덱스 사용을 선호합니다.
참고로 SPM은 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 |
한번 더 수행하고 DBA_SQL_PLAN_BASELINES 뷰에서 SPM을 확인한 결과 해당 SQL이 등록된 걸 확인할 수 있으며, 그 의미는 아래와 같습니다.
- 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 등록
TABLE FULL SCAN 실행계획이 등록되어 있으므로, 이제는 INDEX RANGE SCAN 실행계획을 등록해 보겠습니다. 동일한 방법으로 OPTIMIZER_INDEX_COST_ADJ 파라미터 값을 300에서 1로 다시 설정해서 인덱스를 사용하도록 유도합니다. 참고로 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, OPTIMIZER_USE_SQL_PLAN_BASELINES 2개 파라미터 중 하나라도 TURE일 경우 새로운 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 |
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) ; |
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 36 |
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_TEXT로 확인한 결과, SQL_5009a0e70c6d5325 SQL_HANDLE 값이 동일한것이 2개 있고, SQL_PLAN_502d0ww66unt59995a0e9 값이 서로 다른 PLAN_NAME이 등록되었습니다.
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 |
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) |
XPLAN에서도 확인해보면, 더욱 확실히 알 수 있는데, 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 69 |
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 ; |
이제 SPM이 잘 작동하는지 확인해 보기 위해, 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 85 86 |
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 |
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 58 59 |
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 |
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 |
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 |
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 |
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 ; |
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 |
이번에는 AUTO-CAPTURE가 아닌 MANUAL로 SPB를 등록해 보겠습니다. MANUAL 등록 시, 해당 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 |
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 |
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 68 |
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 |
만약 ACCEPT 값이 NO이고 FIXED만 YES일 경우 어떤 Plan이 선택되는지 확인한 결과, FULL SCAN을 하는 SQL_PLAN_502d0ww66unt5c392520a 사용하는 것을 알 수 있으며 그 이유는, SQL_PLAN_502d0ww66unt59995a0e9 PLAN_NAME의 FIXED가 YES여도 ACCEPT값이 NO이기 때문에 선택이 되지 않는 걸 알 수 있습니다.
이상으로 SPM에 대한 간략한 테스트를 마쳤습니다. 다음 시간에는 SPM을 Export/Import 하는 기능과 Shared Pool에서 SPB를 등록하는 상세 방법에 대해 소개하도록 하겠습니다. 내용을 보고 궁금한 점 있으면 리플을 달아주기 바라며, 아래에는 위에서 소개된 기능에 대해 요약한 내용입니다.
요약
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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; / |
Reference List
- Oracle Document
- http://wiki.gurubee.net/display/CORE/3.+SQL+PLAN+MANAGEMENT
About the Author
View all posts by 강정식● (주)더비소프트 SI&ITO사업부 ERP사업팀 팀장
● 경력 10년(현 LG전자 GERP DBA/Tuning 업무)
● 세미나 : 오라클 SQL 튜닝 방법의 이해, DBMS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝, 개발자를 위한 어플리케이션튜닝 세미나
● 경력 10년(현 LG전자 GERP DBA/Tuning 업무)
● 세미나 : 오라클 SQL 튜닝 방법의 이해, DBMS_XPLAN과 10046 Trace를 이용한 SQL문 튜닝, 개발자를 위한 어플리케이션튜닝 세미나
팀장님 좋은 정보 감사합니다ㅎㅎ