LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@plusultra(Noriyoshi Shinoda)

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

Posted at

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> 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>  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> 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> 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> 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> 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> 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 パッチ名を指定します。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?