DB2 UDB アクセス・プラン速習

2006年4月28日（金）

パラメーカー・マーカーやホスト変数のフィルター・ファクター

資料ページと併せてご覧ください。 第10回資料はこちら

執筆者 プロフェッサー 秦太郎

第4回では範囲の述部のフィルター・ファクターを第5回では等号の述部のフィルター・ファクターの特徴を見ました。今回はこれらの述部が実際の値との比較でなく、パラメーター・マーカーや静的SQLのホスト変数で表現されて実行時に実際の値が与えられるケースを見ましょう。今回は3つのパートからなります。今回も統計情報は表統計と標準の索引統計を取得しています。

1. 範囲述部のパラメーター・マーカーのフィルター・ファクター 2. 範囲述部のホスト変数のフィルター・ファクター 3. 等号述部のパラメーター・マーカーのフィルター・ファクター

範囲述部のパラメーター・マーカー：再現用SQL(1)
上の例は第4回の上側で取り上げたSQLです。下の例はそれをパラメーター・マーカーで表したものです。パラメーター・マーカーは動的SQLプログラミングで使われる手法です。下ではBETWEENが？AND ？に変わっています。
パラメーター・マーカーを使う動的SQLプログラミングは、一度PREPARE処理であらかじめSQL文からアクセス・プランを作成しておき、実行ステートメントで既にできているアクセス・プランのパラメーター・マーカーへ値を代入して実行するものです。

パラメーター・マーカーの利点はPREPARE処理の繰り返しを削減することです。トランザクションレートが高いシステムでこれは効果的です。逆にその考慮点を見ましょう。

範囲述部のパラメーター・マーカー：アクセス・プラン・グラフ(2)
上側はIXSCAN+FETCHのタイプの普通の索引スキャンで、IXSCAN で799行を得る見積もりとなっています。下側はリスト・プリフェッチのタイプの索引スキャンでありIXSCANで5332行を得る見積もりです。下がリスト・プリフェッチとなったのはIXSCANの絞りこむ行数がある程度多いことの結果です。

範囲述部のパラメーター・マーカー：EXPLAIN詳細部分(3)
上のIXSCANの述部のフィルター・ファクターは第四回と同じです。最初の述部C3 <= 9のフィルター・ファクターは0.0009999です。実在する最大値、最小値がそれぞれ100000、0なのでこれは最小値付近の狭い範囲を示すフィルター・ファクターとして妥当といえます。もうひとつの述部2<=C3のフィルター・ファクターは0.9998です。これは実在する最大値、最小値がそれぞれ100000、0なので、そのほとんどすべてという意味で妥当です。実際は、フィルター・ファクターはSYSCAT.COLUMNSのHIGH2KEYとLOW2KEYから算出されています。2つのフィルター・ファクターを組み合わせた総合的なフィルター・ファクター0.000999 + 0.9998 - 1 = 0.0007999から100万行の799への絞込み見積もりが得られています。このようにフィルター・ファクター算出には範囲を示す述部の値が中心的役割を果たしています。

下のIXSCANの述部のフィルター・ファクターはともに0.0177755となっています。これは範囲を指定する値が未定である以上、何らかの仮定にすぎません。総合的な5332.65行への見積もりも仮定からの結果にすぎません。このように、パラメーター・マーカーを使っている場合は範囲述部のフィルター・ファクターは仮定された値です。

このケースでは上はIXSCAN + FETCHであるのに対し 、下はリスト・プリフェッチという違いが出ました。見積もり行数の違いはともかく、パラメーター・マーカー使用のケースもIXSCAN+FETCHにそろえるにはどうすればよいでしょう。

範囲述部のパラメーター・マーカー：照会最適化クラスの変更(4)
省略時の照会最適化クラス5ではリスト・プリフェッチも選択肢に入っています。照会最適化クラスを0としてみるとIXSCAN+FETCHのアクセス・プランに変わりました。コストは(2)の下と(4)を比べて(4)が大きくなっています。コストが大きいにもかかわらず非リスト・プリフェッチが選ばれたのは、照会最適化クラス0ではリスト・プリフェッチは選択肢ではなくなったためです。

これで共にアクセス・プランはIXSCAN+FETCHとなりました。このSQLやこのSQLをSELECT * に変更した程度のSQLではフィルター・ファクターは実行には何ら違いがありません。このような単純なSQLでなく、さらにJOINが続くようなケースではフィルター・ファクターの違いが上位のアクセス・プランの選択に影響します。そのようなケースのためにもう少し試しましょう。

