Db2® が SQL ステートメントのアクセス・パスを選択するときに使用するデフォルトの選択度の見積もり値をオーバーライドすることができます。
始める前に
以下の前提条件を満たしていること。- アクセス・パスを管理するための準備を行う。
- 次の権限のいずれかを所有している。
- SQLADM
- SYSOPR
- SYSCTRL
- SYSADM
- 既存のスキーマまたは入力表用の別個のスキーマを使用する、以下のユーザー表のインスタンスが存在します。
- DSN_USERQUERY_TABLE
- DSN_PREDICAT_TABLE
- DSN_PREDICATE_SELECTIVITY
別個のスキーマを使用する表を使用する方法について詳しくは、各スキーマでの入力 EXPLAIN 表の作成を参照してください。prefix.SDSNSAMP ライブラリーのメンバー DSNTESC および DSNTESH 内に、表および関連する索引のサンプル CREATE ステートメントがあります。
- ステートメントが入ったパッケージは、BIND PACKAGE ステートメントによって作成されています。アクセス・パスに影響を及ぼすステートメント・レベルの方式は、他のステートメント (CREATE FUNCTION、CREATE TRIGGER、および CREATE PROCEDURE ステートメントなど) によって作成されたパッケージ内のステートメントではサポートされません。
このタスクについて
Db2 は、特定のタイプの述部について、フィルター係数を見積もることができません。そのような述部を使用しているステートメントのアクセス・パスを選択するときには、不正確な見積もり値あるいはデフォルトのフィルター係数に依存しなければなりません。
特に、次の項目に関係する述部が例として挙げられます。
- ホスト変数
- パラメーター・マーカー
- 式
- 表の自己結合
- 副照会
ただし、選択度のオーバーライドを作成すると、特定の述部に対するそれらのデフォルトのフィルター係数をオーバーライドできます。述部の選択度のオーバーライド とは、それぞれ、特定の SQL ステートメントの特定の述部の選択度を表すものです。
1 つのステートメントに複数の述部が含まれている場合は、ステートメントの各述部に対して別々に選択度オーバーライドを作成できます。
呼び出し回数が多いステートメントは、呼び出す時により、フィルタリング特性が異なる場合があります。
あるリテラル値では多くの行をフィルタリングする述部であっても、別のリテラル値ではフィルタリング行数が大幅に少なくなることがあります。
したがって、ステートメントに対する 1 セットのオーバーライド値では、すべての実行における述部のフィルタリングを十分に表せない場合があります。
その場合は、1 つのステートメントに対してオーバーライド値のセットを複数個作成します。
オーバーライド値の各セットが、選択度インスタンス です。
選択度インスタンスにはそれぞれウェイト値があります。
このウェイト値により、そのインスタンスの選択度が述部の選択度になると想定されるステートメント実行の割合を表します。
単一のステートメントに対するすべての選択度インスタンスのセットを、ステートメントの選択度プロファイル と呼びます。選択度プロファイルは、その SQL ステートメントのすべてのインスタンスにおける、ステートメントの各述部の選択度を表すものです。
ただし、特定のステートメントに対するオーバーライド値のセットで、そのステートメントのすべてのインスタンスにおけるすべての述部の選択度を完全に表す必要はありません。
特定の述部に対するデフォルトの選択度によって非効率的なアクセス・パスが生成されるために修正が必要という場合に限り、オーバーライドを作成するのが最善です。
必要な場合にのみオーバーライドを作成することで、不必要なオーバーライドが無効になってパフォーマンスを低下させる可能性を最小限に抑えることも可能になります。
データの統計と同様に、選択度のオーバーライドも、時間が経つとデータが変化するために無効になる可能性があります。
手順
ステートメント・レベルの選択度のオーバーライドを作成するには、次のようにします。
- DSN_PREDICAT_TABLE 表および DSN_PREDICATE_SELECTIVITY
表に、適用する述部選択度の値を設定します。 自分のスキーマで修飾されている表を使用することも、別のスキーマの下に複数のインスタンスを作成することもできます。
BIND QUERY コマンドを実行する場合は、使用する入力表のスキーマを指定できます。
追加の表を作成すると、必要な行のみが入力表に存在することを確認するためのプロセスを簡素化できます。 これらの入力表に値を設定するには、次のようにします。
- EXPLAIN 情報をキャプチャーします。 DSN_PREDICAT_TABLE
および DSN_PREDICATE_SELECTIVITY に、選択度のオーバーライドの作成に使用できるモデル行が設定されます。
- オプション: EXPLAIN 出力から該当する行を選択し、別のスキーマの表に挿入します。 後で BIND QUERY コマンドを実行する際にこのスキーマ値を指定することができます。
- オーバーライドの詳細を含むように DSN_PREDICATE_SELECTIVITY 表を変更します。 1 つの述部に対して単一または複数の選択度インスタンスを作成できます。
この表の既存行の以下の値を変更すると、単一のインスタンスを作成できます。
複数の選択度インスタンスを作成する場合は、追加のインスタンス用に新しい行を挿入する必要があります。
- INSTANCE
- 選択度インスタンスを識別するために 0 より大きい値を指定します。
各インスタンスに複数の述部を含めることができますが、1 つのインスタンスの中で同じ述部を重複させることはできません。
複数の行で同じ値を使用すると、関連する述部をグループ化することができます。
ステートメントの全述部をオーバーライドする必要はありません。
同じように、複数のインスタンスが存在する場合、インスタンスごとに行数が異なっているということが可能です。
あるインスタンスでは複数の述部に対してオーバーライドを定義する一方で、別のインスタンスでは 1 つの重要な述部のみをオーバーライドする、ということができます。
すべてのインスタンスのウェイト値の合計が 1.0 と等しければ、20 個の異なるインスタンス値を指定できます。ウェイト値の合計が 1.0 より小さい場合は、指定できるインスタンス値は 19 個のみです。
INSTANCE
値は、連続する値である必要はありません。
- SELECTIVITY
- 述部の選択度の見積もり値を指定します。
- WEIGHT
- 選択度インスタンスのウェイトを示す 0 から 1 までの値を指定します。
同じ INSTANCE 値を含んでいる行はすべて、WEIGHT 値が同じでなければなりません。
複数のインスタンスの WEIGHT 値の合計が 1 を超えてはなりません。
ウェイトの合計が 1 に等しくなるように各インスタンスに重みをつけることをお勧めします。
一方、複数のインスタンスのウェイトの合計が 1 より小さい場合、Db2 は、インスタンスがもう 1 つ存在すると想定します。
この想定されたインスタンスでは、デフォルトの選択度が想定され、指定された各オーバーライドのウェイトの合計を 1 から差し引いたウェイトが使用されます。
- ASSUMPTION
- オーバーライドを指定した各行を示す場合は、「OVERRIDE」と指定します。
選択度オーバーライドとして使用されない行には、「NORMAL」または別の値が入っていなければなりません。
何らかの修正を必要とするデフォルトの選択度に対してのみ、オーバーライドを作成することをお勧めします。
- DSN_USERQUERY_TABLE 表に行を INSERT します。
- 以下の列に、オーバーライドを適用する SQL ステートメントとオーバーライドの有効範囲を指定する値を挿入します。
- QUERYNO
- 選択度オーバーライドを表している、DSN_PREDICAT_TABLE 表および DSN_PREDICATE_SELECTIVITY 表の該当行の QUERYNO 列の値に対応する値を挿入します。
- QUERY_TEXT
- 影響を及ぼすアクセス・パスが含まれるステートメントのテキストを挿入します。
指定するテキストは、静的 SQL ステートメントをバインドして、動的 SQL ステートメントを準備する際に Db2 が使用するステートメント・テキストと一致する必要があります。正常にテキストが一致するようにする方法については、ステートメント・レベルのマッチングの照会テキストの取り込みを参照してください。
- SCHEMA
- SQL ステートメントに、異なるデフォルト・スキーマに解決される可能性のある非修飾オブジェクト名がいくつか含まれている場合、非修飾のデータベース・オブジェクトを識別するスキーマ名を挿入します。ステートメントに非修飾オブジェクト名が含まれる場合、ステートメントが異なるタイミングで異なるスキーマに適用される可能性があるために、可能性のある SCHEMA 値ごとに別個のヒントまたはオーバーライドを作成する必要があります。ステートメントに完全修飾オブジェクト名だけが含まれる場合は、SCHEMA 値は不要です。ただし、特定のスキーマに関連したヒントを見分けやすくするために、SCHEMA 値を挿入することができます。
- HINT_SCOPE
- ステートメントを突き合わせるコンテキストを指定するための値を挿入します。
- 0
- システム全体。Db2 は SQL ステートメントのテキストのみを使用し、SCHEMA 列に値が含まれている場合はその値を使用して、ステートメントが一致するかどうかを判別します。
- 1
- パッケージ・レベル。Db2 は、COLLECTION、PACKAGE、および VERSION 列の値を使用して、ステートメントが一致するかどうかを判別します。
- COLLECTION
- パッケージのコレクション名を挿入します。この値は、HINT_SCOPE の値が 1 の場合のみ必要です。
HINT_SCOPE の値が 0 (この値はオプションです) であれば、値が指定されている場合は、Db2 は、一致する値が SYSIBM.SYSPACKAGE カタログ表に見つからないときに照会のバインド時にエラー・メッセージを出します。HINT_SCOPE が 0 の場合、COLLECTION および PACKAGE の両方を指定するか、両方のフィールドをブランクのままにします。
静的 SQL ステートメントおよび動的 SQL ステートメントで DYNAMICRULES(BIND) オプションを使用する場合、Db2 が SYSIBM.SYSPACKSTMT カタログ表から正しいアプリケーション・デフォルト値を取得できるように、この列の値を指定しなければならない場合があります。
- PACKAGE
- パッケージの名前を挿入します。この値は、HINT_SCOPE の値が 1 の場合のみ必要です。
HINT_SCOPE の値が 0 (この値はオプションです) であれば、値が指定されている場合は、Db2 は、一致する値が SYSIBM.SYSPACKAGE カタログ表に見つからないときに照会のバインド時にエラー・メッセージを出します。HINT_SCOPE が 0 の場合、COLLECTION および PACKAGE の両方を指定するか、両方のフィールドをブランクのままにします。
静的 SQL ステートメントおよび動的 SQL ステートメントで DYNAMICRULES(BIND) オプションを使用する場合、Db2 が SYSIBM.SYSPACKSTMT カタログ表から正しいアプリケーション・デフォルト値を取得できるように、この列の値を指定しなければならない場合があります。
パッケージ限定の有効範囲を使用する主な目的は、ステートメント・レベルのヒントをシステム全体の有効範囲にデプロイする前に、ステージング、検証、およびテストをサポートすることです。
- SELECTVTY_OVERRIDE
- 選択度オーバーライドを作成する行であることを示すには、この列の値を「Y」に設定します。
- ACCESSPATH_HINT
- ステートメント・レベルのアクセス・パスを作成しない行であることを指定するには、この値を「N」に設定します。
同じステートメントにアクセス・パスと選択度オーバーライドを指定することはできません。
- OPTION_OVERRIDE
- ステートメント・レベルの最適化パラメーターを作成するかどうかを指定する値を挿入します。同じステートメントに対してオプションのオーバーライドと選択度のオーバーライドの両方を使用可能にする必要が特にない限り、この列には「N」を指定します。
オプションのオーバーライドを作成する場合は、この列に「Y」を指定します。
オプションのオーバーライドを作成するために指定する他の値の詳細については、ステートメント・レベルにおける最適化パラメーターの指定のトピックを参照してください。
- BIND QUERY コマンドを発行します。 LOOKUP オプションを省略するか、LOOKUP(NO) を指定する必要があります。 専用のスキーマの下で入力表を作成した場合、EXPLAININPUTSCHEMA バインド・オプションとスキーマの名前を指定します。 Db2 はすべての DSN_USERQUERY_TABLE 行および関連する入力表から入力を取得し、データを以下のカタログ表に挿入します。
- SYSIBM.SYSQUERY
- SYSIBM.SYSQUERYPREDICATE
- SYSIBM.SYSQUERYSEL
QUERYID 列はこれらの表の行を相関させます。
例
以下の表は、DSN_PREDICATE_SELECTIVITY 表の 1 セットの行の列値のサブセットを示しています。
これは、2 つの選択度インスタンスで構成される選択度プロファイルを表しています。
各行が、単一の選択度オーバーライドに相当します。
表 1. DSN_PREDICATE_SELECTIVITY 表の値の例| INSTANCE |
WEIGHT |
PREDNO |
SELECTIVITY |
| 1 |
0.45 |
1 |
0.36 |
| 1 |
0.45 |
3 |
0.15 |
| 1 |
0.45 |
7 |
0.15 |
| 2 |
0.25 |
1 |
0.99 |
| 2 |
0.25 |
3 |
0.20 |
| 2 |
0.25 |
7 |
0.001 |
この 6 つの選択度オーバーライドは、ステートメント内の 3 つの異なる述部に適用されます。これらのオーバーライドは、2 つの選択度インスタンスにグループ化されます。
INSTANCE=1 を含んでいる行は、このステートメントの実行の 45% において想定される選択度を表しています。
INSTANCE=2 を含んでいる行は、25% において想定される選択度を表しています。
この 2 つのインスタンスのウェイト値の合計が 0.70 のみであることに注意してください。
この値は、指定されているオーバーライドによって表されているのが、想定される選択度の 70% のみであることを意味しています。
これらのオーバーライドのみがステートメントに定義されている場合、Db2 は、3 つ目の選択度インスタンスが存在するものと想定します。
この暗黙の選択度インスタンスのウェイトは 0.30 です。
Db2 は、選択度インスタンスについてオーバーライドが指定されていないかのようにして、その選択度インスタンスについて見積もりのフィルター係数を使用します。
Db2 が複数の選択度インスタンスを使用してステートメントを最適化する場合、それらの選択度インスタンスのウェイトを考慮した平均が、EXPLAIN コストの見積もりに示されます。
DSN_PREDICAT_TABLE に報告される値も、ウェイトを考慮した平均によるフィルタリングを反映しています。
PREDNO 値から分かるように、この SQL ステートメントのすべての述部に対してオーバーライドが作成されたわけではありません。
何らかの修正を必要とする述部に対してのみ、オーバーライドを作成することが最善です。
見積もりが難しい述部のみをオーバーライドすることが最善です。
そうしないと、ステートメントの選択度プロファイルが不正確になって保守が必要になる可能性があります。
例えば、次のステートメントを考えてみます。
SELECT * FROM T1
WHERE T1.C1 > ?
AND T1.ACCT_NUM = ?
AND T1.SALES_DT BETWEEN ? AND ?
RANGE 述部 (T1.C1 > ?) および BETWEEN 述部 (T1.SALES_DT BETWEEN ? AND ?) のデフォルトの選択度の見積もり値は信頼できません。
使用されるリテラル値が Db2 で認識されていないため、デフォルトの見積もり値は推量でしかありません。
一方、ACCT_NUM = ? 述部の COLCARDF 値は高いと考えられ、選択度の見積もりが信頼できると考えられます。
例えば、T1 表に 1,000,000 行入っていて、ACCT_NUM 列の COLCARDF 値が 250,000 であるとします。
その場合、ACCT_NUM = ? 述部に対して平均 4 行が返されます。
フィルター係数 1/250,000 を指定する ACCT_NUM = ? 述部に対して選択度のオーバーライドを作成することもできますが、それは行わないことが最善です。
時間が経過するにつれて新規アカウントのために T1 表のサイズが 2 倍になる場合、CARDF 値が 2,000,000 に変更されます。
また、ACCT_NUM 列の COLCARDF 値も 500,000 に変更されます。
述部の実際の選択度は 1/500,000 に変更され、以前に作成した選択度のオーバーライドはすべて不正確になります。
ACCT_NUM = ? 述部の選択度の見積もり値については、表の行数の増加および列のカーディナリティーの増加に合わせて変化させると良いでしょう。
一方、範囲述部の選択度は列カーディナリティーの値の変化に合わせてより正確になるということはあまりないので、選択度のオーバーライドがもたらす益は範囲述部の選択度の方が大きいと言えます。
次のタスク
以下のアクションの実行について考慮します。
- 適切なカタログ表の行が作成されていることを確認します。
- QUERY_TEXT 列と SCHEMA 列に値が含まれている DSN_USERQUERY_TABLE 表に行を挿入します。
- 次のコマンドを発行します。
BIND QUERY LOOKUP(YES)
Db2 は、カタログ表に、DSN_USERQUERY_TABLE 行に対応する有効な行が含まれるかどうかを示す以下のメッセージを発行します。- DSNT280I メッセージ (各 DSN_USERQUERY_TABLE 行にカタログ表内の一致する行がある場合)。
- DSNT281I メッセージ (各 DSN_USERQUERY_TABLE 行にカタログ表内の一致する行がない場合)。
- 単一の DSNT290I メッセージ (カタログ表に一致する行がいくつかある場合) または DSNT291I メッセージ (一致する行がない場合)。
また Db2 は、DSN_USERQUERY_TABLE 表の QUERYID 列の値を更新し、SYSIBM.SYSQUERY カタログ表の一致する行の値と一致するようにします。
- DSN_USERQUERY_TABLE 行を削除し、後ほど BIND QUERY コマンドが発行される際に既存のカタログ表の行と置き換わらないようにします。BIND_QUERY コマンドを発行するときに、DSN_USERQUERY_TABLE 行のすべての行に関してカタログ表の行が作成されるか置換されます。
他の入力表のデータに変更が加えられると、DSN_USERQUERY_TABLE に古い行が残っている場合に BIND_QUERY コマンドを再発行すると、予期しない結果になる恐れがあります。