門外不出のOracle現場ワザ

日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部 小田 圭二(おだ けいじ)

目次

• Part1　 SQLを最適化するコストベースオプティマイザの基本機能

  • はじめに

  • CBOを使用する理由

  • SQL文の処理におけるオプティマイザの役割

• Part2　 CBOは何を見てどう判断するのか

  • CBOのアクセスパス選択方法

  • ヒストグラム

  • CBOとバインド変数

  • バインドピーク（Bind peek）

  • I/O + CPUコストモデル

  • CBOとフルスキャン

  • CBOとキャッシュ効率

  • CBOとパラメータ

  • CBOと結合順序

• Part3　 オプティマイザ統計の管理

  • 自動統計収集

  • 統計履歴の自動保存

  • 動的サンプリング

• Part4　 実行計画の固定と自動チューニングオプティマイザの活用方法

  • 実行計画を固定する機能

  • 自動チューニングオプティマイザ

  • CBOを使いこなすためには

Part3　オプティマイザ統計の管理

さて、これまでオプティマイザがどんな情報を利用し、どういう仮定を立て、どういう計算で実行計画を選択しているかについて説明してきました。次に、オプティマイザ統計の管理について知っていただきたいと思います。

オプティマイザに対する重要なインプットは、言うまでもなくオプティマイザ統計であり、これを収集する頻度や精度、ヒストグラムを収集するか、また対象の列はどうするかといった点は、CBOの運用における極めて重要なポイントです。

Oracle 10gではRBOがサポートされなくなり、すべてのユーザーはCBOを使用する必要があります。そのためにOracle 10gは、ユーザーが意識しなくても「オプティマイザ統計管理」を自動的に実行する仕組みを実装しています。 例えば、CBOをまったく知らないユーザーが、Oracle 10gをインストールして表を作成し、データをローディングして索引を作ったとします。10gでは、CREATE INDEX文の実行時にデフォルトで索引のオプティマイザ統計を収集するため、この時点でまず、索引の統計情報が格納されます。

次に、その表を使用してアプリケーションのテストを開始したとします。この時点では表の統計情報が存在しないため、SQLの発行時にオプティマイザ統計の動的サンプリングが行なわれます。これに基づいて、オプティマイザは実行計画を生成します。この時、OracleはSQLの条件句で指定された列の情報をディクショナリに記憶しています。

さらに、その夜の22時になるとオプティマイザ統計を収集するスケジュールジョブが自動的に起動します。昼間作成された表の統計情報が収集され、アプリケーションが条件指定した列に対してはヒストグラムが作成され、翌日には統計情報が揃った状態で使用可能です。

オプティマイザ統計の再収集のタイミングも、ユーザーが意識する必要はありません。Oracleは表ごとに更新された行数の履歴を記録しており、更新された行数の割合が増えてくると、自動的に統計を再収集します。

いかがでしょう。オプティマイザに関する運用が自動化されたのは、魅力的ではないでしょうか。確かに、システム規模が小さく、管理に時間やお金がかけられないシステムにおいては、CBOに対する敷居を下げる優れた実装と言えます。

しかし、大量のデータを扱い、大量のユーザーに対し常に一定のレスポンスとスループットを提供する必要があるミッションクリティカルなシステムでは、「何でも自動（ユーザーが気がつかないところで実行されている）」というのは逆に受け入れられない可能性があります。 このような環境では、いつ、何が、どの程度起きるかという「予測可能性」が重要です。このパート3では、まずOracleの自動統計収集の詳細を説明し、どのように使用できるかを考えていきます。

自動統計収集

GATHER_STATS_JOB

Oracle 10gでは、データベースを作成するとデフォルトでGATHER_STATS_JOBと呼ばれる自動統計収集のためのスケジューラジョブが準備されます 注17。ジョブの定義はLIST1のように確認できます。

LIST1 ジョブの定義

注17：DB作成時の必須スクリプトであるcatproc.sqlから呼ばれるスクリプトによって作成されています。

