コストベースオプティマイザを使用するにはオプティマイザ統計情報が必要です。
ゆえに、オプティマイザ統計情報の自動収集、手動収集を適宜実行し、有効なオプティマイザ統計情報を維持することが重要になります。
ただし、揮発性の高い表のオプティマイザ統計情報の収集については注意が必要です。
揮発性の高い表というのは、必要なデータを一時的に格納する作業領域的な表のように、一時的に大量のデータを保持することもあるが、必要がなくなるとすべて削除するというようなデータ量の変動の大きい表のことを指します。
これらの表についてはオプティマイザ統計情報を収集するタイミングとそれを利用するタイミングで実際のデータ量が大きく異なり、オプティマイザ統計情報が失効する可能性が高くなります。
このような表については以下の２つの方法によって、コストベースオプティマイザの動作を最適化できる可能性があります。
１つはオプティマイザ統計情報を収集せず、動的サンプリングを適用する方法です。
オプティマイザ統計情報を収集しないようにするには、まずオプティマイザ統計情報を削除し、その状態のままロックします。
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('owner_name','table_name');
DBMS_STATS.LOCK_TABLE_STATS('owner_name','table_name');
END;
/
動的サンプリングを適用させるためには、OPTIMIZER_DYNAMIC_SAMPLING 初期化パラメータを 2 以上に設定します。
このようにすることによって、オプティマイザ統計情報がない状態を維持し、常に動的サンプリングを適用することができるようになります。
もう１つはその表に最も典型的なデータが格納されている時にオプティマイザ統計情報を取得し、そのオプティマイザ統計情報を固定する方法です。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('owner_name','table_name');
DBMS_STATS.LOCK_TABLE_STATS('owner_name','table_name');
END;
/
このようにすることによって、データ量の変動に影響されず、安定的なパフォーマンスを発揮できるようになる可能性があります。
どの方法を用いるのが適切なのかはケースバイケースですが、揮発性の高い表を扱う SQL のパフォーマンスが問題になる場合は、上記のような方法を検証してみるべきかと思います。
【関連エントリ】
[Oracle] オプティマイザ統計情報の収集は基本的にOracle任せでOK
[Oracle] オプティマイザ統計情報を手動収集する
[Oracle] ルールベースオプティマイザの廃止と動的サンプリング
[Oracle] 一時表のオプティマイザ統計情報の収集について