Oracle hint在DB2中的实现-访问计划定制

很多熟悉Oracle的朋友都了解Oracle的hint，它允许在SQL语句中插入相关的语法，从而定制SQL的访问计划。在DB2中，对应的是优化概要（Optimizer Guideline），也可以定制任何想要的访问计划。

它提供了两种方式，一种是嵌入式，一种是独立式。

1.       嵌入方式。

             将优化概要嵌入在SQL语句中，使用这种方式需要首先设置注册变量DB2_OPTPROFILE=YES并重启DB2服务器。如果需要在SQL调优的过程中对单条SQL语句尝试各种不同的访问计划，那么这种方式就很适合。

接下来用一个例子来说明。如下所示的SQL语句使用了嵌入式优化概要，它对优化器生成访问计划作出了这样的指示：

§  表的访问路径：访问period表使用索引PERIOD_UIDX；访问product表使用表扫描；访问sales表使用索引扫描，由优化器根据成本模型为其选择合适的索引。

§  连接方式和顺序：period表先与product做连接操作，连接方法为NLJOIN；然后再与sales表连接，连接方法为NLJOIN。

SELECT day,  prod_name,  sum(dollars)

FROM   product prd,  period per,  sales s

WHERE  prd.classkey = s.classkey and

        per.perkey = s.perkey and

        prd.prodkey = s.prodkey and

        prod_name like 'Darjeeling%' and

        day in ('SA', 'SU') and

        year = 2002

group by day, prod_name

/* <OPTGUIDELINES>

     <NLJOIN>

        <NLJOIN>

          <IXSCAN TABLE='per' INDEX='PERIOD_UIDX'/>

          <TBSCAN TABLE='prd'/>

       </NLJOIN>

       <IXSCAN TABLE='S'/>

     </NLJOIN>

</OPTGUIDELINES> */;    

我们可以使用db2exfmt工具打印出这个SQL语句的访问计划，从下面可以看出：访问period表使用了索引扫描PERIOD_UIDX，period表先与product表做NLJOIN，然后与sales再做NLJOIN。优化概要为sales表指定了索引扫描但没有指定特定的索引，优化器根据成本模型最终选择了索引ZZ_I1。

                                          Rows

                                         RETURN

                                         (   1)

                                          Cost

                                           I/O

                                           |

                                         5.15254

                                         GRPBY

                                         (   2)

                                         27910.8

                                           151

                                           |

                                         5.15254

                                         TBSCAN

                                         (   3)

                                         27910.7

                                           151

                                           |

                                         5.15254

                                         SORT

                                         (   4)

                                         27910.6

                                           151

                                           |

                                         604.507

                                         NLJOIN

                                         (   5)

                                         27882.2

                                           151

                           /---------------+---------------

                       9221.8                             0.0655519

                       NLJOIN                              FETCH

                       (   6)                              (  10)

                       14380.9                             10.4282

                         15                                1.06555

               /---------+---------                    /----+----

           104.793                   88            0.0655519       69941

           FETCH                   TBSCAN           IXSCAN    TABLE: DB2ADMIN

           (   7)                  (   9)           (  11)           SALES

           136.917                 219.835          9.89349         Q3

              4                      11                1

         /---+----                  |                |

       822          822             1298             69941

     IXSCAN   TABLE:DB2ADMIN  TABLE:DB2ADMIN  INDEX: DB2ADMIN

     (   8)        PERIOD          PRODUCT           ZZ_I1

     58.1848        Q4               Q5                Q3

        0

       |

       822

 INDEX: DB2ADMIN

   PERIOD_UIDX

       Q4                    

2.       独立方式

                    将优化概要嵌入到SQL语句中，在实际项目中并不是一个好的方式，因为数据是变化的，在某一时刻使用这个访问计划是最优的，在另一个时刻，却可能很差，这时再来修改应用中SQL语句的优化概要，很不方便。独立方式可以解决这个问题，它用独立的文件存储SQL语句和相应的优化概要，并注册到DB2中。这样，当需要改变访问计划时，不用修改SQL语句本身，只要修改独立文件的内容就行了。

                    不同于嵌入方式，使用独立方式时不需要设置注册变量DB2_OPTPROFILE=YES，下面介绍使用独立方式的四个步骤。

（1）创建OPT_PROFILE表

