转：关于DB2的hint使用－－利用优化概要文件进行 SQL 调优

优化概要文件概述

在现代数据库系统中，SQL 优化是非常重要的工作，其结果直接决定了数据库的性能。当今主流的数据库产品，其优化器都采用了基于成本分析的方法来对 SQL 语句进行优化，希望得到效率最高的执行计划。所谓基于成本分析的优化，简而言之就是优化器根据数据的统计信息，来计算出各种可选执行计划的成本，从而选取成本最低的，即最优的执行计划。然而由于各种条件所限，有时数据库中并不具有全部且准确的统计信息，或者在一些特定的情况下，基于统计分析的优化并不能得到真正的最优的执行计划，那么用户就可以应用本篇文章中所要讲到的方法，利用 SQL 优化概要文件，来让优化器生成指定规则的执行计划，从而绕开优化器自带的基于成本分析的优化。
DB2 的这种调优方式，和 Oracle 数据库的 Hint 机制相对应，都是数据库系统提供给用户的可以直接指定执行计划的方式。但相对于 Oracle 的 Hint 机制来说，DB2 的优化概要文件配置更灵活，它即可以针对某一具体的数据表来指定其访问方式，也可以针对一条特定的 SQL 语句，来指定其最终的执行计划，而且并不像 Oracle Hint 机制一样需要改动 SQL 语句本身。
具体来讲，就是用户按着 DB2 提供的指定格式，来编写一个 XML 文件，针对某张特定的数据表或者 SQL 语句，来指定其具体的执行计划细节。清单 1 里是一个简单的示例，这个示例会被应用到第三章的案例分析中。该示例针对给定的 SQL 语句，指定了一条具体的优化规则，指定表 t1 和 t2 之间采用 Hash Join 的连接方式，同时利用 t1 作为小表来建立 Hash Table，视 t2 为大表来进行匹配操作，用户把这个 XML 文件导入到指定的工具表中，并通过设置环境变量使其生效，那么之后，优化器对于这样的 SQL 语句，就会按着这个优化概要文件中所指定的规则，来生成其执行计划，细节将会在第三章中详述。
清单 1. 优化概要文件示例

60]]>

回页首

优化规则分类

用户可以通过优化概要文件来对特定表或特定 SQL 语句指定优化规则，本章将对可以在优化概要文件中指定的各种优化规则分类详述。

常规优化规则

用户可以在优化概要文件中指定的常规优化规则有以下 5 种：REOPT，当 SQL 语句中含有主变量或者参数标记时（Host Variable、Parameter Marker），该选项可以指定是否让编译器在每次进行数据访问前根据已知的主变量和参数标记的值进行重新优化。可用的选项有 ONCE、ALWAYS 或 NONE，ALWAYS 代表着将每次进行数据访问前都根据已知的主变量和参数标记的值进行重新优化，而 ONCE 意味着只在第一次访问前，根据已知的主变量或者参数标值记进行一次重新优化。QRYOPT，优化级别选项。它用来指定 SQL 优化所选择的优化级别，可选的优化级别有 1、2、3、5、7、9，DB2 编译器缺省使用的优化级别是 5 级。DEGREE，并行度选项。它用来指定生成的访问计划的并行度，若并行度为 -1，则表明让优化器根据系统情况去自行选择并行度。RTS （real-time statistics collection），实时统计信息收集选项。该选项来启用或者停止进行实时统计信息收集。DPFXMLMOVEMENT，该选项用来指定在 DPF 环境下 XML 数据的移动规则，可选的规则有 REFERENCE 或 COMBINATION。REFERENCE 代表着在 DPF 环境下，当 XML 数据需要在各分区间移动时，真正的 XML 数据并不移动，只是通过 TQ 将 XML 数据的引用进行传递；COMBINATION 代表着将真正的 XML 数据进行移动。
清单 2. 常对优化规则示例

查询重写规则

用户可以在优化概要文件中指定的查询重写规则有以下 4 种。INLIST2JOIN，用于指定是否将 SQL 语句中的值列表（INLIST）条件改写为连接条件（JOIN）。NOTEX2AJ，用于指定是否把谓词 NOT EXISTS 改写成 ANTI JOIN。NOTIN2AJ，用来指定是否把谓词 NOT IN 改写成 ANTI JOIN。SUBQ2JOIN，用于指定是否将 SQL 语句中的子查询（Sub-Query）改写为嵌套循环连接（JOIN）。
清单 3. INLIST2JOIN 查询重写规则示例 1

Query:  SELECT * FROM T1 WHERE T1.C2 IN (1,3)  Enable INLIST to JOIN Guideline:    Rewritten query:  SELECT Q3.C1 AS "C1", Q3.C2 AS "C2“ FROM (SELECT $INTERNAL_FUNC$()  FROM (VALUES 1, 2) AS Q1) AS Q2, DEACONU2.T1 AS Q3 WHERE (Q3.C2 = Q2.$C0)

清单 4. INLIST2JOIN 查询重写规则示例 2

