DB2 SQLアクセス・パスのチューニング

Craig Mullins, Director of Technology Planning , BMC Software

Craig S. Mullins氏は、BMC Software (テキサス州ヒューストン) のテクノロジー計画ディレクター。氏はDB2、Oracle、SQL Serverを含む複数のデータベース管理システムにアプリケーション開発者、DBA、およびインストラクターとして従事した経験があり、データベース管理分野で広範な実績を有する。以前は、Gartner Groupにおいてデータベース管理分野を網羅するリサーチ・ディレクターを務める。DB2 for OS/390に関する業界最先端の書籍『DB2 Developer's Guide』の著者であり、2002年6月には、最新の著書『Database Administration: Practices and Procedures』がAddison-Wesley社より出版された。さらに、www.dbazine.comにも、編集者および常時コラムニストとして貢献している。『Database Trends & Application』誌に毎月「DBA Corner」コラムを、また『The Database Administration Newsletter』ポータルに四半期ごとの「Database Report」コラムを執筆中。



2003年 1月 23日

はじめに

Visual ExplainはIBM®DB2® Universal Database™ が提供する驚異的なツールで、プログラマーやDBAはこれを使用すると、SQLステートメントのためにDB2オプティマイザーが選択するアクセス・パスを詳しく調べることができます。実際、Visual Explainは、パフォーマンス監視戦略の重要な構成要素として位置付ける必要があります。Visual Explainが提供する情報は次のような詳細情報まで含み、パフォーマンス上の多様な問題の解決には極めて重要です。

  • SQL要求のデータ要件を満たすためにDB2が「見えないところで」実行する作業
  • 使用可能な索引をDB2が使用するかどうか。使用する場合はどのように使用するか
  • 結合条件を満たすためにDB2の表がアクセスされる順序
  • SQLステートメントを満たすためのロック要件
  • 選択されたアクセス・パスに基づくSQLステートメントのパフォーマンス

Borland® Delphi™ 7のプログラマーにとって、Visual ExplainはDB2がSQL要求をどのように実行するかを理解する素晴らしいリソースです。DelphiはCLIネイティブ・インターフェースを使用してDB2と対話します。このため、Delphiでは動的SQLを使用します。DB2は、動的SQLステートメントのアクセス・パスを「その場で」作成します。つまり、SQLステートメントが実行のためにDB2にサブミットされたときにアクセス・パスを作成します。各SQLステートメントの実行の前に、そのステートメント用にDB2が選ぶアクセス・パスをアナリストが前もってレビューする方法はありません。従って、Visual Explainを使用して、すべてのDelphi SQLステートメントに対してDB2が選ぶアクセス・パスを定期的に調べることが重要になります。そうしておけば、どのステートメントが大量のリソースを消費するかがわかります。また、パフォーマンス改善のためにSQLをチューニングする方法のガイダンスも提供されます。

Visual Explainの使用方法を詳しく説明する前に、正確には何がExplain (詳細情報の記述) の対象になるかを説明しておく必要があります。簡単に言えば、Explainの対象はDB2アクセス・パスです。アクセス・パスとは、SQLステートメントの要件を満たすためにDB2が使用するアルゴリズムです。ただし、アクセス・パスにはさまざまなタイプがあり、これらを理解する必要があります。


DB2アクセス・パスのタイプと構成要素

DB2オプティマイザーは、各SQLステートメントに最適アクセス・パスを作成するときに、数ある技法の中から選ぶことができます。これらの技法は、単純な一連の順次読み込みから、データ・アクセスに複数の索引を使用する方法のように極めて複雑な方法までさまざまあります。オプティマイザーがDB2アクセス・パスを作成するために使用する最も一般的な技法をいくつか紹介します。

オプティマイザーが行わなければならない多くの意思決定の中で、最も重要なものは、照会を実行するために索引を使用するかどうかという判断です。この判断を行う前に、オプティマイザーはまず索引が存在するかどうかを判断する必要があります。索引がなくても、任意の表の任意の列を照会できます。このため、オプティマイザーでは索引なしのデータもアクセスできなければなりません。オプティマイザーはスキャンを使用してこれを行います。