ジョブが実行するプログラムはGATHER_STATS_PROGとなっていますが、実際に実行されるストアドプロシージャはLIST2のように確認できます。

LIST2 実行されるストアドプロシージャ

ジョブのスケジュール名がMAINTENANCE_WINDOW_GROUPとなっていますが、このウィンドウグループは次の2つのスケジューラウィンドウを含んでいます。

select * from dba_scheduler_wingroup_members;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ -----------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

「スケジューラウィンドウ」は、ジョブの開始時間と終了時間を定義した「時間枠」を意味します。それぞれのウィンドウの定義はLIST3のように確認できます。

LIST3 スケジューラウィンドウの定義

次のように解釈します。

• WEEKNIGHT_WINDOW 月曜日～金曜日の22時にオープンし、8時間後（明朝6時）にクローズするウィンドウ

• WEEKEND_WINDOW 土曜日の0時にオープンし、2日後（月曜の0時まで）にクローズするウィンドウ

ウィンドウがオープンすると（開始時刻になると）、関連付けられているジョブが開始されます。自動統計収集（GATHER_STATS_JOB）がいつ開始され、完了までどれくらいの時間がかかったかはLIST4のように確認できます。

LIST4 自動統計収集にかかった時間

ジョブのSTOP_ON_WINDOW_CLOSE属性がTRUEであるため、ウィンドウがクローズすると（終了時刻が来ると）、このジョブは実行中であっても強制的に停止されます。統計収集ジョブが完了する前にウィンドウがクローズすると、LIST5のようなトレースファイルが出力されます。

LIST5 ジョブが強制停止された際に出力されたトレースファイル

DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC

次に、実際に自動統計収集を行なっているdbms_stats.gather_database_stats_job_proc（）プロシージャについて説明しましょう。

• 統計収集するオブジェクトの選択

  このプロシージャでは、統計情報が存在しないか、存在するが古くなっていると考えられるオブジェクト（10％以上の行が更新されたオブジェクト）が選択されます。このとき、各オブジェクトには優先順位が付けられ、優先度が高いものから順に統計が収集されます。統計情報が存在しないオブジェクトが最も優先度が高く、その後は更新行数の割合が多い表の順で統計収集されます。

• サンプルサイズ

  サンプルサイズとは、統計収集時にオブジェクトからサンプリングするデータ量のことです。デフォルトではAUTO_SAMPLE_SIZEが使用されています。 AUTO_SAMPLE_SIZEによる統計収集時は、対象オブジェクトの行数の見積もり（行数見積もりのためのサンプリングが事前に行なわれます）に依存してサンプルサイズ（パーセンテージ）を決定しています。

  サンプルサイズは最小で0.000001％であり、25％を超えるサイズが必要であるとみなされる場合（小さな表）は100％に切り上げられます。母体の行数もサンプリングによって見積もられているため、サンプルサイズは毎回異なる可能性があります。実際に何行がサンプリングされたかは、USER_TABLES.SAMPLE_SIZEやUSER_INDEXES.SAMPLE_SIZEから確認できます。

• ヒストグラム生成

  ヒストグラムの生成についてはデフォルトでは「FOR ALL COLUMNS SIZE AUTO」が使用されています。これは「すべての列についてバケット数を自動選択してヒストグラムを作成する」という指定です。 「SIZE AUTO」の場合にどの列にヒストグラムが作られるのかは、それ以前のアプリケーションの実行状況に依存します。Oracleは表のどの列がSQLの条件句として使用されたかモニタリングし、ディクショナリに記録しています。「SIZE AUTO」の場合、このディクショナリを参照して、どの列にヒストグラムを作る価値があるかを判断しています。

  Oracleはこれまでに条件指定のあった列に対してメモリ上で200バケットの高さ調整ヒストグラムを作成し、値の偏り（ポピュラー値が存在、もしくは値のまばらな区間の存在）が見られた場合には、オプティマイザ統計としてディクショナリに格納します。

