Oracle 노하우 및 팁

SQL_PROFILE 사용 방법 2 3 10,921

by 강정식 [강정식] SQL_PROFILE DBMS_SQLTUNE [2013.01.11 17:23:23]

10g부터 소개된 SQL_PROFILE의 기능에 대한 소개를 하고자 한다.

SQL_PROFILE이란, 특정 SQL_ID의 실행계획이 비효율적으로 생성될 경우, SQL 구문 변경 없이 실행계획만 변경시키거나 고정시키기 위해 사용한다.

일반적으로 SQL 튜닝을 하기 위해서는 아래와 같은 절차를 통해 개선을 한다.
  - 튜닝 대상 SQL에 대한 구문 변경
  - 튜닝 대상 SQL에 대한 힌트 사용
  - 튜닝 대상 프로그램에 대한 로직 변경
  - 필요할 경우 인덱스 생성  

문제는 위와 같은 튜닝을 하기 위해서는 DB Object 변경이 수반되어야 한다는 점이다.
운영 상태에서 DB Object 변경은 상당한 Risk(Object Invalidation, Re-Parsing 등)를 가지고 있기 때문에, 해당 이슈를 처리하다가 장애까지 이어질 수 있기 때문에 이와 같은 튜닝을 적용하기에는 무리가 있다.

이 경우, SQL 구문 변경 없이(Object 변경 없이) 원하는 실행계획을 유도할 경우, 긴급하게 이슈 대응이 가능하기 때문에 튜너라면 반드시 이 기능을 숙지하고 있어야 한다.

1. SQL_PROFILE 사용 예제


1-1) 오라클 버전 확인

SELECT * FROM V$VERSION;

 

BANNER                                                              

--------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit    

PL/SQL Release 10.2.0.3.0 - Production                              

 

1-2) 테이블 생성

DROP TABLE DEPT;

 

CREATE TABLE DEPT

       (DEPTNO NUMBER(2),

        DNAME VARCHAR2(14),

        LOC VARCHAR2(13) );

 

1-3) 데이터 생성

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');

INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

 

COMMIT;

/

 

1-4) 인덱스 생성 및 통계정보 생성

CREATE UNIQUE INDEX USER.DEPT_U1 ON USER.DEPT (DEPTNO);

 

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);

 

1-5) 데이터 확인

SELECT * 

FROM   DEPT

;

 

DEPTNO DNAME         LOC        

------ ------------- -----------

    10 ACCOUNTING    NEW YORK   

    20 RESEARCH      DALLAS     

    30 SALES         CHICAGO    

    40 OPERATIONS    BOSTON     

;

 

1-6) 실행계획 확인

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = :B1

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | DEPT_U1 |     1 |       |     0   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

                                                                                       

Predicate Information (identified by operation id):                                    

---------------------------------------------------                                    

                                                                                       

   2 - access("D"."DEPTNO"=TO_NUMBER(:B1))                                             

;



지금까지 DEPT 테이블을 만든 후 데이터, 인덱스 및 통계정보를 생성한 뒤, 실행계획을 살펴보았다.

실행계획은 당연히 DEPTNO 조건이 들어오기 때문에 이 컬럼으로 구성된 DEPT_U1 인덱스를 사용한 것을 볼 수 있다.


이제 이 실행계획을 SQL_PROFILE을 통해 FULL SCAN을 하도록 유도하려고 하는데, 방식은 아래와 같다.



즉, DEPT_U1 인덱스를 사용하는 SQL을 A라 하고, FULL SCAN 하는 SQL을 B라 할 때, SQL_PROFILE을 통해 B로 변경을 하는 방식이다.

1-7) B 실행계획 유도

EXPLAIN PLAN FOR

SELECT /*+ FULL(D) */ -- 힌트추가(KJS)

       *

FROM   DEPT D

WHERE  D.DEPTNO = :B1

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |

--------------------------------------------------------------------------

                                                                          

Predicate Information (identified by operation id):                       

---------------------------------------------------                       

                                                                          

   1 - filter("D"."DEPTNO"=TO_NUMBER(:B1))                                

;



이제 원하는 B 실행계획이 나왔으므로, 이 힌트에 대한 OUTLINE FULL HINT를 통해 값을 가져오도록 한다.


1-8) B 실행계획 OUTLINE FULL HINT 

EXPLAIN PLAN FOR

SELECT /*+ FULL(D) */ -- 힌트추가(KJS)

       *

FROM   DEPT D

WHERE  D.DEPTNO = :B1

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'OUTLINE'));

 

--------------------------------------------------------------------------  

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  

--------------------------------------------------------------------------  

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |  

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |  

