皆さんこんにちは、今年も梅雨の時季になりましたね。雨の日の気分転換などに読んでみてください。
今回は、第38回のSQLチューニングの続きとして、アプリケーションを変更できないときでもSPM（SQL Plan Management）の代わりにSQL文にヒントを設定できる、SQLパッチの手動作成について説明しようと思います。後半に、Oracle Database 19c（Oracle19c）からのヒント使用状況レポートについても説明していますので、参考にしてください。
1. SQLパッチ
SQLパッチは、実行計画が原因の障害（SQL文のコンパイル・エラーや実行エラー）を回避するために、Oracle Database 11gからSQL Repair Advisor（SQL修復アドバイザ）によって自動的に生成されるオブジェクトです（アドバイザで回避策が見つかった場合にSQLパッチが推奨されます）。例えば、索引アクセス中に障害が発生した場合は、その索引なしで実行計画を生成すると、障害を回避することができます。内部的には、ヒントに基づいて実行計画を変更するので、Oracle Database 12cR2（Oracle12cR2）からの手動作成により、SQLチューニングなどでも使用できるようになりました。
（１）手動作成
まずは、SQLパッチの手動作成について簡単に説明します。
SQLパッチは、Oracle12cR2からのDBMS_SQLDIAG.CREATE_SQL_PATCHファンクションによって、手動で作成することができます。ただし、指定できるヒントは、最上位レベルのヒント（主問合せに対するヒント）のみです。そのため、副問合せに指定するときには、問合せブロック名を指定する必要があります（問合せブロック名については、第55回を参照してください）。以下のように、SQL文（SQLテキストまたはSQL ID）とヒント文を指定して実行することで、作成されたSQLパッチ名が戻ります（NAMEパラメータは、ユーザ指定のSQLパッチ名で作成したいときに指定します）。
DECLARE
l_patch_name VARCHAR2(32767);
BEGIN
l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_text=>'<SQLテキスト>',
-- sql_id=>'<SQL ID>', -- SQLをSQL IDで設定したいときに指定する
-- name=>'<SQLパッチ名>', -- SQLパッチ名を設定したいときに指定する
hint_text=>'<ヒント>');
dbms_output.put_line(l_patch_name);
END;
/
それでは、以下のSQLの実行計画を、SQLパッチを使用して全表スキャンから索引スキャンにしたいと思います。
SQL> SELECT * FROM tab02 WHERE c1 < 1000;
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 999 |
|* 1 | TABLE ACCESS FULL| TAB02 | 999 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"<1000)
以下のように、DBMS_SQLDIAG.CREATE_SQL_PATCHファンクションにSQLテキストとヒント’index(@SEL$1 tab02 pk_tab02)’を指定して、SQLパッチ’SYS_SQLPTCH_016aba4983f80004’を作成します。その後の実行では、SQLパッチが使用されて、実行計画が索引アクセスになっています（使用されたSQLパッチは、実行計画のNote部で確認できます）。このような表を指定するヒントには、問合せブロック名を指定する必要があるので注意してください（例のように、システム生成の問合せブロック名’主問合せはSEL$1’を使用できるので問題ないと思います）。
DECLARE
l_patch_name VARCHAR2(32767);
BEGIN
l_patch_name := dbms_sqldiag.create_sql_patch(
sql_text=>’select * from tab02 where c1 < 1000′,
hint_text=>’index(@SEL$1 tab02 pk_tab02)’ );
dbms_output.put_line(l_patch_name);
END;
/
SYS_SQLPTCH_016aba4983f80004
SQL> select * from tab02 where c1 < 1000;
—————————————————————-
| Id | Operation | Name | Rows |
—————————————————————-
| 0 | SELECT STATEMENT | | 999 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB02 | 999 |
|* 2 | INDEX RANGE SCAN | PK_TAB02 | 999 |
—————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“C1″<1000)
Note
—–
– SQL patch “SYS_SQLPTCH_016aba4983f80004” used for this statement
また、以下のように既存のヒントをすべて無効にすることや、すべて無効にして新しいヒントを指定することもできるので、いろいろな使い方ができると思います。
DECLARE
l_patch_name VARCHAR2(32767);
BEGIN
l_patch_name := dbms_sqldiag.create_sql_patch(
sql_id=>'<SQL ID>’,
hint_text=>’IGNORE_OPTIM_EMBEDDED_HINTS‘ );
dbms_output.put_line(l_patch_name);
END;
/
— ヒントをすべて無効にしてFULLヒントだけを指定
DECLARE
l_patch_name VARCHAR2(32767);
BEGIN
l_patch_name := dbms_sqldiag.create_sql_patch(
sql_id=>'<SQL ID>’,
hint_text=>’IGNORE_OPTIM_EMBEDDED_HINTS FULL(@SEL$1 tab02)’ );
dbms_output.put_line(l_patch_name);
END;
（２）SPMとの違い
次に、これまで使用していたSPMとの違いについて説明します。
SPMの問題点は、実行計画をSQL計画ベースラインで固定化することです。それに対してSQLパッチは、SQLプロファイルと同じように、指定されたヒントを使用して実行計画を補正することです。パッケージ・アプリケーションなどをSQLチューニングする場合、実行計画を固定化したくない方のために、使い分けができるようになりました。
これ以外に、すべてのヒントが使用できないことも改善されます。SPMは、アウトライン・データ（V$SQL_PLAN.OTHER_XML）に格納されるものだけになりますが、SQLパッチはそれ以外も使用できます。例えば、PARALLELヒントは、SPMで使用することはできませんが、以下のようにSQLパッチでは使用することができます。
l_patch_name VARCHAR2(32767);
BEGIN
l_patch_name := dbms_sqldiag.create_sql_patch(
sql_text=>’select * from tab02 where c1 < 1000′,
hint_text=>’parallel(2)‘ );
dbms_output.put_line(l_patch_name);
END;
/
SYS_SQLPTCH_016aba35db870003
SQL> select * from tab02 where c1 < 1000;
——————————————————————————-
| Id | Operation | Name | Rows || TQ |IN-OUT| PQ Distrib |
——————————————————————————-
| 0 | SELECT STATEMENT | | 999 || | | |
| 1 | PX COORDINATOR | | || | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 999 || Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 999 || Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TAB02 | 999 || Q1,00 | PCWP | |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
4 – filter(“C1″<1000)
Note
—–
– Degree of Parallelism is 2 because of hint
– SQL patch “SYS_SQLPTCH_016aba35db870003” used for this statement
また、SPMは、Oracle Database Enterprise Edition（EE)でしか使用できませんが、SQLパッチはStandard Edition（SE）でも使用できます（ただし、Oracle Database 18cからは、SEでもSPMの一部の機能が使用できるようになりましたので、これはもう問題ないかと思います）。
（３）確認と無効化
次に、SQLパッチの確認とそれの無効化や削除する方法について説明します。
以下のように、DBA_SQL_PATCHESビューに対してSQL文（SQLテキストまたはSQL ID）で検索すると、SQLパッチ名やSTATUSが確認できます。そのため、作成時にSQLパッチ名を出力していなくても確認することができます。
SQL> COL name FORMAT a30 SQL> COL category FORMAT a20 SQL> COL description FORMAT a30 SQL> SELECT name, category, description, status, force_matching FROM dba_sql_patches 2 WHERE sql_text LIKE 'select * from tab02 where c1 < 1000'; NAME CATEGORY DESCRIPTION STATUS FOR ------------------------------ -------------------- ------------------------------ -------- --- SYS_SQLPTCH_016aba35db870003 DEFAULT ENABLED NO
以下のように、SQLパッチ名を指定して無効化（STATUSをDISABLED）にすることで、SQLパッチを使用しないようにもできます（DROP_SQL_PATCHプロシージャで削除することもできます）。
-- SQLパッチの無効化
exec DBMS_SQLDIAG.ALTER_SQL_PATCH(name=>'SYS_SQLPTCH_016aba35db870003',
attribute_name=>'STATUS', value=>'DISABLED');
-- SQLパッチの削除
exec DBMS_SQLDIAG.DROP_SQL_PATCH(name=>'SYS_SQLPTCH_016aba35db870003');
このように、SPM以外にも手動でヒントを埋め込むことができるので、用途に合わせて使用できるようになり、より使いやすくなりました。
2. ヒント使用状況レポート
ヒントは、オプティマイザが使用しないときに、その理由を知ることが難しい場合がります。そのため、Oracle19cからのヒント使用状況レポートにより、実行計画にヒントが使用されない理由などを出力できるようになりました。ここでは、そのヒント使用状況レポート（ヒント・レポート）について説明します。
（１）ヒントが無視される理由
まずは、ヒントが無視される理由について説明します。
オプティマイザは、ヒント内のエンコードされた命令を使用して、SQL文の実行計画を選択します。ただし、条件によっては、オプティマイザがヒントを使用しないことがありますが、それに対してエラー・メッセージなどは発行されません（無視されます）。そのため、以下のようなヒントが無視される一般的な理由から簡単に説明します。
- 構文エラー
ヒントに入力ミスや無効な引数が含まれている場合です。ヒント・ブロックに複数のヒントがある場合は、1つのヒントに構文エラーがあると、エラー以降のヒントをすべて無視します。例えば、ヒント’/*+ INDEX(t1) MERG(v) USE_NL(t2) */’は、MERG(v)に構文エラーがあるので、MERG(v)とUSE_NL(t2)を無視します（このときUSE_NL(t2)は、解析されないためヒント・レポートにも示されません）。 - 未解決のヒント
未解決のヒントとは、構文エラー以外の理由によるものです。例えば、INDEXヒントで指定した索引名が有効ではない場合です。 - 競合するヒント
競合するヒントの組合せは、ヒントが正しく指定されていても無視されます。例えば、SQL文で同じ表にFULLヒントとINDEXヒントを指定しても、索引スキャンと全表スキャンは同時に実行することはできません（このときほとんどの場合、競合するヒントの両方を無視します）。 - 問合せ変換の影響を受けるヒント
一部のヒントは、問合せ変換によって無効になる場合があります。例えば、PUSH_PREDヒント（述語を強制的にビュー内に入れる）とMERGEヒント（ビューを親問合せにマージする）を指定している場合です（これはビューがマージされると、ビューが使用できなくなるため、PUSH_PREDヒントを適用できなくなるからです）。
（２）レポートの出力
次に、ヒント・レポートの出力について説明します。
ヒント・レポートには、使用されたヒントと無視されたヒントが表示され、ヒントが無視された理由も示されるので、非常に分かりやすくなっています。ヒント・レポートを出力するには、DBMS_XPLANパッケージの以下のファンクションを使用して、それぞれから実行計画を出力するときに指定します。
- DISPLAY（PLAN TABLEから）
- DISPLAY_CURSOR（カーソル・キャッシュから）
- DISPLAY_WORKLOAD_REPOSITORY（AWRから）
- DISPLAY_SQL_PLAN_BASELINE（SQL計画ベースラインから）
- DISPLAY_SQLSET（SQLチューニング・セットから）
このファンクションは、デフォルトで有効になっているので、formatパラメータで値’TYPICAL’を指定するだけでレポートを生成しますが、以下のように値によって表示されるヒントを変更することができます（’ALL’は、’Query Block Name / Object Alias’セクションと’Column Projection Information’セクションも出力されます）。
- 使用さえないヒントのみ表示（TYPICAL、HINT_REPORT_UNUSED）
- 使用されたヒントと使用されないヒントの両方を表示（ALL、HINT_REPORT、HINT_REPORT_USED）
以下は、’TYPICAL’を指定した場合の実行計画とヒント・レポートを、PLAN TABLEから出力した例です。
2 SELECT /*+ INDEX(t2) FULL(@sel$2 t1) */ COUNT(*) FROM jobs t2
3 WHERE t2.job_id IN (SELECT /*+ FULL(t1) UNNEST */ job_id FROM employees t1);
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
--------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | 19 |
| 3 | SORT UNIQUE | | 107 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 |
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T2"."JOB_ID"="JOB_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------
4 - SEL$5DA710D3 / T1@SEL$2
U - FULL(t1) / hint overridden by another in parent query block
指定された4つのヒントのうち、使用されないヒントとしてFULL(t1)だけが出力されています。FULL(t1)ヒントは、親問合せブロックで指定したFULL(@sel$2 T1)ヒントによってオーバーライドされて、適用されなかったことを示しています（これは同じ表に対するヒントは、親問合せブロック内のヒントが優先されるからです）。
（３）レポートの確認
最後に、ヒント・レポートの確認方法について説明します。
ヒント・レポートには、SQL文に対するヒント数（合計ヒント数と未使用ヒント数）を出力してから、それぞれのヒントに対応する実行計画の行番号、問合せブロック名、オブジェクト別名が出力されます。同一オブジェクトでは、未使用のヒントが最初に表示され、その後に使用されたヒントが表示されます。未使用のヒント・テキストが出力されたときに、理由として以下のいずれかの注釈が表示されます（非使用ヒント数に、それぞれの注釈の数も出力されます）。このとき、正しくないヒントや最終計画に存在しない問合せブロックに対するヒントは、行番号が0になります。
- E（構文エラーを示します）
- N（未解決のヒントを示します）
- U（対応するヒントが最終計画で使用されなかったことを示します）
それでは、もう少し多くの情報が出力される以下のヒント・レポート（先程のSQLに2つのヒントを追加して、’HINT_REPORT’を指定した場合）を使用して説明していきます。
2 SELECT /*+ INDEX(t2) FULL(@sel$2 t1) MERGE(@SEL$3) NO_ORDER_SUBQ */ COUNT(*) FROM jobs t2
3 WHERE t2.job_id IN (SELECT /*+ FULL(t1) UNNEST */ job_id FROM tab01 t1);
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => ‘HINT_REPORT’));
…
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 6 (U – Unused (1), N – Unresolved (1), E – Syntax error (1))
—————————————————————————————
0 – SEL$3
N – MERGE(@SEL$3)
0 – SEL$1
E – NO_ORDER_SUBQ
0 – SEL$2
– UNNEST
4 – SEL$5DA710D3 / T1@SEL$2
U – FULL(t1) / hint overridden by another in parent query block
– FULL(@sel$2 t1)
5 – SEL$5DA710D3 / T2@SEL$1
– INDEX(t2)
このレポートには、6つのヒントのうち、3つが使用されないヒントで、別々の注釈が出力されています（MERGE(@SEL$3)ヒントは、問合せブロックSEL$3が存在しないため未解決のヒント、NO_ORDER_SUBQヒントは、有効なヒントではないため構文エラーとして、それぞれの行番号が0で表示されています）。オブジェクト’T1@SEL$2’に対しては、未使用のヒント、使用されたヒントの順で表示されています（こちらの方がFULL(@sel$2 t1)にオーバーライドされたことが分かりやすいと思います）。また、問合せブロックSEL$2のUNNESTヒントが使用されましたが、最終計画にSEL$2は存在しないので、対応する行番号が0で表示されています（これは副問合せをネスタ解除して副問合せがなくなったからです）。このような手順で、ヒント・レポートを見ていくことができます。
3. おわりに
今回は、SQLパッチの手動作成とヒント使用状況レポートについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。