オプティマイザ統計の収集の重要性とオプティマイザ統計の自動収集

01.オプティマイザ統計（統計情報）とは何か？

オプティマイザ統計とは、CBO(Cost Base Opitimizer)がSQLの実行計画を作成する際に参考にするデータベースに格納されたデータの状態を示すサマリ情報です。オプティマイザ統計は統計情報とも呼ばれます。
CBOは以下の手順でSQLの実行計画を作成します。

• 1. いくつかの実行計画の候補を選ぶ
• 2. それぞでの実行計画を実際に実行した場合、どの程度の使用されるコスト
  （I/Oリソース+CPUリソースの見積もり値）が必要であるかを見積もる
• 3. コストが最小であると見積もった実行計画を最終的に選択する

コストの見積もりにもっとも重要な役割を果たすのが、オプティマイザ統計です。
オプティマイザ統計は、テーブルに格納された行数や、行の平均長、列値の最大値、最小値などから構成されており、CBOはこちらのデータをもとに、コストを見積もります。
オプティマイザ統計はDBMS_STATSパッケージの以下のプロシージャを用いて、収集できます。

• GATHER_TABLE_STATS : 指定されたテーブルについてオプティマイザ統計を収集する
• GATHER_SCHEMA_STATSプロシージャ : 指定されたスキーマ内のすべてのオプジェクトについてオプティマイザ統計を収集する
• GATHER_DATABASE_STATSプロシージャ : データベース内のすべてのオプジェクトについてオプティマイザ統計を収集する

02.適切にオプティマイザ統計を収集することの重要性

行数や行の平均長などから構成されるオプティマイザ統計は、いわばテーブルに格納されているデータの特徴を示すサマリ情報と理解することができます。しかし、テーブルに格納されたデータに対して更新を加えた場合、オプティマイザ統計は自動的にメンテナンスされないことに注意が必要です。
たとえば、テーブルに格納されているデータの行数が100であったときにオプティマイザ統計を収集したあと、テーブルに新たにデータを追加して行数が200になったとしても、オプティマイザ統計を再度収集しないかぎり、オプティマイザ統計における行数は100のままです。
したがって、テーブルに大量のデータがINSERTされた場合や、多くの行に対してUPDATEが実行された場合など、データに大きな更新が加えられたときは、オプティマイザ統計を再収集する必要があります。このような状況でオプティマイザ統計を再収集しないと、オプティマイザ統計はテーブルに格納されているデータの特徴を正しく反映した状態になっていませんから、CBOは適切な実行計画を選ぶことはできません。不適切な実行計画が選ばれると、SQLの実行パフォーマンスは最適なものにならず、場合によっては、致命的なパフォーマンス遅延をもたらすこともあります。

03.オプティマイザ統計の自動収集

しかし、データベース管理者が、データの更新状態を逐次把握しておき、データの更新量が多い場合に、オプティマイザ統計収集作業を実行することは面倒なものです。このため、Oracle Database 10gより、Oracle Databaseに組み込まれているジョブ機能を活用して、オプティマイザ統計を自動的に収集する機能が導入されました。
デフォルトの構成で、オプティマイザ統計が収集される時間帯（ウィンドウ）は以下のとおりです。

10g : 月～金 22:00～翌日06:00、土日 全日
11g : 月～金 22:00～翌日02:00、土日 06:00～翌日02:00

上記の時間帯になると、Oracle Databaseは組み込みのジョブ機能により、統計が収集されていないテーブル、以前の統計収集からの変更量が多いテーブルについて、オプティマイザ統計を収集します。

04.オプティマイザ統計の自動収集の注意点

オプティマイザ統計の自動収集はデータベース管理者の管理作業の負荷を軽減するという点ですばらしい機能ですが、いくつかの注意点があります。

デフォルトの収集時間帯が必ずしもすべてのシステムで適切とは限らない

たとえば、平日では22:00に高負荷のアプリケーション処理を実行する必要がある場合、オプティマイザ統計自動収集処理と処理時間がバッティングしてしまいます。
この場合は、アプリケーション処理、またはオプティマイザ統計の収集処理の実行時間をずらすことが有効です。オプティマイザ統計の収集時間帯の変更するためには、処理時間帯を示すウィンドウを変更します。

データが更新されてから収集時間帯までの間は、実行されたSQLのパフォーマンスが最適でない可能性がある

データが大量に更新された場合は、自動収集時間帯になれば、そのテーブルのオプティ マイザ統計が再収集されます。しかし、自動収集時間帯に達する前に、そのテーブルにアクセスするSQLが発行された場合、SQLの処理パフォーマンスが最適でない場合があります。このような状況で、SQLの処理パフォーマンスが問題になる場合は、データ更新後に明示的にオプティマイザ統計を収集する必要があります。

実行計画が変動する可能性がある

オプティマイザ統計を定期的に再収集する運用は、SQLの実行計画がデータの状態を適切に反映するため、一般に適切であるといえます。
しかし、実行計画をできる限り固定化したい場合は、オプティマイザ統計の自動収集を無効化してください。(*1)
(*1) Oracle Database 11g Enterprise EditionではSQL計画管理を利用することで、実行計画の意図しない変動を抑えることができます。SQL計画管理の詳細はマニュアル「パフォーマンス・チューニング・ガイド」をご確認ください。

05.まとめ

オプティマイザ統計はデータの更新に合わせて、適切に再収集する必要があります。Oracle Database 10g以降では管理コストを削減するために自動収集の仕組みがありますが、全てのシステムにおいて適切ではないため、自動収集の設定変更などの調整が必要な場合もあります。