--------------------------------------------------------------------------  

                                                                            

Outline Data                                                                

-------------                                                               

                                                                            

  /*+                                                                       

      BEGIN_OUTLINE_DATA                                                    

      FULL(@"SEL$1" "D"@"SEL$1")                                            

      OUTLINE_LEAF(@"SEL$1")                                                

      ALL_ROWS                                                              

      OPT_PARAM('_optim_peek_user_binds' 'false')                           

      OPT_PARAM('_fast_full_scan_enabled' 'false')                          

      OPT_PARAM('_b_tree_bitmap_plans' 'false')                             

      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                 

      IGNORE_OPTIM_EMBEDDED_HINTS                                           

      END_OUTLINE_DATA                                                      

  */                                                                        

                                                                            

Predicate Information (identified by operation id):                         

---------------------------------------------------                         

                                                                            

   1 - filter("D"."DEPTNO"=TO_NUMBER(:B1))                                  

;



위에서 나온 'Outline Data'를 SQL_PROFILE에 등록한다(등록 시 모두 가져올 필요는 없으며, 필요한 힌트만 취득하면 되나 테스트를 위해 전체를 가져옴)


SQL_PROFILE은 SQL_TEXT를 구분자로 인식하기 때문에 반드시 SQL_TEXT가 일치해야 하므로, 가급적 Shared Pool에 있는 SQL_TEXT를 이용하는 것이 좋다.

이를 이용하기 위해 해당 SQL을 수행토록 한다.



1-9) Shared Pool에 등록 및 SQL_ID 확인

※ 이 절차는 실제 운영 시에는 필요 없으며, 테스트를 위한 절차임

 

-- SQL 실행

var B1 NUMBER

EXEC :B1 := 10

 

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = :B1

;

 

    DEPTNO DNAME                        LOC      

---------- ---------------------------- ---------

        10 ACCOUNTING                   NEW YORK 

;

 

-- SQL_ID 및 FULL_TEXT 확인

SELECT SQL_ID,

       SQL_TEXT,

       SQL_FULLTEXT

FROM   V$SQL

WHERE  SQL_TEXT LIKE '%DEPT D%'

;

 

 

SQL_ID        SQL_TEXT                                     SQL_FULLTEXT            

------------- -------------------------------------------- ------------------------

abfnv4rva7df9 SELECT * FROM   DEPT D WHERE  D.DEPTNO = :B1 SELECT *                

                                                           FROM   DEPT D           

                                                           WHERE  D.DEPTNO = :B1   

;



SQL 실행 후 V$SQL에서 SQL_ID 및 SQL_FULLTEXT를 확인하였으므로, 이를 활용하여 SQL_PROFILE을 등록하도록 하자.


1-10) 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 = 'abfnv4rva7df9';

 

    -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록

    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(

        NAME        => 'DEPT_PROFILE_1',

        DESCRIPTION => 'DEPT_PROFILE_1',

        CATEGORY    => 'DEPT_PROFILE_1',

        SQL_TEXT    => V_SQL_TEXT,

        PROFILE     => SQLPROF_ATTR('BEGIN_OUTLINE_DATA',                                                    

                                    'FULL(@"SEL$1" "D"@"SEL$1")',                                            

                                    'OUTLINE_LEAF(@"SEL$1")',                                                

                                    'ALL_ROWS',                                                                                           

                                    'IGNORE_OPTIM_EMBEDDED_HINTS',                                           

                                    'END_OUTLINE_DATA'  

                                    ),

        REPLACE     => TRUE

    );

END;

/

 

PL/SQL procedure successfully completed.

;



이제 해당 SQL_PROFILE이 DICTIONARY에 등록 되었으며, 해당 내용을 사용해 보도록 하자.



1-11) SQL_PROFILE 사용

-- 1. SQL_PROFILE 사용 전

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = :B1

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

---------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | DEPT_U1 |     1 |       |     0   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

                                                                                       

Predicate Information (identified by operation id):                                    

---------------------------------------------------                                    

                                                                                       

   2 - access("D"."DEPTNO"=TO_NUMBER(:B1))                                             

;

 

-- 2. SQL_PROFILE 활성화

ALTER SESSION SET SQLTUNE_CATEGORY = DEPT_PROFILE_1 ;

 

Session altered.

;

 

-- 3. SQL_PROFILE 사용 후

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = :B1

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------  

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  

--------------------------------------------------------------------------  

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |  

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |  

--------------------------------------------------------------------------  

                                                                            

Predicate Information (identified by operation id):                         

---------------------------------------------------                         

                                                                            

   1 - filter("D"."DEPTNO"=TO_NUMBER(:B1))                                  

                                                                            