• パラレル統計収集

  統計収集はパラレル実行が可能ですが、デフォルトではパラレル度としてオブジェクトのDEGREE属性が使用されます。DEGREE属性はデフォルトでは1であり、シリアル実行となります。表のDEGREE属性は「ALTER TABLE…PARALLEL 4;」のように変更可能です。ただし表のDEGREE属性を変更した場合、統計収集だけでなく、通常の問い合わせもパラレルで実行されるようになりますので注意が必要です。

• オプティマイザ統計収集後の共有カーソルの無効化

  統計の再収集後に、当該オブジェクトに依存している共有カーソルを無効化できます。これにより、統計収集の直後に、そのSQL文を新しい統計情報とともに再解析し、新しい実行計画を生成できます。デフォルトでは統計の再収集後も、共有カーソルを無効化しません。つまり、その共有カーソルがキャッシュアウトし、次回のハードパースのタイミングではじめて、新しい統計値を反映した実行計画が生成されます。

  これは、オプティマイザ統計の収集直後に発行されるSQL文のレスポンスを劣化させないためには有効です。しかし、共有カーソルがキャッシュされている間は新しい統計を取った意味がないため、システム負荷が低くなる時間帯などに共有プールをフラッシュ（alter system flush shared_pool文）するようなオペレーションが必要です。

10gではデフォルト（STATISTICS_LEVEL=TYPICALの場合）で、すべての表に対するDMLの発行状況をOracleがモニタリングし、ディクショナリに記録しています。この記録に基づいて、最後の統計収集以後に大量の行（全体の10%以上）が更新された表については、既存のオプティマイザ統計が「古くなった」ものとみなして再収集の対象とします。 Oracleが記録した内容は、USER_TAB_MODIFICATIONSビューから確認できます。

select table_name,to_char(timestamp,'DD-MON-YY HH24:MI:SS') timestamp,
inserts,updates,deletes from USER_TAB_MODIFICATIONS;

TABLE_NAME   TIMESTAMP              INSERTS    UPDATES    DELETES
------------ ------------------- ---------- ---------- ----------
TEST2        11-NOV-04 02:04:57          57        521         24

この情報はSGA内で管理されていますが、SMONプロセスによって周期的にディクショナリへ書き込まれます。メモリ内の情報をディクショナリへ即時反映するためにはDBMS_STATS.FLUSH_DATABASE_MONI TORING_INFOを実行します。

この記録はオプティマイザ統計が再収集されることでクリアされます。

自動統計収集の使い方

デフォルトで用意されている自動統計収集ジョブを使用するメリットは、以下のような点です。

• スケジュール済みであるため、不注意による統計の取り忘れがなくなる

• ある程度変更があった表のみ再収集されるため、1回の統計収集の負荷は最小限である

• 統計収集はウィンドウの範囲内でしか実行されないため、時間帯を区切った運用がしやすい

• 内部的に記録した列の使用状況に基づいて網羅的に統計収集されるため、開発者が必要性に気づかなかった列にもヒストグラムが作成され、実行計画の精度が高くなる

自動統計収集のメリットを最大限に活かして運用するとすれば、スケジューラウィンドウの設定のみを変更するのが良いでしょう。スケジューラウィンドウは、デフォルトのままで運用できるケースのほうが少ないと言えます。設定は、DBMS_SCHEDULERパッケージもしくはEnterpriseManagerから変更可能です。自動統計収集のためのウィンドウの設定を変更する場合は、以下の点に注意してください。

• 統計収集に伴う負荷でオンライン処理やバッチ処理を遅延させないため、オンライン時間帯やバッチ時間帯とは重ならないようにする（もしくは負荷の低い時間帯を選択する）

• 大量の更新を伴うバッチ処理の場合、データ変動の影響を反映させてオンライン処理に備えるため、バッチ処理の後に統計を収集する

