1. SVC34

    Posted

    SVC34
Changes in title
+DB2DBA: DB2 11.1新機能 - ヒント(最適化ガイドライン)
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,199 @@
+DB2 for LUW 11.1よりSQL中にヒント(最適化ガイドライン)を記述できるようになりました。
+
+# 11.1以前
+DB2 for LUWでは従来V9.1より実行計画を制御するための「最適化プロファイル」機能が提供されていました(後にV8.2にバックポート)。[^1]しかし、これを利用するには以下のようにやや煩雑な手続きが必要でした。
+
+- 対象の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中の最適化ガイドラインは以下のようにブロックコメント/* */内に記述します。
+
+```sql
+SELECT * FROM table WHERE
+/* <OPTGUIDELINES><IXSCAN TABLE='table' INDEX="index"/></OPTGUIDELINES> */
+```
+
+やはりXMLでの記述が必要な点でまだ少しとっつきにくいですが、これでずいぶん導入しやすくなりました。
+
+# 試してみた
+## 表の準備
+以下のような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にかけると、索引PK_EMPLOYEE(PRIMARY KEY制約付与時に自動的に作成されたもの)が使用されていました。
+
+```sql
+select * from employee where id < 10 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
+```
+
+PK_EMPLOYEEではなくIX_EMPLOYEE_DEPTIDを使用してもらいたい場合、以下のように最適化ガイドラインを記述します。IXSCAN要素が索引スキャンの指示であり、TABLE属性に対象の表、INDEX属性に使用する索引を指定します。
+
+```xml
+select * from employee where id < 10 and deptid = 20
+/*
+<OPTGUIDELINES>
+ <IXSCAN TABLE='employee' INDEX='ix_employee_deptid'/>
+</OPTGUIDELINES>
+*/
+```
+
+```
+ 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
+```
+
+
+## 例2 - 結合方式の指定
+次のSQLは、EMPLOYEE表からDEPARTMENT表のいずれかの部門にMANAGERIDとして登録されているレコードを取得します。
+
+```sql
+select emp.id, emp.name , emp.deptid 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に変更したい場合は、以下のように最適化ガイドラインを記述します。NLJOIN要素内で先に指定された表が駆動表となります。
+
+```xml
+select emp.id, emp.name , emp.deptid from employee as emp inner join department as dept on emp.id = dept.managerid
+/*
+<OPTGUIDELINES>
+ <NLJOIN>
+ <ACCESS TABLE='dept'/>
+ <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要素(表スキャン)で表を指定すればアクセス方法を指示することも可能です。
+
+
+# 参考資料
+- IBM Knowledge Center
+ - DB2 for Linux UNIX and Windows 11.1.0
+ - [組み込み最適化ガイドライン](http://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0070117.html)
+ - [プラン最適化ガイドライン](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0024526.html)
+ - [プラン最適化ガイドライン用の XML スキーマ](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0024617.html)
+
+
+
+[^1]: 認知度が低いためか、ミックさんのSQL実践入門にはDB2は「ヒント句を持たず、原則ユーザは実行計画を制御できない」と書かれてしまっていますが...