SQL Patch 적용 사례

SQL Patch로 CLUSTER_BY_ROWID 힌트를 적용하여 성능을 개선한 사례를 살펴보겠습니다. 글을 작성한 이후 2019년에 이미 유사한 내용의 글을 작성한 것을 알았습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT NVL (NULLIF (MOD (ROWNUM, 100), 0), 100), LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000');
CREATE INDEX t1_x1 ON t1 (c1);

 

아래 예제는 나쁜 CF로 인해 1번 오퍼레이션에서 10,000개의 블록 I/O가 발생합니다.

-- 2
SELECT /*+ INDEX(A) */ * FROM t1 a WHERE c1 > 0;

SQL_ID  5wabjtzff74bx, child number 0
-------------------------------------
Plan hash value: 2596206637

-------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  10000 |00:00:00.03 |   10121 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  10000 |00:00:00.03 |   10121 | -- 10000 = 10121 - 121
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |  10000 |00:00:00.01 |     121 |
-------------------------------------------------------------------------------------

 

SQL Patch를 적용하기 위해 앞선 예제에 CLUSTER_BY_ROWID 힌트를 추가한 후 실행 계획을 검증하고 Outline Data 항목에서 힌트를 확인합니다.

-- 3
SELECT /*+ INDEX(A) CLUSTER_BY_ROWID(A) */ * FROM t1 a WHERE c1 > 0;

SQL_ID  7tv953vgfxj7c, child number 0
-------------------------------------
Plan hash value: 2596206637

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |  10000 |00:00:00.02 |     273 |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |  10000 |00:00:00.02 |     273 |          |
|   2 |   SORT CLUSTER BY ROWID             |       |      1 |  10000 |00:00:00.01 |      21 |  361K (0)|
|*  3 |    INDEX RANGE SCAN                 | T1_X1 |      1 |  10000 |00:00:00.01 |      21 |          |
---------------------------------------------------------------------------------------------------------

Outline Data
-------------
      CLUSTER_BY_ROWID(@"SEL$1" "A"@"SEL$1" SORT BATCH=NO)

 

아래 예제로 SQL Patch를 생성합니다.

-- 4
SET SERVEROUT ON

BEGIN
    DBMS_SQLDIAG.DROP_SQL_PATCH (name => 'patch_5wabjtzff74bx', ignore => TRUE);
    
    DBMS_OUTPUT.PUT_LINE (
        DBMS_SQLDIAG.CREATE_SQL_PATCH (
            sql_id    => '5wabjtzff74bx'
          , hint_text => 'CLUSTER_BY_ROWID(@"SEL$1" "A"@"SEL$1" SORT BATCH=NO)'
          , name      => 'patch_5wabjtzff74bx'));
END;
/
patch_5wabjtzff74bx

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

 

첫 번째 예제를 다시 수행하면 SQL Patch에 의해 실행계획이 변경된 것을 확인할 수 있습니다. 사용된 SQL Patch는 Note 항목에 표시됩니다.

-- 5
SELECT /*+ INDEX(A) */ * FROM t1 a WHERE c1 > 0;

SQL_ID  5wabjtzff74bx, child number 0
-------------------------------------
Plan hash value: 2596206637

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |  10000 |00:00:00.02 |     273 |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |  10000 |00:00:00.02 |     273 |          |
|   2 |   SORT CLUSTER BY ROWID             |       |      1 |  10000 |00:00:00.01 |      21 |  361K (0)|
|*  3 |    INDEX RANGE SCAN                 | T1_X1 |      1 |  10000 |00:00:00.01 |      21 |          |
---------------------------------------------------------------------------------------------------------

Outline Data
-------------
      CLUSTER_BY_ROWID(@"SEL$1" "A"@"SEL$1" SORT BATCH=NO)

Note
-----
   - SQL patch "patch_5wabjtzff74bx" used for this statement

 

아래 쿼리로 SQL Patch 관련 정보를 조회할 수 있습니다.

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

NAME                STATUS  FORCE_MATCHING
------------------- ------- --------------
patch_5wabjtzff74bx ENABLED NO

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

-- 6-2
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_5wabjtzff74bx <outline_data><hint><![CDATA[CLUSTER_BY_ROWID(@"SEL$1" "A"@"SEL$1" SORT BATCH=NO)]]></hint></outline_data>

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