SQL 照会がコンパイルされると、多数の最適化技法を使用して、 その照会に最も効果的なアクセス・プランを決定することができます。 複数の最適化技法を使用することにより、以下の効果があります。
このため、最適化クラスを設定することにより、 照会の最適化に適用される技法の数を制限することができます。 これは、特に以下の場合に便利です。
照会最適化クラスとしては、以下に説明するもののいずれかを選択できますが、 クラス 0 とクラス 9 は特殊な環境でのみ使用するようにしてください。 クラス 5 は省略時値です。 クラス 0、1、2 は、貪欲型結合列挙アルゴリズムを使用しています。 複雑な照会の場合には、このアルゴリズムでは代替計画はほとんど考慮に入れないので、 クラス 3 以上に比べてコンパイル時間が大幅に短縮されます。 クラス 3 以上では、動的プログラミング結合列挙アルゴリズムを使用しています。 このアルゴリズムではより多くの代替計画を考慮に入れようとするので、 表の数が増えるにつれて、クラス 0、1、2 に比べてコンパイル時間が大幅に長くなる可能性があります。
このクラスを使用するのは、 照会コンパイル・オーバーヘッドを最小限にすることが必要な特殊な環境の場合だけにしてください。 適切に索引付けされた表にアクセスする非常に単純な動的 SQL ステートメントだけで構成されるアプリケーションでは、 照会最適化クラス 0 が適しています。
注: | 索引 AND 結合は、 スター型結合で見つかった半結合を処理する際に引き続き使用されます。 |
最適化クラス 1 は、マージ走査結合と表走査も使用可能である点を除けば、 クラス 0 と同じ働きをします。
最適化クラス 2 は、動的プログラミングではなく貪欲型結合列挙を使用する点を除けば、 クラス 5 と同様な働きをします。 このクラスは、貪欲型結合列挙アルゴリズムを使用する最適化クラスの中では最も高度な最適化であり、 複雑な照会の場合にあまり代替計画を考慮しないので、 クラス 3 以上と比べてコンパイル時間は少なくて済みます。 したがって、意思決定支援またはオンライン分析処理 (OLAP) 環境において非常に複雑な照会を行う場合には、 このクラスをお勧めします。 このようなケースでは、同じ照会は頻繁には行われないので、 その照会が次に行われるまでそのアクセス・プランがキャッシュに残っていることはほとんどありません。
このクラスは、広い範囲のアプリケーションに適しています。 このクラスを使用すると、最適化プログラムが、 4 つ以上の結合の照会に最適のアクセス・プランを選択できるようになります。 しかし、最適化プログラムがより良いプランを考慮するのに失敗し、 省略時の照会最適化クラスを選択することもあります。
最適化プログラムが、 複合動的 SQL 照会に追加のリソースおよび処理時間が保証されないことを検出すると、 最適化は縮小されます。 縮小のエクステントつまりサイズは、マシンのサイズと述部の数によって決まります。
照会最適化プログラムが実行される照会最適化の量を縮小すると、 通常は適用される照会書き直し規則をすべて適用し続けます。 しかし、照会最適化プログラムは貪欲型結合列挙方法を使用するため、 考慮されるアクセス・プランの組み合わせの数が少なくなります。
照会最適化クラス 5 は、 トランザクションと複合的な照会の両方で構成される混合環境に適した選択です。 この最適化クラスは、 最も価値のある照会変換技法およびその他の照会最適化技法を、 効率的な方法で適用させるよう設計されています。
このクラスでは、 最適化プログラムによって考慮される可能なアクセス・プランの数が大幅に拡張されます。 このクラスは、より包括的な最適化によって、 大型の表を使用する非常に複雑かつ非常に長時間実行する照会に適したアクセス・プランを生成できるかどうかを調べるために使用します。 よりすぐれたプランが見つかったかどうかを、 Explain とパフォーマンスの測定値を使って調べる必要があります。
特定の照会最適化クラスを要求する方法は、 静的 SQL を使用するか動的 SQL を使用するかによって違います。
SET CURRENT QUERY OPTIMIZATION = 1
動的 SQL ステートメントが常に同じ最適化クラスを使用するようにするには、 この SET ステートメントをアプリケーション・プログラムに組み入れることもできます。 詳細については、SQL 解説書 を参照してください。
CURRENT QUERY OPTIMIZATION レジスターが設定されていない場合、 動的ステートメントは、省略時の照会最適化クラスでバインドされます。 動的および静的の両方の SQL の省略時値は、 データベース構成パラメーター DFT_QUERYOPT の値によって決まります。 省略時値を変更していない限り、クラス 5 が省略時の照会最適化クラスとなります。 (このパラメーターの詳細については、省略時の照会最適化クラス (dft_queryopt)を参照してください。) バインド・オプションおよび特殊レジスターの省略時値は、 DFT_QUERYOPT 構成パラメーターに入っているものが使われます。
ほとんどのステートメントは、省略時の照会最適化クラスで、 ある程度の量のリソースを使って、十分に最適化できます。 特定の最適化クラスでの照会コンパイル時間とリソース消費は、主として、照会の複雑度、 特に結合と副照会の数によって影響を受けます。 しかし、コンパイル時間とリソースの使用量も、 様々な最適化クラスに対して実行される最適化の数によって影響を受けます。 最適化クラスの場合、単一の照会と非常に複雑な照会とでは、 照会コンパイル時間とリソース消費に歴然とした差が現れることを予想できます。
どの最適化クラスを使用するかを選択する際には、 以下に示すことを参考にしてください。
照会最適化クラス 1、2、3、5、および 7 はすべて、汎用に適していることに注意してください。
照会コンパイル時間をさらに短くし、 SQL (たとえば、極めて単純なステートメント) の種類を知る必要がある場合のみ、 クラス 0 を考慮するようにしてください。 この SQL には、以下の特性があります。
オンライン・トランザクション処理 (OLTP) のトランザクションは、 この種の SQL の良い例です。
複雑な照会では、最適なアクセス・プランを選択するのにさまざまな量の最適化が必要になる場合があります。 以下の特性を示す照会については、 より高度の最適化クラスを使用することもできます。
完全に正規化されたデータベースに対する判断支援照会または月末報告照会のようなものは、 少なくとも省略時照会最適化クラスが使用される複合照会の良い例です。
もっと高度な照会最適化クラスを使用するもう 1 つの理由は、 照会生成プログラムによって生成される SQL です。 多くの照会生成プログラムが作成する SQL は効率的ではありません。 照会生成プログラムによって生成されたものも含めて、 適切に作成されていない照会の場合は、 さらに最適化を行って良好なアクセス・プランを選択できるようにしなければならない場合があります。 照会最適化クラス 2 以上を使用すると、 あまり上手に作成されていない SQL 照会でも改善することができます。
静的 SQL または動的 SQL の使用、 および同じ動的 SQL が繰り返し実行されるかどうか、 ということも重要な考慮事項です。 静的 SQL の場合、照会コンパイル時間とリソースは 1 回だけ費やされ、 その結果としての計画は大量の時間を使用する可能性があります。 一般に、静的 SQL は常に省略時の照会最適化クラスを使用します。 動的ステートメントは実行時にバインドされ実行されるので、 動的ステートメントのための追加の最適化のオーバーヘッドが生じても総体的なパフォーマンスが向上するのかどうかということを検討してください。 ただし、同じ動的 SQL ステートメントが繰り返し実行される場合には、 選択されたアクセス・プランはキャッシュされることになります。 したがって、照会最適化クラスを選択するときには、 この種のステートメントは静的 SQL ステートメントと同様に扱うことができます。
(静的 SQL と動的 SQL をいつ使用するのかについての詳細は、 アプリケーション開発の手引き を参照してください。)
最適化を追加することで照会が便利になると考えられるが、 その確証がない場合、 またはコンパイル時間およびリソース使用のことを考慮している場合は、 何らかのベンチマーク・テストを実行することができます。 このテストによって、異なる最適化クラスから取得される利点を量で表すことができます。 一般的な技法および db2batch ツールの具体的な使用法については、 第 31 章, ベンチマーク・テストを参照してください。 ベンチマーク・テストを設計して実行するときは、以下に示すように、 アプリケーション内の SQL ステートメントが静的か動的かに合わせて検討を行ってください。
コンパイル時間 + すべての反復の実行時間の合計 -------------------------------------------------------- 反復回数
ここで、反復回数は SQL ステートメントをコンパイルするたびに SQL ステートメントが実行すると予期されている回数を表します。
注: | 初期コンパイルに続いて、 環境の変化に伴ってステートメントの再コンパイルが必要になると、 動的 SQL ステートメントは再コンパイルされます。 ある SQL ステートメントがいったんキャッシュされると、 環境が変わらないと仮定すると、 後続の PREPARE ステートメントはキャッシュされたステートメントを再使用するので、 その SQL ステートメントを再度コンパイルする必要はなくなります。 (動的ステートメントを用いた処理時のパフォーマンスを改善できるキャッシュについては、 カタログ・キャッシュ・サイズ (catalogcache_sz)および パッケージ・キャッシュ・サイズ (pckcachesz)を参照してください。) |
注: | 静的 SQL のコンパイル時間にも関心があるとしても、 ステートメントの合計時間 (コンパイルと実行のための時間) を、 意味のあるコンテキストで使用するのは難しいことです。 合計時間の比較という方法では、 静的 SQL ステートメントは 1 回バインドするたびに何度も実行可能であるという事実や、 通常は実行時にはバインドしないという事実は考慮されていません。 |