全面解析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.PROF1

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（而不是静态的），所以任何基于成本的优化器通常都必须对数据、数据选择性和数据基数做出假设，但是很多情况下，数据的分布呈难以预测的不均匀性，数据域值本身的特性以及表和视图的相互依赖关系会使优化器很易出错。

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

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

◆均匀分布

◆域值

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

均匀分布

考虑以下数据，

C1 1 2 3 3 3 3 7 7 9 10

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 1 2 3 3 3 3 7 7 99 100

那么：

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

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

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

C1 1 2 3 4 5 6 7 8 9 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。

所以，当数据均匀分布时，无论值和范围如何，真实的结果与估计的结果都更加一致。

即使拥有频率值和分位数值之类的分布统计信息（这些信息可以大大减少等于和范围谓词的估计错误），也仍然会出现估计错误无法接受的情况。

域值

a) 现在看看包含以下数据的两个表的连接 T1.C1 = T2.C1，其中一组数据包含另一组数据：

T1.C1 1 2 3 4 5 6 7 8 9 10 T2.C1 1 2 3 4 5 6 7 8 9 10

谓词的选择性定义如下：

Selectivity = 1 / ( max ( C1 colcard , C2 colcard ) ) = 0.1

基数为 10 * 10 * 0.1 = 10。

b) 如果表连接 T1.C1 = T2.C1 中的数据在两组数据相交处稍微有所不同，一个表中的数据没有包含另一个表中的数据：

T1.C1 1 2 3 4 5 6 7 8 9 10 T2.C1 1 2 2 2 2 5 12 13 14 15

在这种情况下，T1.C1 的值，例如 7，不能与 T2.C1 连接，而 T2.C1 的值，例如 12，也不能与 T1.C1 连接，但是估计算法并不知道这一点，因而会做出不准确的假设，认为 T1 中的一个值很可能与 T2 中的任意值连接，反之亦然。

基数仍然是 10 * 10 * 0.1 = 10。

所以成本是一样的，但是 a) 的实际行输出结果为 10，b) 的实际行输出结果为 6。

结果 1 2 2 2 2 5

显然，这里存在不一致性，而且，对于更复杂的连接，这种错误估计的问题很可能变得更糟糕。而 V8 FP9 以上版本提供的 DB2 统计视图特性，正是为弥补这一类由于数据分布和值导致的不一致性而设计的。

为了理解统计视图的作用，我们来考虑一个更实际一点的连接场景：

T1.C1 T1.C2 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H 9 I 10 J T2.C1 1 2 2 2 2 5 5 13 14 15

在这种情况下，连接谓词 T1.C1=T2.C1 和 T1.C2='A'（或 C2 的任何值）返回的基数估计将为 1。但是，如果本地谓词为 T1.C2='B' 或 T1.C2='E'，那么这个估计就错得太厉害了。请看上面两个表在 T1.C1 = T2.C1 上的连接所产生的如下结果。

T1.C1 T1.C2 T2.C1 1 A 1 2 B 2 2 B 2 2 B 2 2 B 2 5 E 5 5 E 5

为了弥补这种估计错误，可以创建和准备一个统计视图，并像下面这样加以利用：

Create view SCHEMA.V1 as select * from T1, T2 where T1.C1 = T2.C1 Alter view SCHEMA.V1 enable query optimization Runstats on table SCHEMA.V1 with distribution

对于统计视图，ENABLE QUERY OPTIMIZATION 子句将导致该视图以及与之相关联的统计信息被用于改进查询优化。这里只需收集包含分布特征的数据 runstats 信息。runstats 信息是统计视图部署的关键，必须提供比基本表更完整的信息。有时候，列组或类似的统计选项会很有用。

现在，统计视图将包含在整个结果集上收集到的关于连接之后的数据分布的统计信息，无论是在 non-DPF 还是 DPF 环境中，这个信息都是完整的，没有推断成分。有时候，runstats 可能要花更多的时间，这可能是由于视图本身的规划没做好。在运行 runstats 之后，以下附加信息会成为已知的信息：

结果的列的 COLCARD

结果的基数

值以及值的计数

然后，这些信息被包括进来，用于帮助优化器在为那些符合条件的查询（这些查询不需要直接引用视图）的选择性估计和基数估计计算成本时做决定。 这将导致更精确的成本计算和更优的访问计划。

