Oracle 노하우 및 팁

SPM(SQL PLAN MANAGEMENT) 사용 방법 #2 0 6 5,574

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 실행계획 생성

?
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=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 실행계획 생성

?
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
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 생성

?
1
2
3
4
5
6
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


?
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
-- 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 테이블 생성

?
1
2
3
4
5
6
BEGIN
 DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(TABLE_NAME  => 'USER_SQLSET',
          SCHEMA_NAME  => USER,
          TABLESPACE_NAME => 'APPS_TS_EXT_DATA');
END;
/





6. SQLSET을 STAGE 테이블에 담도록 수행

?
1
2
3
4
5
6
7
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 수행

?
1
2
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 등록

?
1
2
3
4
5
6
7
8
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 확인

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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에 등록

?
1
2
3
4
5
6
7
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 확인

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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에서 실행계획 확인

?
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
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






?
1
<br><br>
by 김정식 [2013.07.29 11:02:54]
HTML 파일 zip 으로 압축해서 게시글에 첨부하면 내가 다시 수정할께.. 내용이 깨지네..
이전 글에 내가 작성한 html 파일 첨부되어 있어 작성 할 때 참고해봐..
by 강정식 [2013.07.29 11:05:15]
헉.. 벌써 답글이 ㅎㅎ
이번에도 역시나 포멧팅이 잘 안되네요;; 다시 조정해볼께요 ^^
by 강정식 [2013.07.30 16:18:56]
여기 사이트에서는 파일 올리기가 금지되어 있어서 힘드네요;;
아무래도 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 등록이 지원된다고 하네요

 

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