Note                                                                        

-----                                                                       

   - SQL profile "DEPT_PROFILE_1" used for this statement                   

;



위의 결과처럼, Session-Level에서 'DEPT_PROFILE_1' SQL_PROFILE을 사용한 결과, 힌트 없이 FULL SCAN하는 것을 알 수 있으며, 'Note'에서도 'DEPT_PROFILE_1'을 사용했다는 것을 확인할 수 있다.


이와 같이 SQL_PROFILE은 SQL별로 등록할 수 있으며, 이에 대해 DBA_SQL_PROFILES 뷰에서도 확인이 가능하다.


1-12) DBA_SQL_PROFILES View

col NAME            format a30

col DESCRIPTION     format a30

col CATEGORY        format a30

col SQL_TEXT        format a100

col CREATED         format a30

col LAST_MODIFIED   format a30

col TYPE            format a30

col STATUS          format a30

col FORCE_MATCHING  format a30

 

SELECT NAME,

       DESCRIPTION,

       CATEGORY,

       SQL_TEXT,

       CREATED,

       LAST_MODIFIED,

       STATUS,

       FORCE_MATCHING

FROM   DBA_SQL_PROFILES

WHERE  NAME = 'DEPT_PROFILE_1'

;

 

NAME            DESCRIPTION     CATEGORY       SQL_TEXT              

--------------- --------------- -------------- ----------------------

CREATED         LAST_MODIFIED   STATUS         FORCE_MATCHING        

--------------- --------------- -------------- ----------------------

DEPT_PROFILE_1  DEPT_PROFILE_1  DEPT_PROFILE_1 SELECT *              

                                               FROM   DEPT D         

                                               WHERE  D.DEPTNO = :B1 

                                                                     

11-JAN-13       11-JAN-13       ENABLED        NO                    

;

1-13) CATEGORY 기능

- CATEGORY 기능은 2가지 측면에서 활용이 가능함.

  ① CATEGORY GROUPING

    - 'CATEGORY GROUPING'은 하나의 프로그램 내에 여러 SQL을 SQL_PROFILE을 통해 개선을 해야 하는 경우도 있다.

    - 만약, 하나의 프로그램에 5개의 SQL을 등록 후 사용할 경우 Session-Level에서 5개의 SQL_PROFILE을 모두 호출해야 하는데, 이를 CATEGORY에서 하나의 이름으로 등록할 경우, 이 이름으로 한 번만 호출하여 5개 SQL을 모두 사용할 수 있다.

    

  ② System-Level

    - 위의 'CATEGORY GROUPING'을 통해 여러 SQL을 하나의 CATEGORY로 묶을 수 있지만, 이를 사용하기 위해서는 어느 지점에서 반드시 'ALTER SESSION'을 해야 한다.

    - 하지만, 'ALTER SESSION'을 할 수 없는 상황일 경우, 이를 System-Level로 등록하여 세션 변경 없이 사용이 가능하다.

 

- System-Level로 등록하기 위해서는 값을 'Default'로 설정하면 된다.

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 = 'abfnv4rva7df9';

 

    -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록

    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(

        NAME        => 'DEPT_PROFILE_1',

        DESCRIPTION => 'DEPT_PROFILE_1',

        CATEGORY    => 'DEFAULT',

        SQL_TEXT    => V_SQL_TEXT,

        PROFILE     => SQLPROF_ATTR('BEGIN_OUTLINE_DATA',                                                                                   'FULL(@"SEL$1" "D"@"SEL$1")',                                                                           'OUTLINE_LEAF(@"SEL$1")',                                                                               'ALL_ROWS',                                                                                             'IGNORE_OPTIM_EMBEDDED_HINTS',                                                                          'END_OUTLINE_DATA'  

                                    ),

        REPLACE     => TRUE

    );

END;

/




2. SQL_PROFILE 패키지 정리
1) DBMS_SQLTUNE Package
- SQL_PROFILE을 등록 또는 삭제하기 위해서는 DBMS_SQLTUNE 패키지를 사용해야 한다.
  => SQL_PROFILE 등록     : DBMS_SQLTUNE.IMPORT_SQL_PROFILE 
  => SQL_PROFILE 삭제     : DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME =>'DEPT_PROFILE_1'); 
  => SQL_PROFILE DISABLE  : DBMS_SQLTUNE.ALTER_SQL_PROFILE (NAME =>'DEPT_PROFILE_1',ATTRIBUTE_NAME=>'STATUS',VALUE=>'DISABLED');