DB2オプティマイザーでは、ほとんどの場合、索引の使用が優先します。これは、索引によりデータの取り出しが大幅に最適化されるためです。ただし、索引が存在しなければ使用できません。ある種のSQLステートメントは、データの完全なスキャンが一番適しています。たとえば、次のSQLステートメントがあったとします。

  SELECT * FROM EMP;

このステートメントに対してDB2が索引を使用する理由はあるのでしょうか? このステートメントにはWHERE文節がないので、完全スキャンが最適です。WHERE文節が指定してあったとしても、ページの順次スキャンの方が索引による取り出しよりもパフォーマンスが良いとオプティマイザーが判断する可能性があります。従って、索引は選択されない可能性があります。

そもそも索引の存在理由はパフォーマンスの改善であるにもかかわらず、索引なしのアクセスが索引ありの場合よりもパフォーマンスが良いのはどうしてでしょうか? これは、索引アクセスの方が単純なスキャンよりも時間がかかることがあるからです。たとえば、数ページのみを含む小さい表があるとします。全ページを読み取る方が、まず索引ページを読んでからデータ・ページを読むよりも速い可能性があります。大きな表の場合でも、索引の編成によっては、照会を実行するために追加I/Oが必要になることがあります。照会の実行に索引が使用されない場合、結果のアクセス・パスでは表スキャン (または表スペース・スキャン) を使用します。

表スキャンでは、一般的に表の各ページをすべて読み取ります。ただし、場合によっては、スキャン対象ページをDB2が限定できることもあります。さらに、DB2は順次プリフェッチを開始して、ページが要求される前にページを読み込むことができます。SQL要求が行を順番に (ディスク上に格納されている順番で) アクセスする必要がある場合に、順次プリフェッチは特に役立ちます。オプティマイザーは、照会がデータ・ページを順番に読み込むと判断した場合、順次プリフェッチを使用するように知らせることができます。順次プリフェッチにより、照会がデータを要求したときにデータはすでにメモリー内にあるため、表スキャンが順次プリフェッチの先読み処理の恩恵を受けることはよくあります。


高速の索引アクセス

一般的に、DB2データの最速のアクセス方法は、索引を使用する方法です。索引は、特定のデータを見つける効率を上げるような方法で構造化されています。図1は、Bツリー索引の構造を示します。ルートからリーフ・ページまで索引を探索するだけで、要求されているデータが含まれている該当データ・ページをすばやく見つけられます。ただし、DB2が索引を使用する方法は、ステートメントごとに異なります。DB2では多様な内部アルゴリズムを使用して索引構造を探索します (図1を参照)。

図1.Bツリー索引の構造
図1.Bツリー索引の構造

DB2が索引を使用してデータ・アクセス要求を実行するには、その前に次の条件が満足されている必要があります。

  • SQL述部の最低限1つが索引付け可能であること。述部によってはその性質上索引付けができないものがありますが、その場合、オプティマイザーは述部を満足するために索引を使用できません。
  • (任意の索引付け可能述部の) 列の1つが、使用可能な索引の列として存在すること。

以上でわかるように、DB2が索引を使用するときに考慮する要件は極めて単純です。ただし、DB2における索引アクセスについてはまだ多くのことを理解しておく必要があります。索引アクセスには、実際にさまざまなタイプがあります。

まず、最も単純な索引アクセス・タイプは、直接索引参照です。直接索引参照の場合、DB2は索引のルート・ページから開始して中間のリーフ・ページをたどり、該当するリーフ・ページに到達します。このリーフ・ページで、実際のデータ・ページへのポインターを読み取ります。索引エントリーに基づき、DB2は正しいデータ・ページを読み込み、求められる結果を返します。DB2が直接索引参照を実行するためには、索引内の各列に対して値が存在する必要があります。たとえば、EMPLOYEE表があり、そのDEPTNO、TYPE、およびEMPCODEという各列に索引が付けられているとします。ここで、次の照会があったとします。

  SELECT   FIRSTNAME, LASTNAME
  FROM     EMPLOYEE
  WHERE    DEPTNO = 5
  AND      TYPE = 'X'
  AND      EMPCODE = 10;

