最適化プロファイルと最適化ガイドライン

最適化プロファイルは、1 つ以上の SQL ステートメントについての最適化ガイドラインを含めることのできる XML 文書です。各 SQL ステートメントとそれに関連付ける最適化ガイドラインとの間の対応は、SQL テキストおよび SQL ステートメントを明確に識別するために必要な他の情報を使用して確立されます。

Db2® オプティマイザーは、業界で最も高度なコスト・ベースのオプティマイザーの 1 つです。とはいえ、まれにこのオプティマイザーも、必ずしも最適とは言えない実行プランを選択してしまうことがあります。データベースに精通している DBA は、 db2advis, runstats、 db2expln、および最適化クラス設定などのユーティリティーを使用して、データベース・パフォーマンスを向上させるためにオプティマイザーを調整することができます。しかし、すべてのチューニング・オプションを使い尽くしても期待した結果が得られない場合には、Db2 オプティマイザーに明示的な最適化ガイドラインを提供できます。

例えば、データベース統計の更新と、他のすべてのチューニング・ステップの実行を済ませたものの、オプティマイザーが、以下の副照会での SUPPLIERS 表へのアクセスのために I_SUPPKEY 索引をまだ選択していないとします。

    SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
    FROM PARTS P, SUPPLIERS S, PARTSUPP PS
    WHERE P_PARTKEY = PS.PS_PARTKEY
      AND S.S_SUPPKEY = PS.PS_SUPPKEY
      AND P.P_SIZE = 39
      AND P.P_TYPE = 'BRASS'
      AND S.S_NATION = 'MOROCCO'
      AND S.S_NATION IN ('MOROCCO', 'SPAIN')
      AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST) 
                              FROM PARTSUPP PS1, SUPPLIERS S1 
                              WHERE P.P_PARTKEY = PS1.PS_PARTKEY
                                AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
                                AND S1.S_NATION = S.S_NATION))

この場合、明示的な最適化ガイドラインを使用して、オプティマイザーに影響を与えることができます。以下に例を示します。

<OPTGUIDELINES><IXSCAN TABLE="S" INDEX="I_SUPPKEY"/></OPTGUIDELINES>

最適化ガイドラインは、単純な XML 仕様を使用して指定されます。 OPTGUIDELINES エレメント内のそれぞれのエレメントは、Db2 オプティマイザーによって最適化ガイドラインとして解釈されます。この例には、1 つの最適化ガイドライン・エレメントがあります。 IXSCAN エレメントは、オプティマイザーが索引アクセスを使用することを要求します。 IXSCAN エレメントの TABLE 属性は、ターゲット表参照 (表参照の直接的な名前を使用) を示し、INDEX 属性は索引を指定します。

以下の例は、前の照会に基づいており、最適化プロファイルを使用してどのように最適化ガイドラインを Db2 オプティマイザーに渡すことができるのかを示しています。

<?xml version="1.0" encoding="UTF-8"?>

<OPTPROFILE VERSION="9.1.0.0">
<STMTPROFILE ID="Guidelines for SAMP Q9"> 
  <STMTKEY SCHEMA="SAMP">
    SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
    FROM PARTS P, SUPPLIERS S, PARTSUPP PS
    WHERE P_PARTKEY = PS.PS_PARTKEY
      AND S.S_SUPPKEY = PS.PS_SUPPKEY
      AND P.P_SIZE = 39
      AND P.P_TYPE = 'BRASS'
      AND S.S_NATION = 'MOROCCO'
      AND S.S_NATION IN ('MOROCCO', 'SPAIN')
      AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST) 
                              FROM PARTSUPP PS1, SUPPLIERS S1 
                              WHERE P.P_PARTKEY = PS1.PS_PARTKEY
                                AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
                                AND S1.S_NATION = S.S_NATION))
  </STMTKEY>
  <OPTGUIDELINES><IXSCAN TABLE="S" INDEX="I_SUPPKEY"/></OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

各 STMTPROFILE 要素は、1 つのアプリケーション・ステートメントに対して一連の最適化ガイドラインを提供します。ターゲットとなるステートメントは、STMTKEY サブエレメントによって識別されます。次いで、最適化プロファイルはスキーマ修飾名を付けられて、データベースに挿入されます。挿入された最適化プロファイルは、BIND コマンドや PRECOMPILE コマンドでこの修飾名が指定されたときに、ステートメントに対して施行されます。

最適化プロファイルを使用すると、アプリケーションやデータベースの構成を変更することなく、オプティマイザーに最適化ガイドラインを渡すことができます。行うことは、ただ単純な XML 文書を作成し、それをデータベースに挿入して、BIND コマンドや PRECOMPILE コマンドでその最適化プロファイルの名前を指定するだけです。オプティマイザーにより最適化ガイドラインと該当するステートメントが、自動的にマッチングされます。

最適化ガイドラインは、包括的なものにする必要はありませんが、希望する実行プランをターゲットにしたものでなければなりません。 Db2 オプティマイザーは、ガイドラインがあっても、他の候補となるアクセス・プランを引き続き考慮し、既存のコスト・ベースのメソッドを使用して機能します。最適化ガイドラインは、特定の表参照をターゲットにしていれば、通常の最適化の設定をオーバーライドできません。例えば、表 A と B の間のマージ結合を指定する最適化ガイドラインは、最適化クラス 0 では無効です。

組み込み最適化ガイドラインによって、SQL ステートメントの末尾に最適化ガイドラインを指定することもできます。以下に例を示します。

/*<OPTGUIDELINES><IXSCAN TABLE="S" INDEX="I_SUPPKEY"/></OPTGUIDELINES>*/

この方法で最適化ガイドラインを指定する場合、追加の構成は必要ありません。

オプティマイザーは、無効な最適化ガイドラインや適用できない最適化ガイドラインは無視します。無視された最適化ガイドラインがある場合、実行プランが作成され、理由コード 13 で SQL0437W の警告が戻されます。その後で、最適化ガイドラインの処理に関する詳細な診断情報は、EXPLAIN ステートメントを使用して入手できます。