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테이블생성.txt1 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://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 |