[Db2] 最適化プロファイルの基本的な使い方 (IM-10-00P)

最適化プロファイルを使用する際には、以下の流れで作業します。

• 1. 事前準備
  2. 最適化ガイドﾞラインの作成
  3. 最適化プロファイルの作成
  4. 最適化プロファイルの使用

また、続けて最適化プロファイルを更新する方法、無効化する方法を記載します。

• 5. 最適化プロファイルの更新
  6. 最適化プロファイルの無効化

注: インライン最適化ガイドラインを使うと、これらのステップを実行せず、SQL ステートメントに直接最適化ガイドラインを付与できます。具体的な方法は「運用上の考慮点」を参照してください。

[1. 事前準備]
1-1) レジストリー変数 DB2_OPTPROFILE の設定
V9.1 GA (FP0) では、最適化プロファイルを使用可能にするために、 DB2_OPTPROFILE を YES に設定する必要があります。以下のように db2set コマンドで設定してください。

db2set DB2_OPTPROFILE=YES
db2stop
db2start

V9.1 FP1 以降では設定は不要です。ただし V11.1 より前のバージョンでインライン最適化ガイドラインを利用する場合は DB2_OPTPROFILE=YES の設定が必要です。

1-2) Explain 表の作成
最適化ガイドラインの定義や、指定した最適化プロファイルの適用確認などのために、 EXPLAIN ツールを使用します。そのために Explain 表を作成しておく必要があります。Explain 表の作成と削除の方法は、以下の Technote を参照してください。Explain 表は各データベースで EXPLAIN ツールを実行するユーザーごとに一回作成します。
[Db2] Explain 表の作成方法と削除方法

1-3) SYSTOOLS.OPT_PROFILE 表の作成
最適化プロファイルの定義は SYSTOOLS.OPT_PROFILE 表に登録されるため、事前にこの表を作成する必要があります。SYSINSTALLOBJECTS プロシージャーで SYSTOOLS.OPT_PROFILE 表を作成してください。(これは、各データベースで一回のみ実行します。)

• db2 "CALL SYSINSTALLOBJECTS('OPT_PROFILES','C','','')"

• 例)
  db2 set current explain mode EXPLAIN
  db2 SELECT * FROM DB2INST1.T1 WHERE C1=1
  db2 set current explain mode NO
  db2exfmt -d sample -o db2exfmt.out

2-2) 最適化ガイドラインの定義
最適化ガイドラインを XML ファイルで定義します。

STMTKEY エレメントで指定する SQL ステートメントが最適化の対象となる SQL ステートメントです。 2-1) で取得した db2exfmt.out の Original Statement から抜き出してください。SQL ステートメントは大文字小文字やスペースを含め Original Statement と完全に一致する必要があります。また、XML 要素を含む SQL の場合は CDATA セクションを利用します。
OPTGUIDELINES エレメントでアクセスおよび結合要求で使用されるオブジェクトを指定します。

以下のシナリオでは MyOptGudelines.xml というファイル名の最適化ガイドラインを作成し、DB2INST1.T1 に対して索引スキャン・アクセス要求を指定しています。

• 例)
  【表定義】
   CREATE TABLE DB2INST1.T1 (C1 INT, C2 CHAR(10))
   CREATE INDEX DB2INST1.IXT1C1 ON T1(C1)
  
  【照会】
   SELECT * FROM DB2INST1.T1 WHERE C1=1
  
  【最適化ガイドライン】
  <?xml version="1.0" encoding="UTF-8"?>
  <OPTPROFILE VERSION="11.1.4.5">
   <STMTPROFILE ID="Statement Profile 1">
    <STMTKEY SCHEMA="MYSCHEMA">
     SELECT * FROM DB2INST1.T1 WHERE C1=1
    </STMTKEY>
    <OPTGUIDELINES>
     <IXSCAN TABLE='DB2INST1.T1' INDEX='IXT1C1' />
    </OPTGUIDELINES>
   </STMTPROFILE>
  </OPTPROFILE>

補足) SQL に特殊な XML 文字 (以下の例では「<」) が含まれている場合は、以下のように CDATA セクションを利用します。

