DB2DBA: DB2 11.1新機能 - ヒント（最適化ガイドライン）

DB2 for LUW 11.1よりSQL中にヒント（最適化ガイドライン）を記述できるようになりました。

11.1以前

DB2 for LUWでは従来V9.1より実行計画を制御するための「最適化プロファイル」機能が提供されていました（後にV8.2にバックポート）。¹しかし、これを利用するには以下のようにやや煩雑な手続きが必要でした。

• 対象のSQL文とそれに対するヒント（最適化ガイドライン）をXMLで記述した最適化プロファイルを作成する
• SYSTOOLS.OPT_PROFILE表を作成し、作成した最適化プロファイルをINSERTする
• 作成した最適化プロファイルの使用をSQL実行時に宣言する

その他の方法として統計情報の数字を手動で更新して固定するという裏技がありますが、これは複雑なSQLを意図した実行計画に誘導するのが難しく、またすべてのSQLに影響が出るためチューニング対象でないSQLの実行計画が悪化する懸念もあります。

なお、DB2 for z/OSでは「ヒント」という名称で実行計画制御機能が提供されていますが、XMLを用いないことを除き利用方法は最適化プロファイルに類似しており、やはりSQL中には指定できません。

11.1での改良点

DB2 for LUW 11.1では最適化ガイドラインをSQL中に記述し、他のRDBMSのヒント機能と同じように利用することが可能になりました。SQL中の最適化ガイドラインは以下のようにブロックコメント/* */内に記述します。

SELECT * FROM table WHERE col > 100
/* <OPTGUIDELINES><IXSCAN TABLE='table' INDEX="index"/></OPTGUIDELINES> */

やはりXMLでの記述が必要な点でDB屋にとってはまだ少しとっつきにくいですが、これでずいぶん導入しやすくなりました。

最適化プロファイルのXMLスキーマはsqllibディレクトリ下のmisc/DB2OptProfile.xsdで提供されています。

試してみた

表の準備

以下のようなEMPLOYEE表とDEPARTMENT表を用意します。EMPLOYEE表には従業員の所属部門を示すDEPTID、DEPARTMENT表には部門のマネージャを示すMANAGERIDが設定されていますが、どちらも参照制約は設定していません。

create table employee(id int not null, name varchar(32) not null, deptid int)
alter table employee add constraint pk_employee primary key(id)
create index ix_employee_deptid on employee(deptid)

create table department(id int not null, name varchar(32) not null, managerid int)
alter table department add constraint pk_department primary key(id)
create index ix_department_managerid on department(managerid)

例1 - 索引の指定

以下のようにIDとDEPTIDが指定されたEMPLOYEE表に対するSQLをEXPLAINにかけると、索引IX_EMPLOYEE_DEPTIDが使用されていました。

select * from employee where id < 1000 and deptid = 20

              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
             2.10667
             FETCH
             (   2)
             6.84511
                1
          /----+----\
       6.32           158
      IXSCAN    TABLE: SVC34
      (   3)       EMPLOYEE
     0.0314387        Q1
         0
        |
        158
  INDEX: SVC34
 IX_EMPLOYEE_DEPTID
        Q1

IX_EMPLOYEE_DEPTIDではなくPK_EMPLOYEE（PRIMARY KEY制約付与時に自動的に作成されたもの）を使用してもらいたい場合、以下のように最適化ガイドラインを記述します。IXSCAN要素が索引スキャンの指示であり、TABLE属性に対象の表、INDEX属性に使用する索引を指定します。

select * from employee where id < 1000 and deptid = 20
/*
<OPTGUIDELINES>
    <IXSCAN TABLE='employee' INDEX='pk_employee'/>
</OPTGUIDELINES>
*/

             Rows
            RETURN
            (   1)
             Cost
              I/O
              |
            2.10667
            FETCH
            (   2)
            6.88827
               1
         /----+----\
     52.6667         158
     IXSCAN    TABLE: SVC34
     (   3)       EMPLOYEE
    0.0503776        Q1
        0
       |
       158
 INDEX: SVC34
   PK_EMPLOYEE
       Q1

