レベル: 中級 プロフェッサー奏太郎, ,
IBM
2005年 12月 16日 今回はアクセス・プラン選択の主要な要因であるフィルター・ファクターについて見ましょう。
資料ページと併せてご覧ください。 第4回資料はこちら
再現用SQL(1)
上下のSQLは似ていますが、述部BETWEENの限定する範囲が異なっています。上は2から9までと狭く、下は2から9999までと広く応答時間も上が速くなっています。述部の列C3は単一索引列です。今回のデータも第一回に載せたものを使っています。
アクセス・プラン・グラフの着眼点(2)
上のアクセス・プランは索引スキャン(IXSCAN)です。また、索引スキャンの結果、表から行をFETCHしています。IXSCANオペレーターの上の値799.927は見積もり行数です。元の索引の行数はINDEXオブジェクトの上の数字1e+6（100万）行でしたから、1%未満に絞られています。
下のアクセス・プランは表スキャン(TBSCAN)です。TBSCANオペレーターの上の値99970は見積もり行数です。表スキャンをしつつ述部で絞った結果99.97万行となる見積もりです。元の表はTABLEオブジェクトの上の数字1e+06(100万)行でしたから99.97%に絞られています。同じようなSQLで述部は索引列ですが、下のSQLでは索引を使っていません。この違いはなぜでしょうか？
アクセス・プラン詳細部分の着眼点(3)
上側：IXSCANオペレーターの中に述部のフィルター・ファクターの項目があります。フィルター・ファクターは述部の選択度(SELECTIVITY)を示します。3)のStop Key Predicateで示されている述部C3<=9 は0.000999901の選択度、つまり0.09%に絞り込みます。4)のStart Key Predicate で示される述部：2<=C3 は0.9998の選択度、つまり99.8%に絞り込みます。つまりほとんど絞られていません。C3の値は0以上10,000以下の乱数・整数で作成しましたからこの絞り込み度は直感的に妥当です。
2つのフィルター・ファクターの前者をf1、後者をf2とするとその共通部分fは簡単な算数によってf = f1+f2-1となります。これで計算するとf=0.000999901 + 0.9998 - 1 =1.000799901 -1 =0.000799901 (0.0079%)です。元の索引レコードが100万行あるので、絞り込み後は799.9行です。これは上のアクセス・プラングラフの着眼点(2)で見た値に合致します。
下側：TBSCANオペレーターの中にも述部のフィルター・ファクターの項目があります。3)のSargable Predicate で示された述部C3<=9999 は0.9999 の選択度、つまり99.99%の絞り込みを行なうと見込まれ、4)のSargable Predicateで示された述部2<=C3は0.9998の選択度、つまり99.98%の絞り込みを行なう見込みです。これらはどちらも、ほとんど絞りこまないといえ、その共通部分もほとんど絞り込まないことになります。共通部分の選択度fはf=0.9999 + 0.9998 - 1 = 1.9997 - 1= 0.9997 (99.97%)です。表の行数は100万行なので、999700行が絞り込み後の見積もり行数となります。
これらを比較すると、索引列の述部がよく絞り込まれる上(0.0079%)では索引経由のアクセス：IXSCANが選ばれ、全然絞り込まれない下の述部の場合(99.97%)は索引があってもそれを経由しないアクセス：表スキャンTBSCANが選ばれた結果となっています。これは性能最適化の点で妥当な選択といえるでしょう。表の99.98%を読むためにわざわざ索引の99.98%をも経由することは回り道であって決して早くなりません。
その判定にはコスト評価が使われます。コストはCPUコスト、 IOコスト、 memoryコスト、 (区分データベース環境では)通信コストから決まります。フィルター・ファクターはその重要な要因のひとつIOコストに影響します。次に統計情報を見たあとでこのIOコストを見ましょう。
db2exfmt 統計情報の着眼点(4)
上側：IXSCANで使われた索引の統計情報が載っています。行数(number of rows) 100万行 索引階層(index tree levels) 3、 索引リーフページ数(index leaf pages) 1357です。また、索引名と索引を構成する列名C3、元表名が載っています。
下側：索引オブジェクトは使われていないので表についてしか統計情報が載っていません。この表をバッファープールへ読み込むために必要なページ数(number of bufferpool pages)は9265ページとなっています。
IOコスト
IOコストはそのスキャン(IXSCANやTBSCAN)でアクセスするページ数です。IXSCANのStart Key PredicateとStop Key Predicateの場合、索引ルートページからリーフページまでを索引階層に沿って下がり該当する範囲の索引リーフページの索引レコードが読まれます。それがDB2カーネルの動きですが、オプティマイザーはその際にアクセスされる予定のページ数を見積もります。
索引ルートページから索引リーフページへ下がるページ数は索引階層が3レベルなので3ページです。但し、索引ルートページは一度読まれると必ずバッファープールに残るよう、バッファープールサービス上で特別扱いされているのでIOコストには含まれません。このため3 - 1 =2ページが最低のIOコストとなります。さらに、C3 between 2 and 9 を満たす索引レコードは複数あるはずですから該当するものを全部得るために最小のキー値2から始めて大きいキー値の方へ述部を満たす限り右にリーフページを読んでいきます。この右に読むページ数の見積もりは全リーフページ数にフィルター・ファクターによる選択度をかけた1357 * 0.000799901 = 1.085465ページです。索引階層をリーフページまで下がった段階でリーフページの最初の1ページはすでに数えていますから、1.085465ページのうち1ページはダブル・カウントになります。この重複を除いて2 + 1.085465 - 1 = 2.0854565が見積もりIOコストです。このIOコストはアクセス・プラングラフとアクセス・プラン詳細に載っています。
表スキャンのIOコストは表の統計情報にあるnumber of buffer pool pages 9265と一致します。
“表スキャン”とだけ聞くと索引スキャンより遅いように感じるかもしれませんが、このケースで索引スキャンが使われてしまうと表スキャンより非効率です。それは、もし同じ述部がIXSCANでアクセスされたとするならば9265ページの99.97%に加えて索引リーフページ1357の99.97%と索引階層の中間ノードのページ1ページも読むので索引スキャンのIOコストは10619.81となり表スキャンよりはるかに大きく、これが大差となって最終的なコスト比較で表スキャンに負けることになります。
このようにIOコストの見積もりにフィルター・ファクターが利用されます。
db2batchスナップショット・モニター出力の着眼点(5)
以上のようなアクセス・プランの”計画”が実行されたケースの実績をスナップショット・モニター出力で見てみましょう。
上側：索引スキャンで索引ページが読み取られ、対応する表のデータページが読み取られていることがわかります。前回のようなリスト・プリフェッチは起こっていません。
下側：Buffer pool index logical read はありません。索引を読まずに表だけをアクセスする表スキャンが起こっています。前回と同じく非同期IOサーバーが動いていてプリフェッチが行なわれています。
範囲型フィルター・ファクターの算出(6)
述部に使われたC3列が0以上10,000以下であることに対応する統計情報があります。SYSSTAT.COLUMNS列のHIGH2KEYに最大から2番目の列値を記録し、LOW2KEYで最小から2番目の列値を記録します。この2つで実在するデータの幅を表わすことができます。分散統計が取られていなければ均一にデータが分布していると過程され、この幅とC3<= 値や 値<=C3の述部の幅とが比較されてフィルター・ファクターが算出されます。HIGH2KEY とLOW2KEYはdb2exfmtの最後に出力される統計情報には載りません。これらを把握するにはSYSCAT.COLUMNSからSELECTするかdb2look ユーティリティの-mオプションで取得できるSYSSTAT.COLUMNSを復元するためのUPDATE文から読み取ることができます。
ダミー・データの存在
HIGH2KEYとLOW2KEYはなぜ最大値や最小値でなく、最大から2番目や最小から2番目となっているのでしょう？これはお客様が最大や最小にダミー・データを持つことが多々あるであろうというIBM開発部門の想定のようです。実際の最大値と最小値より一つ内側の値を使って実質的なデータの上限値、下限値とみなし存在するデータの最大幅を得るという考えです。逆にこれを知っていれば、例えばダミー・データを最大に二種、最小二種持てば良くも悪くもダミー・データの二番目の値がそのままHIGH2KEY、LOW2KEYとなります。
範囲型フィルター・ファクターの共通部分の算出方法
最後にf = f1 + f2 -1 の説明を補足します。黙々と計算式を書くならば次のようになります。
f = Prob( x <= C3 and C3 <= y )
= 1 - Prob(NOT( x<=C3 and C3<=y))
= 1 - Prob( x > C3 or C3 > y )
= 1 - ( Prob(x >C3) + Prob(C3>y) )
= 1 - ((1-Prob(x<=C3)) +( 1-Prob(C3<=y))))
= 1 - (( 1 - f1) + ( 1 - f2))
= 1 - 1 + f1 - 1 + f2
= f1 + f2 -1 |
ポイントは確率のORは和に分解できることと、残りの事象の確立は元の事象の1の補数であることを使っています。このような確率の数学を使わなくても、図形的に見て小学校クラスの算数でも示せます。0から1までの長さ1の横棒を書いて、その下に左端0か1の方へ長さf1の横線、その下に右端1から0の方へ長さf2の横線を書くとそれらの長さの合計は次の左辺です。また、2本の横線f1とf2を動かして重ねてみると、長さ1になりプラスf1とf2の重なりが出ることが図形的にわかります。それが右辺です。 f1 + f2 = 1 + (f1とf2の共通部分) よってf = (f1とf2の共通部分) = f1 + f2 - 1 となります。
同義語について
フィルター・ファクターとSELECTIVITY
フィルター・ファクターはメインフレームのDB2で使われていて昔からある用語です。DB2 for Linux/unix/windowsでもフィルター・ファクターはEXPLAIN表とその列名としてが踏襲されています。具体的にはEXPLAIN_PREDICATE表のFILTER_FACTOR列でDOUBLE属性です。
SELECTIVITYはDB2 for Linux /unix/windows V8でDB2_SELECTIVITYレジストリー変数が出てきてから使われるようになった名称です。述部に対する0と１の間の絞り込み率の意味であることはどちらも変わりません。最近のIBMの外部カンファレンスではSELECTIVITYの方が説明に使われています。
まとめ
今回はWHERE条件の述部の指定する幅の広さ、狭さを定量的に把握するフィルター・ファクターを見ました。DB2は統計情報に基づいて述部の幅が索引の幅に対して広いか狭いかを適切に判定してコスト評価を行いアクセス・プランを選択します。述部の示す幅が広い時には、索引をも経由することは返ってコストが増えてしまうため選ばれません。
クイズ
- CHAR(8)の年月日列があり索引として指定されています。年月日の範囲検索のWHERE条件を持つSELECT文が発行されます。同じSELECT文に対して、どちらの環境が索引スキャンになりやすいでしょうか？
- ダミー・データとして'99991231'が入っている
- ダミー・データとして'99991231'と'30000101'が入っている
- 固有索引の全列がそれぞれあるキー値と等しいことを条件にして一行を取り出す索引スキャンがあったとします。そのアクセス・プランのIXSCANの部分のIOコストは次の場合にそれぞれいくつでしょう？
- 索引階層が3
- 索引階層が2
- 索引階層が1
参考文献
著者について  | 
| | プロフェッサー奏太郎
DB2を知り尽くすスーパーフクロウ！
生息地： 日本／趣味： アクセス・プランを眺めること／好物： 納豆入りみそ汁 |
記事の評価
| 
