全面解析IBM DB2 9中的查询优化新特性

大多数主流关系数据库管理系统，例如 IBM DB2、Oracle 和 Microsoft® SQL Server，都依赖于基于成本的优化器设计，来在数据库服务器环境中的一组经常变化的条件（包括变化的查询特征和变化的数据）的影响下，从很多可能的计划中选择一个最佳 SQL 执行计划。具体而言，DB2 SQL 优化决定受系统配置（I/O 存储特征、CPU 并行性和速度、缓冲池和排序堆设置、通信带宽）、模式（索引、约束）、DB2 注册表变量、DB2 优化级别和统计信息（关于表、列和索引的统计信息）的影响。这么多复杂的因素，再加上数据本身的动态性，使得最佳计划的评估对于任何数据库系统而言通常都是一个复杂的过程。

考虑到生成最佳 SQL 执行计划是一项不简单的任务，DB2 对其已臻成熟的成本模型继续进行了改进，并加入了新的功能，以提供更好的信息来帮助成本模型做出决定。统计视图是一种强大的、新型的统计，它可以表示复杂谓词或表之间的关系。REOPT 绑定选项将查询优化推迟到 OPEN 时有可用输入变量的时候。然后，优化器可以将输入变量的值与编目统计进行比较，并为谓词计算出一个更好的选择估计。统计视图和 REOPT 都使优化器可以计算出更精确的基数估计，而后选择一个最佳查询执行计划。对于优化器不能选择最佳查询执行计划的例外情况，DB2 已经增加了诸如 SELECTIVITY 子句和优化指南之类的特性。

在本文的讨论中，我们来看看优化指南和统计视图这两个最新的增强。通过本文，您可以了解这些增强的作用是什么，以及在某些情况下，在非数据分区（non-DPF）和数据分区（DPF）环境中，如何在应用程序内充分利用它们。

DB2优化概要文件和嵌入式指南

Version 8 FP9, DB2 for Linux, UNIX, and Windows 中包括优化概要文件功能，该功能将一个指南传递给优化器，用于指导优化器为 SQL 查询生成所需的执行计划，以覆盖默认的成本模型。

很多人都曾在应用程序中碰到这样的情况：大多数查询工作负载都经过了适当的调优，并取得了较好的性能，但是，随着用户期望的增长，加上系统的复杂性和多样性，仍然有少数 SQL 语句无法通过调优取得预期的性能。虽然人们已经尽了最大的努力力图通过改变数据库（例如使用索引建议器或者其他方法来改进索引、更新统计信息、改善数据群集及更改参数）来调优 SQL 语句，但是问题仍然存在。有时候，我们希望更直接地影响优化器，同时尽量避免更改应用程序。

这时候可以考虑使用优化指南。然而需要注意的是，先进的优化器在生成一个特定的访问计划时，必然有其原因，所以在应用指南之前，务必理解是什么原因导致查询的性能低下。优化指南使用起来并不难，但更具有挑战性的任务是根据给定的数据库环境判断 SQL 语句的问题出在哪里，并选择适当的指南加以应用。

优化概要文件的工作原理

首先选择一组您想要影响其访问计划的查询。然后，将这些查询和一些适当的指南放到一个 XML 优化概要文件中。为了通过验证，这个优化概要文件必须遵从优化指南 XML 模式，并由一些区段组成，如清单 1 所示。

清单1.XML 优化概要文件

 XML 优化概要文件以 OPTPROFILE 区段开始，该区段表明版本属性。这个全局区段将规则全局地应用到所有 SQL 语句上。例如，可以指定使用哪个 REOPT 选项，使用哪个 MQT 表，或者使用什么样的查询优化。statement profile 区段则表明将哪些特定的规则应用于 STMTKEY 元素中的 SQL 语句上。