• 複雑な問い合わせを発行する（帳票出力のような）バッチ処理が行なわれる場合、バッチ前に統計を収集し、それまでの更新の影響を反映させる

• ウィンドウのオープン期間を短くし過ぎない。サイズの大きなオブジェクトの統計収集が期間内に完了しなくなる可能性がある

場合によっては、毎晩のウィンドウ（WEEKNIGHT_WINDOW）は使用せず、週末だけ実施するという判断も可能です。

「統計収集の頻度はどれくらいが妥当か？」という質問は昔からありますが、正解は「何のために統計収集しているのか」を考えれば分かります。統計の再収集は、「SQL実行計画を変えたほうが良いほどのデータの変動」を検出するためであり、実行計画を変える必要のない程度の変動であれば、収集処理は無駄な作業です。一般的に、OLTP系のシステムでは最適な実行計画が1週間や2週間のオーダーで変わるものではありません。以前のバージョンのOracleで統計収集が半年に一度で十分だったのであれば、10gにアップグレードした途端に毎晩実施するような必要はありません。

ただし、そのような「経験的に適切な収集頻度が分かっている」システム以外では、最適な収集頻度を見つけるためにかなりのテストと試行錯誤が必要になります。そのような作業にコストを払えない環境では、何らかのしきい値を設けてプロアクティブに（予防的に）新しい統計を用意しておくと良いでしょう。10gの自動統計収集はこのようなシステムに最適と言えます。 反対に、自動統計収集のデメリットを挙げてみましょう。

• 1回の統計収集の負荷、所要時間が予測できない統計収集のたびに、対象となる表の数やサンプルサイズ、作成するヒストグラムの数が異なる可能性があるため、ウィンドウのオープン時間が短いと必要な統計収集をすべて完了できないことがあります。

• 必要なヒストグラムや統計の精度（サンプルサイズ）などを個別に指定できない DBMS_STATS.SET_PARAM（）を使用して、自動統計収集が使用するいくつかのデフォルト値を変更できますが、オブジェクトごとの個別対応はできません。

• アプリケーションが走行してからでないと必要な統計（ヒストグラム）が収集されない 事前準備としてDBMS_STATSを使用して手動でヒストグラムを収集しても、アプリケーションの走行前に自動統計が収集されると、「使用されていない列」としてヒストグラムが消されます。

• 実際には必要ないオペレーションの可能性がある

内部的に記録した行の更新状況や列の使用状況に基づいて一律に統計再収集を決定したり、ヒストグラム作成を決定したりしているため、再収集したものの統計的な変化がない場合があります。条件指定されたことのある列は、アプリケーションの変更によって使用されなくなったとしても、しばらくの間（6ヶ月）はヒストグラム作成の対象となります。

安定性や予測可能性（所要時間やリソース消費量がいつも同じで、正確な見積もりが可能であること）を最大限重視する場合は、デフォルトの自動統計収集はオフにして、手動で収集 注18したほうが良いでしょう。その際、統計収集パラメータとして「自動設定」は使用せず、数値で指定可能なもの（サンプルサイズ／バケット数／パラレル度）は数値で指定することをお勧めします。また、オブジェクトごとに個別の収集パラメータを指定して、きめ細かな管理をしたい場合にも、自動統計収集をオフにした上で手動で収集してください。

>注18：各種統計収集パラメータ（サンプリング方法／サンプルサイズ／ヒストグラム対象列／パラレル度／パーティション表の扱い／SQLの無効化など）を適切に設定して、DBMS_STATSパッケージプロシージャをコールします。

ただし、このような手動収集で適切なパラメータ値を決定するためには、やはりテストを繰り返す必要があります。とはいえ、オンライン処理のレスポンスやバッチ処理の所要時間は厳密な管理をしていないのに、オプティマイザ統計収集のみ厳密な設定をするためにテストを繰り返す必要はないでしょう。システムの重要性に応じて判断してください。