Query:  SELECT * FROM T1 WHERE T1.C2 IN (1,3)  Disable INLIST to JOIN Guideline:    Rewritten query:  SELECT Q1.C1 AS "C1", Q1.C2 AS "C2“  FROM DEACONU2.T1 AS Q1 WHERE Q1.C2 IN (1, 3)

访问方式规则

用户可以在优化概要文件中指定的访问方式规则主要有以下 5 种：TBSCAN，该选项指定采用表扫描的方式对特定的数据进行访问。IXSCAN，该选项指定通过索引对特定的数据进行访问。LPREFETCH ，该选项指定采用 List Prefetch 的方式来对特定的数据进行访问。IXAND，该选项指定采用 Index And 的方式来对特定的数据进行访问。IXOR，该选项指定采用 Index Or 的方式来对特定的数据进行访问。
清单 5. List Prefetch 访问方式规则示例

Query:  SELECT C1 FROM T1  Guideline:        Merge Sort Join Guideline:        Hash Join Guideline:

回页首

优化概要文件应用实例

准备工作

1. 建立工具表
在应用优化概要文件前，需要先建立一个特定的工具表 SYSTOOLS.OPT_PROFILE，用户编写的优化概要文件首先将被导入到该表中。该工具表中的三列分别存储着优化概要文件所属的 SCHEMA，名称以及优化概要文件本身。
清单 7. 工具表生成脚本

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. 导入优化概要文件
清单 8 为一数据源示例文件，它的三项数据分别对应着工具表中的三列，通过清单 9 中所示的 Import命令可以将该数据源文件导入到工具表里，同时 XML格式的优化概要文件也将被导入到工具表中 BLOB数据类型的 PROFILE列中。
清单 8. 数据源文件 OptProfile

XQPENG, HSJOIN, HSJOIN.XML

清单 9. Import 导入命令

IMPORT FROM OptProfile OF DEL MODIFIED BY LOBSINFILE  INSERT INTO SYSTOOLS.OPT_PROFILE

3. 设置数据库配置变量
清单 10 用来设置数据库配置变量 DB2_OPTPROFILE。注意在运行完变量设置命令后，需要重新启动数据库来使其生效。
清单 10. 设置数据库配置变量

db2set DB2_OPTPROFILE=YES

4. 设置数据库环境变量
清单 11 用来设置环境变量，来启用刚导入的优化概要文件。
清单 11. 设置数据库环境参数

SET CURRENT OPTIMIZATION PROFILE=XQPENG.HSJOIN

5. 当我们不再需要驻留在缓存中的优化概要文件时，可以使用清单 12 中的 FLUSH命令来清除它们。
清单 12 清除缓存中的优化概要文件

FLUSH OPTIMIZATION PROFILE CACHE XQPENG.HSJOIN  FLUSH OPTIMIZATION PROFILE CACHE ALL

应用优化概要文件

对于清单 13 中的查询语句，基于统计信息的估算，优化器得出表 t2 中的符合条件的数据行数多于表 t1 中的符合条件的数据行数，故会将 t2 表作为 Hash Join 中的小表来首先生成 Hash 值列表，然后在遍历 t1 表进行匹配。然而根据用户的实际经验，这是一个错误的估计，因此我们通过一个特定的优化概要文件，来指示让优化器生成我们希望的访问计划，即由 t1 作为 Hash Join 中的小表来生成 Hash 值列表。清单 14 和 15 给出了优化器在给定优化概要文件之前和之后所生成的访问计划。
清单 13. SQL 查询语句

select t1.c3,t2.c3 from t2 ,t1 where t1.c1=t2.c1  and t1.c1<100 and t2.c2 >60

清单 14. 没有指定优化概要文件时的访问计划

Access Plan:  -----------   Total Cost:  804.126   Query Degree: 1   Rows   RETURN   ( 1)   Cost   I/O   |   154.429   HSJOIN   ( 2)   804.126   52   /-----+------   200.185 77.2143   TBSCAN TBSCAN   ( 3) ( 4)   385.013 416.576   26 26   | |   2000 2000  TABLE: XQPENG TABLE: XQPENG                                  T2 T1   Q2 Q1

清单 15. 指定优化概要文件后的访问计划

Access Plan:  -----------   Total Cost:  804.126   Query Degree: 1   Rows   RETURN   ( 1)   Cost   I/O   |   154.429   HSJOIN   ( 2)   804.126   52   /-----+------   200.185 77.2143   TBSCAN TBSCAN   ( 3) ( 4)  385.013 416.576   26 26   | |   2000 2000  TABLE: XQPENG TABLE: XQPENG                                  T1 T2   Q1 Q2

回页首

结束语

如果在应用了所有的常用调优技术之后，例如建索引，收集统计信息等，SQL 优化器给出的执行计划仍然具有明显的缺陷，这时用户就可以考虑采用优化概要文件的方式，来指示优化器生成指定的执行计划。但是建议用户在使用这个特性时要谨慎，对于优化概要文件，优化器需要额外的查询匹配开销，此外，优化概要文件里的特定内容也可能会随着时间和数据库状态的变化而改变。所以，需要定期地检查它们的实现，以便从它们的使用中获取最大限度的收益。


原文见：
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1008pengxq/