次にパラメーター・マーカーのついたケースでフィルター・ファクターを上のケースにそろえる方法を見ます。

SELECTIVITY句(5)
この例はSQL文にSELECTIVITYをじかにコーディングする方法です。(2)の上側に表示されたフィルター・ファクターと同じ値を述部の後に指定することでパラメーター・マーカーを使っていてもそのフィルター・ファクターが使われ、アクセス・プランは(3)上側と実質的に同じになりました。Total CostもIXSCAN+FETCHである形式も同じです。 IXSCANのPredicateのところに指定されたSELECTITIVYも表示されています。前提としてdb2set DB2_SELECTIVITY=YESの指定が必要です。

これはフィルター・ファクターを直接指定することができる方法ですが、あまり汎用的な方法とは言えません。次により汎用的なREOPTオプションによる方法を見ましょう。

REOPTオプション(6)
組込み動的SQLではPREPARE文で動的にSQL文の実行準備を行います。SQL文はパラメーター・マーカーを含むものも含まないものも受け付けられます。パラメーター・マーカーを含まないSQLのアクセス・プランはこれまで見てきたものです。例えば今回の(1)の上です。パラメーター・マーカーを含むSQLのアクセス・プランは今回の(1)の下側のようなもので、そのままでは(2)の下や(3)の下で見たように仮定されたフィルター・ファクターが使われてアクセス・プランが選ばれます。パラメーター・マーカーの値がわかるのはその後です。

REOPTオプションでBINDした動的組込みSQLでは、実行時にパラメーター・マーカーの値がわかった段階でその値を使ってアクセス・プランが決まります。この時、与えられた述部の値を元にフィルター・ファクターが算出されて適切なアクセス・プランが得られます。

ソース・プログラムのSQL文は範囲述部にパラメーター・マーカーを使ったものです。
PREPARE後、カーソルのOPEN時にパラメーター・マーカーの値を与えています。
この静的組み込みSQLプログラムのビルドにはprepコマンドとbindコマンドを使いました。bindコマンドではreopt onceオプションを使っています。reopt onceは述部の値によるアクセス・プラン選択を最初の1回だけ行うオプションです。他にはreopt alwaysオプションも選べて毎回アクセス・プラン選択を行うこともできます。bindコマンドにもうひとつ指定したオプションはexplain reopt　です。これはREOPT（オプティマイゼーション）が行われた時にEXPLAIN表にアクセス・プラン情報を書き出すオプションです。これにより実行させればEXPLAIN表にアクセス・プラン情報が書かれてdb2exfmtユーティリティーで出力できます。

このケースもアクセス・プランは(1)の上側と実質的に同じになっています。また、IXSCANのPredicate Textの？の右に実際の値、[2]や[9]が代入されたことが表示されています。

このように、REOPTオプションによってパラメーター・マーカーを使っていても実行時に使われた値に沿ったフィルター・ファクターおよびアクセス・プランが選ばれました。

上に戻る

パラメーター・マーカーは動的SQLプログラミングで変数を与えるために使われる方法です。同様に静的SQLプログラミングではホスト変数によって実行時に変数を与えることができます。これに対するreoptの効果を見ましょう。

ホスト変数の組込み静的SQL(7)
上はREOPTオプションなし（省略時のreopt none）、下はREOPT ONCE指定の組込み静的SQLです。静的SQLのホスト変数はEXEC SQLで始まるSQL文の中にDECLARE SECTIONで宣言した変数：ホスト変数を指定できます。ホスト変数は：で始まることで識別されます。組込みSQLのPREPのステップで“：ホスト変数”がプログラミング言語の表記に展開されてコンパイル可能になります。

EXPLAINの出力に関して、上のREOPTなしではexplain yes指定でBIND時にEXPLAIN表へのアクセス・プラン情報の書き出しが行われます。下のreopt onceではexplain reopt オプションで実行時の再オプティマイゼーション時に、実際の値に基づいたアクセス・プラン情報が書き出されます。

次にその内容を比較します。

ホスト変数の組込み静的SQL：アクセス・プラン・グラフ(8)
上はREOPTしていません、(2)の下と同じアクセス・プラン・グラフになっています。下はREOPTの結果、IXSCANは(2)の上と同じIOコスト、コスト、見積もり行数となっています。またTotal Costも(2)の上と(8)の下は同じです。(8)の下はNLJOINが含まれている点が(2)の上より多いですが、NLJOINの累計コストはFETCHの累計コストから増えていない（共に6505.61）のでNLJOINはコスト上、特に意味がないといえます。

