SPMって使ってる？ - ASMのきもち

この記事は、JPOUG Advent Calendar 2022 18日目の記事です。
昨日はmultilayerさんのAutonomous DatabaseのSLAを99.995%にしてみた！の記事でしたね！
タイトルからもう、強つよですよね…！！Autonomous Data Guardは現在営業さんからおすすめされているのですが。本当にボタンポチで使えちゃうんだ…スイッチオーバーいいな…なんて、DR/BCPを検討している私には、非常に胸アツな記事でした。

さて、仕事では主にRDS for Oracleを使っており、AWS界隈では割とマイナー道を突き進んでおります。
先日RDS for Oracleでは新しいSSDストレージ「gp3」というのが出まして。
これ面白いんですよ、IOPSとスループットが可変なんです！これはそれぞれの性能でsqlldrやったら楽しそう！と思い記事にしようと思ったんですが…

AWSのサービス条件

貴社は以下を行うことはできません。
・Oracle ソフトウェアまたは Oracle ソフトウェアにおける所有権の他の個人または法人への譲渡、付与、または権利移転。貴社が Oracle ソフトウェアにおける担保権を付与しようとした場合も、担保を得た当事者は Oracle ソフトウェアを使用または移転する権利を有しません。
・Oracle ソフトウェアを、レンタル、タイムシェア、サブスクリプションサービス、ホスティング、外注に使用すること。
・Oracle またはそのライセンサーの所有権通知を除去または変更すること。
・Oracle ソフトウェアを、何らかの形で第三者の事業運営のために第三者に利用可能にすること。
・Oracle ソフトウェアの複製、リバースエンジニアリング（相互運用性のために法に求められる場合を除く）、逆アセンブル、逆コンパイルを行うこと（Oracle ソフトウェアが生成したデータ構造や同様の資料を検討することを含む）。
・Oracle ソフトウェア上で実行したベンチマークテスト結果を公開すること。

だめでした。

ということで、別話題に変更！
SPMについて簡単に説明＆実はAWRから実行計画を引き抜けるんだよ！ということを軽くご紹介させていただこうと思います。
注：詳細な手順はMy Oracle Support（MOS）に記載されておりますので、ここではその一部だけ少し書くだけに留めさせていただこうと思います。正しい手順は必ずMOSの内容をご参照ください。また、このブログのSQLの動作確認はOracleDB19cが前提になります。

Agenda

SPMってそもそも何だっけ？
SPMを使う上での注意点
SPMで実行計画を固定化する手順
AWRから実行計画を抜き出してSPMで実行計画を固定化

SPMってそもそも何だっけ？

皆さんの中には、実行計画が意図せず変わってしまいパフォーマンス影響が出てしまい、処理が終わらない！性能劣化が！なんていうご経験ありませんか。
そんなときに助けてくれるOracleDBの便利機能、それがSPMです。
正式名は「SQL Plan Management」略して SPM になります。
端的に言うとSQL実行計画を管理する機能で、このSQL IDは絶対、この実行計画で動いてね！！　という機能になります。

仮に、SQL ID 1234567890abc が、今まで 1234567890 の実行計画（PLAN_HASH_VALUE）であったのに対し、統計情報更新等で急に 0987654321 になったシナリオを例とします。
それまでSQL ID 1234567890abc は実行計画 1234567890 で30秒で実行できていたのですが、実行計画 0987654321 で10分経っても過処理が終わらない！なんてことが、重要な処理で発生したら。バッチ処理なら後続処理が遅れて、場合によっては事業影響大。オンライン処理なら場合によってはシステムダウンと判定されるレベルかもしれないです。
そんな時、SPMが助けてくれます！
SPMに「SQL ID 1234567890abc は実行計画 1234567890 で絶対動いて！！」と設定すると、その瞬間からSQL ID 1234567890abc は実行計画 1234567890 を用いて実行し、元の30秒の性能で動き始めます。
な、なんて便利な機能…！恐ろしく便利な機能ですね、OracleDBさん！
詳細は津島博士のパフォーマンス講座　第75回　SQL Plan Managementについてを是非読んでみてください！
OracleDB 19の新機能の説明も見どころですよ。

SPMを使う上での注意点

この機能は本当に便利なのですが、ご利用の際は以下の点を注意＆考えて使っていただきたいなと思っています。

1. SQL IDをKEYとして実行計画を固定化するため、SQL IDが変わった場合はこの設定は実質「無効化」されます

この話の前に、ちょっとSQL IDについておさらい。
そもそもSQL IDってどう算出されているかというと、実行されているSQL文をMD5で変換した末尾の8バイト、なんですよね。
ではSQL IDがどのタイミングで変わるかというと「実行されるSQL文が1バイトでも変わったら」になります。
例えば半角空白1つでも入ると、別SQLになったとOracleDBは受け取ります。条件(where)がリテラルの場合、その値が変わると別SQLと受け取ります。
逆にバインド変数の場合は、変数の値が何であれ同一のSQLともなされます。

ということで、実行計画を固定化したまま動かしたくない場合は、SQL IDが変わらないように実行する際一切変更しないこと。（該当SQLの開発を凍結するイメージがよいかと）
変更する場合は、必ず同時に変更後のSQL IDに対し実行計画を固定化する等を検討してください。

2. 実行計画を固定化をDBサーバー側で固定化するのがそもそも良いのか

