前回のおさらい
前回、「SQL実行計画の変更 その2(1/2)」では、謎のSQLの正体を突き止めたものの、なぜ開発環境では性能問題が出なかったのか？というところで終わりました。
果たしてたろーちゃんは、この問題を解決できるのでしょうか？
開発環境での実行計画
謎のSQLの正体は分かりましたが、なぜ開発環境では性能問題が出なかったのでしょうか？
たろーちゃんは、開発環境の Statspack にも注目することにしました。
たろー（例のSQLは、開発環境だと全然負荷が掛かってない。）
たろー（なぜだろう？実行計画も本番環境と同…じ…………………！？）
ディクショナリビューの実行計画
たろー「違う！本番環境と開発環境で、ディクショナリビューへのSELECTの実行計画がまるで違う！！」
みなさんは、USER_TABLES や USER_IND_COLUMNS にアクセスしたことはあるでしょうか？
メジャーなディクショナリビューですので、アクセスした方が多いかと思います。
しかし、アクセスした時の実行計画まで見たことある方って、どれくらいいるでしょうか？
実はこれらのディクショナリビューは、Oracle Database内部の固定表(X$)や内部表を結合しており、中身は複雑な実行計画になっているのです。
たろー「本番環境と開発環境で違うのは、『シングルインスタンス構成かRAC構成か？』だけど、もしかしたらそれが オプティマイザ が実行計画を決定する上で、重要な要素になっているのかもしれない。あくまで仮説だけど……」
原因は分かったけれど・・・
たろーちゃんは、原因をAさんに話しました。
Aさん「えー！そんなことになってたのか。」
たろー「まさかディクショナリビューの中身の実行計画が違うとは思わなかったよ。SYSスキーマの全オブジェクトの統計情報を取り直してみたが、実行計画は変わらなかった。」
Aさん「しかし、どうしたらいいんだ？」
たろー「・・・。」
Aさん「・・・。」
たろー「そのフレームワーク使うのを、辞めないか？」
Aさん「バカ言うな、今更そんなこと出来るわけないだろう？」
たろー「だって、ディクショナリビューの中身にヒント句を埋め込むなんて出来ないし・・・。お手上げ状態だよ。」
Aさん「┐(｡・ε・｡)┌」
たろー「┐(｡・ε・｡)┌」
対応策
Aさん「このままだと、御客様に説明が出来ないよ。なんとかならないかな？」
たろー「『開発環境も２ノードRAC構成にしてくれなかったから、正確な性能テストが出来ませんでした』じゃダメなの？」
Aさん「あのなぁ…、火に油を注ぐだけに決まってるだろ、そんなの。
普通に考えたら、２ノードRAC構成がシングルインスタンス構成より遅いなんてことはないんだから。」
たろー「うーむ、どうしたものかなぁ…。」
Aさん「この前のSPMを使ってなんとか出来ないの？」
たろー「SPMは Enterprise Edition じゃないと使えないんだよ。」
Aさん「えー？じゃあどうすればいいんだ？？」
たろー「……まてよ、SPMは使えないけどアウトラインなら…。アウトライン を使った プランスタビリティ なら、なんとかなるかもしれない！」
Aさん「プランスタビリティ？どうやって使うの？」
たろー「まず、開発環境で早い実行計画を取得するんだ。」
SQL> alter session set create_stored_outlines = outline_taro; ＜フレームワークが実行しているSQLを実行する＞ SQL> alter session set create_stored_outlines = false;
たろー「処理が正常に終了したら、作成されたアウトラインを確認する。」
SQL> SELECT category,name,used,enabled,sql_text FROM user_outlines;
CATEGORY NAME USED ENABLED SQL_TEXT
------------- ----------------------------- ------------ ---------------- -------------------------------
OUTLINE_TEST SYS_OUTLINE_XXXXXXXXXXXXXXXXX UNUSED ENABLED ＜フレームワークが実行しているSQL＞
※アウトラインの名称『SYS_OUTLINE_XXXXXXXXXXXXXXXXX』のX部分にはOralceで自動採番された数字の羅列が入ります。
たろー「次に、その実行計画を本番環境に持っていく。」
※アウトライン移行方法の詳細はMy Oracle SupportのドキュメントID 1707310.1 をご覧ください。
たろー「そして、本番環境で対象のSQLを実行する前にalter session set use_stored_outlinesをしてやれば・・・。」
SQL> alter session set use_stored_outlines = outline_taro;
＜フレームワークが実行しているSQLを実行する＞
：：：中略：：：
Note
-----
- outline "SYS_OUTLINE_XXXXXXXXXXXXXXXXX" used for this statement
Aさん「おお、早くなったぞ！体感的に開発環境と同じスピードだ！」
たろー「よし、Noteからもプランスタビリティが効いてるのが分かった！
アプリケーションでSQL実行前にこのalter session set use_stored_outlinesを実行するよう修正してくれ。
それでパフォーマンス改善出来る！」
Aさん「・・・・・・・・・。」
たろー「どうしたの？Aさん？」
Aさん「・・・・出来ないよ。」
たろー「どうして？またソースを修正出来る人がいないとか、か？」
Aさん「違う、そうじゃない。ALTER SESSION 文のような DDL は発行出来ないフレームワークなんだよ。」
たろー「なんだって？！どこまで取り回しの効かないフレームワークを使ってるんだよ！」
Aさん「そんなこと言ったって、仕方ないだろう（汗）」
たろー「折角、光が見えてきたのに・・・。」
苦肉の策
たろー「アプリケーションで対応出来ないなら、データベース側でなんとかしないとダメなのか・・・。ならば仕方がない。データベーストリガーを使おう。」
CREATE OR REPLACE TRIGGER auto_start_outline AFTER STARTUP ON DATABASE BEGIN EXECUTE IMMEDIATE 'alter system set use_stored_outlines = outline_taro'; END auto_start_outline ; /
Aさん「おお！これは！」
たろー「こうしておけば、データベース起動時に必ずアウトラインを使うよう設定されるんだ。」
業務が停止しないよう、２ノードRACを１インスタンスずつ停止＆起動すると・・・。
Aさん「おお！レスポンスが改善されてる！さっきより全然早いぞ！」
たろー「アウトラインが効いてるんだ。Statspack からも、開発環境と同じ実行計画になっているのを確認できたよ。」
Aさん「サンキュー！たろーちゃん！！」
アウトラインとSPM
アウトライン は11gR1 より非推奨の機能となっていますが、Standard Edition では SPM を使うことが出来ないため、まだまだ使っている現場も多いようです。
ただ18cからは、SPM の一部の機能が Standard Edition でも使用できるようになっていますので、今後は SPM を使うシーンが増えていくことでしょう。
今回の「心臓外科医の術式」いかがだったでしょうか？
ヒント句 を埋め込むことができないディクショナリビューの実行計画を変更する、見事な「術式」だったのではないでしょうか？
次回も頑張りますので、応援よろしくお願い致します。
アプリケーション開発者は開発効率だけを考えるのではなく、柔軟にデータベースに対応できるよう、取り回しの効くフレームワークを使うようにしてね。
お願いだから（＾＾；
あと、御客様に一言・・・。ちゃんとした性能テストを行うには、本番環境と同等な構成の開発環境が必要です。予算の御都合があるのは重々承知しておりますが、その辺りも是非考慮に入れて頂けると幸いです。
投稿者プロフィール
