SQL パッチを試す（Oracle Database 19c）

# SQL パッチとは
SQL パッチはアプリケーションが発行する SQL 文に自動的にヒントを付与する機能です。Oracle Database Enterprise Edition だけでなくStandard Edition 2 でも利用できます。SQL 文を変更することができないパッケージ製品が発行する SQL 文であってもチューニングのためのヒントを付加することができます。

# SQLパッチの作成と確認
SQL パッチを作成するには DBMS_SQLDIAG パッケージの CREATE_SQL_PATCH 関数を実行します。
この関数には以下のパラメーターが定義されています。この関数は成功すると作成された SQL パッチ名を返します。

|パラメーター名|データ型|説明|備考|
|:---|:---|:---|:---|
|sql_text|CLOB|ヒントを付与するSQL文|sql_id と sql_text はどちらかを指定|
|sql_id|CLOB|ヒントを付与する SQL 文の ID||
|hint_text|CLOB|ヒント句||
|name|VARCHAR2|SQL パッチの名前|指定しない場合は自動生成|
|description|VARCHAR2|説明||
|category|VARCHAR2|カテゴリー|指定しない場合は DEFAULT|
|validate|BOOLEAN|ヒントの検証を行うか|デフォルトTRUE|


SQL 文で指定する場合にはチューニング対象 SQL 文と大文字／小文字の区別まで一致している必要があります。一例としてインデックス検索（INDEX RANGE SCAN）される SELECT 文を全件検索に変更するための FULL ヒントを付与します。

デフォルトではインデックス検索が行われる SELECT 文に自動的にヒントを付けたい場合を考えます。画面の出力は SQL*Plus で SET AUTOTRACE ON 文を実行しています。まずインデックス検索が行われていることを確認します。

```SQL
SQL> VAR id NUMBER
SQL> EXEC :id := 1000
SQL> SELECT * FROM data1 WHERE c1 = :id;

        C1 C2
---------- ----------
      1000 data1
...
      1000 data1

64行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 2076263317

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |    63 |   693 |    67   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATA1      |    63 |   693 |    67   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX1_DATA1 |    63 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

```

SQL パッチを作成します。上記 SELECT 文の SQL_ID（1a52smhyx8avx）を指定します。hint_textパラメーターには付与するヒント構文を記述します。

```SQL:
SQL>  DECLARE
  2        patch_name VARCHAR2(32767);
  3   BEGIN
  4        patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
  5            sql_id => '1a52smhyx8avx',
  6            hint_text => 'FULL(@SEL$1 data1)');
  7   END;
  8  /
PL/SQLプロシージャが正常に完了しました。
```

作成した SQL パッチは、DBA_SQL_PATCHES カタログから確認できます。上記では SQL パッチの名前を指定していないので、「SYS_SQLPATCH_」で始まる名前が自動生成されています。SQL_ID を指定して SQL パッチを作成していますが、カタログに格納されるのは実行された SQL 文そのものです。このためインスタンスを再起動しても SQL パッチの効果は失われません。上記の例では FULL ヒントだけ指定していますが、複数のヒントを指定する場合にはスペースで区切って記述します。IGNORE_OPTIM_EMBEDDED_HINTS を指定すると既存のヒントを無効にできます。

```SQL:
SQL> SELECT * FROM dba_sql_patches;

NAME                           CATEGORY    SIGNATURE SQL_TEXT                                           CREATED                        LAST_MODIFIED                  DESCRIPTIO STATUS   FORCE_MATCHING    TASK_ID TASK_EXEC_ TASK_OBJ_ID TASK_FND_ID TASK_REC_ID
------------------------------ ---------- ---------- -------------------------------------------------- ------------------------------ ------------------------------ ---------- -------- -------------- ---------- ---------- ----------- ----------- -----------
SYS_SQLPTCH_0184955806db000a   DEFAULT    7.6011E+18 SELECT * FROM data1 WHERE c1=:id                   22-11-20 23:01:24.955827       22-11-20 23:01:24.000000                  ENABLED  NO
```

SCOTT ユーザーで接続し、SQL パッチで指定された SQL 文を実行してみます。実行計画が TABLE ACCESS FULL に変更されていることがわかります。Note 部分に SQL パッチが使われたことを示す文が出力されています。

```SQL:
SQL> VAR id NUMBER
SQL> EXEC :id := 1000
SQL> SELECT * FROM data1 WHERE c1 = :id;

        C1 C2
---------- ----------
      1000 data1
...
      1000 data1

64行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 2673507129

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    63 |   693 |  7778   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DATA1 |    63 |   693 |  7778   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"=TO_NUMBER(:ID))

Note
-----
   - SQL patch "SYS_SQLPTCH_0184955806db000a" used for this statement
...
```

# ヒントが使われない場合
DBMS_SQLDIAG.CREATE_SQL_PATCH 関数に指定するヒント文字列（hint_text パラメーター）には問合せブロック名が必要になります。下記ではヒントが有効にならない例を確認します。
まずSELECT 文に直接記述する場合、以下の SELECT 文のヒントは有効です。

```SQL:
SQL> SELECT /*+ FULL(data1) */ * FROM data1 WHERE c1 = :id2;

        C1 C2
---------- ----------
      1000 data1
...
      1000 data1

実行計画
----------------------------------------------------------
Plan hash value: 2673507129

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    63 |   693 |  7778   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DATA1 |    63 |   693 |  7778   (1)| 00:00:01 |
---------------------------------------------------------------------------
...
```

しかし SQL パッチを同じ構文のヒントで作成すると無効になります。以下は前述の SELECT 文と同じヒント構文で SQL パッチを作成しています。

```SQL:
SQL> DECLARE
  2         patch_name VARCHAR2(32767);
  3  BEGIN
  4         patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
  5         sql_id => '5fp8p8xzcs3ns',
  6         hint_text => 'FULL(data1)');
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。
```

SQL ヒントを利用する SELECT 文を実行し、実行計画を確認します。Note 部分に SQL ヒントが使われていることが示されますが、ヒント・レポートにはヒント対象が見つからない「Unresolved (1)」であると記載されています。

```SQL:
SQL> SELECT * FROM data1 WHERE c1 = :id2;

        C1 C2
---------- ----------
      1000 data1
...
      1000 data1

実行計画
----------------------------------------------------------
Plan hash value: 2076263317

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |    63 |   693 |    67   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATA1      |    63 |   693 |    67   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX1_DATA1 |    63 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=TO_NUMBER(:ID2))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         N -  FULL(data1)

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

...
```

## ヒントの検証
マニュアル上は DBMS_SQLDIAG.CREATE_SQL 文には、ヒントの検証を行うかを示す VALIDATE パラメーターがあります。デフォルト値は TRUE です。しかし上記の検証を行った範囲ではこのパラメーターに何を指定してもエラーにはならず、ヒントが正しく使われるかどうかは関係ありませんでした。

# SQL パッチの管理
SQL パッチの変更を行うには DBMS_SQLDIAG.ALTER_SQL_PATCH プロシージャを実行します。有効／無効の区別や、DESCRIPTION、CATEGORY の変更を行うことができます。ただし対象 SQL 文やヒントは変更できません。SQL パッチの削除を行う場合は DBMS_SQLDIAG.DROP_SQL_PATCH プロシージャに SQL パッチ名を指定します。