門外不出のOracle現場ワザ第4章 Oracleデータベースの頭脳 「オプティマイザ」徹底研究日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部
小田 圭二(おだ けいじ) 目次
Part3 オプティマイザ統計の管理さて、これまでオプティマイザがどんな情報を利用し、どういう仮定を立て、どういう計算で実行計画を選択しているかについて説明してきました。次に、オプティマイザ統計の管理について知っていただきたいと思います。 自動統計収集GATHER_STATS_JOBOracle 10gでは、データベースを作成するとデフォルトでGATHER_STATS_JOBと呼ばれる自動統計収集のためのスケジューラジョブが準備されます 注17。ジョブの定義はLIST1のように確認できます。
注17：DB作成時の必須スクリプトであるcatproc.sqlから呼ばれるスクリプトによって作成されています。
ジョブが実行するプログラムはGATHER_STATS_PROGとなっていますが、実際に実行されるストアドプロシージャは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のように確認できます。
次のように解釈します。
ウィンドウがオープンすると（開始時刻になると）、関連付けられているジョブが開始されます。自動統計収集（GATHER_STATS_JOB）がいつ開始され、完了までどれくらいの時間がかかったかはLIST4のように確認できます。
ジョブのSTOP_ON_WINDOW_CLOSE属性がTRUEであるため、ウィンドウがクローズすると（終了時刻が来ると）、このジョブは実行中であっても強制的に停止されます。統計収集ジョブが完了する前にウィンドウがクローズすると、LIST5のようなトレースファイルが出力されます。
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC次に、実際に自動統計収集を行なっているdbms_stats.gather_database_stats_job_proc（）プロシージャについて説明しましょう。
10gではデフォルト（STATISTICS_LEVEL=TYPICALの場合）で、すべての表に対するDMLの発行状況をOracleがモニタリングし、ディクショナリに記録しています。この記録に基づいて、最後の統計収集以後に大量の行（全体の10%以上）が更新された表については、既存のオプティマイザ統計が「古くなった」ものとみなして再収集の対象とします。 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を実行します。 自動統計収集の使い方デフォルトで用意されている自動統計収集ジョブを使用するメリットは、以下のような点です。
自動統計収集のメリットを最大限に活かして運用するとすれば、スケジューラウィンドウの設定のみを変更するのが良いでしょう。スケジューラウィンドウは、デフォルトのままで運用できるケースのほうが少ないと言えます。設定は、DBMS_SCHEDULERパッケージもしくはEnterpriseManagerから変更可能です。自動統計収集のためのウィンドウの設定を変更する場合は、以下の点に注意してください。
場合によっては、毎晩のウィンドウ（WEEKNIGHT_WINDOW）は使用せず、週末だけ実施するという判断も可能です。
内部的に記録した行の更新状況や列の使用状況に基づいて一律に統計再収集を決定したり、ヒストグラム作成を決定したりしているため、再収集したものの統計的な変化がない場合があります。条件指定されたことのある列は、アプリケーションの変更によって使用されなくなったとしても、しばらくの間（6ヶ月）はヒストグラム作成の対象となります。 注18：各種統計収集パラメータ（サンプリング方法／サンプルサイズ／ヒストグラム対象列／パラレル度／パーティション表の扱い／SQLの無効化など）を適切に設定して、DBMS_STATSパッケージプロシージャをコールします。
ただし、このような手動収集で適切なパラメータ値を決定するためには、やはりテストを繰り返す必要があります。とはいえ、オンライン処理のレスポンスやバッチ処理の所要時間は厳密な管理をしていないのに、オプティマイザ統計収集のみ厳密な設定をするためにテストを繰り返す必要はないでしょう。システムの重要性に応じて判断してください。 最後に、自動統計収集が無駄に統計収集をしているかもしれないという点は、それが自動統計収集の本質であり、無駄であろうとも機械的に収集することで陳腐化するのを防ぐというアプローチであることを理解する必要があります。「無駄に収集しているかどうか」は人間の管理者の経験によってのみ判断できることです。データの統計的な変動がほとんどないことが分かっているシステムでは、自動統計収集を使用する必要はありません。適宜、手動で収集してください。
自動統計収集はCBOに対する敷居を下げる優れた機能と言えます。しかし、統計収集によって行なわれる作業が「いつも同じである」という安定性を重視する場合や、統計収集の最適な頻度や対象オブジェクトが分かっているようなケース、オブジェクトレベルできめ細かな管理をしたい場合は手動統計収集をお勧めします。自動統計収集を利用する場合は、スケジューラウィンドウを適切に設定し、統計収集の実行状況を監視すると良いでしょう。 次のプロシージャを実行することにより、自動統計収集をオフにできます。
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
ONにする場合は「ENABLE」プロシージャを実行します。 統計履歴の自動保存定期的な統計収集のリスク「オプティマイザ統計を再収集したら、SQL実行計画が変化し、性能が劣化した」という経験がリスクとして認識され、定期的な統計が収集されていないケースがあります。この問題についてはCBOの本質として、最後に「CBOを使いこなすためには」の部分で考察しますが、このリスクはCBOで運用する上では避けられないものと言えます。
しかしながら、厳密に「適切な精度／頻度」とは何かというのは、経験によってしか分かりません。ある程度運用していて、問題が起こらない場合の精度／頻度が「適切」なわけです。そのため、適切な設定が分かるまでは「適切でない」設定での試行錯誤を繰り返すことになります。 注19：オプティマイザの仮定を補正するいくつかの方法についてパート2で述べましたが、これも含めてソフトウェアとしてのオプティマイザの「限界」がある、と理解しても構いません。
統計履歴の自動保存とリストア9iではこのような統計情報のバックアップをユーザーが管理する必要がありましたが、10gでは、オブジェクトの新しいオプティマイザ統計を収集すると、以前の統計を自動的に保存してくれます。これは履歴として管理され、デフォルトでは31日前までの履歴が保存されています（履歴情報はSYSAUX表領域内に格納されています）。履歴を保存しているため、以前の任意の時点の統計を、現在値としてディクショナリにリストア可能です。つまり、31日前までのどの時点にでも復元できます。 統計履歴が自動保存されるのはDBMS_STATSパッケージを使用して統計収集した場合のみです。ANALYZEコマンドによって統計収集を行なった場合、統計履歴は自動保存されません。 「統計収集後、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の統計は、上の例では参照していない索引統計もしくは列統計です。
次にデータディクショナリの統計がリストアされたことを確認します（LIST9）。
原因の分析中に再度、統計収集が行なわれて同じ問題が再発することを防ぐため（ほかのスキーマの統計は問題ないと判断できる場合）、DBMS_STATS.LOCK_SCHEMA_STATSで統計情報をロックします（LIST10）。
ロックされた統計は、自動統計収集が行なわれても更新されません。ロック解除する際はUNLOCK_SCHEMA_STATSプロシージャを実行します。
次はテストDBにおいて、不適切な実行計画を引き起こしたと思われる統計の問題点を調査します。サンプルサイズが小さすぎないか、ヒストグラムのバケット数が変化していないかなどを確認します。場合によってはSQLチューニングやサポートセンターへの問い合わせが必要になるケースも考えられます。原因が究明され、対処法を実装したら統計情報のロックを解除し、定期的な統計収集の運用を再開します。 オプティマイザ統計の再収集によってSQL実行計画が変化し、性能が劣化する現象は、起き得ることとして運用を設計しておく必要があります。 動的サンプリングOracleでは事前に収集されたオプティマイザ統計ではなく、SQLの実行時（ハードパース時）に動的に統計情報をサンプリングし、その結果を元に実行計画の生成が可能です。このような統計収集のことを「動的サンプリング」と言います。 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行であったことが分かります。 動的サンプリングの用途動的サンプリングは、例えば1秒未満の高速なレスポンスが要求されるシステムにおいては、無視できない処理オーバーヘッドです。また、短期間における大きなデータ変動のないシステムにおいては、事前に準備しておいた統計を使用したほうがよほど効率的です。 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）とみなします。 Oracle 10gでは、統計収集されていない表に対するハードパース時には動的サンプリングが行なわれます。通常、高速なレスポンスが要求されるOLTPシステムでは、動的サンプリングはオーバーヘッドとなります。有効な用途としては一時表に対するアクセスがあります。DWH系のシステムでは相関関係のある列に対する選択率を適切に見積もるのに有効な場合があります。 動的サンプリングの設定にはoptimizer_dynamic_samplingパラメータを用います。デフォルト値は2であり、本文で説明したような動作をします。値を0に設定すると、動的サンプリングはOFFになります。設定値を大きくすることにより、サンプリングされるブロック数を増やせます。なお9i R2ではデフォルト値は1であり、動的サンプリングが発生する条件は10gよりも厳しくなっています。 Part4 実行計画の固定と自動チューニングオプティマイザの活用方法 >>
|
||