例2 - 結合方式の指定

次のSQLは、EMPLOYEE表からDEPARTMENT表のいずれかの部門にMANAGERIDとして登録されているレコードを取得します。

select emp.* from employee as emp inner join department as dept on emp.id = dept.managerid

今回RUNSTATSをかけていないためと思われますが、EXPLAINをかけるとHASH JOINが選択されてしまいました。

               Rows
              RETURN
              (   1)
               Cost
                I/O
                |
                158
              HSJOIN^
              (   2)
              7.07611
                 1
        /-------+--------\
      158                  158
    TBSCAN               IXSCAN
    (   3)               (   4)
    6.93013             0.120305
       1                    0
      |                    |
      158                  158
TABLE: SVC34         INDEX: SVC34
   EMPLOYEE      IX_DEPARTMENT_MANAGERID
      Q2                   Q1

これをNested Loop JOINに変更したい場合は、以下のように最適化ガイドラインを記述します。FIRST='TRUE'が指定された表が駆動表となります。なお、表に別名をつけている場合は最適化ガイドラインでも別名を指定しないと有効に働かないようです。

select emp.* from employee as emp inner join department as dept on emp.id = dept.managerid
/*
<OPTGUIDELINES>
    <NLJOIN>
        <ACCESS TABLE='dept' FIRST='TRUE'/>
        <ACCESS TABLE='emp'/>
    </NLJOIN>
</OPTGUIDELINES>
*/

                     Rows
                    RETURN
                    (   1)
                     Cost
                      I/O
                      |
                      158
                    ^NLJOIN
                    (   2)
                    16.5858
                       2
            /---------+----------\
          158                       1
        IXSCAN                   FETCH
        (   3)                   (   4)
       0.120305                  6.83796
           0                        1
          |                   /----+----\
          158                1            158
    INDEX: SVC34          IXSCAN    TABLE: SVC34
IX_DEPARTMENT_MANAGERID   (   5)       EMPLOYEE
          Q1             0.0272431        Q2
                             0
                            |
                            158
                      INDEX: SVC34
                        PK_EMPLOYEE
                            Q2

ACCESS要素で表を指定するとその表に対するアクセス方法はオプティマイザが決定しますが、前述のIXSCAN要素（索引スキャン）やTBSCAN要素（表スキャン）で表を指定すればアクセス方法を指示することも可能です。

3つ以上の表の結合順序を指定する場合、以下のようにxxJOINの要素をネストさせれば良いです。多数の表の結合になると、階層構造を表現できるXMLの特性が活きてくると思います。

<OPTGUIDELINES>
    <NLJOIN>
        <HSJOIN>
            <ACCESS TABLE='table1'/>
            <ACCESS TABLE='table2'/>
        </HSJOIN>
        <IXSCAN TABLE='table3' INDEX='ix3'/>
    </NLJOIN>
</OPTGUIDELINES>

エラーの確認方法

SQL中の最適化ガイドラインはもともとコメントであるため、文法誤りやオブジェクト名の間違いがあってもSQL実行時にはエラー・警告は発生しません。誤りがある場合はEXPLAINのExtended Diagnostic Informationに警告メッセージが出力されるので、必ず確認するようにしましょう。

Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier:  1
Diagnostic Details:     EXP0004W  The optimization profile or embedded
                        optimization guideline is either not well-formed or
                        is invalid. Line number "1", character number "32".

参考資料

• IBM Knowledge Center
  • DB2 for Linux UNIX and Windows 11.1.0
    • 組み込み最適化ガイドライン
    • プラン最適化ガイドライン
    • プラン最適化ガイドライン用の XML スキーマ

1. 認知度が低いためか、ミックさんのSQL実践入門にはDB2は「ヒント句を持たず、原則ユーザは実行計画を制御できない」と書かれてしまっていますが... ↩