この3つの列のうち1つか2つだけが指定されている場合は、直接索引参照は実行できません。これは、DB2に3つそれぞれの列の値がなく、フル索引キーに一致しないからです。この場合は、代わりに索引スキャンが選ばれます。索引スキャンには、マッチング索引スキャンと非マッチング索引スキャンという2つのタイプがあります。マッチング索引スキャンは絶対位置決め、非マッチング索引スキャンは相対位置決めと呼ばれることがあります。表スキャンに関する前述の説明を思い出してください。索引スキャンはこれに似ています。索引スキャンでは、索引のリーフ・ページが順番に読み取られます。

マッチング索引スキャンは、直接索引参照の場合とほぼ同じような方法で、索引のルート・ページから開始してリーフ・ページまでたどります。ただし、索引の完全なキーが使用できないため、DB2は保持している値を使用して、一致する値がすべて取り出されるまでリーフ・ページをスキャンする必要があります。前述の照会を作成し直してみます。今回はEMPCODE述部を使用しません。

 SELECT   FIRSTNAME, LASTNAME
 FROM     EMPLOYEE
 WHERE    DEPTNO = 5
 AND      TYPE = 'X';

マッチング索引スキャンでは、ルートから開始して索引を探索することにより、DEPTNOとTYPEの適切な値を使用して最初のリーフ・ページを見つけることができます。しかし、この値の組み合わせと異なるEMPCODE値を持つ索引エントリーが複数ある可能性があります。従って、有効なDEPTNOとTYPE、および異なるEMPCODEという組み合わせがなくなるまで、右にあるリーフ・ページが順番にスキャンされます。

マッチング索引スキャンを要求するには、索引キーに高位列を指定する必要があります。前の例では、これはDEPTNOです。これで、DB2が索引構造のルート・ページから該当するリーフ・ページまで探索する開始点が提供されます。この高位列を指定しないとどうなるのでしょうか? 前述の照会を変更して、DEPTNO述部を指定しないとします。

 SELECT   FIRSTNAME, LASTNAME
 FROM     EMPLOYEE
 WHERE    TYPE = 'X'
 AND      EMPCODE = 10;

この場合は、非マッチング索引スキャンを使用できます。この場合、キーの最初の列が使用できないため、DB2は索引ツリー構造を使用できません。非マッチング索引スキャンは、索引内の最初のリーフ・ページから開始し、使用可能な述部を適用して後続のリーフ・ページを順番にスキャンします。ルート・ページと中間にあるリーフ・ページは使用されません。

特殊なタイプの索引スキャンがあり、これは索引専用アクセスと呼ばれます。必要なデータがすべて索引内にある場合、DB2はデータ・ページをまったく読み込まなくてすみます。たとえば、次のような場合です。

SELECT   DEPTNO, TYPE
FROM     EMPLOYEE
WHERE    EMPCODE = 10;

このデータベースには、DEPTNO、TYPE、EMPCODEの各列に対する索引が含まれていることを思い出してください。前述の照会では、要求された列はこの3つの列だけです。従って、すべてのデータが索引内に存在するため、DB2は表をアクセスする必要がまったくありません。

DB2で使用できるもう1つ別の索引アクセス・タイプに、複数索引アクセスというタイプがあります。複数索引アクセスでは1つのアクセス・パスに対して複数の索引を使用します。たとえば、EMPNO列に対する索引IX1とDEPTNO列に対する索引IX2という2つの索引しかないEMPLOYEE表に対する照会を考えてみます。次の照会を要求し、特定の部門に属する社員を表示します。

SELECT   LASTNAME, FIRSTNME, MIDINIT
FROM     EMPLOYEE
WHERE    EMPNO IN ('000100', '000110', '000120')
AND      DEPTNO = 5;