自動統計収集ではアプリケーションが実際に走行しないと必要なヒストグラムが作られません。これは、リグレッションツールなどを使用してシステムのカットオーバー前に網羅的にアプリケーションを走行させられない環境では問題かもしれません。その場合、必要なヒストグラムが分かっていれば手動で作成し、しばらく自動統計収集を止めた状態で運用し、アプリケーションのすべての機能が使用されたころを見計らって自動統計収集を開始するというアイディアもあります。

最後に、自動統計収集が無駄に統計収集をしているかもしれないという点は、それが自動統計収集の本質であり、無駄であろうとも機械的に収集することで陳腐化するのを防ぐというアプローチであることを理解する必要があります。「無駄に収集しているかどうか」は人間の管理者の経験によってのみ判断できることです。データの統計的な変動がほとんどないことが分かっているシステムでは、自動統計収集を使用する必要はありません。適宜、手動で収集してください。

自動統計収集では、いつ、どの表が統計収集されたか、サンプルサイズはどの程度であったか、どの列にヒストグラムが作成されたかを監視することをお勧めします。これらの情報は将来、手動収集に切り替える必要ができた場合の重要なインプットになります。また、統計収集の頻度があまりに低い表は、更新がほとんど発生していないことも考えられますが、表が大きすぎてウィンドウ内で完了していない可能性もあります。「GATHER_STATS_JOB」のところで記述したようにトレースファイルも確認して、強制中断されているオブジェクトがある場合はウィンドウの長さが妥当かどうかを検討してください。監視例をLIST6およびLIST7に示しておきます。

LIST6 監視例(1)：昨晩22時以降に統計収集された表の確認

LIST7 監視例(2)：過去１ヶ月以上、統計収集されていない表の確認

自動統計収集はCBOに対する敷居を下げる優れた機能と言えます。しかし、統計収集によって行なわれる作業が「いつも同じである」という安定性を重視する場合や、統計収集の最適な頻度や対象オブジェクトが分かっているようなケース、オブジェクトレベルできめ細かな管理をしたい場合は手動統計収集をお勧めします。自動統計収集を利用する場合は、スケジューラウィンドウを適切に設定し、統計収集の実行状況を監視すると良いでしょう。

次のプロシージャを実行することにより、自動統計収集をオフにできます。

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

ONにする場合は「ENABLE」プロシージャを実行します。

▲ ページTOPに戻る

統計履歴の自動保存

定期的な統計収集のリスク

「オプティマイザ統計を再収集したら、SQL実行計画が変化し、性能が劣化した」という経験がリスクとして認識され、定期的な統計が収集されていないケースがあります。この問題についてはCBOの本質として、最後に「CBOを使いこなすためには」の部分で考察しますが、このリスクはCBOで運用する上では避けられないものと言えます。 もちろんこの問題でトラブルに陥らないために、最低でも以下に注意しなければなりません。

• 統計収集対象に「漏れ」がないこと（列に対するヒストグラムも含め、必要なオブジェクトが必要なレベルで統計収集されていること）

• 適切な精度（サンプルサイズ）で収集されていること

• データの変動に応じた適切な頻度で再収集されていること

• システム状況の変動に応じてシステム統計が収集されていること

しかしながら、厳密に「適切な精度／頻度」とは何かというのは、経験によってしか分かりません。ある程度運用していて、問題が起こらない場合の精度／頻度が「適切」なわけです。そのため、適切な設定が分かるまでは「適切でない」設定での試行錯誤を繰り返すことになります。

また、たとえ統計が「適切」に収集されていたとしても、オプティマイザがコストとして評価するときの仮定が現実と異なっていれば、最適ではない実行計画が選択される可能性があります 注19。したがって、統計再収集によってSQL実行計画が変化し性能が劣化するという現象は、程度の違いはあるものの、起き得ることと考えて運用を設計しておくべきです。

これを念頭に置いた最も現実的な運用は、統計再収集の前に現在の統計情報のバックアップを取っておき、新しく収集した統計情報によって何らかのトラブルが発生した場合は、とりあえず以前の統計情報をリストアし、SQLの性能を以前の状態に戻すというものです。

