프린트 하기

OS환경 : Oracle Linux 6.8 (64bit), Oracle Linux 7.4 (64bit)


DB 환경 : Oracle Database 11.2.0.3, Oracle Database 19.4.0.0


테스트명 : SPM(SQL Plan Management) SQL PLAN BASELINE 이용한 Export / Import 테스트

SPM을 이용하여 실행계획을 변경하는 방법을 설명함

11gr2 DB에서 생성한 실행계획(SQL PLAN BASELINE)을 Export 하여 19c DB로 Import해서 실행계획을 변경하는 시나리오

*해당 시나리오 및 기능은 별다른 파라미터 설정을 하지 않고 사용 할 수있습니다.

*이전 게시글과 다르게 DBMS.SQLTUNE 패키지를 사용하지 않고 DBMS.SPM 패키지만 사용하였습니다.

(이전게시글 : https://positivemh.tistory.com/446)


11gr2 DB 접속 후 샘플 테이블 생성

(아래 파일 다운로드 후 복사하여 서버에서 vi로 emp.sql 생성)

EMP,DEPT테이블생성.txt

1
2
3
SQL> conn scott/tiger
Connected.
SQL> @emp.sql



테스트용 인덱스 생성

1
SQL> create index scott.emp_n1 on scott.emp (deptno); 



19c DB 접속 후 샘플 테이블 생성

1
2
3
SQL> conn scott/tiger
Connected.
SQL> @emp.sql



테스트용 인덱스 생성

1
SQL> create index scott.emp_n1 on scott.emp (deptno); 



11gr2 DB 쿼리 조회

1
2
3
4
5
6
7
8
9
10
11
SQL> alter session set optimizer_index_cost_adj=3000;
 
Session altered.
 
SQL> set autot on;
SQL>
select count(empno),
max(empno)
from emp
where deptno = 20
;



조회 결과

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
COUNT(EMPNO) MAX(EMPNO)
------------ ----------
       5       7902
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
 
---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |    26 |    3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |    1 |    26 |           |      |
|*  2 |   TABLE ACCESS FULL| EMP  |    5 |   130 |    3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("DEPTNO"=20)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
      7  recursive calls
      0  db block gets
     17  consistent gets
      0  physical reads
      0  redo size
    606  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

optimizer_index_cost_adj을 3000으로 조정해서 FULL 스캔을 타는것을 볼 수 있음



19c DB 쿼리 조회

1
2
3
4
5
6
7
8
9
10
11
SQL> alter session set optimizer_index_cost_adj=3;
 
Session altered.
 
SQL> set autot on;
SQL>
select count(empno),
max(empno)
from emp
where deptno = 20
;



조회 결과

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
COUNT(EMPNO) MAX(EMPNO)
------------ ----------
       5       7902
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2231994318
 
-----------------------------------------------------------------------------------------------
| 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 BATCHED| EMP    |     5 |    35 |     1    (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN             | EMP_N1 |     5 |       |     1    (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTNO"=20)
 
 
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      2  consistent gets
      0  physical reads
      0  redo size
    638  bytes sent via SQL*Net to client
    421  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

optimizer_index_cost_adj을 3으로 조정해서 INDEX를 타는것을 볼 수 있음



11gr2 DB에서 해당 쿼리 SQL_ID와 PLAN_HASH_VALUE 확인

1
2
3
4
5
6
7
8
9
SQL> 
select sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like 'select count(empno)%'
;
 
SQL_ID         PLAN_HASH_VALUE SQL_TEXT
-------------  --------------  -----------
cxybbzmz26g5x  2083865914      select count(empno), max(empno) from emp where deptno = 20



11gr2 DB에서 SQL PLAN 베이스라인 수동 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>
set serveroutput on
declare
  l_plans_loaded PLS_INTEGER;
begin
  l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
  sql_id => 'cxybbzmz26g5x');
  dbms_output.put_line('Plans Loaded: ' || l_plans_loaded);
end;
/
Plans Loaded: 1
 
PL/SQL procedure successfully completed.



11gr2 DB에서 등록한 SQL PLAN 베이스라인 확인

1
2
3
4
5
6
7
8
9
10
SQL> 
select sql_handle, 
plan_name, 
to_char(created, 'YYYY/MM/DD HH24:MI:SS') created
from dba_sql_plan_baselines
where sql_text like 'select count(empno)%';
 
SQL_HANDLE                 PLAN_NAME                  CREATED
-------------------- ------------------------------  --------------------
SQL_36d621c2f29efad7  SQL_PLAN_3dpj1sbt9xyqrc392520a 2019/09/19 21:33:45



11gr2 DB에서 등록된 SQL PLAN 베이스라인을 담을 수 있는 STAGING 테이블 생성

1
2
3
4
5
6
7
8
9
10
SQL>
begin
 dbms_spm.create_stgtab_baseline(
         table_name => 'SPM_STAGING',
         table_owner => user,
         tablespace_name => 'USERS');
end;
/
 
PL/SQL procedure successfully completed.



11gr2 DB에서 SQL PLAN 베이스라인을 STAGING 테이블에 적재

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> 
set serveroutput on
declare
  l_plans_packed PLS_INTEGER;
begin
  l_plans_packed := dbms_spm.pack_stgtab_baseline(
  table_name => 'SPM_STAGING',
  table_owner => user);
  dbms_output.put_line('Plans Packed: ' || l_plans_packed);
end;
/
Plans Packed: 1
 
PL/SQL procedure successfully completed.



11gr2 DB에서 Datapump용 directory 생성 및 권한부여

1
2
3
4
5
6
7
8
9
10
SQL> conn / as sysdba
Connected.
 
SQL> create directory spm_move as '/home/oracle/';
 
Directory created.
 
SQL> grant read, write on directory spm_move to scott;
 
Grant succeeded.



11gr2 DB에서 Datapump로 Export수행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ expdp scott/tiger directory=spm_move dumpfile=spm_staging.dmp tables=scott.SPM_STAGING
 
Export: Release 11.2.0.3.0 - Production on Thu Sep 19 21:40:17 2019
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=spm_move dumpfile=spm_staging.dmp tables=scott.SPM_STAGING 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."SPM_STAGING"                       24.34 KB       2 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/spm_staging.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 21:40:49



11gr2 DB 서버에서 19c DB 서버로 spm_staging.dmp 파일 복사

1
2
3
$ scp spm_staging.dmp oracle@10.10.10.40:/home/oracle/
oracle@10.10.10.40's password: 
spm_staging.dmp                         100%  160KB 160.0KB/s   00:00



19c DB 서버에서 파일 확인

1
2
3
4
$ pwd
/home/oracle
$ ls
spm_staging.dmp



19c DB에서 Datapump용 directory 생성 및 권한부여

1
2
3
4
5
6
7
8
9
10
SQL> conn / as sysdba
Connected.
 
SQL> create directory spm_move as '/home/oracle/';
 
Directory created.
 
SQL> grant read, write on directory spm_move to scott;
 
Grant succeeded.



19c DB에서 Datapump로 Import수행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ impdp scott/tiger directory=spm_move dumpfile=spm_staging.dmp
 
Import: Release 19.0.0.0.0 - Production on Sat Feb 26 14:51:05 2022
Version 19.4.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=spm_move dumpfile=spm_staging.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."SPM_STAGING"                       24.34 KB       2 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Sat Feb 26 14:51:10 2022 elapsed 0 00:00:04



19c DB에 Datapump로 Import 받은 SQL PLAN 베이스라인을 Unpack 진행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL>
set serveroutput on
declare
  l_plans_unpacked PLS_INTEGER;
begin
  l_plans_unpacked := dbms_spm.unpack_stgtab_baseline(
  table_name => 'SPM_STAGING',
  table_owner => USER,
  creator => 'SCOTT');
  dbms_output.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
Plans Unpacked: 1
 
PL/SQL procedure successfully completed.



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
SQL>
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','MANUAL-LOAD-FROM-STS')
;
 
SQL_HANDLE               PLAN_NAME                                               ORIGIN                  ENA ACC
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------------------------- --- ---
FIX       OCO SQL_TEXT
--- ---------- --------------------------------------------------------------------------------
SQL_36d621c2f29efad7
SQL_PLAN_3dpj1sbt9xyqrc392520a
MANUAL-LOAD              YES YES NO       3
select count(empno),
max(empno)
from emp
where deptno = 20



19c DB 쿼리 조회

1
2
3
4
5
6
7
8
9
10
11
SQL> alter session set optimizer_index_cost_adj=3;
 
Session altered.
 
SQL> set autot on;
SQL>
select count(empno),
max(empno)
from emp
where deptno = 20
;



조회 결과

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
COUNT(EMPNO) MAX(EMPNO)
------------ ----------
       5       7902
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
 
---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |    7 |    3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |    1 |    7 |           |      |
|*  2 |   TABLE ACCESS FULL| EMP  |    5 |    35 |    3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("DEPTNO"=20)
 
Note
-----
   - SQL plan baseline "SQL_PLAN_3dpj1sbt9xyqrc392520a" used for this statement
 
 
Statistics
----------------------------------------------------------
    125  recursive calls
     68  db block gets
    132  consistent gets
      1  physical reads
      18212  redo size
    638  bytes sent via SQL*Net to client
    421  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     10  sorts (memory)
      0  sorts (disk)
      1  rows processed

optimizer_index_cost_adj을 3으로 조정했음에도 불구하고 INDEX를 타지 않고 FULL 스캔을 하는것을 볼 수 있음

그리고 Note 에 "SQL_PLAN_3dpj1sbt9xyqrc392520a" SQL plan baseline 을 사용했다고 표시됨




참조 : https://positivemh.tistory.com/446

https://positivemh.tistory.com/217

https://argolee.tistory.com/15

http://blog.goodus.com/83

http://www.gurubee.net/article/59965



저작자표시

'ORACLE > Performance Tuning ' 카테고리의 다른 글

sql 실행계획 확인 및 cpu 등 성능확인 스크립트  (3) 2020.03.26
오라클 데이터펌프 expdp 시 세부 trace log남기기  (0) 2020.02.23
SPM(SQL Plan Management) SQLSET 이용한 Export / Import 테스트 (실행계획 변경)  (0) 2019.09.19
개발자를위한 실시간 SQL 모니터링 DBMS_SQL_MONITOR 이용  (0) 2019.02.18
automatic evolution task 파라미터 설정  (0) 2019.02.18

티스토리툴바