DB2は、EMPNO述部に対するIX1とDEPTNO述部に対するIX2のいずれを使用するべきでしょうか? 両方とも使用してみたらどうか、というのが複数索引アクセスの真髄です。複数索引アクセスには、述部がANDまたはORを使用して結合されているかどうかに応じて、2つのタイプがあります。


結合方式の概要

ここまでは、表が1つだけの単純なアクセス・パスに関してみてきました。結合や複雑なSQLステートメントの場合はどうなるのでしょうか? DB2オプティマイザーには、表データの結合に使用できる一連の技法が含まれています。FROM文節で複数のDB2表が参照されている (またはJOIN文節が指定されている) 場合、SQLはDB2に対して結合を実行することを要求しています。結合条件に基づき、一連の命令を実行して複数の表からのデータを組み合わせる必要があります。

DB2はこれをどのように行うのでしょうか? 複数の表に対する各照会が、別々のアクセス・パスに分解されます。DB2オプティマイザーは表のうちの2つを選択し、その結合を実行するための最適アクセス・パスを作成します。DB2はこれをランダムに行うのではなく、最適とみなされる結合方法に基づいてアクセス・パスを選択します。次にオプティマイザーは、照会全体が最適化されるまで、他の表との結合を続けます。

表を結合するとき、オプティマイザーは使用に最適の結合アルゴリズムを判断する必要があります。結合アルゴリズム (結合方式) は、表を組み合わせるための基本的な手順を定義するものです。DB2が使用できる結合タイプには、ネスト・ループ結合、マージ・スキャン結合、ハッシュ結合という3種類があります。それぞれの結合方式はそれぞれ違う方法で機能しますが、結果は同じです。ただし、結合方式として何を選ぶかが、結合のパフォーマンスに重要な影響を与えます。従って、それぞれの結合方式を理解し、選ぶ基準となる要因を理解しておく必要があります。

基本的なステップは、どの結合方式にも共通しています。一般に、最初に決めることは、どの表を最初に処理するかということです。この表を外部表と呼びます。この決定の後、外部表に対して一連の操作が実行され、その表を結合のために準備します。次に、その表の行が2つ目の表 (内部表と呼ぶ) に組み合わされます。結合が実行される前または結合中 (あるいはその両方) に、内部表に対しても一連の操作が実行されます。結合はすべて同じようなステップで構成されていますが、前述の3種類の結合方式は共通部分以外はかなり違います。オプティマイザーはそれぞれの結合方式の長所と短所を理解し、その方式を使用するとパフォーマンスにどのような影響があるかを判断します。システム・カタログ内の現在の統計情報に基づき、オプティマイザーは、内部表および外部表として最適の表がどれかも判断します。オプティマイザーにより使用されるいくつかの考慮事項の概要を次に示します。

  • 表が小さければ小さいほど外部表として選択される可能性が高い。これにより、内部表を再アクセスする回数が減ります。
  • 選択述部を適用できる表は外部表として選択される可能性が高い。この場合、外部表に適用された述部を満足する行のだけが内部表からアクセスされるからです。
  • 表の1つに対して索引参照を実行できる場合は、その表が内部表の候補になる。表に索引がない場合は、内部表の候補になりません。これは、外部表の各行に対して、その都度内部表全体をスキャンする必要があるからです。
  • 重複項目が最も少ない表は、結合における外部表として選択される傾向がある。

もちろん、以上のどれも厳密な規則ではありません。最終的に、オプティマイザーは詳細なコスト見積りに基づいて外部表と内部表を選択します。次に、DB2で使用可能な結合のタイプとそれぞれのタイプの違いを説明します。

最も一般的な結合方式のタイプは、ネスト・ループ結合 (NLJ) です。NLJを実行するには、外部表の中で適格行が特定され、次に内部表がスキャンされてその行に一致する行が検索されます。適格行とは、表内の列の述部が一致する行です。内部表のスキャンが完了すると、外部表内の別の適格行が特定されます。一致する行がないかどうか再び内部表がスキャンされます。このように操作が続行します。内部表のスキャンの繰り返しは、I/Oコストを最小化するために通常は索引を使用して行われます。