下面使用以上讨论的相同的示例数据，阐释在使用和不使用查询的统计视图的情况下基数估计的差别。

select * from T1,T2 where T1.C2='B' and T1.C1=T2.C1

1) 不使用统计视图 - 在这种情况下，对于数据的基数的估计明显不准确。根据估计，访问计划中的散列连接将返回 1 行，而实际上是 4 行。

清单2. 不使用统计视图情况下的访问计划

Rows RETURN ( 1) Cost I/O | 1 HSJOIN ( 2) 15.1653 2/-----+----- 10 1 TBSCANTBSCAN ( 3)( 4) 7.58162 7.583011 1| | 10 10 TABLE:TABLE: KCHEN.T2KCHEN.T1

2) 使用统计视图 - 在这种情况下，估计到的基数为 4 行，这相对于第 1 种情况有很大的提高，并且这次该估计是完全准确的。

注意，解释输出将包含以下诊断信息，以表明正在使用统计视图。在这方面，db2exfmt 工具非常有助于确定是否正在使用统计视图。

Diagnostic Details: EXP0147W. The following statistical statistical view may have been used by the optimizer to estimate cardinalities: 'KCHEN '.'V1'.

清单 3. 使用统计视图情况下的访问计划

Rows RETURN ( 1) Cost I/O | 4 HSJOIN ( 2) 15.1653 2 /-----+----- 10 1 TBSCANTBSCAN ( 3)( 4) 7.58162 7.58301 1 1 | | 10 10 TABLE: TABLE: KCHEN.T2 KCHEN.T1

通过利用包含关于连接的附加信息（包括通过在 runstats 期间执行查询而得到的 runstats 信息，但是没有持久地物化详细的实际结果集）的统计视图，基数估计得到了改善。针对一个或多个查询的统计视图的使用是透明的，不必直接引用它。统计视图有点类似于物化查询表（Materialized Query Table，MQT），不同的是统计视图不需要物化。实际上，DB2 中对统计视图的支持与对 MQT 的支持具有类似的局限性。当前，统计视图不支持 SUM、MAX 之类的聚合函数，也不支持 distinct 操作和 UNION、EXCEPT 或 INTERSECT 之类的集合操作。

在 DB2 V8 FP9 中，需要设置注册表变量 DB2_STATVIEW，并且统计视图中只能有 2 个表引用。此外，还需要手动收集统计信息，因为 runstats 不能工作于统计视图。在 DB2 V9，所有这些限制都已经被去掉了。

通常，在涉及事实表和很多维表的星型连接场景中，统计视图中将包括维表的列（本地谓词列尤其重要），而事实表中的列则不需要包括进来。这是因为当事实表与维表连接时，事实表中列的数据分布不会改变，因此优化器只需根据事实表的列的分布统计，就可以得到准确的选择估计。一个例外是在 V8 中，由于 MQT 路由限制，任何查询谓词引用的事实表列都必须包括在统计视图中。

给定一个 3 表连接，可以创建多个统计视图，即 T1 和 T2、T1 和 T3 以及 T1、T2、T3 上的统计视图，这些视图将包含生成最佳访问计划所需的所有统计信息，实际上前 2 个视图就足够了。

例子1:

Select * from T1, T2, T3 where T1.C1=T2.C1 and T1.C2=T3.C2 and T2.C1=2 and T3.C2 = 'B'Create view SCHEMA.V11 as select T2.* from T1, T2 where T1.C1 = T2.C1 Create view SCHEMA.V12 as select T3.* from T1, T3 where T1.C2 = T3.C2

其中，T1 是与维表 T2 和 T3 有 FK-PK 关系的事实表。

为改善估计，创建 V11 和 V12 这两个统计视图。

结束语

当查询性能中出现例外情况时，优化指南和统计视图特性对于弥补访问计划估计的不准确性非常有用。如果应用了所有标准的调优技术之后，仍然得不到期望的结果，那么可以考虑这两个特性。但是使用这两个特性时要谨慎，因为对于优化指南，需要额外的查询匹配开销，而对于统计视图，又有编译方面的开销。此外，优化指南和统计视图的特定内容也可能会随着时间和数据库状态的改变而改变，例如，优化指南可能会随着数据量而变化，统计视图中的统计信息也有可能过时。所以，需要定期地检查它们的实现，以便从它们的使用当中最大限度地获益。