• <?xml version="1.0" encoding="UTF-8"?>
  <OPTPROFILE VERSION="11.1.4.5">
   <STMTPROFILE ID="Statement Profile 2">
    <STMTKEY SCHEMA="MYSCHEMA">
      <![CDATA[SELECT * FROM DB2INST1.STAFF WHERE ID < 30 ]]>
    </STMTKEY>
    <OPTGUIDELINES>
      <IXSCAN TABLE='DB2INST1.STAFF' INDEX='DB2INST1.IND1' />
    </OPTGUIDELINES>
  </STMTPROFILE>
  </OPTPROFILE>

2-3) 最適化ガイドラインの IMPORT
作成したガイドラインを IMPORT するための CSV ファイルを作成します。この例では、MyProfile.del という名前で、DB2INST1.PROFILE1 という名前の最適化プロファイルとして IMPORT するための CSV ファイルを作成しています。

• 例)
  "DB2INST1","PROFILE1","MyOptGudelines.xml"

[3. 最適化プロファイルの作成]
3-1) 2-3) で作成した CSV ファイルを IMPORT して最適化プロファイルを作成します。

• 例)
  db2 import from MyProfile.del of del modified by lobsinfile insert into SYSTOOLS.OPT_PROFILE

[4. 最適化プロファイルの使用]
4-1) CURRENT SCHEMA 特殊レジスターを 2-2) で作成したガイドラインの STMTKEY エレメントで指定したものに、CURRENT OPTIMIZATION PROFILE 特殊レジスターを 2-3) で指定した最適化プロファイルに設定します。

• 例)
  db2 set current schema MYSCHEMA
  db2 set current optimization profile DB2INST1.PROFILE1

4-2) Explain を採取し、指定した最適化プロファイルが使用されていることを確認します。

• 例)
  db2 set current explain mode EXPLAIN
  db2 SELECT * FROM DB2INST1.T1 WHERE C1=1
  db2 set current explain mode NO
  db2exfmt -d sample -o db2exfmt.out
  
  [db2exfmt.out 抜粋]
  Profile Information:
  --------------------
  OPT_PROF: (Optimization Profile Name)
          DB2INST1.PROFILE1
  STMTPROF: (Statement Profile Name)
          Statement Profile 1

[5. 最適化プロファイルの更新]
5-1) 2-2) で作成した XML ファイルを編集後、以下の手順でプロファイルに反映させます。

• 例)
  db2 delete from SYSTOOLS.OPT_PROFILE where schema='DB2INST1' and name='PROFILE1'
  db2 import from MyProfile.del of del modified by lobsinfile insert into SYSTOOLS.OPT_PROFILE
  db2 flush optimization profile cache DB2INST1.PROFILE1

[6. 最適化プロファイルの無効化]
6-1) CURRENT OPTIMIZATION PROFILE 特殊レジスターを NULL にします。

• db2 set current optimization profile NULL  

運用上の考慮点
最適化プロファイルは、すべてのチューニング・オプションを使い尽くしても期待する結果が得られない場合に使用するようにしてください。
オプティマイザーは無効な最適化ガイドラインや適用できない最適化ガイドラインは無視します。そのため、必ず指定したアクセスおよび結合要求がアクセスプランとして選択されるとは限りません。

V11.1 以降、上記のステップを実行しなくても、SQL ステートメントの末尾にコメント形式でインライン最適化ガイドラインを付与できます。
例
$ cat select_w_guideline.sql
---------------------------------------
SELECT * FROM DB2INST1.T1 WHERE C1=1
/*
  <OPTGUIDELINES>
   <IXSCAN TABLE='DB2INST1.T1' INDEX='IXT1C1' />
  </OPTGUIDELINES>
*/ ;
---------------------------------------
$ db2 -tvf select_w_guideline.sql


関連情報
パスポート・アドバンテージによく寄せられる質問
最適化プロファイルと最適化ガイドライン

お問合せ先
技術的な内容に関して、パスポート・アドバンテージの契約のもと Db2 テクニカル・サポートへお問い合わせください。
Db2 テクニカル・サポート