DB2で使用される結合方式の2つ目のタイプは、マージ結合 (MJ) です。MJでは、結合対象の表が結合述部により順番になっている必要があります。つまり、各表は、結合条件を示す列により順番にアクセスする必要があるということです。この順番は、ソートまたは索引アクセスのいずれかの結果です。外部表と内部表が両方とも正しい順番になっていることが確認された後、各表が順番に読み取られ、結合列が突き合わされます。マージ・スキャン結合の実行中、いずれの表も読み取られるのは1回だけです。

結合方式の3つ目のタイプは、DB2が実行されているプラットフォームに依存します。DB2 for OS/390 and z/OSの場合はハイブリッド結合があります。ハイブリッド結合は、データとポインターを組み合わせ、結合対象の表から行をアクセスして組み合わせます。この結合タイプの詳細はこの記事の範疇外なので割愛します。

Linux、UNIX、およびWindows用のDB2の場合、3つ目の結合タイプはハッシュ結合です。ハッシュ結合では、式table1.ColX = table2.ColYの述部が1つ以上必要で、この2つの列タイプは同じである必要があります。内部表がスキャンされ、ソート・ヒープ割り振りから抽出されたメモリー・バッファーに行がコピーされます。メモリー・バッファーは、結合述部の列から計算された「ハッシュ・コード」に基づいてパーティションに分割されます。1つ目の表のサイズが、使用可能ソート・ヒープ領域を超える場合、選択されたパーティションからのバッファーが一時表に書き込まれます。内部表を処理した後、外部表がスキャンされ、「ハッシュ・コード」を比較して外部表の行が内部表の行と突き合わされます。ハッシュ結合では、かなりの量のメモリーが必要になることがあります。このため、ハッシュ結合で実用的なパフォーマンス上の利点を生み出すためには、sortheapデータベース構成パラメーターの値とsheapthresデータベース・マネージャー構成パラメーターを変更しなければならないことがあります。

さて、それでは、どのような場合に以上の結合方式のうちのどれを使うべきなのでしょう? 一般に、ネスト・ループ結合は、結合のための適格行の数が少ないときに実行コストの観点から優先的に使用されます。適格行の数が増えると、マージ結合の方が適しています。ハッシュ結合の場合は、内部表がメモリー・バッファーに保持されます。メモリー・バッファーの数が少なすぎると、ハッシュ結合ではメモリーがあふれてしまいます。オプティマイザーはこれを回避しようとし、2つの表のうちの小さい方を内部表として選択し、大きい方を外部票として選択します。

パフォーマンスの一般化の結果は、適格行の正確な数だけでなく、データベース設計、データベース編成、統計情報の正確性、ハードウェアのタイプ、DB2環境の設定などの要因にも依存します。


最適化クラスを使用した検索方法の指定

また、結合方式の選択は、使用されている最適化クラスにも依存します。最適化クラスは、オプティマイザーがSQLステートメントをコンパイルし最適化するときに使用するさまざまな検索方法を指定します。このため、前述のアクセス・パス技法のすべてがオプティマイザーに対して常に使用可能になっているとは限りません。そうではなく、最適化クラスに基づいて、異なる技法がオプティマイザーで使用できるようになります。最適化クラスの目的は、使用する検索方法と最適化技法を考慮するときに、どれだけ多くの方法を綿密に考慮すべきかをDB2に対して指示することです。一般に、オプティマイザーが考慮する検索方法の数が多ければ多いほど、照会のためのアクセス・プランがより適切になります。ただし、オプティマイザーが考慮するように指示される検索方法の数が増えるほど、SQLを実行可能アクセス・パスにコンパイルする時間が長くなります。幸いにも、照会を最適化するときに適用される技法の数を制限するように最適化クラスを設定することができます。これは、単純な照会、リソース上制限のあるシステム、および動的SQLの場合に非常に役立ちます。表1に最適化クラスの概要を示します。

