Oracle12.2でSPMを使用して実行計画を固定化する

はじめに

Oracleを利用していて、統計情報の変化により実行計画が変わり稀にSQLの性能が劣化することがあります。その場合は元の実行計画を利用するように設定する対応がとられることが多いかと思います。
その場合に実施する対応としては、主に以下の2つがあります。
他にも統計情報を固定しても良いかもしれませんが、他のSQLにも影響が出るので基本は以下の2つで良いかも思います。

• SQLにヒント句を入れて、意図した実行計画が利用されるようにする。
• SQL Plan Management（SPM）を利用して、実行計画を固定する。

今回はSPMを利用した方法を試してみます。前提条件は以下のとおりとします。

• Oracle 12.2(12cR2)を利用する。
• 本番環境では元の実行計画が残っているか分からないため、開発環境の実行計画をインポートした上で固定する。

準備

テスト用のテーブルとデータを準備します。作成したのはSPM_TESTテーブルで、ダミーデータを1万レコード投入しています。
1万レコードを入れているのは、インデックス付与した場合にインデックススキャンが選択されるようにするためです。

create table spm_test (id number, cdate timestamp, contents varchar2(30));

insert into spm_test select rownum, to_date('2019-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 10000);
commit;

条件句にid=1を指定し、1万件から1件だけ選択される検索を実行します。

select * from spm_test where id = 1;

実行後、v$#sqlareaからSQL_IDとPLAN_HASH_VALUEを取得します。

SQL> set linesize 500
SQL> column sql_text format a40
SQL> column sql_id format a20
SQL> column plan_hash_value format 999999999999
SQL> select sql_text, sql_id, plan_hash_value from v$sqlarea where sql_text like 'select * from spm_test where id%';

SQL_TEXT                                 SQL_ID               PLAN_HASH_VALUE
---------------------------------------- -------------------- ---------------
select * from spm_test where id = 1      fxrcn142uggtr              278342638

先ほど取得したSQLのSQL_IDとPLAN_HASH_VALUEをdbms_spm.load_plans_from_cursor_cacheファンクションに指定し、先ほどのSQLの実行計画を計画ベースラインにロードします。
DBMS_SPMパッケージはDBA権限を持つユーザで実行します。
もしくは実行ユーザにADMINISTER SQL MANAGEMENT OBJECT権限を付与します。
※注：昔の性能が良かった時の実行計画か、ヒント句をつけて改善した実行計画を計画ベースラインにロードします。

• SQL_ID: fxrcn142uggtr
• PLAN_HASH_VALUE: 278342638

var res number
exec :res :=dbms_spm.load_plans_from_cursor_cache( sql_id => 'fxrcn142uggtr', plan_hash_value => '278342638');

SQLの実行計画が計画ベースラインにロードされたことを確認する。
ロードされた実行計画はdba_sql_plan_baselinesに格納されています。

column sql_handle format a30
column plan_name format a35
column created format a30
column last_modified format a30
select sql_handle, sql_text, plan_name from dba_sql_plan_baselines;

SQL_HANDLE                     SQL_TEXT                                 PLAN_NAME
------------------------------ ---------------------------------------- -----------------------------------
SQL_e507783239314071           select * from spm_test where id = 1      SQL_PLAN_fa1vs68wm2h3jf2fc655a

実行の結果、先ほどのSQL（select * from spm_test where id = 1）が格納されていることが確認できました。

SQL_HANDLE, PLAN_NAMEはこれから使用するのでメモしておきます。

• SQL_HANDLE: SQL_e507783239314071
• PLAN_NAME: SQL_PLAN_fa1vs68wm2h3jf2fc655a

実行したSQLが自動でキャプチャーされないようにロードしたベースラインを固定（FIXED=YES）しておきます。
FIXED=NOの場合、登録されているベースラインよりもコストが低い実行計画が見つかると、ベースラインに登録されてしまいます。

ここ（ALTER_SQL_PLAN_BASELINEファンクション）には以下のように記載されています。

• 'YES'にすると、時間が経過してもSQL計画ベースラインは進化しません。固定された計画は、固定されていない計画より優先されます。

var res number
exe :res := dbms_spm.alter_sql_plan_baseline(-
sql_handle =>'SQL_e507783239314071',-
plan_name =>'SQL_PLAN_fa1vs68wm2h3jf2fc655a',-
attribute_name => 'FIXED',-
attribute_value => 'YES');

ここで実行計画を取得してみると、以下のようになります。
インデックスをつけていないので、TABLE ACCESS FULL（フルスキャン）となっています。
本来なら

set serveroutput off
select * from spm_test where id = 1;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fxrcn142uggtr, child number 0
-------------------------------------
select * from spm_test where id = 1

Plan hash value: 278342638

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |    19 (100)|          |
|*  1 |  TABLE ACCESS FULL| SPM_TEST |     1 |    43 |    19   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------

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

   1 - filter("ID"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline SQL_PLAN_fa1vs68wm2h3jf2fc655a used for this statement

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


23 rows selected.

次にインデックスを付与してみます。本来ならINDEX SCANになるはずですが、SPMで固定化しているためフルスキャンの実行計画が使用されるはずです。

create index idx_spm_test01 on spm_test(id); 

SQL> select * from spm_test where id = 1;

        ID CDATE                                                                      CONTENTS
---------- --------------------------------------------------------------------------- ------------------------------
         1 01-JAN-19 12.00.00.000000 AM                                               TEST DATA1234567890123456789

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fxrcn142uggtr, child number 2
-------------------------------------
select * from spm_test where id = 1

Plan hash value: 278342638

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |    19 (100)|          |
|*  1 |  TABLE ACCESS FULL| SPM_TEST |     1 |    44 |    19   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------

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

   1 - filter("ID"=1)

Note
-----
   - SQL plan baseline SQL_PLAN_fa1vs68wm2h3jf2fc655a used for this statement


22 rows selected.

インデックスを作成しても、TABLE ACCESS FULLとなっています。
以下の記述から、SPMが利用されていることが分かります。

"SQL plan baseline SQL_PLAN_fa1vs68wm2h3jf2fc655a used for this statement"

次にベースラインを使用しないように設定してみます。次のようにセッション内でパラメータを変更します。

• optimizer_use_sql_plan_baselines = false

その場合、以下のようにINDEX RANGE SCANが選ばれた実行計画となります。パラメータ変更前と違って、"SQL plan baseline SQL_PLAN_fa1vs68wm2h3jf2fc655a used for this statement"と表示されていないことが分かります。

SQL> alter session set optimizer_use_sql_plan_baselines = false;

Session altered.

SQL> select * from spm_test where id = 1;

        ID CDATE                                                                      CONTENTS
---------- --------------------------------------------------------------------------- ------------------------------
         1 01-JAN-19 12.00.00.000000 AM                                               TEST DATA1234567890123456789

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fxrcn142uggtr, child number 1
-------------------------------------
select * from spm_test where id = 1

Plan hash value: 4279793498

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |       |       |    2 (100)|                                                                                                                                                                                                   |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST       |     1 |    44 |    2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN                  | IDX_SPM_TEST01 |     1 |       |    1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   2 - access("ID"=1)


19 rows selected.

別環境に実行計画を移行する

先ほどまでの手順で計画ベースラインに実行計画がロードできたことを確認できました。
次は別環境に実行計画を移行するため、以下の手順を実行してきます。

1. （移行元）実行計画をSPMにロードする
2. （移行元）計画ベースラインをエクスポートするためのテーブル（ステージング表）を作成する
3. （移行元）ステージング表に計画ベースラインをエクスポートする
4. （移行元）expdpでステージング表をダンプする
5. （移行元→移行先）ダンプファイルを移行先へコピーする（FTP、SCPなど）
6. （移行先）impdpでダンプファイルからステージング表をインポートする
7. （移行先）ステージング表から、計画ベースラインをインポートする

1はこれまでの手順で実行済みですので、2の手順から続きを実行していきます。

（移行元）計画ベースラインをエクスポートするためのテーブル（ステージング表）を作成する

• CREATE_STGTAB_BASELINEプロシージャ

exec DBMS_SPM.CREATE_STGTAB_BASELINE (-
    table_name =>'STG_BL',-
	table_owner =>'test01',-
	tablespace_name=>'users'-
);

（移行元）ステージング表に計画ベースラインをエクスポートする

• PACK_STGTAB_BASELINEファンクション

var res number
exec :res := DBMS_SPM.PACK_STGTAB_BASELINE(table_name =>'STG_BL',-
  table_owner => 'test01',-
  sql_handle =>'SQL_e507783239314071', -
  plan_name =>'SQL_PLAN_fa1vs68wm2h3jf2fc655a');

（移行元）expdpでステージング表をダンプする

手順は省略

（移行元→移行先）ダンプファイルを移行先へコピーする（FTP、SCPなど）

手順は省略

（移行先）impdpでダンプファイルからステージング表をインポートする

手順は省略

（移行先）ステージング表から、計画ベースラインをインポートする

• UNPACK_STGTAB_BASELINEファンクション

var res number
exec :res := DBMS_SPM.UNPACK_STGTAB_BASELINE(-
  table_owner => 'test01',-
  table_name =>'STG_BL',-
  sql_handle =>'SQL_e507783239314071');


-- 計画ベースラインを検索
set linesize 300
column sql_handle format a30
column plan_name format a35
column sql_text format a40
select sql_handle, sql_text, plan_name from dba_sql_plan_baselines;


SQL_HANDLE                     SQL_TEXT                                 PLAN_NAME
------------------------------ ---------------------------------------- -----------------------------------
SQL_e507783239314071           select * from spm_test where id = 1      SQL_PLAN_fa1vs68wm2h3jf2fc655a

検索して、実行計画で計画ベースラインが使用されフルスキャンになっていることを確認します。
なお、SPM_TESTテーブルはあらかじめインデックスを作成しておき、ベースラインをインポートする前はインデックススキャンが使用されていることを確認しています。

SQL> select * from spm_test where id = 1;

        ID CDATE                                                                      CONTENTS
---------- --------------------------------------------------------------------------- ------------------------------
         1 01-JAN-19 12.00.00.000000 AM                                               TEST DATA1234567890123456789

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fxrcn142uggtr, child number 2
-------------------------------------
select * from spm_test where id = 1

Plan hash value: 278342638

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |    19 (100)|          |
|*  1 |  TABLE ACCESS FULL| SPM_TEST |     1 |    44 |    19   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------

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

   1 - filter("ID"=1)

Note
-----
   - SQL plan baseline SQL_PLAN_fa1vs68wm2h3jf2fc655a used for this statement


22 rows selected.

計画ベースラインを削除する

計画ベースラインが不要となった場合は、DBMS_SPM.DROP_SQL_PLAN_BASELINEファンクションを使用して削除することができます。
plan_nameのデフォルトはNULLで、sql_handleで紐づくSQLの全ての実行計画が削除されます。

• DROP_SQL_PLAN_BASELINEファンクション

var res number
exec :res :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_e507783239314071',plan_name=>'SQL_PLAN_fa1vs68wm2h3jf2fc655a');

最後に補足

• 今回の手順では、SQL文のカーソル・キャッシュから計画ベースラインへロードしました。12.2からは加えてDBMS_SPM.LOAD_PLANS_FROM_AWRファンクションを使用してAWRからも簡単にロードすることができるようになっています。なお、12.1まではSQL Tuning Set（STS）を利用することでAWRからロードできますが、Tuning Packライセンスが必要です。
• SPMを使用するにはEnterprise Editionライセンスが必要となります。ただし、18cからはStandard EditionでもSPMの一部機能が利用できるようです。以下参照。

Oracle Database 18cからStandard Editionでも実行計画固定のためにSQL Plan Management（SPM）の一部機能が利用できるようになりました。
https://www.ashisuto.co.jp/db_blog/article/201809_execution_plans.html

• SPMを使用してSQLの実行計画を固定化しましたが、SQLが変更されると当然ですが無効になります。SQLを変更する際はヒント句を付与するなど、実行計画が変わらないように注意しましょう。
• 計画ベースライン等のデータはSYSAUX表領域に保存されます。

参考

• Oracle Database 12cリリース2 DBMS_SPM
• Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス
  12c リリース1 (12.1) 148 DBMS_SPM
• SPM 計画ベースラインを移行する方法
• SPMで実行計画を固定する方法
• Loading sql plans into SPM from AWR