2) SQL_PROFILE 활성화
- 활성화   : ALTER SESSION SET SQLTUNE_CATEGORY = DEPT_PROFILE_1 ;
- 비활성화 : ALTER SESSION SET SQLTUNE_CATEGORY = FALSE;






3. FORCE_MATCH 기능
- SQL_PROFILE의 구분자는 위에서 설명한 것처럼 SQL 구문으로 체크를 한다.
- 그런데, 만약 바인드 변수가 아닌 Literal 값으로 들어오는 Dynamic SQL일 경우, SQL 구문이 모두 틀리기 때문에 SQL_PROFILE을 등록하여도 공유해서 사용을 할 수가 없다.
- 이 때, FORCE_MATCH 기능을 사용할 경우 CURSOR_SHARING 기능처럼 Literal 값을 바인드 변수로 자동변경 해주므로, 하나만 등록하여도 나머지 SQL을 공유해서 사용 할 수 있다.

1) 기존 SQL_PROFILE 삭제

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

 

PL/SQL procedure successfully completed.

 

2) Literal SQL을 CATEGORY 'DEFAULT'로 하고, FORCE_MATCH 기능 없이 등록하여 공유가 되는지 체크

DECLARE

BEGIN

    -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록

    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(

        NAME        => 'DEPT_PROFILE_1',

        DESCRIPTION => 'DEPT_PROFILE_1',

        CATEGORY    => 'DEFAULT', -- System-Level로 등록

        SQL_TEXT    => 'SELECT *

                        FROM   DEPT D

                        WHERE  D.DEPTNO = 10',

        PROFILE     => SQLPROF_ATTR('BEGIN_OUTLINE_DATA',                                                    

                                    'FULL(@"SEL$1" "D"@"SEL$1")',                                            

                                    'OUTLINE_LEAF(@"SEL$1")',                                                

                                    'ALL_ROWS',                                                                                           

                                    'IGNORE_OPTIM_EMBEDDED_HINTS',                                           

                                    'END_OUTLINE_DATA'  

                                    ),

        REPLACE     => TRUE

    );

END;

/

 

3) 실행계획 확인

 

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 10

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |

--------------------------------------------------------------------------

                                                                          

Predicate Information (identified by operation id):                       

---------------------------------------------------                       

                                                                          

   1 - filter("D"."DEPTNO"=10)                                            

                                                                          

Note                                                                      

-----                                                                     

   - SQL profile "DEPT_PROFILE_1" used for this statement                 

;

 

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 20

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------------------- 

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | 

--------------------------------------------------------------------------------------- 

|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 | 

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 | 

|*  2 |   INDEX UNIQUE SCAN         | DEPT_U1 |     1 |       |     0   (0)| 00:00:01 | 

--------------------------------------------------------------------------------------- 

                                                                                        

Predicate Information (identified by operation id):                                     

---------------------------------------------------                                     

                                                                                        

   2 - access("D"."DEPTNO"=20)                                                          

;

 

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 30

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------------------- 

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | 

--------------------------------------------------------------------------------------- 

|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 | 

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 | 

|*  2 |   INDEX UNIQUE SCAN         | DEPT_U1 |     1 |       |     0   (0)| 00:00:01 | 

--------------------------------------------------------------------------------------- 

                                                                                        

Predicate Information (identified by operation id):                                     

---------------------------------------------------                                     

                                                                                        

   2 - access("D"."DEPTNO"=30)                                                          

;

 

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 40

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------------------- 

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | 

--------------------------------------------------------------------------------------- 

|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 | 

|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 | 

|*  2 |   INDEX UNIQUE SCAN         | DEPT_U1 |     1 |       |     0   (0)| 00:00:01 | 

--------------------------------------------------------------------------------------- 

                                                                                        

Predicate Information (identified by operation id):                                     

---------------------------------------------------                                     

                                                                                        

   2 - access("D"."DEPTNO"=40)                                                          

;



위의 결과에서 보듯이, 'DEPT=10'에 대해서만 SQL_PROFILE을 등록하였기 때문에, 이 경우에만 FULL SCAN을 하고 나머지 20, 30, 40일 경우 INDEX SCAN을 사용하였다. 즉, 20, 30, 40인 경우에는 SQL_PROFILE을 사용하지 못하는 것을 확인할 수 있다.


이제는 FORCE_MATCH를 사용하여 CURSOR_SHARING 기능이 사용되는지 체크해보도록 하자.


4) Literal SQL을 CATEGORY 'DEFAULT'로 하고, FORCE_MATCH 기능 포함하여 등록한 후 공유가 되는지 체크

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

 

PL/SQL procedure successfully completed.

 

DECLARE

