使用DB2优化概要强制修改DB2的执行计划

DB2中SQL语句的执行计划是DB2优化器根据统计信息来制定的，有的时候，需要人工干预，比如需要强制使用索引扫描。这时候可以使用优化概要来实现。下面的例子中，SQL原本使用了表扫描，在使用了优化概要强制规定使用索引扫描之后，执行计划变成了索引扫描。

测试环境

$ db2levelDB21085I  This instance or install (instance name, where applicable: "e105q5a") uses "64" bits and DB2 code release "SQL10055" with level identifier "0606010E".Informational tokens are "DB2 v10.5.0.5", "s141128", "IP23626", and Fix Pack "5".Product is installed at "/opt/IBM/db2/V10.5.5".

未使用优化概要时的执行计划

新建一张分区表，并建立非分区索引，发现SQL语句的访问计划并未使用索引扫描，而是使用了TBSCAN

$ db2 connect to sample$ db2 "create tablespace tbs1"   $ db2 "create tablespace tbs2"$ db2 "create tablespace tbs3"$ db2 "CREATE TABLE T1 (ID INTEGER , NAME CHAR(20) ) PARTITION BY RANGE(ID) (PART P1 STARTING(1) ENDING(40) IN TBS1, PART P2 STARTING(41) ENDING(80) IN TBS2, PART P3 STARTING(81) ENDING(120) IN TBS3)"$ db2 "CREATE INDEX IDX1 ON T1 (ID) NOT PARTITIONED"$ db2 "insert into t1 values(1,'a'),(2,'b'),(41,'c'),(42,'d'),(81,'e'),(82,'f')"$ db2 "runstats on table t1 and indexes all"$ db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL$ db2 "set current explain mode explain"$ db2 "select name from t1 where id = 42"$ db2 "set current explain mode no"$ db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o output1.txt

执行计划如下：

$ cat output1.txtOriginal Statement:------------------select   name from   t1 where   id = 42Optimized Statement:-------------------SELECT   Q1.NAME AS "NAME" FROM   E105Q5A.T1 AS Q1 WHERE   (Q1.ID = 42)Access Plan:-----------        Total Cost:             6.81003        Query Degree:           1        Rows        RETURN       (   1)        Cost          I/O          |          1        TBSCAN       (   2)       6.81003           1          |          6  DP-TABLE: E105Q5A          T1         Q1

创建优化概要文件

需要先创建一个表systools.opt_profile。还要有一个xml文件：a1.xml中的E105Q5A是实例名，也是默认的模式名。OPTGUIDELINES规定表T1使用索引扫描，使用的索引为IDX1

$ db2 "create table systools.opt_profile (schema VARCHAR(128) not null, name varchar(128) not null, profile blob (2M) not null, primary key (schema, name))"$ cat a1.xml<?xml version="1.0" encoding="UTF-8"?><OPTPROFILE VERSION="10.5.0.5"><STMTPROFILE ID="Use Index Scan instead of Table Scan"><STMTKEY SCHEMA="E105Q5A"><![CDATA[select name from t1 where id = 42]]></STMTKEY><OPTGUIDELINES><IXSCAN TABLE="T1" INDEX="IDX1"/></OPTGUIDELINES></STMTPROFILE></OPTPROFILE>$ cat insert.del"E105Q5A", "PROF1", "a1.xml"$ db2 "import from insert.del of del modified by lobsinfile insert into systools.opt_profile"$ db2set DB2_OPTPROFILE=YES$ db2 terminate$ db2stop$ db2start

使用优化概要文件

$ db2 connect to sample$ db2 set current optimization profile='PROF1' $ db2 set current explain mode explain$ db2 "select name from t1 where id = 42"$ db2 set current explain mode no$ db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o output2.txt

再次查看执行计划，发现使用了索引扫描:

$ cat output2.txtProfile Information:--------------------OPT_PROF: (Optimization Profile Name)        E105Q5A.PROF1STMTPROF: (Statement Profile Name)        Use Index Scan instead of Table ScanOriginal Statement:------------------select   name from   t1 where   id = 42Optimized Statement:-------------------SELECT   Q1.NAME AS "NAME" FROM   E105Q5A.T1 AS Q1 WHERE   (Q1.ID = 42)Access Plan:-----------        Total Cost:             6.81152        Query Degree:           1              Rows              RETURN             (   1)              Cost                I/O                |                1              FETCH              (   2)             6.81152                 1          /-----+-----\        1               6      IXSCAN    DP-TABLE: E105Q5A      (   3)            T1   0.00173777          Q1        0        |        6  INDEX: E105Q5A       IDX1       Q1

可以看到，根据DB2优化器的估计，这里使用IXSCAN比使用TBSCAN的cost还要高，所以DB2选择了表扫描。在后续的测试中，发现当表的记录数很多的时候，即使没有使用优化概要，DB2也会选择索引扫描。

参考链接

http://www-01.ibm.com/support/docview.wss?uid=swg21430976

其他的说明：
https://www.ibm.com/developerworks/data/library/techarticle/dm-0612chen/
https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1008pengxq/
https://www.ibm.com/developerworks/data/library/techarticle/dm-1202storedprocedure/
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0060612.html