OPT_PROFILE表用于存储优化概要文件，可以调用sysinstallobjects存储过程自动创建或者使用CREATE TABLE语句手工创建。

如下所示，其中schema和name是组合主键，用来唯一确定某个优化概要。

call sysinstallobjects('opt_profiles', 'c', '', '');

或者

CREATE TABLE SYSTOOLS.OPT_PROFILE (

               SCHEMA  VARCHAR(128) NOT NULL,

               NAME    VARCHAR(128) NOT NULL,

               PROFILE BLOB (2M)    NOT NULL,

               PRIMARY KEY ( SCHEMA, NAME )

             );

（2）创建OPT_PROFILE文件

优化概要文件是一个标准的XML文件。如下所示，名为opt_prof1.xml的优化概要文件中，根节点为OPTPROFILE，其下可以有多个STMTPROFILE，每个STMTPROFILE指定一条SQL语句的优化概要。STMTPROFILE主要由两部分组成，STMTKEY包含SQL语句，而OPTGUIDELINES指定相应的优化概要，在STMTKEY中可以用属性SCHEMA指定SQL语句所引用表的默认模式，比如例子中指定默认模式为DB2ADMIN。

<?xml version="1.0" encoding="UTF-8"?>

<OPTPROFILE>

  <STMTPROFILE ID="Q1">

    <STMTKEY SCHEMA="DB2ADMIN"><![CDATA[

    SELECT DAY,  prod_name, sum(dollars)

    FROM product prd, period per, sales s

    WHERE prd.classkey = s.classkey

      and per.perkey = s.perkey

      and prd.prodkey = s.prodkey

      and prod_name like 'Darjeeling%'

      and day in ('SA', 'SU')

      and year = 2002

    group by day, prod_name

    ]]></STMTKEY>

    <OPTGUIDELINES>

      <NLJOIN>

        <NLJOIN>

          <IXSCAN TABLE='per' INDEX='PERIOD_UIDX'/>

          <TBSCAN TABLE='prd'/>

        </NLJOIN>

        <IXSCAN TABLE='S'/>

      </NLJOIN>

    </OPTGUIDELINES>

  </STMTPROFILE>

</OPTPROFILE>

（3）注册优化概要

注册优化概要，即将优化概要文件存储到第一步创建的SYSTOOLS.OPT_PROFILE表中。首先编辑如下内容，并保存到opt_prof1.del中。这里的内容与表SYSTOOLS.OPT_PROFILE结构一致，第一个值指定优化概要的模式DB2ADMIN，第二个值指定优化概要的名字OPT_PROF1，第三个值指定优化概要文件的名字，示例中为opt_prof1.xml。

-- 文件opt_prof1.del

"DB2ADMIN", "OPT_PROF1", "opt_prof1.xml"

然后用import命令将opt_prof1.del导入到表SYSTOOLS.OPT_PROFILE中，这样就完成了这个优化概要的注册，所使用的命令如下所示：

import from opt_prof1.del of del modified by lobsinfile

     insert into SYSTOOLS.OPT_PROFILE;

注册概要文件后，使用下面的flush命令刷新缓存中的优化概要。

flush optimization profile cache all;

（4）使用优化概要

最后，使用set current optimization profile命令设置当前要使用的优化概要。优化概要由SYSTOOLS.OPT_PROFILE中schema和name组合主键标识，示例中为DB2ADMIN.OPT_PROF1，于是接下来的查询就能用到这个优化概要：

set current optimization profile = 'DB2ADMIN.OPT_PROF1';

set current schema db2admin;

set current explain mode explain;

SELECT day, prod_name, sum(dollars)

FROM product prd , period per, sales s

WHERE prd.classkey = s.classkey

  and per.perkey = s.perkey

  and prd.prodkey = s.prodkey

  and prod_name like 'Darjeeling%'

  and day in ('SA', 'SU')

  and year = 2002

group by day, prod_name;

set current explain mode no;

在db2exfmt打印的访问计划中，如下所示，其中有一段关于优化概要的信息，示例中这个SQL语句使用的优化概要为DB2ADMIN.OPT_PROF1，而语句优化概要名字为Q1，与opt_prof1.xml中STMTPROFILE的ID一致。

Profile Information:

--------------------

OPT_PROF: (Optimization Profile Name)

  DB2ADMIN.OPT_PROF1

STMTPROF: (Statement Profile Name)

  Q1

Original Statement:

------------------

SELECT ... ...