概要
SQLのチューニング方法の1つに実行計画を制御する、ということが考えられます。実行計画を制御する方法も、統計情報を取り直す、オプティマイザ関連のパラメータを設定する、などなど様々な方法が考えられます。
Oracle Databaseのオプティマイザでは、SQL文のコメントの中にヒントと呼ばれる句を埋め込むことで、実行計画を制御できます。ヒント句を埋め込む方法は、1つのSQL文の実行計画を制御する方法としては強力で、チューニング方法の最後の砦のようなものですが、SQLを発行する環境（アプリケーション）によっては、ヒント句を埋め込めない、というケースもあります。
SQL Plan Management(SPM)を活用することで、「SQL文にヒントをつけないままで、ヒントをつけた場合と同じ実行計画を適用させる」ということが可能です。本記事は、その手順に関して、簡単なケースで説明します。本記事の内容はSPMがメインなので、前提知識として下記記事を参照してどのような機能か理解いただくとよいです。
第1回 Oracle Database 12c R2新機能でSQL Plan Managementを"賢く"使い倒せ！
Oracle Databaseのチューニング方法は、とてもいろんな方法がありますが、その方法の1つとしてストックしておくと、困ったときに役立つこともある、かもしれません。
本記事のタイトルにある「SQL文にヒント句を追加しないでヒント句を追加した場合と同じようにSQL実行計画を制御する」という方法に関しては、本記事の方法とは別でSQLパッチ(DBMS_SQLDIAG.CREATE_SQL_PATCH)という機能を活用する方法も考えられます。このSQLパッチを活用する方法に関しては、下記の記事が参考になります。
津島博士のパフォーマンス講座：第72回 SQLパッチとヒント使用状況レポートについて
2020年1月ごろの情報・環境を元に本記事は記載しています。
1. 本記事の作業の前提
本記事の内容を実行するには下記が必要です。
- Oracle Database Enterprise Editionがインストールされている環境のOracle Databaseインスタンス
- SQLの実行環境(SQL*PlusやSQL Developer)
本記事はOracle Database 19cを利用して書いています。
本記事の内容はSPMを活用する方法です。SPMは、Oracle Database EEでは「制限なく」利用できます。Oracle Databaseの18c以降では、Standard Edition2でSPMを「制限付き」で利用できるようになりました。ただし本記事の内容は、Enterprise Editionを前提として記載しています。この制限に関しては、下記を参照ください。
Databaseライセンス情報ユーザー・マニュアル このマニュアル「SQL計画の管理」を参照
2. 本記事の作業の流れ
実際の作業手順の説明の前に、作業の流れを図で説明します。流れを理解いただくことで、実際の手順でやっていることがスムーズに理解できる、と思います。
まず、チューニング対象とするSQLを実行させます。この記事の例では、そのSQLが「インデックススキャン」というインデックスを利用する実行計画になっています。これを元のSQLはそのままで、「フルテーブルスキャン」で実行させるようにします。
実行させた、「元のSQL」の実行計画をSPM用に読み込みます。このあと同じSQLに対しては、SPM計画ベースラインに読み込まれて「enabled='YES'」になっている実行計画が適用されます。SPM計画ベースラインの中では、SQL文ごとにSQL_HANDLEという識別子が振られていて、その識別子に紐づく形で実行計画が記録されています。
ここの例では、SPM計画ベースラインに読み込んでいる「インデックススキャン」の実行計画は適用させたくないので、「enabled='NO'」とします。
「元のSQL」にフルテーブルスキャンをさせるためのヒント句を追加したSQLを実行します。もちろんフルテーブルスキャンで実行計画が生成されます。この実行計画を「元のSQL」に適用させたいわけです。
この図がポイントです。「ヒント句をつけてフルテーブルスキャンにさせた実行計画」を「元のSQL用」にSPM計画ベースラインに読み込ませます。ここで「元のSQL用」であることを、SPM計画ベースライン内のSQL識別子であるSQL_HANDLEで指定します。
最初と同じようにヒントがない「元のSQL」に対して、enabled='YES'であるSPM計画ベースラインの中の実行計画（フルテーブルスキャンになっている）が適用されます。
以上が、本記事で説明する手順の流れです。
3. 準備
本記事で説明する手順の準備として、テスト用のユーザーを作成し、テスト用のテーブルとデータを入力します。（とても小さいテストケースです）
まずOracle Databaseにsysで接続して下記を実行します。
CREATE USER spm_test_user identified by (パスワードを指定);
(実行結果)
User SPM_TEST_USERは作成されました。
GRANT CONNECT, RESOURCE TO spm_test_user;
(実行結果)
Grantが正常に実行されました。
ALTER USER spm_test_user QUOTA UNLIMITED ON users;
(実行結果)
User SPM_TEST_USERが変更されました。
GRANT SELECT_CATALOG_ROLE TO spm_test_user;
(実行結果)
Grantが正常に実行されました。
GRANT ADMINISTER SQL MANAGEMENT OBJECT to spm_test_user;
(実行結果)
Grantが正常に実行されました。
作成したspm_test_userというユーザーで接続して、テスト用のテーブルをデータを作成します。spm_test_tabという表を作成していて、pkというインデックスが作成されています。
CREATE TABLE spm_test_tab (
id NUMBER(2),
val VARCHAR2(10),
CONSTRAINT pk PRIMARY KEY ( id )
);
(実行結果)
Table SPM_TEST_TABは作成されました。
COL index_name format a20
SELECT
index_name
FROM
user_indexes
WHERE
table_name = 'SPM_TEST_TAB';
(実行結果)
INDEX_NAME
--------------------
PK
INSERT INTO spm_test_tab VALUES ( 1, 'aaa' );
(実行結果)
1行挿入しました。
INSERT INTO spm_test_tab VALUES ( 2, 'bbb' );
(実行結果)
1行挿入しました。
INSERT INTO spm_test_tab VALUES ( 3, 'ccc' );
(実行結果)
1行挿入しました。
COMMIT;
(実行結果)
コミットが完了しました。
本来は作成した表、インデックスに対して統計情報を取得すべきですが、本記事では不要なので取得していません（紹介していません）。ご了承を。
SPMを利用する作業の前に、SPM計画ベースラインに情報が入っていないか確認しておきます。
SELECT
sql_handle,plan_name,enabled,accepted,fixed,sql_text
FROM
dba_sql_plan_baselines
ORDER BY
sql_handle;
(実行結果)
行が選択されていません
ここの例では、SPM計画ベースラインに何も読み込まれていません。なにか出力された場合は、SPM計画ベースラインに読み込まれているものがあります。本記事で実行するSQL文のSPM計画ベースラインでなければ、本記事の実行はできます。SPM計画ベースラインの中身を削除するには下記の記事の内容が参考になります。
もしあなた以外の方が、このデータベースを利用していて、すでに読み込まれているSPM計画ベースラインがあれば、それは利用されているものかもしれません。削除すると大きな影響があるので簡単には削除しないでください。
もしこの記事で利用しているデータベースがあなた専用でテストのためだけに利用しているのであれば、このあとの作業のためにメモリ領域（SGAの共有プール領域）をクリアするとわかりやすくなります。クリアするにはデータベースにsysで接続して、下記のように実行します。この手順は実施しなくても、本記事の作業は進められます。
alter system flush shared_pool;
(実行結果)
System FLUSHが変更されました。
もしあなた以外の方が、このデータベースを利用しているのであれば、共有プールをクリアすると性能に影響があるので簡単には実施しないでください。
4. 元のSQL(ヒントなし)を実行してSPMに読み込む
まず、今回チューニング対象とするSQLを実行させて、その実行計画をSPM計画ベースラインに読み込ませます。SQL文中にコメントでtest_spmというキーワードを入れていますが、これはあとから検索しやすいようにするためです。
select /* test_spm */ val
from spm_test_tab
where id = 2;
(実行結果)
VAL
----------
bbb
このSQLを実行すると、下記のようにインデックススキャンで実行されていました。ここでは実行計画をSQL Developerの「自動トレース（autotrace）」を使って確認しています。
共有プールの中身をV$SQLを参照して確認します。SQL文が、「select」で始まって「test_spm」というキーワードが含まれるものを検索します。
COL sql_fulltext format a30
SELECT sql_id, plan_hash_value,sql_fulltext
FROM v$sql
WHERE sql_text like 'select%test_spm%';
(実行結果)
SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- ------------------------------
gyd7h3htj4u5k 2445271874 select /* test_spm */ val
from spm_test_tab
where id = 2
gyd7h3htj4u5k 2445271874 select /* test_spm */ val
from spm_test_tab
where id = 2
今回の記事の作業を実施していた中では、SQL_ID（SQL文）とPLAN_HASH_VALUE（実行計画）が同じエントリが2つ出てきました。同じSQL文と実行計画のエントリが2つあります。ここでは、このSQL文と実行計画（インデックススキャンを選択しています）の組み合わせを、SPM計画ベースラインに読み込ませます。
共有プールのカーソルキャッシュにある内容をSPM計画ベースラインに読み込ませるにはDBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEを利用します。下記のように実行します。
ここで、V$SQLを検索して確認しているSQL_IDの値（gyd7h3htj4u5k）を指定しています。
variable cnt number;
execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'gyd7h3htj4u5k');
(実行結果)
PL/SQLプロシージャが正常に完了しました。
SPM計画ベースラインの中身を確認します。次のように実行します。
col sql_handel FORMAT a20
col plan_name FORMAT a40
col sql_text FORMAT a40
SELECT
sql_handle,plan_name,enabled,accepted,fixed,sql_text
FROM
dba_sql_plan_baselines
ORDER BY
sql_handle
(実行結果)
SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ---------------------------------------- --- --- --- ----------------------------------------
SQL_ba62e00c6e03b17a SQL_PLAN_bnsr01jr07cbu824e5d90 YES YES NO select /* test_spm */ val
from spm_test_tab
where id = 2
読み込まれていることがわかります。この実行計画は採用させないので、enabled='NO'と変更します。dbms_spm.alter_sql_plan_baselineを利用します。先に確認したSQL_HANDLEとPLAN_NAMEの値を指定します。
variable cnt number;
execute :cnt := dbms_spm.alter_sql_plan_baseline( -
sql_handle => 'SQL_ba62e00c6e03b17a', -
plan_name => 'SQL_PLAN_bnsr01jr07cbu824e5d90', -
attribute_name => 'enabled', -
attribute_value => 'no' -
);
(実行結果)
PL/SQLプロシージャが正常に完了しました。
もう一度、SPM計画ベースラインの中身を確認して、enabled='NO'であることを確認しましょう。
SELECT
sql_handle,plan_name,enabled,accepted,fixed,sql_text
FROM
dba_sql_plan_baselines
ORDER BY
sql_handle
(実行結果)
SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ---------------------------------------- --- --- --- ----------------------------------------
SQL_ba62e00c6e03b17a SQL_PLAN_bnsr01jr07cbu824e5d90 NO YES NO select /* test_spm */ val
from spm_test_tab
where id = 2
5. ヒントを追加したSQLを実行してSPMに読み込む
元のSQLにヒント句を追加したものを実行して、SPM計画ベースラインに読み込ませます。ヒント句を追加したSQLを実行します。
select /* test_spm */
/*+FULL(spm_test_tab)*/
val
from
spm_test_tab
where
id = 2;
(実行結果)
VAL
----------
bbb
この例では/*+FULL(spm_test_tab)*/というヒント句を追加することで、spm_test_tabに対するフルテーブルスキャンを選択させます。実際にフルテーブルスキャンになっているか、SQL Developerから確認します。
spm_test_tabに対するフルテーブルスキャンになっていることがわかります。さきほどと同じように共有プールの中身をV$SQLを参照して確認します。
SELECT sql_id, plan_hash_value, sql_fulltext
FROM v$sql
WHERE sql_text like 'select%test_spm%';
(実行結果)
SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- ------------------------------
adw9q78zjsrrh 1107868462 select /* test_spm */
/*+FULL(spm_test_tab)*/
val
from
spm_test_tab
adw9q78zjsrrh 1107868462 select /* test_spm */
/*+FULL(spm_test_tab)*/
val
from
spm_test_tab
（ここでは「元のSQL」の情報はクリアしているのですが、最初に実行した「元のSQL」の結果も確認できるかもしれません。）
ヒント付きのSQL文に紐づいているSQL_IDとPLAN_HASH_VALUEを確認してください。ここでは同じ値のセットで2つあります。これを指定して、DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEでカーソルキャッシュからSPM計画ベースラインに読み込ませます。その際、**先に確認しているSPM計画ベースライン上の「元のSQL」のSQL_HANDLEの値を指定します。**ここが本記事のポイントです。
variable cnt number;
execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'adw9q78zjsrrh', -
plan_hash_value=>'1107868462', -
sql_handle=>'SQL_ba62e00c6e03b17a');
(実行結果)
PL/SQLプロシージャが正常に完了しました。
SPM計画ベースラインの中身を確認します。
SELECT
sql_handle,plan_name,enabled,accepted,fixed,sql_text
FROM
dba_sql_plan_baselines
ORDER BY
sql_handle;
(実行結果)
SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ---------------------------------------- --- --- --- ----------------------------------------
SQL_ba62e00c6e03b17a SQL_PLAN_bnsr01jr07cbu824e5d90 NO YES NO select /* test_spm */ val
from spm_test_tab
where id = 2
SQL_ba62e00c6e03b17a SQL_PLAN_bnsr01jr07cbub65c37c8 YES YES NO select /* test_spm */ val
from spm_test_tab
where id = 2
1つSPM計画ベースラインが追加されていることがわかります。もともとenabled='NO'のものがありましたが、enabled='YES'のものが追加されているのです。同じSQL_HANDLEの値(ここの例ではSQL_ba62e00c6e03b17a)に対して2つの登録があります。(２つはPLAN_NAMEは異なり、実行計画は異なります）
enabled='YES'となっている新しく追加された実行計画が適用されるはずです。
6. 元のSQLを実行してSPMの実行計画が適用されていることを確認する
最初と同じように「（ヒント句をつけてない）元のSQL」を実行して、実行計画を確認してみます。
select /* test_spm */ val
from spm_test_tab
where id = 2;
SPM計画ベースラインに登録されてenabled='YES'となっている方の実行計画（フルテーブルスキャン）が採用されていることを確認できます。
以上で、本記事で説明する手順は終了です。
7. 参照するとよいドキュメント
オプティマイザの実行計画の選択に影響するOracle Databaseの機能は、SPMだけではありません。下記のドキュメントは実行計画に影響するOracle Databaseの他の機能とSPMが相互作用する仕組みについて説明しています。SPMに関して、より詳細を知ることができるので、使いこなすためには参照するとよいです。
第1回 Oracle Database 12c R2新機能でSQL Plan Managementを"賢く"使い倒せ！
Oracle Database 19cのSQL計画管理 Oracleホワイト・ペーパー / 2019年 3 月 13 日
SPMの活用方法は下記の記事もあります。
Oracle DatabaseのSQL Plan Management(SPM)とSQL Tuning Set(STS)の活用：データベースのアップグレード（バージョンアップ）でのSQL実行計画の変動を制御する
SQLのヒントの書き方に関しては、さまざまな情報がありますが、下記が参考になります。
Oracle Database SQL言語リファレンス 19c:ヒント
津島博士のパフォーマンス講座 第55回 オプティマイザ・ヒントについて