프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

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테이블생성.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 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://blog.goodus.com/83

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

티스토리툴바