본문 바로가기
Oracle/튜닝

[DBMS_SQLTUNE] Profile로 SQL변경없이 실행계획 변경해보기

by 취미툰 2023. 1. 20.
반응형

출처 : http://www.gurubee.net/article/58105

 

SQL_PROFILE 사용 방법

10g부터 소개된 SQL_PROFILE의 기능에 대한 소개를 하고자 한다. SQL_PROFILE이란, 특정 SQL_ID의 실행계획이 비효율적으로 생성될 경우, SQL 구문 변..

www.gurubee.net

 

 

해당 내용은 쿼리 수정없이 실행계획을 변경할 수 있습니다.

하지만 방법 중 SQLTUNE_CATEGORY 라는 system 파라미터를 변경해야 하므로 사용 시 주의를 요합니다.

 

결론은,

SQL A(원래 SQL)과 SQL B(원하는 실행계획이 있는 SQL)을 준비하고, SQL B의 실행계획중 OUTLINE을 이용하여 SQL A에 적용하여 실행계획을 변경합니다.

 

SQLTUNE_CATEGORY 에대한 설명

SESSION 레벨과 SYSTEM 레벨 두개로 설정할 수 있으며 기본적으로는 DEFAULT로 설정되어 있습니다.

사용하지 않을 시에는 FALSE로 설정해서 사용할 수 있습니다.

명령어

--세션 레벨
ALTER SESSION SET SQLTUNE_CATEGORY = EMP_PROFILE ;

--시스템 레벨
ALTER SYSTEM SET SQLTUNE_CATEGORY = EMP_PROFILE ;
Parameter type String
Syntax SQLTUNE_CATEGORY = category_name
Default value DEFAULT
Modifiable ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB Yes
Basic No

출처 : https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/SQLTUNE_CATEGORY.html

 

테스트

12.2.0.1에서 수행하였습니다.

 

1.원본 SQL 확인

SQL A(원본 SQL)

select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname from emp e, dept d
where e.deptno = d.deptno 
and e.empno in (select min(e2.empno) from emp2 e2 group by e2.empno ) ;

 

실행계획

3번라인의 EMP가 TABLE FULL SCAN인 것을 확인할 수 있습니다.

이것을 INDEX FULL SCAN으로 변경하겠습니다.

SQL_ID  1juqqfdt7avud, child number 0
-------------------------------------
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dn
ame from emp e, dept d where e.deptno = d.deptno  and e.empno in 
(select min(e2.empno) from emp2 e2 group by e2.empno )
 
