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) ;
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) ;
BEGIN DBMS_SQLTUNE.CREATE_SQLSET (SQLSET_OWNER => USER, SQLSET_NAME => 'TEST_SQLSET', DESCRIPTION => 'A TEST SQL TUNING SET'); END; /
-- 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 )) ;
BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(TABLE_NAME => 'USER_SQLSET', SCHEMA_NAME => USER, TABLESPACE_NAME => 'APPS_TS_EXT_DATA'); END; /
BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLSET(SQLSET_NAME => 'TEST_SQLSET', SQLSET_OWNER => USER, STAGING_TABLE_NAME => 'USER_SQLSET', STAGING_SCHEMA_OWNER => USER); END; /
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
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; /
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 )) ;
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; /
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 ;
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 ;