注19：オプティマイザの仮定を補正するいくつかの方法についてパート2で述べましたが、これも含めてソフトウェアとしてのオプティマイザの「限界」がある、と理解しても構いません。

統計履歴の自動保存とリストア

9iではこのような統計情報のバックアップをユーザーが管理する必要がありましたが、10gでは、オブジェクトの新しいオプティマイザ統計を収集すると、以前の統計を自動的に保存してくれます。これは履歴として管理され、デフォルトでは31日前までの履歴が保存されています（履歴情報はSYSAUX表領域内に格納されています）。履歴を保存しているため、以前の任意の時点の統計を、現在値としてディクショナリにリストア可能です。つまり、31日前までのどの時点にでも復元できます。

統計履歴が自動保存されるのはDBMS_STATSパッケージを使用して統計収集した場合のみです。ANALYZEコマンドによって統計収集を行なった場合、統計履歴は自動保存されません。

「統計収集後、SQL実行計画が変化し、性能が劣化した」というケースでは、この新機能と以前からある統計情報のエクスポート／インポート機能を利用して、以下のような運用が可能です。

• 統計再収集後に一部のSQLの性能が劣化したことが判明（原因の詳細な分析や、パラメータを変えて再度、統計収集する時間がない場合に以下を実行）

• 現在の統計値をエクスポートして退避

• 適切な性能が出ていた過去の時点を指定して、オプティマイザ統計をリストア（この時点で本番システムは過去の性能に戻る）

• エクスポートして退避した統計値を、テスト環境にインポートし、問題を分析

• 分析中は本番環境の自動統計収集を停止するか、問題があると思われる一部の統計をロックし、上書きされないようにする

• テスト環境での分析結果を受け、必要な対策を行なった上で統計収集を再開

問題が発生したときに、単純に過去の統計に戻しただけでは、それ以降の統計収集でも再度同じ問題が発生するかもしれません。そのため、テスト環境に問題の統計を移行して解析することは重要です。

本番環境のオプティマイザ統計をエクスポートし、テスト環境にインポートすることで、実データ量が少ないテスト環境や開発環境において、本番環境で選択されるであろう実行計画をシミュレートできます。SQLチューニング時に本番環境が使用できない場合（多くの場合そうだと思いますが）、テスト環境で最良のチューニング方法を検討する場合に非常に有効な方法です。

以下にスキーマ単位で統計をリストアする手順例を示します。この例ではTESTスキーマを使用しています。まず、現在の統計のタイムスタンプ（収集時刻）を確認します（索引統計、列統計のタイムスタンプの確認は省略しています）。

alter session set nls_date_format='yy/mm/dd hh24:mi:ss';
select table_name,last_analyzed,stattype_locked
from user_tab_statistics order by table_name;

TABLE_NAME LAST_ANALYZED     STATTYPE_LOCKED
---------- ----------------- ----------------
J_TEST1    04/11/23 22:00:49
J_TEST2    04/11/23 22:00:50

統計表を作成し、現在の統計を退避しておきます。

--統計表TEST_STATTABをTESTスキーマに作成
exec dbms_stats.CREATE_STAT_TABLE('TEST','TEST_STATTAB')
--統計表TEST_STATTABにTESTスキーマのすべてのオブジェクト統計をエクスポート
exec dbms_stats.EXPORT_SCHEMA_STATS('TEST','TEST_STATTAB')
--統計表にエクスポートされた統計値のタイムスタンプを確認
select distinct d1 from TEST_STATTAB;
D1
-----------------
04/11/23 22:00:49
04/11/23 22:00:50
04/11/23 22:00:51

現在の統計と同じ時刻の統計がエクスポートされています。22:00:51の統計は、上の例では参照していない索引統計もしくは列統計です。 次に、USER_TAB_STATS_HISTORYで履歴を確認し、DBMS_STATS.RESTORE_SCHEMA_STATSを使って正常なパフォーマンスだった時点の統計をリストアします（LIST8）。

