#jpoug オプティマイザ統計の保留とSQL計画管理を発表してきたので今度は実際に使ってみた! 【JPOUG Advent Calendar 2016 21日目】

公開日: 

このエントリは、JPOUG Advent Calendar 2016 の 21日目の記事です。 なお、昨日、20日目の記事は、守田 典男様の 12c DataGuard 遠隔同期インスタンスをつくってみた でした。

いつも少し時間が経つとオプティマイザ統計の遅延とSQL計画管理の機能がなんか似てて今一どう違うのかが忘れてしまうのでその備忘録として何度もアウトプットして出すことで忘れないようにしようと思い立ち、重い筆を持って書き連ねています。

sponcer link

大まかな概要や違いなどはDatabase Lounge Tokyo #3で以前発表させていただきました。

Oracleでモテる実行計画を固定させる2つの方法 from Daiki Mogmet Ito

簡単に言えば、オプティマイザ統計の保留を使えばモテる!

さらに金さえあれば、SQL計画管理を使ってこの世の全ての女性を虜にできる!!

若干盛られている感がありますが、多分こんな感じです。

今回は発表では語りきれなかった実際のやり方について残しておこうかと思います。

オプティマイザ統計の保留

発表資料に沿って、実際の実行計画とともに解説していこうと思います。

今回はmoteoユーザさんのmotemoteテーブルにて、統計情報が保留されることで実行計画がどう変わるかを見てみます。

準備編

まずはmoteoユーザに適当なmotemoteテーブルを作ります。

※ちなみに1201を入れた理由としては12/01が私の誕生日だからです。忘れてもらっても構いませんが。

適当にモテ度を注入したところで統計情報を取得しておきます。

割りと時間ギリギリに執筆を始めてるのがもろわかりですね。

さて、ここで一旦実行計画を見てみます。

「INDEX RANGE SCAN」でアクセスしていることがわかります。

※ちなみに、motedoカラムを1201の条件にした理由としては、12/01は私の誕生日だからです。もちろん忘れてもらっても構いませんが。

ここで取得した統計情報を信じ、一旦motemoteテーブルを保留とします。

この状態で実行計画が変わるような改革を起こして統計情報を取得しなおします。

確変的データ量として1201行挿入してみました。

※ちなみに1201行にした理由としては12/01は私の誕生日だからです。忘れてもらっても構いませんが。

実践編