如果有问题的 SQL 查询不容易访问到，那么借助 XML 优化概要文件可以带来很大的方便。例如，SQL 查询可能处在一个应用程序中，而这个应用程序是不能更改的。在这种情况下，可以使用概要文件，在查询文本成功匹配之后，通过触发与查询相关联的指南来影响查询行为。该环境中的所有 SQL 语句将尝试从活动的优化概要文件中查找匹配项，而这种匹配是高效率、低开销的。

 如何启用优化概要文 

一个数据库中可以有很多个优化概要文件，但是在实际情况中，更灵活的做法是创建一个主优化概要文件，将所有规则（statement profile）组织在一起，然后只需激活此概要文件，根据应用程序环境的不同，可以选择以下几种方法之一来激活概要文件。另外还需要将 DB2_OPTPROFILE 注册表变量设置为 YES。

1.在CLP环境中：

使用 “SET CURRENT OPTIMIZATION PROFILE=KCHEN.PROF1” 语句在会话级将概要文件与所有 SQL 语句关联，直到连接重置或者概要文件重置。这条语句还可以嵌入到应用程序中。

2.对于 CLI 应用程序或使用旧的 JDBC 驱动程序的JDBC应用程序 

在db2cli.ini配置文件中设置 CURRENTOPTIMIZATIONPROFILE 关键字来关联概要文件。对于 SAMPLE 数据库，这个关键字是在 data source 区段中设置的。

[SAMPLE]

CURRENTOPTIMIZATIONPROFILE=KCHEN.PROF1

 经过这样设置后，应用程序执行中的 SQL 将尝试与 KCHEN.PROF1 中的 SQL 语句进行匹配，以查找指定的规则，这些规则将覆盖执行环境中常规的优化。

3.对于使用JCC Universal Driver的JDBC应用程序：

采用 JCC Universal Driver 的 JDBC 应用程序并不使用 DB2 CLI 层。虽然可以将一个系统包和绑定文件与动态 SQL 执行相关联，但最好的做法是将 “SET CURRENT OPTIMIZATION PROFILE” 语句嵌入在 Java™ 应用程序中，在会话级关联概要文件。

4.对于 SQL PL 过程 

在创建 SQL PL 过程之前，使用 SET_ROUTINE_OPTS 过程调用将概要文件的名称与 DB2 V8 FP13+ 或 DB2 V9 FP1+ 中特定的 SQL PL 相关联。

CALL SYSPROC.SET_ROUTINE_OPTS('OPTPROFILE KCHEN.PROF1')

SQL PL 过程包含的 SQL 语句具有一些执行属性，例如隔离级别或优化级别，这些属性只能通过 DB2_SQLROUTINE_PREOPTS 注册表变量来覆盖。也可以用 SYSPROC.SET_ROUTINE_OPTS 过程覆盖该选项。要激活一个概要文件，可以使用该存储过程来关联指南。

 5.对于 C/C++ 应用程序中的嵌入式SQL：

对于嵌入式 C/C++ 应用程序，使用 OPTPROFILE 绑定选项。 嵌入式 SQC 程序需要使用 PREP 命令来编译，该命令将创建绑定文件。这个绑定文件需要通过 OPTPROFILE 选项绑定到数据库，例如：

bind prog1.bnd OPTPROFILE KCHEN.PROF

6.对于含嵌入式静态 SQL 语句的 SQLJ 应用程序：

在定制阶段使用 BINDOPTIONS 参数关联概要文件。这个静态 SQLJ 程序 prog1 被按如下所示进行翻译和编译：

sqlj prog1.sqlj

db2sqljcustomize -url jdbc:db2://SERVER:PORT/SAMPLE -user USER -password PASSWORD

-bindoptions "OPTPROFILE KCHEN.PROF1" -storebindoptions prog1_SJProfile0

所有使用旧的 JDBC 驱动程序的 JDBC 程序，都将使用 db2cli.ini 中的设置。使用 Universal JDBC 驱动程序的 JDBC 程序属于上述的第 3 类情况。需要注意的是，由于 SQLJ 为 SELECT SQL 语句生成一个隐式的 “DECLARE CURSOR” 子句，因此，为了使指南得到应用，优化概要文件除了包括 SELECT 语句外，还需要包括 “DECLARE CURSOR” 子句。

