SQL Patch

지난 글에 이어 12.1 버전부터 사용할 수 있는 SQL Patch 기능을 살펴보자. SQL Profile과 유사하지만 좀 더 간편하게 사용할 수 있다.

아래의 쿼리는 NL 조인으로 인해 37개의 블록 I/O가 발생했다. SQL Id는 0rqgrz3cbr96q다.

-- 1 : 0rqgrz3cbr96q
SELECT /*+ ORDERED USE_NL(B) */
       *
  FROM dept a, emp b
 WHERE b.deptno = a.deptno;

---------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     14 |      37 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |      37 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |       8 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |     14 |      29 |
---------------------------------------------------------------

아래는 해당 쿼리에 IGNORE_OPTIM_EMBEDDED_HINTS 힌트를 패치한다. sql_id 대신 sql_text를 입력할 수도 있다.

-- 2
DECLARE
    name    VARCHAR2(4000);
BEGIN
    name := DBMS_SQLDIAG.CREATE_SQL_PATCH (
                 sql_id    => '0rqgrz3cbr96q'
               , hint_text => 'IGNORE_OPTIM_EMBEDDED_HINTS'
               , name      => 'patch_0rqgrz3cbr96q');
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL Patch 후에 소트 머지 조인으로 실행 계획이 변경된 것을 확인할 수 있다. Note에 적용된 SQL Patch가 표시된다.

-- 3
----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |     14 |      11 |
|   1 |  MERGE JOIN                  |         |      1 |     14 |      11 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |       4 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       2 |
|*  4 |   SORT JOIN                  |         |      4 |     14 |       7 |
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |       7 |
----------------------------------------------------------------------------

Note
-----
   - SQL patch "patch_0rqgrz3cbr96q" used for this STATEMENT

아래 PL/SQL 블록은 기존 SQL Patch를 삭제하고 해시 조인으로 수행되도록 힌트를 패치한다. SQL Patch는 DROP_SQL_PATCH 프로시저로 삭제할 수 있다.

-- 4
DECLARE
    name    VARCHAR2(4000);
BEGIN
    DBMS_SQLDIAG.DROP_SQL_PATCH (name => 'patch_0rqgrz3cbr96q', ignore => TRUE);

    name := DBMS_SQLDIAG.CREATE_SQL_PATCH (
                 sql_id    => '0rqgrz3cbr96q'
               , hint_text => 'IGNORE_OPTIM_EMBEDDED_HINTS LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1") USE_HASH(@"SEL$1" "B"@"SEL$1")'
               , name      => 'patch_0rqgrz3cbr96q');
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

실행 계획이 해시 조인으로 변경된 것을 확인할 수 있다.

-- 5
---------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     14 |      18 |
|*  1 |  HASH JOIN         |      |      1 |     14 |      18 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |       7 |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |       8 |
---------------------------------------------------------------

Note
-----
   - SQL patch "patch_0rqgrz3cbr96q" used for this STATEMENT

SQL Patch와 관련된 내용은 dba_sql_patches 뷰에서 확인할 수 있다.

-- 6
SELECT name, status, force_matching
  FROM dba_sql_patches;

NAME                STATUS  FORCE_MATCHING
------------------- ------- --------------
patch_0rqgrz3cbr96q ENABLED NO

1개의 행이 선택되었습니다.

힌트 내용은 아래 쿼리로 조회할 수 있다. 문서화된 뷰는 없는 것으로 보인다.

-- 7
SELECT a.name , b.comp_data
  FROM sys.sqlobj$ a
     , sys.sqlobj$data b
 WHERE a.obj_type = 3
   AND b.signature = a.signature
   AND b.category = a.category
   AND b.obj_type = a.obj_type;

NAME                COMP_DATA
------------------- -----------------------
patch_0rqgrz3cbr96q <outline_data><hint>...

1개의 행이 선택되었습니다.