Oracle 노하우 및 팁
SPM(SQL PLAN MANAGEMENT) 사용 방법 #2
5
1,988
by 강정식 [강정식] SPM SPB SQL PLAN MANAGEMENT SQL PLAN BASELINE [2013.07.29 11:00:16]
지난주에 SPM에 대해 소개 및 사용방법에 대해 간략히 살펴보았으며, 이번시간에는 SPM Export, Import 하는 방법에 대해 알아보고자 한다.
- SPM 사용 시, 해당 DB에서 원하는 Plan을 등록할 수도 있지만 타 DB에서 Plan을 얻은 후 Target DB에 Import 할 수도 있다.
- 이런 Export / Import 하는 방법을 알아보도록 한다.
※ 테스트를 위한 Object 및 SQL*PLUS Format은 지난 시간에 사용했던것을 재 사용함.
1. Export DB 실행계획 생성
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)
;
- 현재 테스트 방식은 Import DB의 실행계획을 Export DB의 실행계획으로 대체하려고 함.
- 이를 위해, Export DB에서 FULL SCAN 실행계획 생성함.
2. Import DB 실행계획 생성
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 | 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)
;
- Import DB에서 INDEX RANGE SCAN 실행계획 생성함
3. Export DB에서 SQL_SET 생성
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (SQLSET_OWNER => USER,
SQLSET_NAME => 'TEST_SQLSET',
DESCRIPTION => 'A TEST SQL TUNING SET');
END;
/
- Export 실행계획을 담을 수 있는 SQLSET 생성
4. Cursor Cache로부터 해당 SQL을 SQL_SET Load
-- 4-1) SQL_ID 확인
SELECT SQL_ID,
SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT MAX(EMPNO)%'
;
SQL_ID SQL_TEXT
-------------------------- -------------------------------------------------------------------------
dutyd69g2dxr0 SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 97
;
-- 4-2) SQLSET_LOAD
DECLARE
L_CURSOR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN L_CURSOR FOR
SELECT VALUE(P)
FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('SQL_ID = ''dutyd69g2dxr0''', -- BASIC_FILTER
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'SQL_PLAN')) P;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_OWNER => USER,
SQLSET_NAME => 'TEST_SQLSET',
POPULATE_CURSOR => L_CURSOR);
END;
/
PL/SQL procedure successfully completed.
;
-- 4-3) SQLSET 확인
SELECT *
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('TEST_SQLSET', -- SQLSET_NAME
NULL, -- BASIC_FILTER
NULL, -- OBJECT_FILTER
NULL, -- RANKING_MEASURE1
NULL, -- RANKING_MEASURE2
NULL, -- RANKING_MEASURE3
NULL, -- RESULT_PERCENTAGE
NULL, -- RESULT_LIMIT
'ALL', -- ATTRIBUTE_LIST
NULL, -- PLAN_FILTER
USER -- SQLSET_OWNER
))
;
- Cursor Cache로부터 SQL_ID 확인
- SQL_ID를 통해 SQLSET Load함
- 이후 SQLSET 확인
5. Export DB에서 등록된 SQLSET을 담을 수 있는 STAGE 테이블 생성
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(TABLE_NAME => 'USER_SQLSET',
SCHEMA_NAME => USER,
TABLESPACE_NAME => 'APPS_TS_EXT_DATA');
END;
/
6. SQLSET을 STAGE 테이블에 담도록 수행
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(SQLSET_NAME => 'TEST_SQLSET',
SQLSET_OWNER => USER,
STAGING_TABLE_NAME => 'USER_SQLSET',
STAGING_SCHEMA_OWNER => USER);
END;
/
7. 서버에서 Export / Import 수행
Export DB > exp user/password file=user_sqlset.dmp tables=user.user_sqlset Import DB > imp user/password file=user_sqlset.dmp full=y ignore=y
8. Import DB에서 Export 받은 SQLSET 등록
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(SQLSET_NAME => 'TEST_SQLSET',
SQLSET_OWNER => USER,
REPLACE => TRUE,
STAGING_TABLE_NAME => 'USER_SQLSET',
STAGING_SCHEMA_OWNER => USER);
END;
/
9. Import DB에서 SQLSET 확인
SELECT *
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('TEST_SQLSET', -- SQLSET_NAME
NULL, -- BASIC_FILTER
NULL, -- OBJECT_FILTER
NULL, -- RANKING_MEASURE1
NULL, -- RANKING_MEASURE2
NULL, -- RANKING_MEASURE3
NULL, -- RESULT_PERCENTAGE
NULL, -- RESULT_LIMIT
'ALL', -- ATTRIBUTE_LIST
NULL, -- PLAN_FILTER
USER -- SQLSET_OWNER
))
;
10. Load된 SQLSET을 SPM에 등록
DECLARE
MY_PLANS PLS_INTEGER;
BEGIN
MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_OWNER => USER, SQLSET_NAME => 'TEST_SQLSET', FIXED => 'YES');
DBMS_OUTPUT.PUT_LINE('PLANS LOADED: ' || MY_PLANS);
END;
/
11. SPM 확인
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 ORIGIN IN ('MANUAL-LOAD')
;
SQL_HANDLE PLAN_NAME ORIGIN ENABLE ACCEPT FIXED OCO SQL_TEXT
-------------------- ------------------------------ ------------ ------ ------ ------ ----- -----------------------
SQL_5009a0e70c6d5325 SQL_PLAN_502d0ww66unt5c392520a MANUAL-LOAD YES YES YES SELECT MAX(EMPNO),
COUNT(EMPNO)
FROM EMP
WHERE DEPTNO = 97
;
12. Import DB에서 실행계획 확인
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
;
지금까지, SPM Export/Import를 통해 원하는 실행계획을 가져올 수 있는 방법에 대해 알아보았다.
다음 시간에는 Cursor Cache를 이용하여 SPM에 등록하는 방법에 대해 좀 더 알아보고자 한다.
참고
- - Oracle Document
- - http://wiki.gurubee.net/display/CORE/3.+SQL+PLAN+MANAGEMENT
Blog
- - http://blog.naver.com/xsoft
by 김정식
[2013.07.29 11:02:54]
HTML 파일 zip 으로 압축해서 게시글에 첨부하면 내가 다시 수정할께.. 내용이 깨지네..
이전 글에 내가 작성한 html 파일 첨부되어 있어 작성 할 때 참고해봐..
이전 글에 내가 작성한 html 파일 첨부되어 있어 작성 할 때 참고해봐..
by 강정식
[2013.07.29 11:05:15]
헉.. 벌써 답글이 ㅎㅎ
이번에도 역시나 포멧팅이 잘 안되네요;; 다시 조정해볼께요 ^^
이번에도 역시나 포멧팅이 잘 안되네요;; 다시 조정해볼께요 ^^
by 강정식
[2013.07.30 16:18:56]
여기 사이트에서는 파일 올리기가 금지되어 있어서 힘드네요;;
아무래도 ZIP 파일은 올리지 못할거 같아요;;
우선 링크만 걸어놓을께요..
아무래도 ZIP 파일은 올리지 못할거 같아요;;
우선 링크만 걸어놓을께요..
by 아발란체
[2013.07.29 11:54:14]
와... 새로운 세계다... >ㅅ<)/
좋은 정보 감사합니다 ~ !
by 초보DBA
[2014.01.16 13:54:17]
예제를 따라하는 도중에
3. Export DB에서 SQL_SET 생성
이부분에서 에러가 발생합니다.
ORA-13718: 이 기능을 사용하려면 튜닝 패키지 라이센스 또는 실제 응용 프로그램
테스트 옵션이 필요합니다.
ORA-06512: "SYS.DBMS_SYS_ERROR"~, 79행
ORA-06512: "SYS.DBMS_SQLTUNE", 1190행
ORA-06512: "SYS.DBMS_SQLTUNE", 4350행
ORA-06512: 2행
왜 뜨는건지 모르겠습니다. ㅠㅠ 답변좀 주세요
by 타락천사
[2019.11.21 11:20:42]
Enterprise Edition 만 가능해서 그래요..
18c Standard Ediditon 부터 하나의 SQL 에 대해서 하나의 Plan 등록이 지원된다고 하네요