当应用程序执行时，将 SQL 与活动的概要文件中的指南相比较。如果存在一个匹配的 STMTKEY ，指南就会开始起作用；反之，假如指南被认为是不适用的或无效的，那么就会返回一个 rc = 13 的 SQL0437W。DB2 Explain 工具对于帮助确定指南是否被选择非常有用。Explain 的输出会指明优化概要文件的名称和有效的指南。概要文件中的指南通常覆盖用于应用程序设置的常规优化，从而使概要文件能够更好地控制计划评估。

优化指南的例子

优化概要文件中的任何指南都必须遵从 DB2 提供的 XML 模式。如果没有正确地指定指南，那么指南将无效，并且在大多数情况下，将返回 rc = 13 的 SQL0437W。优化概要文件存储在一个名为 SYSTOOLS.OPT_PROFILE 表中。如果从这个表中更新或删除一个指南，那么需要通过发出 FLUSH OPTIMIZATION PROFILE CACHE 语句更新缓存，使之可以被使用。需要注意的是，SQL 语句测试匹配是大小写敏感的，但在尝试匹配之前，DB2 将去除冗余空格和控制字符。

下面的例子演示了优化概要文件在 3 类情况下的使用，即常规优化、查询重写和计划优化。

例子1: 总是使用索引 T1X （计划优化）

假设在表 T1 的 (c2, c1) 列上有一个索引 T1X。根据优化器的成本计算，对于以下查询，会导致一个表扫描。下面的代码展示了如何强制使用一个索引。

例子2: 总是使用 REOPT（常规优化）

可以使用 REOPT 指南，将查询优化推迟到运行时输入变量已知的时候。可能的选项有 ONCE、ALWAYS 或 NONE。

 例子3:只使用DB2 V9 中的Optimization Level 0（常规优化）

通常，对于一个应用程序而言，优化级别是固定的，但是如果要使一条特定的 SQL 语句在一个不同的优化级别上执行，那么可以创建以下优化指南：

 例子4:只使用 DB2 V9 中的Runtime degree ANY（常规优化）

可以有很多方法来修改内部分区的查询的运行时等级。下面的代码展示了优化指南如何为查询指定运行时等级以及如何影响查询的执行。

例子5: INLIST 改为嵌套循环连接（查询重写）

将值列表（inlist）改为使用 GENROW 函数非常有效，可以提高查询的性能。在这个例子中，值列表被放在内存中的一个表中。

P.P_SIZE, P.P_TYPE, S.S_NATION

FROM KCHEN.PARTS P, KCHEN.SUPPLIERS S, KCHEN.PARTSUPP PS

WHERE P_PARTKEY = PS.PS_PARTKEY AND

S.S_SUPPKEY = PS.PS_SUPPKEY AND

P.P_TYPE IN ('BRASS', 'BRONZE') AND

P.P_SIZE IN (31, 31, 33, 34) AND

S.S_NATION = 'PERU']]>

例子6: 子查询改为连接（查询重写）

在这个例子中，在查询重写期间，通过使用带 ENABLE 属性的 SUBQ2JOIN，将一个子查询转换成一个连接，以便更好地对其进行优化。

FROM KCHEN.PARTSUPP PS, KCHEN.LINEITEM

WHERE PS.PS_PARTKEY = L_PARTKEY AND

PS.PS_PARTKEY = ANY (

SELECT P_PARTKEY FROM KCHEN.PARTS

WHERE P_BRAND <> 'Brand#45' AND

P_NAME = 'peach snow puff bisque misty' AND

P_TYPE <> 'TIN')

GROUP BY PS_PARTKEY]]>

  例子7: 影响连接顺序 3、4、1、2 （计划优化）