表1.DB2の最適化クラス

クラス説明
0オプティマイザーが最小限の最適化を使用してアクセス・プランを生成するように指示します。ネスト・ループ結合と索引スキャン・アクセス方式のみが使用可能です。統計情報の使用は限定されます (たとえば、非均一分散統計は考慮しません)。
1クラス0と同様の働きをしますが、マージ結合、表スキャン、および非常に基本的な照会書き換え (プラスさらにいくつかの機能) が追加されます。q
2最適化はクラス1よりも大幅に向上し、コンパイル・コストはクラス3よりもかなり低く抑えます。このクラスは、使用可能なすべての統計、ほとんどの照会書き換え規則、リスト・プリフェッチ、およびサマリー表経路指定を利用します。動的プログラミングではなく煩雑な結合列挙を使用する点を除けば、クラス5と同様の働きをします。
3このクラスは、DB2 for OS/390が使用する照会最適化に最も近いものです。適度の最適化を提供し、適量のコンパイル対象リソースを必要とします。
5大幅な最適化を提供し、クラス3よりも大量のコンパイル対象リソース必要とします。動的SQLに追加のリソースが保証されていない場合を、オプティマイザーがインテリジェントに判断します。クラス5は、複雑な照会と単純な照会の混在環境に適した選択です。
7このクラスはクラス5と同様の働きをしますが、クラス5では使用できない最適化技法がいくつか追加されています。このクラスは、動的SQLに追加のリソースが保証されていない場合を判断しません。
9使用可能なすべての最適化技法を使用します。

表1に説明されている照会最適化クラスはどれでも選択できますが、クラス0と9は特殊な場合にのみ使用します。クラス0、1、および2では煩雑な結合列挙アルゴリズムを使用します。複雑な照会の場合、このアルゴリズムが考慮する代替プランの数ははるかに少なく、コンパイル時間はクラス3以上よりもかなり短くなります。クラス3以上では、動的プログラミング結合列挙アルゴリズムを使用します。このアルゴリズムが考慮する代替プランの数は煩雑なタイプに比べてはるかに多く、表の数が増えるため、クラス0、1、2に比べてコンパイル時間はかなり長くなります。

具体的な照会最適化クラスの設定方法は、使用しているSQLが静的SQLか動的SQLかに依存します。静的SQLステートメントの場合、最適化クラスはPREPおよびBINDコマンドに指定します。SYSCAT.PACKAGESカタログ表のQUERYOPT列が、パッケージのバインドに使用された最適化クラスを記録します。動的SQLステートメントでは、SQL SETステートメントを使用して設定されるCURRENT QUERY OPTIMIZATIONという特殊レジスターにより指定される最適化クラスを使用します。

最後に、2つのタイプの検索方法とその特性を簡単に定義しておきます。1つ目のタイプはクラス0、1、2で使用される煩雑な結合列挙です。このタイプの結合列挙では、2つの表の結合方式がいったん選択されると、その方式はその後の最適化処理中に変更されません。従って、多数の表を結合するときには、絶対的に最善のアクセス・プランが選択されるとは限りません。2、3個の表だけを結合する照会では、煩雑な結合列挙ともう1つの検索方法の動的プログラミング結合列挙が同じアクセス・プランを選択する可能性が大です。動的プログラミング結合列挙では、結合対象の表の数が増えるため、必要な時間とリソースが増えます。煩雑な型結合列挙の場合に比べて、最善のアクセス・プランが選択される可能性が大です。


Visual Explainの使用

以上でDB2がSQL要求を満足するために選択するアクセス・パスに関して基本的に理解できたので、DB2が照会にどのアクセス・パスを使用するかを明らかにする方法を見てみましょう。これは、Explainを使用して行います。1つのSQLステートメントまたはパッケージになっている一連のSQLステートメントが、Explainの対象になります。もちろん、パッケージをExplain処理するときは、静的SQLのみがExplainされます。Delphiの場合は、前述のようにSQLは静的ではなくすべて動的であるため、これは役に立ちません。