BEGIN

    -- DBMS_SQLTUNE.IMPORT_SQL_PROFILE 패키지를 이용하여 등록

    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(

        NAME        => 'DEPT_PROFILE_1',

        DESCRIPTION => 'DEPT_PROFILE_1',

        CATEGORY    => 'DEFAULT',-- System-Level로 등록

        SQL_TEXT    => 'SELECT *

                        FROM   DEPT D

                        WHERE  D.DEPTNO = 10',

        PROFILE     => SQLPROF_ATTR('BEGIN_OUTLINE_DATA',                                                    

                                    'FULL(@"SEL$1" "D"@"SEL$1")',                                            

                                    'OUTLINE_LEAF(@"SEL$1")',                                                

                                    'ALL_ROWS',                                                                                           

                                    'IGNORE_OPTIM_EMBEDDED_HINTS',                                           

                                    'END_OUTLINE_DATA'  

                                    ),

        REPLACE     => TRUE,

        FORCE_MATCH => TRUE  -- CURSOR_SHARING 기능 사용  

    );

END;

/

 

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 10

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------                               

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                               

--------------------------------------------------------------------------                               

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |                               

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |                               

--------------------------------------------------------------------------                               

                                                                                                         

Predicate Information (identified by operation id):                                                      

---------------------------------------------------                                                      

                                                                                                         

   1 - filter("D"."DEPTNO"=10)                                                                           

                                                                                                         

Note                                                                                                     

-----                                                                                                    

   - SQL profile "DEPT_PROFILE_1" used for this statement    

;

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 20

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------                               

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                               

--------------------------------------------------------------------------                               

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |                               

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |                               

--------------------------------------------------------------------------                               

                                                                                                         

Predicate Information (identified by operation id):                                                      

---------------------------------------------------                                                      

                                                                                                         

   1 - filter("D"."DEPTNO"=20)                                                                           

                                                                                                         

Note                                                                                                     

-----                                                                                                    

   - SQL profile "DEPT_PROFILE_1" used for this statement  

;

 

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 30

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------                               

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                               

--------------------------------------------------------------------------                               

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |                               

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |                               

--------------------------------------------------------------------------                               

                                                                                                         

Predicate Information (identified by operation id):                                                      

---------------------------------------------------                                                      

                                                                                                         

   1 - filter("D"."DEPTNO"=30)                                                                           

                                                                                                         

Note                                                                                                     

-----                                                                                                    

   - SQL profile "DEPT_PROFILE_1" used for this statement  

;

 

EXPLAIN PLAN FOR

SELECT *

FROM   DEPT D

WHERE  D.DEPTNO = 40

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

--------------------------------------------------------------------------                               

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                               

--------------------------------------------------------------------------                               

|   0 | SELECT STATEMENT  |      |     1 |    20 |     5   (0)| 00:00:01 |                               

|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    20 |     5   (0)| 00:00:01 |                               

--------------------------------------------------------------------------                               

                                                                                                         

Predicate Information (identified by operation id):                                                      

---------------------------------------------------                                                      

                                                                                                         

   1 - filter("D"."DEPTNO"=40)                                                                           

                                                                                                         

Note                                                                                                     

-----                                                                                                    

   - SQL profile "DEPT_PROFILE_1" used for this statement                                                

;



이처럼, SQL_PROFILE에서 FORCE_MATCH 기능을 통해 Literal SQL도 극복할 수 있으므로, 아주 유용한 기능이라 할 수 있다.


4. 주의사항
1) SQL_PROFILE 체크는 SQL 구문으로 체크하기 때문에 Shared Pool에 등록된 SQL을 SQL_PROFILE 시 사용하는 것이 좋다.
2) 1)에서 이야기한 것 처럼 SQL 구문으로 인식하기 때문에, 튜닝 SQL이 힌트가 아닌 구문 변경일 경우 사용할 수 없다.
3) CATEGORY 등록 시 'DEFAULT' 즉, System-Level로 등록할 경우 시스템에 부하를 줄 수 있기 때문에 가급적 긴급 대응일 경우에만 사용하는 것이 좋다.
by 타락천사 [2013.01.11 20:49:03]

설명이 좋네..
난, 지금까지,  CREATE_TASK  ==>  DBMS 가 튜닝 한 것를  acccept  만 하는것만 알고 있었는데.( 구글링 예제가 거의 이 패턴이라.)
정말 고마워 ㅇ.ㅇ
by 박민철 [2014.08.21 11:01:31]

내공이 부족해 알수가 없네요..^^

좀더 공부하고 다시 볼께요 ㅡㅜ

by 지하드 [2023.11.01 15:56:41]

좋은글 감사합니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입