そもそも何故実行計画が意図せず変わってしまうのかという話は、それだけで記事が書けてしまうほど深い議題です。
一般的にはテーブルの論理設計の問題（似たようなINDEXを使いすぎている等）と言われていますが、データのカーディナリティが起因するなど、原因は多岐に渡ります。
そのため、全ての場合において「この課題を解決したらいい」というものではないのは、皆さんご存知かと思います。
この点はオプティマイザの気持ちになって考える必要があるため、門外不出のOracle現場ワザ第4章　Oracleデータベースの頭脳「オプティマイザ」徹底研究にて、小田さんが詳しく書かれていますので、ぜひご一読ください。CBOが何故誤った選択をするのかと言うの例も、詳しく書かれています。

さて、SQLの実行計画を固定化するということについて。
OracleDBをあまり利用されていない方に、DBサーバ側で実行計画を固定化の話をすると驚かれます。
というのもこの機能、PostgreSQLでも追加パッケージ入れないと使えない機能（スミマセン使ったことはないです）ですし、MySQLには現時点でも存在しない機能だと思います。なので、この概念はOracleDB利用であることが前提に、現時点ではなるかと思います。
またアプリケーション開発という観点で見たとき、SQLの挙動をDB側で制御するのか良しとするのか。という考えもあります。私個人としてはこちらの考えですね。

実際、実行計画を固定化するということ事態、サービスレベルと内容によりケースバイケースだと思っています。
例えば銀行系の決済処理のSQL。性能命。このように非常に重要度の高い処理の場合は、実行計画を固定化する運用は必要だと思います。
ただし仮にそうだとしても、ヒント句等でSQL文側で実行計画を固定化するのが良いです。そのSQLを改修するたびに実行計画をDBで固定化するより、そもそもSQL側で宣言する（hash joinしか使わないよ、等）の方が、開発する側もSQLを改修する際に配慮ができますし、検証時に「このSQLは実行計画変動の課題を抱えているぞ」とコードをみて理解できるからです。
これについても、先に挙げた「何故実行計画が変動するのか」の理由、つまり根本原因を究明することの方が大事だと考えています。また同じように実行計画がコロコロ変わるSQLを生み出してしまう可能性がありますからね。

あくまで個人の意見ですが、この機能は止血策として使う機能であり、恒久的に使用する機能ではない。と考えています。
便利すぎるOracleDBが悪い。褒めてます。

SPMで実行計画を固定化する手順

さて本題のSPMで実行計画を固定化する方法ですが…MyOracleSupportの「SQL 計画管理 (SPM) の使用方法 - プラン・スタビリティ動作例 (ドキュメントID 2650514.1)」こちらをご参照になった上で、ぜひ使ってみてください。
この機能、なんとOracle Database 18cからはStanderd Editionでも使えるようになりました！ただしSEは1つのSQL IDに対してしか使えませんので、ご注意を。EEには制限はないです。

一例をあげますと、だいたいこんな感じのSQLを数回実行するだけで、即実行計画固定化が反映されます。実は簡単！

--- SQLベースラインの確認
select sql_handle, sql_text, plan_name, enabled,accepted, fixed from dba_sql_plan_baselines;

--- カーソルキャッシュから実行計画を読み込む
var res number
exec :res :=dbms_spm.load_plans_from_cursor_cache(sql_id => '<SQL_ID>', plan_hash_value => '<PLAN_HASH_VALUE>');

--- 実行計画固定化の適用
var res number
 exec :res := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( -
 sql_handle => '<SQL_HANDLE>', -
 plan_name => '<PLAN_NAME>', -
 attribute_name => 'FIXED', -
 attribute_value => 'YES' );

AWRから実行計画を抜き出してSPMで実行計画を固定化

更に応用技！
過去、上記実行計画固定化をしようと思ったのですが「カーソルキャッシュに…適用したい実行計画が…な、い…」という絶望を味わったことがあります。週次や月次のバッチのSQLで、もうカーソルキャッシュに以前の安定稼働した実行計画が残っていなかったのです。

なんとなんと、OracleDBはAWRから実行計画をロードして、SQLチューニングセットを作って実行計画固定化出来るんです！
つまり、AWRの保存期間内の実行計画は復元出来るということです。え…シンプルにOracleDBすごくないですか…？！
こちらもMyOracleSupportの「自動ワークロードリポジトリ(AWR)からSQL計画管理(SPM)にSQL計画をロードする方法 (ドキュメントID 2677758.1)」に手順が記載されていますので、ぜひ使ってください。
尚、この機能はAWRが利用できる前提になります。つまりEnterPrise Edtionのみ利用可能、且つAWRはOracle Diagnostics Packのライセンスが必要になりますので、ご注意を。

こちらも一例を挙げますね。

--- AWRのスナップショットIDを確認する
select s.snap_id snap_id,to_char(s.end_interval_time,'dd Mon YYYY HH24:mi') snapdate from dba_hist_snapshot s  order by snapdate;

--- SQLチューニングセットを作成
exec DBMS_SQLTUNE.CREATE_SQLSET('<SQL_TUN_NAME>');

--- 存在するSNAP IDから実行計画を取得しSQLチューニングセットに入れる
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap=><SNAP_ID>,end_snap=><SNAP_ID>,basic_filter =>'sql_id='||CHR(39)||'<SQL_ID>'||CHR(39)||' and plan_hash_value=<PLAN_NUMBER>',attribute_list=>'ALL',dbid=><DB_ID>)) p;
DBMS_SQLTUNE.LOAD_SQLSET('<SQL_TUN_NAME>, baseline_ref_cursor);
end;
/

まとめ

SPMはSQL IDに対し実行計画固定化できちゃう機能です
OracleDB 18c以上なら、SEでも使えちゃうよ
便利機能だけど止血策として使うのがオススメです
手順はとっても簡単
AWRから過去の実行計画をロードして実行計画固定化もできちゃうよ！

明日はSekiguchiさんの記事ですね！わくわく楽しみだなぁ♪