通常，查询的连接顺序很大程度上决定了查询的执行性能，因为越早地过滤行，效率越高。可以使用以下指南来影响连接顺序。注意，当出现多个表引用时，使用 TABLEID 属性，而不是 TABID 属性。

where t71.c1 = t72.c1 and

t72.c2 = t74.c2 and

t74.c1 = t73.c1 and

t73.c2 = t71.c2 and

t71.c3 = t74.c3 and

t72.c3 = t73.c3]]>

例子8: 客户使用情况（计划优化）

在批处理运行过程中，当刷新一个 MQT 时，客户会遇到性能问题。当为 MQT 定义中涉及的表 tab2 填充数据时，就会触发对 MQT 的刷新。下面的例子代码可以演示这个问题。

 create table tab1 (i int, b char(30))

create table tab2 (i int, b char(150))

create table mqt1 (cnt,val) as

(select count(*), tab2.b from tab2, tab1 where tab1.b=tab2.b group by tab2.b)

data initially deferred refresh immediate

create index i11 on tab1 (i asc, b asc 

create index i12 on tab1 (b asc, i asc)

create index i21 on tab2 (i asc, b asc 

create index i22 on tab2 (b asc, i asc 

insert into tab2 values(14,substr(char(current timestamp),1,5) 

在这个场景中，经过分析，可以确定使用索引 I11 来访问表 TAB1 是最优的，但是优化器的默认行为不会这么做，即使在调优之后也仍然不会这样做。但是，可以通过创建下面的指南来影响优化器，使之考虑 I11 索引，从而将 MQT mqt1 的刷新速度提高两倍以上。

统计视 

基本上，关系数据库中的数据会因事务和批量更新而发生变化 —— 即使是数据集市或数据仓库中的内容也会随着时间而变化。SQL 工作负载常常是动态的 SQL（而不是静态的），所以任何基于成本的优化器通常都必须对数据、数据选择性和数据基数做出假设，但是很多情况下，数据的分布呈难以预测的不均匀性，数据域值本身的特性以及表和视图的相互依赖关系会使优化器很易出错。

由于查询是动态的，在编译时并不知道其选择标准，因此，即使有了关于数据的完整的分布统计，仍然可能生成错误的计划。如果优化器能预知查询结果（或部分查询结果），那么该信息对于帮助确定更精确的访问计划将非常有用。

基本上，可以有以下两点假设：

◆均匀分布

◆域值

为了理解统计视图，我们首先看看以上两点假设，通常情况下这两点假设可能是错误的。因此，在进行查询计划优化时，就需要使用统计视图。

均匀分 

考虑以下数据 

runstats（无分布）将提供关于 C1 的以下信息 

CARD = 10,

COLCARD = 6,

LOW2KEY = 2,

HIGH2KEY = 9

那么：

◆C1=3 的行的数量将被估计为 10/6 = 1.67。

◆C1=4 和 C1=8 之间的值域被估计为 ((8-4)/(9-2)) * 10 = 5.71。

但是，如果将数据变化一下，以反映数据不均匀、大跨度的分布，如下所示 

那么：

 C1=3 的行的数量被估计为 10/6 = 1.67。

C1=4 与 C1=8 之间的值域被估计为 ((8-4)/(99-2)) * 10 = 0.41 

如果数据是完全均匀分布的，如下所示 

在本文的讨论中，我们来看看优化指南和统计视图这两个最新的增强。

通过本文，您可以了解这些增强的作用是什么，以及在某些情况下，在

非数据分区（non-DPF）和数据分区（DPF）环境中，如何在应用程序

内充分利用它们。

10

那么：

C1=3 的行的数量将被估计为 10/10 = 1。

C1=4 与 C1=8 之间的值域被估计为 ((8-4)/(9-2)) * 10 = 5.71。

C1=3 与 C1=7 之间的值域被估计为 ((7-3)/(9-2)) * 10 = 5.71。