概要
Oracle Databaseのアップグレード（バージョンアップ）において、SQLの性能の変動の原因となりうることの1つに実行計画に変動が考えられます。
アップグレードによってSQLの実行計画に変動があることだけなら問題というわけではありません。新機能（オプティマイザの動作が少し変わったというレベルのものもあります）により実行計画が変わることは十分考えられます。実行計画の変動で、性能がよくなったり、性能の劣化があっても許容できる範囲であれば、実行計画を制御する必要はないと考えられます。
一方、実行計画の変動により許容できない性能の劣化が発生することも考えられます。そのようなケースでは、なんらかの対策（チューニング）が検討されます。対策の1つとして「SQLの実行計画を前のバージョンで利用されていた計画になるように制御する」ということが考えられます。
実行計画を制御する方法もいくつか考えられます。SQL Plan Management(SPM)はその1つです。SPMに関しては下記の記事が参考になります。本記事の内容はSPMがメインなので、前提知識として下記記事を参照してどのような機能か理解いただくとよいです。
第1回 Oracle Database 12c R2新機能でSQL Plan Managementを"賢く"使い倒せ！
SPMの考え方は、SQLとその実行計画の組み合わせを「計画ベースライン」としてデータベースに登録しておき、そのSQLの実行時は、動的に生成された実行計画ではなく、計画ベースラインとして「登録されている」実行計画を採用させることで、実行計画を制御しようというものです。
(※)計画ベースラインは、実際には実行計画を固定化するために使用される内部的なヒントの集合です。すべてのSQLで実行計画を固定できるわけではありません。
SQL Tuning Set(STS)は、あるデータベース上で実行されたSQLの実行時の情報（実行計画や性能に関するデータ）を記録したものです。STSに含まれているSQLと実行計画の組み合わせをSPMの計画ベースラインとして読み込むことができます。STSに関しては下記が参考になります。
Oracle DatabaseのSQL Tuning Set(STS)の活用：STSへSQL実行時の情報を記録する
アップグレードの際、下位のバージョンで取得したSTS上にあるSQLと実行計画の組み合わせを、上位のバージョンで計画ベースラインに読み込ませておいて、SPMを利用してアップグレードに伴う実行計画の変動を制御できます（すべてのSQLで有効とは限りません）。これはアップグレードに伴うSQL性能問題の対策の1つとなります。本記事はSPMとSTSを組み合わせて利用する方法に関して説明します。
2020年1月ごろの情報・環境を元に本記事は記載しています。
1. 本記事の作業の前提
本記事で紹介しているシナリオでは、Oracle Database 11g R2(11.2.0.4)からOracle Database 19cへアップグレードすることを想定しています。まず11g R2の環境で取得したSTSがあります。そのSTSから19cで実行計画に変動があり対策が必要と考えられるSQLを抽出したSTSを作っています。そのSTSのSQLに関して19c上でSPMを利用して実行計画の変動を制御してみます。
実行するには下記が必要です。
- Oracle Database EEがインストールされている19cの環境のOracle Databaseインスタンス
- SQLの実行環境(SQL*PlusやSQL Developer)
- 実行計画を制御する対象とするSTS(11g R2の環境で取得したものから抽出したもの)
本記事の例では、11g R2におけるいくつかのSQLと実行計画がSTSに記録されており、そのSQLを19cで実行するときの実行計画と比較するにあたり、Real Application Testing(RAT)のSQL Performance Analyzer(SPA)を実行させて、その結果を紹介しています。RATのSPAの利用方法の説明は本記事の目的ではありません。下記が参考になります。
Oracle Real Application TestingのSQL Performance Analyzerの活用：データベースのアップグレード（バージョンアップ）でのSQLテスト
「上記の必要なもの」にはRATオプションが必要であることを記載していませんが、2つのバージョンの実行計画の比較のためにSPAを利用する場合は、当然RATオプションが必要になります。STSとSPMを利用するだけであれば、オプションのないOracle Database EEで利用できます。
19cのデータベースはマルチテナント環境が多いと考えられますが、テスト用に1つのPDBを用意しています。STSは19cのPDB環境にコピーしておきます。テストのためのSQL群がアクセスするスキーマ（表など）とデータも19cのPDBに入れておきます。統計情報も取得しておきます。
2. 本記事の作業の流れ
本記事の作業の流れを3つのステップごとに説明します。最初に、対象としたSQLが含まれるSTSに関して、STSに記録しされている11g R2での実行計画と19cで実行した際の実行計画を比較します。本記事の例ではSQLは4つあり、4つとも11g R2と19cでは実行計画が異なります。（実行計画が異なるSQLを抽出しています。）
2つ目のステップが本記事のメインで、STSにあるSQLと実行計画の組み合わせを、SPMの計画ベースラインに読み込ませます。
3つ目のステップは確認作業です。実施する作業は1つ目と同じです。SPMにより計画ベースラインに登録されている実行計画が利用されていれば、STSに記録されている実行情報と同じものでSQLが実行されることになります。
本記事の例は下記の記事の続きになっています。
Oracle Real Application TestingのSQL Performance Analyzerの活用：データベースのアップグレード（バージョンアップ）でのSQLテスト
こちらの記事の最後の方（「5.6. (参考)STSからSQL_IDを指定してSQLを抽出する」）で抽出したSTS03という名前のSTSを例にして、本記事の作業を実施しています。実行例で実施しているSQLはすべてspaadminというSPAを利用するために作成してDBAロールを付与したユーザーで実施しています。
3. STSに記録されている実行計画とSQLをテストした際の実行計画の比較（RATのSPA機能の活用）
11g R2で取得したSTSから、実行計画に変動がありアプリケーションから発行されたSQL群として抽出されたSTS（STS03という名前です)を、本記事の中では利用しています。STS03の中身に関して下記のように確認します。
col sql_text FORMAT a40
select
SQL_ID,
PLAN_HASH_VALUE,
SQL_TEXT
FROM
DBA_SQLSET_STATEMENTS
WHERE
SQLSET_OWNER = 'SOE' and
SQLSET_NAME = 'STS03'
ORDER BY
SQL_ID;
(実行結果)
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
0y1prvxqc2ra9 1430386540 SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
1qf3b7a46jm3u 797201223 SELECT ORDER_ID, LINE_ITEM_ID, PRODUCT_I
D, UNIT_PRICE, QUANTITY,DISPATCH_DATE, R
7hk2m2702ua0g 2416436141 WITH NEED_TO_PROCESS AS (SELECT ORDER_ID
, CUSTOMER_ID FROM ORDERS WHERE ORDER_ST
c13sma6rkr27c 670080582 SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
c13sma6rkr27c 3101473678 SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
SQL文は4種類ですが実行計画は5種類あることがわかります。SQL_IDがc13sma6rkr27cであるSQLに関しては2種類の実行計画がSTSに記録されています。STSを取得した11g R2の環境で本SQLを実行したとき(複数回実行されてます)、2種類の実行計画が生成されていた、と考えられます（理由はいくつか考えられますが、ここでは深堀しません）。このことは、本記事ののちの実行例の結果の考察に影響するので重要です。
このSTS03を使って、STS内にある実行計画と19cで実行させたときの実行計画を比較した結果を紹介します。詳細な手順は上記で紹介済みの記事「Oracle Real Application TestingのSQL Performance Analyzerの活用：データベースのアップグレード（バージョンアップ）でのSQLテスト」をご確認ください。SPAタスクの実行の際2回の試行は下記のような設定で実施しています。
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK03',
execution_type => 'CONVERT SQLSET',
execution_name => 'first trial'
);
END;
/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPATASK03',
execution_type => 'TEST EXECUTE',
execution_name => 'second trial'
);
END;
/
1回目の試行ではexecution_type => 'CONVERT SQLSET'としてSTSから情報を取り出しています。2回目の試行では接続している19cのデータベースに対してexecution_type => 'TEST EXECUTE'として実行させています。その2つの試行に関して比較レポートが次のようになりました（レポートの抜粋です）。
赤枠のところで確認できるように、4つのSQLに関してすべて実行計画が変わっています。これは、そのようにSQLを抽出しているので当然です。
4. STSに記録されている実行計画をSPMの計画ベースラインに読み込む
この章が本記事のメインです。STSとSPMを組み合わせて利用します。この章の作業に関しては、RATオプションは不要でEEであれば実施できます。
4.1. SPMの計画ベースラインの中を確認する
最初にSPM関連のパラメータを確認しておきます。
show parameters sql_plan
(実行結果)
optimizer_capture_sql_plan_baselines FALSE
optimizer_use_sql_plan_baselines TRUE
2つのパラメータともにデフォルトになっており、これで本記事の作業は実施できます。
optimizer_use_sql_plan_baselinesがTRUEになっていれば計画ベースラインに登録されている実行計画を利用します。これがFALSEの場合はSPMは動作しないので、動作させるためにはALTER SYSTEM文などでTRUEに設定してください。optimizer_capture_sql_plan_baselinesがFALSEでも、STSから計画ベースラインに実行計画を登録することができます。
先にSPMの計画ベースラインの中身を確認しておきます。ここでの実行例ではまだ何もありません。検索結果がある場合、すでに計画ベースラインにいくつかのSQLに関して登録されています。計画ベースラインの中身を空にする方法は、この章の最後に紹介します。
SELECT
sql_handle,plan_name,enabled,accepted,fixed,sql_text
FROM
dba_sql_plan_baselines
ORDER BY
sql_handle
(実行結果)
行が選択されていません
4.2. STSに記録されている実行計画をSPMの計画ベースラインに読み込む
STSに記録されている実行計画をSPMの計画ベースラインに読み込みます。次のように実行します。
DECLARE
v_plan_cnt PLS_INTEGER;
BEGIN
v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_owner => 'SOE',
sqlset_name => 'STS03',
fixed => 'YES'
);
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
DBMS_SPM.LOAD_PLANS_FROM_SQLSETをSTS名とオーナーを指定して実行します。fixed => 'YES'と指定することで、計画ベースラインに固定された実行計画として読み込みます。
ここでの実行例では指定したSTS03のすべてを読み込んでいますが、basic_filterを利用してさまざまな条件でSTSからSPMの計画ベースラインへ読み込むこともできます。下記が参考になります。
Oracle Database SQLチューニング・ガイド 19c:28.4.4 SQLチューニング・セットからの計画のロード
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c:164.3.1 SQLSET_ROWオブジェクト・タイプ
Oracle Database SQLチューニング・ガイド 19c:23 SQLチューニング・セットの管理
4.3. 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_7aa3962c9ae2db34 SQL_PLAN_7p8wq5kdf5qtna4fc17b5 YES YES YES WITH NEED_TO_PROCESS AS (SELECT ORDER_ID
, CUSTOMER_ID FROM ORDERS WHERE ORDER_ST
SQL_97036e1ea811e28c SQL_PLAN_9f0vf3un13snc1e026c42 YES YES YES SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
SQL_f11c011b65257c41 SQL_PLAN_g27013dkkaz21f61e851f YES YES YES SELECT ORDER_ID, LINE_ITEM_ID, PRODUCT_I
D, UNIT_PRICE, QUANTITY,DISPATCH_DATE, R
SQL_fdf0214a24814e13 SQL_PLAN_gvw1198k82mhmc9bf7f17 YES YES YES SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
SQL_fdf0214a24814e13 SQL_PLAN_gvw1198k82mhme24fca53 YES YES YES SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
5つの「SQLと実行計画の組み合わせ」が実行計画ラインに読み込まれています。ここでSQL_HANDLEがSQL_fdf0214a24814e13であるものが2つあることがわかります。STS03に記録されていた通りに4つのSQLと5つの実行計画が読み込まれています。
5つの「SQLと実行計画の組み合わせ」ともにenabledとacceptedがYESです。SPMによりenabledとacceptedがYESである「SQLと実行計画」の組み合わせが利用されます。ここではSQL_HANDLEがSQL_fdf0214a24814e13である1つのSQLに対して、2つの実行計画がともにenabledとacceptedがYESとなっています。このような場合に該当するSQLが実行されるときは、どちらの実行計画が採用されることになります。
計画ベースラインの登録内容を変更して（どちらか1つをenabled=NOとして）、明示的に実行計画の選択を制御することができます。その方法に関しては次の章で紹介します。ここではこのまま進めます。
4.4. (参考)SPMの計画ベースラインの中を削除する
ここの実行例では計画ベースラインに登録されたまま次の章に進みます。計画ベースラインを空にしたい場合は、次のように実行します。（ここでの実行例ではこの作業は実施していません）
DECLARE
i NATURAL;
cursor cur_spm is
select distinct sql_handle from dba_sql_plan_baselines;
BEGIN
FOR r_spm IN cur_spm LOOP
i := dbms_spm.drop_sql_plan_baseline(r_spm.sql_handle);
END LOOP;
END;
/
(実行結果)
PL/SQLプロシージャが正常に完了しました。
計画ベースラインが空になっていることは下記のようにして確認できます。
SELECT
sql_handle,plan_name,enabled,accepted,fixed,sql_text
FROM
dba_sql_plan_baselines
ORDER BY
sql_handle
(実行結果)
行が選択されていません
5. 再度STSに記録されている実行計画とSQLをテストした際の実行計画の比較
計画ベースラインに読み込まれた効果を確認するために、先ほどと同じようにSPAを使ってSQLをテストして実行計画を比較してみます。SPAの比較レポートは次のようになりました。
さきほどは4つのSQLの実行計画がすべて変動していましたが、1つを除き同じ実行計画とレポートされています。SPMで実行計画を制御できていることは確認できました。
実行計画が異なるとレポートにある1つのSQLはsql_idがc13sma6rkr27cのものです。最初にSTS03の中身を確認したときに、このsql_idのSQLは実行計画が2つ記録されていました。それはそのまま計画ベースラインに読み込まれています。その影響で実行計画に変動があったとレポートされています。確認してみます。
該当するSQLに関して、より詳細なレポートを確認すると下のようになっています（抜粋です）。
赤枠で囲ったところにあるように、SPMにより計画ベースラインに登録さているPLAN_NAMEがSQL_PLAN_gvw1198k82mhme24fca53の実行計画が利用されています。ただSTSに記録されている「同じSQLの2つの実行計画の比較対象」のうちもう一つの方と比較されてレポートされています。
該当するSQLのSTSに記録されている2つの実行計画に関しては、元々STSが取得された11g R2の環境ではどちらも利用されていたものと考えられます。「実行計画の変動を制御する」という点だけだと、19cでどちらを利用してもよいかもしれません。実際には性能のより良いものにした方がよいかもしれませんが。2つの違いは気にするほどの差ではないかもしれません。
このような場合に、選択される実行計画を明示的に指定するには、計画ベースラインで利用しない実行計画に関してenabledをNOと変更します。次のように実行します。
DECLARE
rc INTEGER;
BEGIN
rc := dbms_spm.alter_sql_plan_baseline(
plan_name => 'SQL_PLAN_gvw1198k82mhme24fca53',
attribute_name => 'enabled',
attribute_value => 'no'
);
end;
(実行結果)
PL/SQLプロシージャが正常に完了しました。
計画ベースラインの該当する実行計画に関して、enabledがNOになっていることを確認します。
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_7aa3962c9ae2db34 SQL_PLAN_7p8wq5kdf5qtna4fc17b5 YES YES YES WITH NEED_TO_PROCESS AS (SELECT ORDER_ID
, CUSTOMER_ID FROM ORDERS WHERE ORDER_ST
SQL_97036e1ea811e28c SQL_PLAN_9f0vf3un13snc1e026c42 YES YES YES SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
SQL_f11c011b65257c41 SQL_PLAN_g27013dkkaz21f61e851f YES YES YES SELECT ORDER_ID, LINE_ITEM_ID, PRODUCT_I
D, UNIT_PRICE, QUANTITY,DISPATCH_DATE, R
SQL_fdf0214a24814e13 SQL_PLAN_gvw1198k82mhmc9bf7f17 YES YES YES SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
SQL_fdf0214a24814e13 SQL_PLAN_gvw1198k82mhme24fca53 NO YES YES SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME
, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIG
変更されたことが確認できました。先ほどと同じようにSPAを使ってSQLをテストして実行計画を比較してみます。SPAの比較レポートは次のようになりました。
4つのSQLがすべて同じ実行計画になったとレポートされています。先ほどと同じくレポートのsql_idがc13sma6rkr27cのSQLに関する詳細を確認します。
今度は、計画ベースラインからもう一つの実行計画（PLAN_NAMEがSQL_PLAN_gvw1198k82mhmc9bf7f17のもの）が利用されていることがわかります。
6. (参考)SPMによる計画ベースラインに登録されている実行計画が利用できなかった場合
前章でSPAでテストした結果のレポートを確認すると、SPMが動作している場合は下記のような出力がされていました。
Notes -
SQL plan baseline "SQL_PLAN_9f0vf3un13snc1e026c42" used for this statement
下記のような出力があったときは、計画ベースラインに登録されている実行計画があるが利用できなかった場合です（原因はいくつか考えられます）。
- baseline_repro_fail = yes
7. 参照するとよいドキュメント
オプティマイザの実行計画の選択に影響するOracle Databaseの機能は、SPMだけではありません。下記のドキュメントは実行計画に影響するOracle Databaseの他の機能とSPMが相互作用する仕組みについて説明しています。SPMに関して、より詳細を知ることができるので、使いこなすためには参照するとよいです。
Oracle Database 19cのSQL計画管理 Oracleホワイト・ペーパー / 2019年 3 月 13 日