SPM(SQL PLAN MANAGEMENT) 사용 방법 #2


지난주에 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


'ORACLE > 튜닝' 카테고리의 다른 글

[Hint] SWAP_JOIN_INPUTS  (0) 2017.07.19
Oracle Real-Time SQL Monitoring  (0) 2017.06.30
SPM(SQL PLAN MANAGEMENT) 사용 방법 #1  (0) 2017.06.15
SQL 튜닝 - Query Block Name 사용  (0) 2017.06.15
SQL 튜닝- 조건절 OR 사용시.. (Predicate , Query Block Name)  (0) 2017.06.15

+ Recent posts

티스토리툴바