今週の名言

「人は日本の歴史に50ページ書いてもらうより､世界の歴史に1ページ書いてもらうことを心掛けねばならぬ｡」
後藤新平

シリーズまとめ

「オプティマイザ統計の保留」について検証してきた今回のシリーズも序章を含めると１０回目になりました。

この機能は他にもいろいろ検証して確認したいことはあるのですが、他のテーマも追いかけたいので区切りがよいところでまとめておきたいと思います。

新しい機能にどう向き合うか

私はITというものに対して、ウンチクを語る対象ではなく道具としてビジネスに貢献するものでなければならないという強い思いがあります。

従ってある新機能を目にした時には「これは何の役にたつのだろうか？」とか「あの問題に対してよい解決策になるのではないか？」という視点で確認してみたいという気持ちになります。

Oracle10g以降ルールベース・オプティマイザ（RBO）がサポートされなくなりましたが、クルマがマニュアル・シフトからオートマチック・シフトになったのと同じくらいのインパクトがあったのではないでしょうか。

パラメータ・チューニングやヒント句を駆使して性能改善をバリバリ行ってきたエンジニアにとって、Oracle10gは大きな変換点だったのではないかと思います。
先日もある飲み会で「Oracle9iは結構面白いバージョンだった。」というような意見を一人のベテランエンジニアから聞きました。

Oracle10g以降自動化が進んで、DBAが手を動かして問題解決するという機会は確かに減りましたが、自動化を過信したいわゆる「自動化の落とし穴」という問題も深刻になってきました。

このシリーズは「自動化の落とし穴」にまつわる混乱を「オプティマイザ統計の保留」の検証（序章②）で紹介するところから始めました。

コストベース・オプティマイザ（CBO）というそれまでとは違った仕組みに対して、実行計画が意図せず急変してしまったと言う理由で、統計情報を取ることに消極的であったりヒント句で実行計画をガチガチに固めてしまっていたりする例を未だによく見かけます。

もうすぐOracle12c R2がリリースされるという現在となっては「オプティマイザ統計の保留」はすでに新機能とは呼べなくなっているかもしれませんが、CBOの仕組みをよく理解し、この機能を活用することによって「失敗のない運用」を行うことは十分可能であると思います。

「オプティマイザ統計の保留」の特徴

それではシリーズを振り返ってこの機能の特徴を整理していきます。

1. 保留統計情報は実行計画に影響を与えない

実行計画は公開されたディクショナリ統計情報によって算出されます。従ってプライベート・エリアに格納されディクショナリ統計を更新しない保留統計情報は実行計画に何ら影響を与えることがないというのが一番の特徴です。

一度算出された実行計画を変動させる要因がないということは、実行計画は事実上固定されます。

2. 保留統計情報は履歴を持たない

保留統計は履歴を持ちません。これは以下のような２つの意味を持ちます。

• 最新の統計情報のみが保持される
• プライベート・エリア（SYSAUX表領域）を圧迫しない

定期的に統計情報を取得しても、古い統計情報の履歴を削除する必要はありません。

3. セッション単位で保留統計情報を使った実行計画が確認できる

OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをセッション単位で一時的に「TRUE」に設定し、SQLを実行すると保留統計情報を使用した実行計画が作成されます。

これをExplain PlanやSQL*PlusのAutotrace機能で確認することで、公開統計情報を使用した実行計画と比較することができます。

4. 保留統計情報は手動で公開することができる

もし、保留統計情報を使用した実行計画の方が優れていることを確認できれば、DBMS_STATS.PUBLISH_PENDING_STATSプロシージャを実行することにより任意のタイミングで保留統計情報を公開することができます。

これにより、より優れた実行計画を永続的に使用することができます。

5. 保留統計情報はStandard Editionでも使うことができる

今まで触れてこなかったのですが、この機能はStandard Editionでも使用することができます。

Enterprise EditionではSQL計画管理（SPM:SQL Plan Management）により、統計情報に左右されない安定した実行計画の選択を行うことができます。

SPMのように高度な実行計画の管理はできませんが、SEでも「オプティマイザ統計の保留」によって同じような運用を行うことができます。（手動で運用する分のコストはかかります。）

ベスト・プラクティス

それでは、「オプティマイザ統計の保留」を活用した運用のベスト・プラクティスを考えてみましょう。

もちろん、環境によって個別の要件があるので、柔軟に対応することは重要です。

1. 初期状態では統計情報は「公開」しておく

全く統計情報を取得しない状態で「保留」にすることは可能ですが、統計情報を取得してそれを使用した実行計画が作成されるまでは「公開」のままにしておいた方がよいかもしれません。

2. 実行計画に問題がないことを確認し、テーブル単位で公開属性を「保留」に変更する

公開属性をテーブル単位で「保留」に変更すれば、少なくともそのテーブルに関する実行計画は新たな統計情報によって不安定になることなく固定されます。

ただし、一つのテーブルは多くのSQL文で使用されていますので、あるテーブルの属性変更がどのSQLに関係しているのかを把握するためには、以前このブログで紹介した「V$SQL_PLANでCRUD表モドキを作ってみる②」を参考にして調査をしてもよいかもしれません。

3. 定期的に統計情報を取得して評価する

公開属性が「保留」になっていれば、どんなに統計情報を取得したとしても実行計画が変更されることはありません。従って統計情報を定期的に取得することは継続すべきであると考えます。

これは自動統計情報収集機能にまかせてもよいし、DBAが任意にスケジュールしてもよいかもしれません。

「公開」と「保留」統計情報を比較し、より正しい実行計画が得られることが明らかであれば、その保留統計を都度公開していけばよいのです。

4. 統計情報のロックをうまく使い分ける

揮発性の高いテーブル、つまり一日中に頻繁にTRANCATEが実行されたり、バルク・インサートの対象となっているような、すなわち定期的な統計情報の取得でSELECT時使用される正確な統計情報を収集することができないテーブルに対しては、保留統計情報を使用しても最適な結果が得られないことが考えられます。

そのような場合は、統計情報を削除した後にロックし統計情報がない状態で運用した方がよいかもしれません。

このようなテーブルに対しては、SQL文実行時に動的統計が自動的に収集されます。（ダイナミック・サンプリング）

統計情報の「保留」と「ロック」を必要に応じて使い分けることでより最適な運用を図ることができます。

今回でこのシリーズは終わりにしますが、まだ検証していないことも沢山ありますので、またこのテーマを再開させるかもしれません。

次回は

次回は12cのある新機能に注目して検証していきたいと思います。