Explainが要求されると、SQLステートメントがDB2オプティマイザー経由で渡され、DB2が選択するアクセス・パスがコード形式で一連のDB2 Explain表に取り出されます。このExplain表は標準のDB2表にすぎず、列、データ・タイプ、および長さを事前に定義しておく必要があります。Explain表は自動的に作成されるものではないことを覚えておいてください。Explainを使用するには、使用者 (またはDBA) が、Explain表をまず作成する必要があります。explain.ddlというDB2 CLPスクリプトが、DB2のインストールされているsqllibディレクトリーのmiscサブディレクトリーにあります。このスクリプトを実行すると、Explain表が作成されます。Explain表を正常に作成した後は、DB2アクセス・パスのExplainに使用できるオプションがいくつかあります。

Visual Explainは極めて使いやすく、簡単なポイント・アンド・クリック・コマンドとプルダウン・メニューのGUIを使用して操作できます (図2を参照)。Visual Explainは単独のツールとしてアクセスすることも、DB2コントロール・センターからアクセスすることもできます。Visual Explainの最大の利点はアクセス・パスをグラフィカルに記述することで、Explain表のコード化された情報を理解する必要はありません。それぞれのアクセス・パス操作がツリー構造の色別に分けられたノードに配置されます。ノード上にマウスを置いてクリックするだけで、アクセス・パスのその部分の引数、統計、コスト見積りが表示されます。Visual Explainは、db2vexp.exeとしてコマンド行から実行することもできます。

図2.Visual ExplainのGUI
図2.Visual ExplainのGUI

図2の例は、DB2がPK_15という索引の索引スキャンを使用して、ATTRIBUTE_REGION表からデータをSELECTしたことを示します。ノードをクリックすると、アクセス・パスの各部分の構成要素に関する詳細を表示できます。前項でアクセス・パスを検討したように、スキャン、索引アクセス、およびソートを示すノードがあります。図3は、IXSCANノードをクリックした結果を示します。REGION_TYPE_ID列に対して索引スキャンが実行されることがわかります。Explainの出力を調べれば、DB2がそれぞれのSQL照会を満足するために使用するアクセス・パスがすぐわかります (図3を参照)。

図3.Visual Explainの詳細表示
図3.Visual Explainの詳細表示

わかりやすいアクセス・パス情報を提供するには、Visual Explainのようなツールがない場合は手動による処理が必要です。さらに、手動によるExplainの出力を理解するには、Explain表に含まれているコード化情報を解釈できる必要があります。Visual Explainを使用すれば、Explain表の実際の書式や内容を気にする必要はありません。Visual Explainがすべて処理してくれます。

DB2にはほかにもEXPLAINツールがあります。その1つがdb2explnです。これは、静的パッケージのみのアクセス・パスのテキスト記述を提供する最小機能のみのツールです。このツールはDelphiには役に立ちません。Delphiの場合は、dynexplnというツールが使えます。これは、動的SQL照会のテキスト分析を提供します。dynexplnツールは、動的照会をパッケージ化してからdb2explnを呼び出して操作を実行させます。一般的には、Visual Explainを使用した方が使いやすく、SQLのチューニングに必要な基本情報も提供されます。

Delphiユーザーにとっては、Delphiプログラム内のSQL SELECTステートメントのアクセス・パスの表示にVisual Explainを使用すると良いでしょう。大部分のSELECTステートメントの索引アクセスを取得するようにします。これを行うには、追加索引を作成する (本番環境で行う場合は、DBAの指示に従うようにしてください) か、索引可能述部を含めるようにSQLステートメントを変更します。使用されている結合方式を分析し、その意味を理解します。たとえば、マージ・スキャン結合がソートを必要とするか、それでパフォーマンスが低下するか、などです。

