動的サンプリングは表を作成した直後など、オプティマイザ統計情報が欠落した状態でもコストベースオプティマイザが機能するようフォローする便利な機能です。
また、一時表や揮発性の高い表のようにデータ量の変動の激しい表については、統計情報を削除した状態でロックし、SQL の実行時に動的サンプリングを利用して統計情報を収集することによって、より最適な実行計画を得ることができます。
動的サンプリングの落とし穴
ただし、この動的サンプリングには落とし穴があります。動的サンプリング機能が有効なのは、ハードーパース時のみです。
つまり、最初の実行時には統計情報が存在しない表を動的にサンプリングしてデータ量に応じた最適な実行計画を適用しますが、2 回目以降の実行時には動的サンプリングは行われず、最初の実行時に決定された実行計画が再利用されます。
ゆえに、2 回目以降の実行時に、最初の実行時に動的サンプリングの対象となった表の内容が著しく変化している場合は、データ量にそぐわない不適切な実行計画によって SQL が実行されてしまうことになり、パフォーマンスに深刻な影響を与えることがあります。
複数の表を結合するような複雑な SQL の場合は、致命的なパフォーマンスの悪化を引き起こす可能性もありますので特に注意が必要です。
動的サンプリングの無効化
SQL を実行するたびに動的サンプリングを実行できるような機能があれば、このようなパフォーマンス悪化のリスクは回避できるのですが、動的サンプリング自体に実行コストがかかるためか、残念ながらそのような機能はないようです。このようなリスクを回避するためには、どうやら動的サンプリングが使用されないようにするほかないようです。
動的サンプリングが使用されないようにする一番手っ取り早い方法は、動的サンプリング機能を無効化することです。
動的サンプリング機能を無効にするには、無効にする範囲に応じて2通りの方法があります。
インスタンスレベル、セッションレベルで無効にする場合 OPTIMIZER_DYNAMIC_SAMPLING 初期化パラメータの値を 0 にします。
SQL、表単位で無効にする場合は DYNAMIC_SAMPLING ヒントを指定し、サンプリング・レベルを 0 にします。
例えば、SQL 単位で無効にする場合は
SELECT /*+ dynamic_sampling(0) */ FROM EMP E ...SQL 内の表単位で無効にする場合は
SELECT /*+ dynamic_sampling(E 0) */ FROM EMP E ...というような形式で DYNAMIC_SAMPLING ヒントを指定します。
ダミーの統計情報の作成
動的サンプリングを無効にした場合、コストベースオプティマイザは”統計が欠落しているときの表のデフォルト値”を使用して実行計画を決定します。統計が欠落しているときの表のデフォルト値はOracleのマニュアルによると以下のようになっているようです。
| 表統計 | オプティマイザによって使用されるデフォルト値 |
|---|---|
| カーディナリティ | ブロック数×（ブロック・サイズ－キャッシュ層）÷行の平均の長さ |
| 行の平均の長さ | 100バイト |
| ブロック数 | 100、またはエクステント・マップに基づく実際の値 |
| リモート・カーディナリティ | 2000 行 |
| リモートの行の平均長さ | 100バイト |
ゆえに、実際に平均的なデータ量がこのデフォルト値に比較的近い場合は、動的サンプリングを無効にしても良好なパフォーマンスが期待できますが、著しく異なる場合は良好なパフォーマンスは期待できなくなります。
このような場合は、対象となる表に平均的なダミーデータを登録し、その状態で統計情報を取得してその統計情報をロックしてしまうという方法も考えられます。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('owner_name','table_name');
DBMS_STATS.LOCK_TABLE_STATS('owner_name','table_name');
END;
/
この問題、以前触れた”バインド・ピーク機能の落とし穴 ”とよく似ています。
動的サンプリング機能にしても、バインド・ピーク機能にしても、実際のデータを考慮してより最適なSQLの実行計画を決定してくれる非常に優れた機能ですが、これがハード・パース時にしか機能しないために致命的なパフォーマンスの問題を引き起こしてしまうという危険性もはらんだ両刃の剣となってしまっているのが、なんとも残念なところです。
キャッシュされた実行計画で見積もられているコストよりも著しく実行コストが大きくなると判断した場合に、実行計画を破棄してSQLを再解析するような機能でも追加されればもう少し使いやすいと思うのですが．．．
今後の機能拡張に期待したいところです。
Oracle11gではこのあたりどうなっているのでしょうか？