2014年12月6日土曜日

optimizer_dynamic_sampling=11

久々の投稿となります。
今日は、JPOUG Advent Calendar 2014の6日目のエントリです。
1µmくらい役に立つプレゼントになればと思ってます。


前日の第三の柴田ことgonsuke777さんが「Bind Peek を もっと使おうぜ!」という
煽り記事を書いていましたので、
私も呪文のように唱えられるoptimizer_dynamic_sampling=0で有名な
動的統計(動的サンプリング)の12cの今について書こうと思います。

動的統計は12c(または11.2.0.4.0リリース)以前は動的サンプリングという名前でした。
機能を単純に説明すると、オプティマイザ統計(俗に言う統計情報)が
不十分な場合にそれを補完(補填?)するための機能です。

第三の柴田がBind Peekを使おうと言っていますが、
それだって不十分な統計情報のもと使っても何の意味もありません!
なぜなら、Bind Peekは与えられたバインド変数をもとに実行計画を生成しますが、
その元ネタとなるのは統計情報だからです。
そんな不十分な統計情報を補うための機能、動的統計こそ至高!!


のはずですが、
・実行計画が変わる要素となる
・この機能が動くと実行計画の生成時間(解析時間)が長くなる

という理由でBind Peekと同じ道を歩むかのように無効化されてきました。。
(optimizer_dynamic_sampling=0設定)
※2個目の理由はシビアなレスポンスが求められるシステムでは
 止めることもよいとは思います。



そんな動的統計が12c(11.2.0.4.0含む)で進化を遂げます。
それがoptimizer_dynamic_sampling=11設定です。
11に設定するとどのような動作になるか。


Oracle® Database SQLチューニング・ガイド12cリリース1(12.1) B71277-02 では
オプティマイザで動的統計を使用するタイミングは「オプティマイザが必要と判断した場合は、
自動的に動的統計が使用されます。結果の統計は統計リポジトリ内で永続的であり、
他の問合せに対しても利用できます。」
サンプル・サイズ(ブロック)は「自動的に決定」となっています。

分かり難いですよね。
私が検証した結果からは、以下のように言いかえられると思いました。
-----
今までのレベル1~4の条件に合致した場合やSQLがパラレル実行された場合に動作するとともに、
SQL計画ディレクティブで動的統計の収集が登録されている場合や、
統計情報が失効した場合にも収集するようになった。
※通常、最後に統計情報が収集されて以降、表内の10%以上の行が変更されると、
 統計情報は失効したとみなされます。

また、動的統計として動的統計取得対象のSQLについて共有プールのSQL領域内、
または自動ワークロード・リポジトリ(DBA_HIST_XXX)から
去のSQL実行時の統計を取得することにより、
他の問合せでも利用できる。
-----


端的に書くと以下がポイントになると思います。
=====
★統計情報が失効している場合にも動的統計が取得出来るようになった
 11.2.0.3.0までは統計情報が取得されていない場合には動的統計が動きましたが、
 統計情報が陳腐化した場合にも動作してくれるようになった。
 ⇒バッチ処理時などのワーク表やデータ量・割合の変動が大きい表も
  取得対象となります。
  (今までは統計情報NULLでロックし、動的統計を動かすというような手法で対応していた)


★動的統計は過去の実行時統計を再利用している
 DBA_HIST_SQLSTATやV$SQLなどから過去の実行結果を参照して、
 動的統計情報を取得しているように見えます。
 ※詳細を調査したい人はSQLトレースを取得するといいと思います。
 いうなれば、第三の柴田が言っていた、
 カーディナリティフィードバック(12cでの名称は自動再最適化の統計フィードバック)が
 動作するようなイメージです。
 ⇒12cより前のリリースでは、動的統計もカーディナリティフィードバックもSQLカーソルが
  エージアウトすると折角取得した統計情報補完情報が失われていた。
  ※カーディナリティフィードバックの補完情報は今のリリースでも失われます。
===


また、統計情報が失効しているオブジェクトを参照する他のSQLや
SQLカーソルがエージアウトされて、カーディナリティフィードバックの情報が失われても、
統計情報を補完した状態でSQLを実行出来るようにするために追加された機能が、
そう、SQL計画ディレクティブ!!(のはず)


と、12cではオプティマイザ周りの機能が色々と進化していますが、
長くなるので今日はここまで。
これら機能を使う使わないなどの考え方は、
第三の柴田がDDDで説明した資料を参考にするとよいと思います。



最後に注意点を記載しておくと、

・統計情報が失効している場合や過去のSQL実行統計から情報を取得する動作は
 optimizer_dynamic_samplingが11に設定されている場合のみ動く

・DYNAMIC_SAMPLINGヒントでは11を指定しても無効(有効範囲は0~10)
 Oracle® Database SQL言語リファレンス 12cリリース1 (12.1)

・SQL計画ディレクティブにより動的統計収集が実行される場合は、
 optimizer_dynamic_sampling=2が設定されていても11の動作となる。
 停止したい場合には、optimizer_dynamic_sampling=0の設定が必要。

・OPTIMIZER_ADAPTIVE_FEATURES=FALSEを設定しても動的統計の機能は停止しない


また、とても重要な点として、
レベル11の設定は11.2.0.4.0や12cからの新機能(機能拡張)なので、
検証を十分に実施してから使用するのが良いと思います。
※デフォルトの設定はoptimizer_dynamic_sampling=2となっています。


ではでは、「第三の柴田」というキーワードが流行ることを願いつつ
これを機会に今後は12cネタをぽつぽつ書いていこうと思います。



JPOUG Advent Calendar 2014、
明日の扉は渡辺 剛 さんです。よろしくお願いします!

3 件のコメント:

  1. > ★統計情報が失効している場合にも動的統計が動くようになった

    マジですか。。。STALE_STATS列のYES/NO で動きが変わるのか(´・ω・`)

    返信削除
  2. 取れば分かるさ、SQLトレース!(ゝ∀・*)
    再帰SQLがめっさ増えてて、アグレッシブに動いている姿を刮目して見よ!(m9^-')ビシッ

    返信削除
  3. optimizer_dynamic_sampling=11
    ですが、SQLがクラッシュして結果が返ってこなくなるバグがあります。
    機能を有効化する方向の初期化パラメータ設定は、多くの場合オラクルのバグを踏みます。

    返信削除