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

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

测试环境

$ db2level
DB21085I  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.txt
Original Statement:
------------------
select 
  name 
from 
  t1 
where 
  id = 42


Optimized 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.txt
Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
        E105Q5A.PROF1
STMTPROF: (Statement Profile Name)
        Use Index Scan instead of Table Scan

Original Statement:
------------------
select 
  name 
from 
  t1 
where 
  id = 42


Optimized 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