ホスト変数の組込み静的SQL：EXPLAIN詳細部分(9)
上のREOPTなしでは、(3)の下と同様に仮定されたフィルター・ファクターが使われています。下のREOPT ONCEのケースではPredicate Textにホスト変数の記号と共に、実行時に代入された値[9]や[2]が表示され、(3)の上と同じ述部の値に即したフィルター・ファクターが使われていることが確認できます。

このように組み込み静的SQLのホスト変数についても、範囲述部で使われると値が未定であることによる最適化の限界がありましたが、REOPTで解決されます。

上に戻る

範囲の述部に関してはその示す幅がデータの存在する幅に対して広いか狭いかがフィルター・ファクターで表されるので、述部の値をパラメーター・マーカーで表現した場合と、実際の値を使った場合では違いが出ました。

次に列を等号で値と比較する等号の述部をパラメーター・マーカーに変更した場合を見ます。

パラメーター・マーカーの動的SQL(10)
等号術部のフィルター・ファクターは第5回で見ました。上は、第5回の(1)の上側の例です。下はその等号の述部が比較する値をパラメーター・マーカーに置き換えたものです。C2列は索引列になっています。

パラメーター・マーカーの動的SQL：アクセス・プラン・グラフ(11)
上下のパラメーター・マーカーの使用有無の違いによらず、アクセス・プラン・グラフは全く同じになっています。

パラメーター・マーカーの動的SQL：EXPLAIN詳細部分(12)
上下ともに述部のフィルター・ファクターは1/32768 となっています。上は第5回で見たように1/FULLKEYCARD (=1/FIRSTKEYCARD=1/COLCARD) です。このフィルター・ファクターには統計情報から直接得られていて述部の値そのものは寄与していません。このため述部の値が変数に変わっても同じ結果となっています。

このように、列と値を等号で比較する述部のフィルター・ファクターはREPOTに頼らなくても適切です。ただし、これは今回のように標準の表統計と標準の索引統計を取得した場合、つまり、分布を含まない統計情報の場合についていえます。

(10)(11)(12)で動的SQLのパラメーター・マーカーの等号述部についてREOPTは不要であることを見ました。資料は略しますが、同様にホスト変数を使った静的組込みSQLの等号の述部のフィルター・ファクターもこれと同様に値が指定されたケースと同じフィルター・ファクターが得られます。

REOPTが利用できるケース(13)
REOPTはパラメーター・マーカー、ホスト変数、特殊レジスターに対して使用可能です。特殊レジスターの例はCURRENT DATE, CURRENT TIME, CURRENT TIMESTAMPなどです。逆にこのことはDATE型列 >= CURRENT DATEと書くことと実際に日付を使ってDATE型列 >= ‘2006-04-01’ などと書く場合とフィルター・ファクターが異なることを意味します。範囲述部の特殊レジスターもパラメーター・マーカーやホスト変数と同様にフィルター・ファクターは仮定されます。REOPTの対象にできます。

上に戻る

範囲の述部に関して、パラメーター・マーカーやホスト変数や特殊レジスターは仮定されたフィルター・ファクターが使われます。実際の値を使った最適化はREOPTオプションを使えば可能です。

等号で比較する述部に関して、パラメーター・マーカーやホスト変数や特殊レジスターとの比較は分布を含まない標準の統計情報では値と比較する場合と変わりません。

クイズ

1.	次のそれぞれは正しいでしょうか？
	1)	パラメーター・マーカーを使った動的SQLはPREPAREの時間を削減できる
	2)	パラメーター・マーカーを使った動的SQLの範囲検索は実行性能がよい
	3)	静的SQLでホスト変数を使った範囲検索は実行性能がよい
	4)	静的SQLでホスト変数を使った等号の述部の検索は実行性能がよい

2.	今日が2006/04/01だとします。複雑なSQLで次のどちらが性能がよいでしょう？あるいはどちらがよりよく最適化されるでしょう？　ただしREOPTは使わないとします。
	1)	BETWEEN 日付列 BETWEEN CURRENT DATE AND CURRENT DATE - 1
	2)	BETWEEN 日付列 BETWEEN ‘2006-04-01’ AND ‘2006-03-31’

上に戻る

上に戻る