準備が終わったところで,早速保留の成果を確認してみましょう。魔法のおまじないとしてoptimizer_use_pending_statisticsパラメータをfalseにしておきます。(デフォルトはtrue

その状態で実行計画を再度見てみます。

大量にデータを入れた後に統計情報を取得したにも関わらず、新しい統計情報は使われず、見積もりも1行のままの古い統計情報で実行計画を実行しているのがわかります。

では、取得した統計情報はどこにいるのでしょうか。

それは、あなたの心の中…ではなく、「XXX_TAB_PENDING_STATS」に格納されています。

きちんと1202行あることがわかりますね。

(どうでもいい情報ですが、私の誕生日は12/02ではないので要注意です)

では、この保留した統計情報を使うためにoptimizer_use_pending_statisticsをtrueにする呪文をかけ、その状態で実行計画を再度確認してみましょう。

保留された統計情報を使っているので見積もりの行も1202行と一致し、それにあった実行計画になっていますね。

ちなみに索引が使われなくなったのは、1201のデータが多すぎるので索引を使った検索よりも全表検索の方が速いと判断されているためです。

誕生日アピールしすぎて無視されちゃいましたね。

保留の統計情報を使う

保留の統計情報を確認してみて、問題なければDBMS_STATS.PUBLISH_PENDING_STATSを使用して公開することによって、その統計情報が使われるようになります。

公開したので、保留の統計情報は消えてなくなります。

保留の結果

こんな感じで統計情報を取得しても一旦保留といった形で保管されるので、統計情報を取得したら性能が悪くなるんじゃないかという不安の解決や、新しい統計情報での実際の性能を気軽に確認したり、誕生日をアピールしすぎて周りから疎まれることができます。

SQL計画管理

次は、SQL計画管理について、実行計画履歴からSQL計画ベースラインにもってきて承認する様子を実際の実行計画とともに解説してみます。

今回は2016年 恋人にしたい男性有名人ランキングで見事1位にランクインした嵐の【相葉雅紀】さんを参考にもっとモテモテになるような方法を解説しています。

準備編

dbaのビューをみたり色々実行したりするので、MOTEOユーザには今回からDBAになってもらいます。

日本一モテル男テーブルを作成して適当にデータを入れます。

※ちなみに、知っている方はご存知かもしれませんが、1201のデータを入れた理由は相葉さんの誕生日ではなく私の誕生日を入れています。

次にoptimizer_capture_sql_plan_baselinesをtrueにし、発行したSQLを片っ端からSQL計画ベースラインに登録していきます。

dba_sql_plan_baselinesビューの中を見るときに、私の誕生日(12月1日)でもある1201をキーワードとして引っ掛けることで、いい感じにmotedoが1201を検索しているイカしたSQLが登録されていることがわかります。

次に相葉ちゃんに大量にファンが増えてmotedoが急上昇しても軽く処理ができるように、索引を作成し再度、初回とは別の実行計画をSQL計画ベースラインに載せようとしてみます。

2つめの実行計画も無事SQL計画ベースラインにのりましたが、ACCEPTEDカラムがNOで承認されていないことがわかります。

実行編

準備ができたところでoptimizer_use_sql_plan_baselinesをtrueにし、SQL計画ベースラインにある実行計画のみを使用するように設定し、実行計画を見てみます。

索引は作成したはずなのに最初にSQL計画ベースライン記録した全表検索での実行計画を使用していることがわかります。

Noteにも「SQL plan baseline “SQL_PLAN_bk8jbr10fswhx204adfd1” used for this statement」と出ていてさらにSQL計画ベースラインの実行計画を使っているのがわかりますね。

後から記録された実行計画は承認がされない限りオプティマイザが使ってくれなくなります。

承認編

今度は索引作成後に作成された実行計画を承認して使えるようにしましょう。

以下のような、流れで承認をすることが出来ます。

展開タスク作成→展開タスク実行→タスクの結果をレポート→タスク内の推奨事項を実装

ということで早速、未承認の実行計画のレポートを表示してみましょう

ぶわっとレポートが出ますが、以下の様なことが言われているのがわかります。

簡単に言うと検証した実行計画は今よりよくなるよ!と教えてくれてます。

ということで、推奨にもある通り、承認のプロセスを実行してみましょう。

ACCEPTEDがYESになって承認されました。

この状態で、実行計画を見てみましょう。

無事承認された実行計画を使ってくれるようになりました!

SPM展開アドバイザ

ここまで手動で承認のフローを解説してきましたが、12cよりなんと!

自動で既存の承認済の計画よりもパフォーマンスが高い場合に展開をしてくれるSPM展開アドバイザというタスクが追加されました!! (パチパチ)

自動SQLチューニング・タスクが実行される時についでに実行されているようです。

何もしなくてもいい感じによしなに承認してくれるのでいい時代になりましたね。

SQL計画管理の総括

今回は、optimizer_capture_sql_plan_baselinesを用いてSQL計画ベースラインにロードしましたが、ロードする方法としては他にもSQLチューニング・セット、共有SQL領域、ステージング表、ストアド・アウトラインなどからロードすることが可能です。

cf: SQL計画ベースラインのロード

これを使えばいい感じに良い実行計画だけを扱えるようになりますが、注意してほしいのがこれはEnterpriseEditionのみの機能になるのでご注意下さい。

あと、あまりSQL計画管理では誕生日のアピールができていませんでしたが、12月1日は私の誕生日です。

もちろん皆さん明日には忘れると思います。

まとめ

そんなこんなで統計情報の保留とSQL計画管理を使えばきっとみなさんモテモテになると思いますので明日から是非機会があれば使ってみて下さい。

最後にここで皆さんにお知らせがあります。

実は、

今月の1日、

.


はい、つまり12月1日は・・・

.

..

私の誕生日でした!!!!!!!!!!!

みなさんびっくりしたかもしれませんね。

もしよろしかったら私のほしいものリストから是非ロイスを買って下さい。

ブラさんはたくさんもらったのでもうお腹いっぱいです。

see you next year

明日は、mutatsuさんの記事になります。

みなさん、今年も良いお年とクリスマスと誕生日をお送り下さい。

  • このエントリーをはてなブックマークに追加
  • Pocket
PAGE TOP ↑