Explainの結果は、DB2システム・カタログ内の統計に依存することを理解しておいてください。Explainを使用する前に、DB2システム・カタログ統計が最新状態になっていることを確認してください。オプティマイザーが効率的なアクセス・プランを選択するためには、DB2表、索引、および列に関するシステム・カタログ内の統計情報が正確であることが不可欠です。最近統計情報が収集されていない場合は、Explainを実行する前に、情報が適切であることを確認します。

最後に、SQLチューニングを成功裏に行うには、ほかにも必要なものがあるので注意してください。SQLパフォーマンスを正しく分析するには、Explainの結果だけでは足りません。正しいパフォーマンス分析には次のものが必要です。

  • 実際のSQLステートメント
  • アクセスまたは変更される (あるいはその両方) オブジェクトのDDL (またはシステム・カタログ情報) のリスト
  • SQLステートメントが埋め込まれているDelphiコード
  • Explainが実行されたときに存在していた実際のシステム・カタログ統計
  • SQLステートメントが実行されるDB2環境に関する知識 (バッファーやロック・パラメーターの設定など)
  • SQLが実行される環境に関する知識 (オペレーティング・システム、プロセッサーの数とタイプ、メモリーの量など)
  • SQLステートメントが実行された (またはこれから実行される) ときのシステム内の並行アクティビティーに関する知識

Explainの出力とともに以上のような情報を使用して、特定のSQLステートメントのパフォーマンスを予測できます。SQLが埋め込まれているハイレベル言語に関する情報をExplainでは提供できないため、アプリケーションのパフォーマンスを調節するにはDelphiコードが重要です。Explainの出力にはSQLステートメントのための効率的なアクセス・パスが示される可能性がありますが、そのステートメントが何千回も実行されるループ内に埋め込まれている場合は、パフォーマンスが低下することはほぼ確実です。

Explainは、結合述部、ローカル述部、およびGROUP BY文節やORDER BY文節に索引が正しく適用されてソートが回避されていることを確認するために使用します。さらに、表の中のデータに関する知識を基に、正しいタイプの結合が実行されているかどうか、結合の内部表と外部表として正しい表が使用されているかどうかを判断します。このような詳細情報に注意を払うことが、最適化アプリケーションと遅いアプリケーションの違いとなって現れます。


まとめ

Visual Explain機能を効果的に使用することにより、DelphiプログラマーはDB2 SQL要求を満足するために使用されるアクセス・パスを理解できます。DB2がデータ要求を満足させるために選択できる技法は多数あり、それぞれの技法の効率性には差があります。このようなことに関して知識を備えたDelphiプログラマーは、Explainを使用してコードを最適化し、その結果DB2データへの効率的なアクセスを実現できます。


DB2 SQLチューニングのためのWebリソース

参考文献

コメント

developerWorks: サイン・イン

必須フィールドは(*)で示されます。


IBM ID が必要ですか?
IBM IDをお忘れですか?


パスワードをお忘れですか?
パスワードの変更

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


お客様が developerWorks に初めてサインインすると、お客様のプロフィールが作成されます。会社名を非表示とする選択を行わない限り、プロフィール内の情報(名前、国/地域や会社名)は公開され、投稿するコンテンツと一緒に表示されますが、いつでもこれらの情報を更新できます。

送信されたすべての情報は安全です。

ディスプレイ・ネームを選択してください



developerWorks に初めてサインインするとプロフィールが作成されますので、その際にディスプレイ・ネームを選択する必要があります。ディスプレイ・ネームは、お客様が developerWorks に投稿するコンテンツと一緒に表示されます。

ディスプレイ・ネームは、3文字から31文字の範囲で指定し、かつ developerWorks コミュニティーでユニークである必要があります。また、プライバシー上の理由でお客様の電子メール・アドレスは使用しないでください。

必須フィールドは(*)で示されます。

3文字から31文字の範囲で指定し

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


送信されたすべての情報は安全です。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=60
Zone=Information Management
ArticleID=322896
ArticleTitle=DB2 SQLアクセス・パスのチューニング
publish-date=01232003