Plan hash value: 3703573721
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |        |       |    10 (100)|          |       |       |          |
|*  1 |  HASH JOIN            |          |      1 |    56 |    10  (10)| 00:00:01 |  1162K|  1162K|  905K (0)|
|*  2 |   HASH JOIN SEMI      |          |      1 |    43 |     7  (15)| 00:00:01 |  1162K|  1162K| 1705K (0)|
|   3 |    TABLE ACCESS FULL  | EMP      |     16 |   480 |     3   (0)| 00:00:01 |       |       |          |
|   4 |    VIEW               | VW_NSO_1 |     14 |   182 |     4  (25)| 00:00:01 |       |       |          |
|   5 |     HASH GROUP BY     |          |     14 |    56 |     4  (25)| 00:00:01 |  1186K|  1186K| 1234K (0)|
|   6 |      TABLE ACCESS FULL| EMP2     |     16 |    64 |     3   (0)| 00:00:01 |       |       |          |
|   7 |   TABLE ACCESS FULL   | DEPT     |      4 |    52 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / E@SEL$1
   4 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
   5 - SEL$683B0107
   6 - SEL$683B0107 / E2@SEL$2
   7 - SEL$5DA710D3 / D@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$683B0107")
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2" UNNEST_SEMIJ_VIEW)
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$5DA710D3" "E"@"SEL$1")
      NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      FULL(@"SEL$5DA710D3" "D"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "VW_NSO_1"@"SEL$5DA710D3" "D"@"SEL$1")
      USE_HASH(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      USE_HASH(@"SEL$5DA710D3" "D"@"SEL$1")
      FULL(@"SEL$683B0107" "E2"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$683B0107")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - access("E"."EMPNO"="MIN(E2.EMPNO)")

 

2.SQL B(원하는 실행계획이 있는 SQL 확인)

 

인덱스 생성

create index emp_idx1 on emp(empno,deptno);

 

SQL B

select /*+ INDEX(e EMP_IDX1) */e.empno,e.ename,e.job,e.mgr,e.hiredate,e.
sal,e.comm,e.deptno,d.dname from emp e, dept d where e.deptno = 
d.deptno  and e.empno in (select min(e2.empno) from emp2 e2 group by 
e2.empno )

 

실행계획

3,4 ID를 확인해보면 INDEX FULL SCAN을 타는 것을 확인할 수 있습니다.

실행계획 밑의 OUTLINE DATA를 확인하면 INDEX FULL SCAN을 위한 OUTLINE이 나와있습니다.

      INDEX(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "E"@"SEL$1")

이 부분입니다. 이부분으로 SQL PROFILE을 생성합니다.

SQL_ID  chy8njq10wrq5, child number 0
-------------------------------------
select /*+ INDEX(e EMP_IDX1) */e.empno,e.ename,e.job,e.mgr,e.hiredate,e.
sal,e.comm,e.deptno,d.dname from emp e, dept d where e.deptno = 
d.deptno  and e.empno in (select min(e2.empno) from emp2 e2 group by 
e2.empno )
 
Plan hash value: 3955590758
 
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |        |       |    13 (100)|          |       |       |          |
|*  1 |  HASH JOIN                            |          |      1 |    56 |    13   (8)| 00:00:01 |  1162K|  1162K|  903K (0)|
|*  2 |   HASH JOIN SEMI                      |          |      1 |    43 |    10  (10)| 00:00:01 |  1162K|  1162K| 1706K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     16 |   480 |     6   (0)| 00:00:01 |       |       |          |
|   4 |     INDEX FULL SCAN                   | EMP_IDX1 |     16 |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    VIEW                               | VW_NSO_1 |     14 |   182 |     4  (25)| 00:00:01 |       |       |          |
|   6 |     HASH GROUP BY                     |          |     14 |    56 |     4  (25)| 00:00:01 |  1186K|  1186K| 1234K (0)|
|   7 |      TABLE ACCESS FULL                | EMP2     |     16 |    64 |     3   (0)| 00:00:01 |       |       |          |
|   8 |   TABLE ACCESS FULL                   | DEPT     |      4 |    52 |     3   (0)| 00:00:01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / E@SEL$1
   4 - SEL$5DA710D3 / E@SEL$1
   5 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
   6 - SEL$683B0107
   7 - SEL$683B0107 / E2@SEL$2
   8 - SEL$5DA710D3 / D@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$683B0107")
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2" UNNEST_SEMIJ_VIEW)
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "E"@"SEL$1")
      NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      FULL(@"SEL$5DA710D3" "D"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "VW_NSO_1"@"SEL$5DA710D3" "D"@"SEL$1")
      USE_HASH(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      USE_HASH(@"SEL$5DA710D3" "D"@"SEL$1")
      FULL(@"SEL$683B0107" "E2"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$683B0107")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - access("E"."EMPNO"="MIN(E2.EMPNO)")

 

3.SQL profile 생성

생성 시 가장 중요한것은 CATEGORY입니다. DEFAULT로 사용해도 무방하지만 CATEGORY가 여러개 있다면, 다른 카테고리마다 쿼리 수정없이 실행계획을 변경해야 하는 SQL들이 많다면 관리의 어려움이 있을 것입니다. 잘 판단하여 수행합니다. 테스트에서는 DEFAULT로 수행하였습니다.

 

SQL PROFILE

DECLARE
    -- SQL_FULLTEXT 데이터타입이 CLOB이므로, 변수도 CLOB으로 선언
    V_SQL_TEXT CLOB;
BEGIN
    -- V$SQL의 SQL_FULLTEXT를 변수에 저장
    SELECT SQL_FULLTEXT
    INTO   V_SQL_TEXT
    FROM   V$SQL
    WHERE  SQL_ID = '1juqqfdt7avud'
    and rownum=1;
    -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
        NAME        => 'EMP_PROFILE', -- 이름 구분용으로 사용
        DESCRIPTION => 'EMP_PROFILE', --PROFILE 설명
        CATEGORY    => 'DEFAULT', ---**가장 중요**
        SQL_TEXT    => V_SQL_TEXT,
        PROFILE     => SQLPROF_ATTR('INDEX(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))',                    --변경 PROFILE의 OUTLINE을 한줄씩 작성                               
                                    'BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "E"@"SEL$1")'                                           
                                    ),

        REPLACE     => TRUE

    );
END;
/

확인은 dba_sql_profiles 딕셔너리뷰를 통해서 확인 가능합니다

select name,category,sql_text,status,force_matching from dba_sql_profiles
where name='EMP_PROFILE'

NAME	CATEGORY	SQL_TEXT	STATUS	FORCE_MATCHING
-------------------------------------------
EMP_PROFILE	DEFAULT	select ...	ENABLED	NO

 

4.파라미터 변경

 

파라미터 확인

select name,value,default_value from v$parameter
where name like '%sqltune%';

NAME	VALUE	DEFAULT_VALUE
--------------------------------
sqltune_category	DEFAULT	DEFAULT

 

이미 default이지만 테스트이므로 한번더 적용해줍니다.

ALTER SYSTEM SET SQLTUNE_CATEGORY = DEFAULT ;

 

 

 

5.다시 SQL A 수행

SQL A

select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname from emp e, dept d
where e.deptno = d.deptno 
and e.empno in (select min(e2.empno) from emp2 e2 group by e2.empno ) ;

 

실행계획

SQL ID는 동일하지만

3,4ID의 실행계획이 변경된 것을 확인할 수 있습니다.

SQL_ID  1juqqfdt7avud, child number 1
-------------------------------------
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dn
ame from emp e, dept d where e.deptno = d.deptno  and e.empno in 
(select min(e2.empno) from emp2 e2 group by e2.empno )
 
Plan hash value: 3955590758
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |       |       |    13 (100)|          |
|*  1 |  HASH JOIN                            |          |     1 |    56 |    13   (8)| 00:00:01 |
|*  2 |   HASH JOIN SEMI                      |          |     1 |    43 |    10  (10)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |    16 |   480 |     6   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN                   | EMP_IDX1 |    16 |       |     1   (0)| 00:00:01 |
|   5 |    VIEW                               | VW_NSO_1 |    14 |   182 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY                     |          |    14 |    56 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL                | EMP2     |    16 |    64 |     3   (0)| 00:00:01 |
|   8 |   TABLE ACCESS FULL                   | DEPT     |     4 |    52 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$683B0107")
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2" UNNEST_SEMIJ_VIEW)
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "E"@"SEL$1")
      NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      FULL(@"SEL$5DA710D3" "D"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "VW_NSO_1"@"SEL$5DA710D3" "D"@"SEL$1")
      USE_HASH(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      USE_HASH(@"SEL$5DA710D3" "D"@"SEL$1")
      FULL(@"SEL$683B0107" "E2"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$683B0107")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - access("E"."EMPNO"="MIN(E2.EMPNO)")
 
Note
-----
   - SQL profile EMP_PROFILE used for this statement

 

6.SQL profile 삭제

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME =>'EMP_PROFILE');

 

끝.

 

++) 추가 테스트

FORCE_MATCH 옵션사용해보기

literal 값으로 들어오는 dynamic sql일 경우에도 cursor_sharing기능처럼 바인드 변수처럼 자동 변환 해줘서 실행계획을 공유해서 사용할 수 있습니다. 즉, 바인드변수가 아니라 literal 값을 넣어서 SQL을 수행하면 SQL ID가 변경됩니다. 그럼 특정 SQLID로만 SQL profile을 만들어서 사용하니까 literal값이 바뀌면 SQL profile은 사용이 불가능하게 되죠.

이때 바인드 변수처럼 자동변환해서 실행계획을 공유할 수 있게 하는 기능입니다. 사용은 DBMS_SQLTUNE.IMPORT_SQL_PROFILE 사용 시 옵션을 추가해서 사용합니다.

 

 

1.SQL과 실행계획 확인

SQL A

D.DEPTNO = 10 , 20 ,30 ,40으로 변경해가면서 테스트를 진행합니다.

SELECT *
FROM   DEPT D , EMP E
WHERE   D.DEPTNO = E.DEPTNO  
AND     D.DEPTNO = 10
;

실행계획

현재 SORT MERGE 조인으로 풀리는 것을 NL조인으로 풀리게 변경할 것입니다.

SQL_ID  373d62z9hvw4s, child number 0
-------------------------------------
SELECT * FROM   DEPT D , EMP E WHERE   D.DEPTNO = E.DEPTNO   AND     
D.DEPTNO = 10
 
Plan hash value: 37243564
 
----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |        |       |       |          |
|   1 |  MERGE JOIN CARTESIAN                 |          |      4 |       |       |          |
|*  2 |   TABLE ACCESS FULL                   | DEPT     |      1 |       |       |          |
|   3 |   BUFFER SORT                         |          |      4 |  2048 |  2048 | 2048  (0)|
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |      4 |       |       |          |
|*  5 |     INDEX SKIP SCAN                   | EMP_IDX1 |      4 |       |       |          |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DEPTNO"=10)
   5 - access("E"."DEPTNO"=10)
       filter("E"."DEPTNO"=10)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX_SS(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

 

2.변경할 실행계획이 있는 SQL과 실행계획 확인

SQL B

SELECT /*+ LEADING(D) USE_NL(E) */*
FROM   DEPT D , EMP E
WHERE   D.DEPTNO = E.DEPTNO  
AND     D.DEPTNO = 10
;

실행계획

OUTLINE을 확인하여 NL조인으로풀리게 하는 OUTLINE을 확인합니다.

      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")

 

SQL_ID  7xvdwc89ty1a7, child number 0
-------------------------------------
SELECT /*+ LEADING(D) USE_NL(E) */* FROM   DEPT D , EMP E WHERE   
D.DEPTNO = E.DEPTNO   AND     D.DEPTNO = 10
 
Plan hash value: 2776721703
 
------------------------------------------------------------------
| Id  | Operation                            | Name     | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |        |
|   1 |  NESTED LOOPS                        |          |      4 |
|*  2 |   TABLE ACCESS FULL                  | DEPT     |      1 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |      4 |
|*  4 |    INDEX SKIP SCAN                   | EMP_IDX1 |      4 |
------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DEPTNO"=10)
   4 - access("E"."DEPTNO"=10)
       filter("E"."DEPTNO"=10)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX_SS(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

 

3.SQL profile 생성

DECLARE
    -- SQL_FULLTEXT 데이터타입이 CLOB이므로, 변수도 CLOB으로 선언
    V_SQL_TEXT CLOB;
BEGIN
    -- V$SQL의 SQL_FULLTEXT를 변수에 저장
    SELECT SQL_FULLTEXT
    INTO   V_SQL_TEXT
    FROM   V$SQL
    WHERE  SQL_ID = '373d62z9hvw4s'
    and rownum=1;
    -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
        NAME        => 'DEPT_EMP_JOIN',
        DESCRIPTION => 'DEPT_EMP_JOIN',
        CATEGORY    => 'DEFAULT',
        SQL_TEXT    => V_SQL_TEXT,
        PROFILE     => SQLPROF_ATTR('LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")',                                                    
                                    'USE_NL(@"SEL$1" "E"@"SEL$1")'                                           
                                    ),
        REPLACE     => TRUE,
        FORCE_MATCH => TRUE  -----이 부분을 추가해줍니다
    );
END;
/

 

dba_sql_profile 확인

select name,category,sql_text,status,force_matching from dba_sql_profiles
where name='DEPT_EMP_JOIN'

NAME	CATEGORY	SQL_TEXT	STATUS	FORCE_MATCHING
-------------------------------------------
DEPT_EMP_JOIN	DEFAULT	select ...	ENABLED	YES

 

4.deptno=10,20,30,40으로 변경해가며 실행계획 확인

 

변경해가면서 확인해보면 SQLID는 모두 다릅니다 .하지만 실행계획 마지막부분에 Note를 보면 SQL profile DEPT_EMP_JOIN used for this statement 를 통해서 생성한 SQL profile이 적용되고 있음을 확인할 수 있습니다. 비록 SQL profile을 생성할 때 deptno=10인 경우의 SQLID에만 생성했지만 옵션을 통해 모두 적용된다고 할 수 있습니다.

 

deptno=10

SELECT *
FROM   DEPT D , EMP E
WHERE   D.DEPTNO = E.DEPTNO  
AND     D.DEPTNO = 10
;

SQL_ID  373d62z9hvw4s, child number 1
-------------------------------------
SELECT * FROM   DEPT D , EMP E WHERE   D.DEPTNO = E.DEPTNO   AND     
D.DEPTNO = 10
 
Plan hash value: 2776721703
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS                        |          |     4 |   200 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                  | DEPT     |     1 |    20 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     4 |   120 |     3   (0)| 00:00:01 |
|*  4 |    INDEX SKIP SCAN                   | EMP_IDX1 |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX_SS(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DEPTNO"=10)
   4 - access("E"."DEPTNO"=10)
       filter("E"."DEPTNO"=10)
 
Note
-----
   - SQL profile DEPT_EMP_JOIN used for this statement

 

deptno=20

SQL_ID  dv6pnhjwra8u3, child number 0
-------------------------------------
SELECT * FROM   DEPT D , EMP E WHERE   D.DEPTNO = E.DEPTNO   AND     
D.DEPTNO = 20
 
Plan hash value: 2776721703
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS                        |          |     6 |   300 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                  | DEPT     |     1 |    20 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     6 |   180 |     3   (0)| 00:00:01 |
|*  4 |    INDEX SKIP SCAN                   | EMP_IDX1 |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX_SS(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DEPTNO"=20)
   4 - access("E"."DEPTNO"=20)
       filter("E"."DEPTNO"=20)
 
Note
-----
   - SQL profile DEPT_EMP_JOIN used for this statement

 

deptno=30

SQL_ID  6ghgv6hp75cy9, child number 0
-------------------------------------
SELECT * FROM   DEPT D , EMP E WHERE   D.DEPTNO = E.DEPTNO   AND     
D.DEPTNO = 30
 
Plan hash value: 2776721703
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS                        |          |     6 |   300 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                  | DEPT     |     1 |    20 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     6 |   180 |     3   (0)| 00:00:01 |
|*  4 |    INDEX SKIP SCAN                   | EMP_IDX1 |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX_SS(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DEPTNO"=30)
   4 - access("E"."DEPTNO"=30)
       filter("E"."DEPTNO"=30)
 
Note
-----
   - SQL profile DEPT_EMP_JOIN used for this statement

 

deptno=40

 SQL_ID  c1jb168guszgk, child number 0
-------------------------------------
SELECT * FROM   DEPT D , EMP E WHERE   D.DEPTNO = E.DEPTNO   AND     
D.DEPTNO = 40
 
Plan hash value: 2776721703
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |       |       |     5 (100)|          |
|   1 |  NESTED LOOPS                        |          |     1 |    50 |     5   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                  | DEPT     |     1 |    20 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     1 |    30 |     2   (0)| 00:00:01 |
|*  4 |    INDEX SKIP SCAN                   | EMP_IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX_SS(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO" "EMP"."DEPTNO"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("D"."DEPTNO"=40)
   4 - access("E"."DEPTNO"=40)
       filter("E"."DEPTNO"=40)
 
Note
-----
   - SQL profile DEPT_EMP_JOIN used for this statement

 

끝

반응형
저작자표시

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

공간관리와 Direct Path Load  (0) 2023.05.10
자동으로 튜닝을 권고해주는 SQL_TUNE ADVISOR  (0) 2023.03.10
Dynamic Sampling에 대한 개념 및 테스트  (0) 2022.12.27
Join Factorization  (0) 2022.10.28
AWR 딕셔너리 VIEW을 이용한 SQL Tuning 정보 추출  (0) 2022.08.30

관련글

댓글

티스토리툴바