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) SQLSET 이용한 Export / Import 테스트
SPM을 이용하여 실행계획을 변경하는 방법을 설명함
11gr2 DB에서 생성한 실행계획(SQLSET)을 Export 하여 19c DB로 Import해서 실행계획을 변경하는 시나리오
*해당 시나리오 및 기능은 별다른 파라미터 설정을 하지 않고 사용 할 수있습니다.
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 max(empno), count(empno) from emp where deptno = 30 ; |
조회 결과
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 | MAX(EMPNO) COUNT(EMPNO) ---------- ------------ 7900 6 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 | 6 | 156 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("DEPTNO"=30) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 605 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 max(empno), count(empno) from emp where deptno = 30 ; |
조회 결과
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 | MAX(EMPNO) COUNT(EMPNO) ---------- ------------ 7900 6 Execution Plan ---------------------------------------------------------- Plan hash value: 2231994318 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 6 | 156 | 1 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_N1 | 6 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DEPTNO"=30) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 637 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에서 Export를 위해 11gr2 DB에서 실행계획을 담을 수 있는 SQLSET 생성
1 2 3 4 5 6 7 8 9 | SQL> begin dbms_sqltune.create_sqlset (sqlset_owner => user, sqlset_name => 'test_sqlset', description => 'a test sql tuning set'); end; / PL/SQL procedure successfully completed. |
11gr2 DB에서 해당 쿼리 SQL_ID 확인
1 2 3 4 5 6 7 8 9 10 | SQL> select sql_id, sql_text from v$sql where sql_text like 'select max(empno)%' ; SQL_ID SQL_TEXT -------------------------- ------------------------------------------------------------------------- 2xhqw339j462x SELECT MAX(EMPNO), COUNT(EMPNO) FROM EMP WHERE DEPTNO = 30 |
11gr2 DB에서 SQLSET 로드
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> declare l_cursor dbms_sqltune.sqlset_cursor; begin open l_cursor for select value(p) from table(dbms_sqltune.select_cursor_cache('sql_id = ''2xhqw339j462x''', 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. |
11gr2 DB에서 SQLSET 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> select * from table(dbms_sqltune.select_sqlset('test_sqlset', 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 )) ; |
11gr2 DB에서 등록된 SQLSET을 담을 수 있는 STAGING 테이블 생성
1 2 3 4 5 6 7 8 9 10 | SQL> begin dbms_sqltune.create_stgtab_sqlset( table_name => 'USER_SQLSET', schema_name => USER, tablespace_name => 'USERS'); end; / PL/SQL procedure successfully completed. |
11gr2 DB에서 SQLSET을 STAGING 테이블에 적재
1 2 3 4 5 6 7 8 9 10 | SQL> begin dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'test_sqlset', sqlset_owner => user, staging_table_name => 'USER_SQLSET', staging_schema_owner => user); end; / 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=user_sqlset.dmp tables=scott.USER_SQLSET Export: Release 11.2.0.3.0 - Production on Thu Sep 19 19:15:44 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=user_sqlset.dmp tables=scott.USER_SQLSET Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 576 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."USER_SQLSET" 49.90 KB 3 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /home/oracle/user_sqlset.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 19:16:09 |
11gr2 DB 서버에서 19c DB 서버로 user_sqlset.dmp 파일 복사
1 2 3 4 5 6 7 | $ scp user_sqlset.dmp oracle@10.10.10.40:/home/oracle/ The authenticity of host '10.10.10.40 (10.10.10.40)' can't be established. RSA key fingerprint is 7e:67:b3:2b:16:16:d0:59:ee:90:a1. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.10.10.40' (RSA) to the list of known hosts. oracle@10.10.10.40's password: user_sqlset.dmp 100% 276KB 276.0KB/s 00:00 |
19c DB 서버에서 파일 확인
1 2 3 4 | $ pwd /home/oracle $ ls user_sqlset.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=user_sqlset.dmp Import: Release 19.0.0.0.0 - Production on Sat Feb 26 13:01:38 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=user_sqlset.dmp Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."USER_SQLSET" 49.90 KB 3 rows Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at Sat Feb 26 13:01:54 2022 elapsed 0 00:00:14 |
19c DB에서 Import 받은 SQLSET 등록
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> conn scott/tiger Connected. SQL> 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; / PL/SQL procedure successfully completed. |
19c DB에서 SQLSET 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> 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 )) ; |
19c DB에 Import 된 SQLSET을 SPM에 등록
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> 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; / 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 | 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_7672fafd8090f33c SQL_PLAN_7cwruzq091wtwc392520a MANUAL-LOAD-FROM-STS YES YES YES select max(empno), count(empno) from emp where deptno = 30 |
ORIGIN 컬럼을 in절로 조회한 이유 :
19c이상은 MANUAL-LOAD-FROM-STS로 표기되고 그 이하버전은 MANUAL-LOAD로 표기되기 때문에
조건에 2가지 다 명시해놓음
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 max(empno), count(empno) from emp where deptno = 30 ; |
조회 결과
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 | MAX(EMPNO) COUNT(EMPNO) ---------- ------------ 7900 6 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 | 6 | 156 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("DEPTNO"=30) Note ----- - dynamic statistics used: dynamic sampling (level=2) - SQL plan baseline "SQL_PLAN_7cwruzq091wtwc392520a" used for this statement Statistics ---------------------------------------------------------- 64 recursive calls 14 db block gets 41 consistent gets 0 physical reads 3512 redo size 637 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를 타지 않고 FULL 스캔을 하는것을 볼 수 있음
그리고 Note 에 "SQL_PLAN_7cwruzq091wtwc392520a" SQL plan baseline 을 사용했다고 표시됨
참조 : https://positivemh.tistory.com/217
https://argolee.tistory.com/15
http://www.gurubee.net/article/59965
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 데이터펌프 expdp 시 세부 trace log남기기 (0) | 2020.02.23 |
|---|---|
| SPM(SQL Plan Management) SQL PLAN BASELINE 이용한 Export / Import 테스트 (실행계획 변경) (0) | 2019.09.19 |
| 개발자를위한 실시간 SQL 모니터링 DBMS_SQL_MONITOR 이용 (0) | 2019.02.18 |
| automatic evolution task 파라미터 설정 (0) | 2019.02.18 |
| 순간적인(1~2분 사이) 과다 실행된 SQL 찾기 (0) | 2019.02.12 |