LIST8 正常なパフォーマンスだった時点の統計をリストア

次にデータディクショナリの統計がリストアされたことを確認します（LIST9）。

LIST9 データディクショナリの統計がリストアされた

原因の分析中に再度、統計収集が行なわれて同じ問題が再発することを防ぐため（ほかのスキーマの統計は問題ないと判断できる場合）、DBMS_STATS.LOCK_SCHEMA_STATSで統計情報をロックします（LIST10）。

LIST10 統計情報をロック

ロックされた統計は、自動統計収集が行なわれても更新されません。ロック解除する際はUNLOCK_SCHEMA_STATSプロシージャを実行します。 リストア前にTEST_STATTAB表に退避しておいた統計は、通常のexp/impでテストDBに移行し、ディクショナリに反映させます（LIST11）。

LIST11 テストDBへ移行してディクショナリに反映

次はテストDBにおいて、不適切な実行計画を引き起こしたと思われる統計の問題点を調査します。サンプルサイズが小さすぎないか、ヒストグラムのバケット数が変化していないかなどを確認します。場合によってはSQLチューニングやサポートセンターへの問い合わせが必要になるケースも考えられます。原因が究明され、対処法を実装したら統計情報のロックを解除し、定期的な統計収集の運用を再開します。

オプティマイザ統計の再収集によってSQL実行計画が変化し、性能が劣化する現象は、起き得ることとして運用を設計しておく必要があります。

10gではデフォルトで統計履歴が自動保存されており、31日前までのどの時点にでも戻せます。統計再収集によって性能問題が発生した場合は、保存された統計をリストアすることによって以前の性能に戻して運用できます。この場合、単純に過去の統計に戻しただけでは、それ以降の統計収集でも同じ問題が再発する可能性があるため、テスト環境に問題の統計を移行して解析することをおすすめします。

▲ ページTOPに戻る

動的サンプリング

Oracleでは事前に収集されたオプティマイザ統計ではなく、SQLの実行時（ハードパース時）に動的に統計情報をサンプリングし、その結果を元に実行計画の生成が可能です。このような統計収集のことを「動的サンプリング」と言います。

Oracle 10gではデフォルトで、事前に収集された統計情報が存在しない表に対してSQL文を発行したときは、64ブロックのブロックサンプリングが実行されます。 例えば、次の問い合わせが参照しているTEST2表には、統計情報が存在しないとします。

select count(col1) from TEST2 where COL2=10 and COL3=10;

動的サンプリングのために、以下のようなSELECT文が再帰コールとして発行されています。

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS
IGNORE_WHERE_CLAUSE
   NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */
NVL(SUM(C1),0),
NVL(SUM(C2),0)
FROM
   (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TEST2")
FULL("TEST2")
NO_PARALLEL_INDEX("TEST2") */ 1 AS C1, CASE WHEN
"TEST2"."COL2"=10 AND
   "TEST2"."COL3"=10 THEN 1 ELSE 0 END AS C2

   FROM "TEST2" SAMPLE BLOCK (73.255814 , 1) SEED (1) "TEST2") SAMPLESUB

SAMPLE BLOCK句でTEST2表に対する約73%のブロックサンプリングが指定されています。TEST2表は86ブロックの表であり、73.255814パーセントのサンプリングは63ブロック（≒64ブロック）に相当します。このサンプリングSQLを実行すると、次のような出力が得られます。

NVL(SUM(C1),0) NVL(SUM(C2),0)
-------------- --------------
7807           100

7807行がサンプリングされ、ユーザーの問い合わせ条件である「COL2=10 AND COL3=10」にヒットした行は100行であったことが分かります。 このほかに、索引の統計情報を取得するためのサンプリングSQLも実行されます。

動的サンプリングの用途

動的サンプリングは、例えば1秒未満の高速なレスポンスが要求されるシステムにおいては、無視できない処理オーバーヘッドです。また、短期間における大きなデータ変動のないシステムにおいては、事前に準備しておいた統計を使用したほうがよほど効率的です。

動的サンプリングを行なう価値のある代表例としては、「一時表（Temporary Table）」に対するアクセスがあります。一時表にデータを一時的に置いておき、そのデータを使用して別の処理を行なうようなアプリケーションの場合、一連の処理が始まる前は一時表は空ですから、事前に統計収集しても意味がありません。このようなケースにおいて動的サンプリングは最適と言えます。 もう一点、動的サンプリングのメリットは、単一表の列同士に相関関係があるようなケースで、それを反映した選択率を導出できる点があります。例えば、EMP表のJOB列とSALARY列を考えてみましょう。「WHERE JOB='MANAGER'and SALARY>5000」という条件で問い合わせたとします。通常、オプティマイザはそれぞれの列に相関関係はなく、独立しているとみなすため、次のように選択率を計算します。

Sel(JOB='MANAGER' and SALARY>5000) =
Sel(JOB='MANAGER') * Sel(SALARY>5000)

例えば、Sel（JOB='MANAGER'）が0.1で、Sel（SALARY>5000）が0.1だとしたら、オプティマイザはEMP表からの選択率は0.01（0.1×0.1）とみなします。 しかし、実際にはJOB='MANAGER'を満たすレコードがすべてSALARY>5000を満たしていたらどうでしょう（「マネージャの給料は高い」という相関関係です）。本当の選択率はSel（JOB='MANAGER'）と同じになり、0.1です。 動的サンプリングによって「WHERE JOB='MANAGER' and SALARY>5000」の条件で実際に行をサンプリングしてみることにより、そのような相関関係がある場合の選択率を正しく検出できます（検出された統計値の精度は、サンプリングされるブロック数に依存します）。

DWH系のシステムであれば、このような相関関係のある列に対する問い合わせを適切にハンドリングさせるために、動的サンプリングを使用できるかもしれません。OLTP系のシステムで、安定性を重視して自動統計収集を意図的に停止するような場合は、統計情報を取り忘れたオブジェクトに対して動的サンプリングが発生しないように、この機能もOFFにしたほうが良いでしょう。 なお、動的サンプリングの使用法を決めるパラメータOPTIMZER_DYNAMIC_SAMPLINGはセッションレベルで変更可能（ALTER SESSION文）であり、SQLレベルで設定する場合には、DYNAMIC_SAMPLING（表名 n）ヒントを利用可能です（nはサンプリングレベル）。

Oracle 10gでは、統計収集されていない表に対するハードパース時には動的サンプリングが行なわれます。通常、高速なレスポンスが要求されるOLTPシステムでは、動的サンプリングはオーバーヘッドとなります。有効な用途としては一時表に対するアクセスがあります。DWH系のシステムでは相関関係のある列に対する選択率を適切に見積もるのに有効な場合があります。

動的サンプリングの設定にはoptimizer_dynamic_samplingパラメータを用います。デフォルト値は2であり、本文で説明したような動作をします。値を0に設定すると、動的サンプリングはOFFになります。設定値を大きくすることにより、サンプリングされるブロック数を増やせます。なお9i R2ではデフォルト値は1であり、動的サンプリングが発生する条件は10gよりも厳しくなっています。

Part4　実行計画の固定と自動チューニングオプティマイザの活用方法 >>

▲ ページTOPに戻る

"門外不出のOracle現場ワザ" インデックスに戻る

Copyright © 2009, Oracle Corporation Japan. All rights reserved.

無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle

Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

小田 圭二(おだ けいじ)

1996年日本オラクル入社。人事教育本部にて、新卒や中途採用社員に対し、データベースやOS、ネットワークの講師を5年ほど経験した後、2000年にテクノロジーコンサルティング本部に異動。 テクノロジーのコンサルタントとして、主に大規模ミッションクリティカルシステムを担当。

ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき」。 スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。