编写并调优查询语句以优化性能最佳实践-数据库

捐助

编写并调优查询语句以优化性能最佳实践

次浏览

2018-8-21

编辑推荐:

本文来自于ibm，描述了在 DB2 数据库性能方面最小化 SQL 语句的影响的最佳实践。

介绍

查询性能不能只考虑某一次的问题，而应该贯穿于应用程序开发的整个生命周期，在设计、开发、生产各个阶段中都要考虑它。

SQL 是一个非常灵活的语言，也就是说有很多途径同样可以获得正确的结果。这种灵活性也意味着利用 DB2 优化器具有优势，一些查询会优于其他的查询。

在查询运行的过程中，DB2 优化器会为每个 SQL 语句选择一个查询计划。优化器模拟不同的访问计划的运行成本，并选择其中一个成本最低的访问计划。如果一个查询包括很多复杂的搜索条件，DB2 优化器在某些情况下可以重写谓词，不过在某些一些情况下却不能。

对于一些比较复杂的查询，一个 SQL 语句的准备或编译的时间可能会比较长，尤其是 BI 应用程序中使用的 SQL 语句。你可以通过调整设计和你的数据库配置来帮助缩短语句编辑时间。这包括选择正确的优化级别并正确设置其他注册变量。

优化器也需要精确输入以获得精确的查询计划。这意味着你需要收集精确的统计信息，并潜在的使用高级统计功能，比如统计视图和列组统计信息。

你也可以使用 DB2 工具（尤其是 DB2 explain 工具）来调优查询。 DB2 编译器可以抓取动态或静态语句关于访问计划和环境的信息。利用抓取的信息来理解单个语句的运行，所以你可以调整它们以及你的数据库管理器配置来提高性能。

编写 SQL 语句

SQL 是很强大的语言，它允许你指定语法不同而语义相同的关系型描述。不过，一些语义相同的变化比起其他的更容易优化。虽然 DB2 优化器有很强的查询重写能力，但也并不总是可以把一个 SQL 语句重写成最优的形式。某些 SQL 结构也可能会限制优化器对访问计划考虑。下面的章节描述了需要避免的某些 SQL 结构，并对如何替换或避免它们提出了建议。

在搜索条件中避免复杂的描述

在搜索条件中避免复杂的描述，这样的描述阻止了优化器使用编目统计信息来评估一个精确的选择。这个描述也可能会限制选择可以应用这些谓词的访问计划。在优化的查询语句的重写阶段，优化器可以重写一批描述以允许优化器评估一个精确的选择；不过它不能处理所有可能性。

在描述中避免使用连接谓词

在描述中使用连接谓词把连接方法限制为了嵌套循环。另外，对基数的评估可能不准确。下面是一些连接描述的例子：

WHERE SALES.PRICE * SALES.DISCOUNT = TRANS.FINAL_PRICE
WHERE UPPER(CUST.LASTNAME) = TRANS.NAME

避免对本地谓词有过多的列描述

使用相反的描述来替代应用中的一个在本地谓词上有太多列的描述。考虑下面的例子：

XPRESSN(C) = 'constant'
INTEGER(TRANS_DATE)/100 = 200802

你可把它们语句重写为：

C = INVERSEXPRESSN('constant')
TRANS_DATE BETWEEN 20080201 AND 20080229

应用多列的描述将妨碍索引开始和结束键的使用，从而导致不准确的可选评估，并需要在查询运行时花费额外的时间来处理。这些描述同样会阻止对查询的重写优化，比如在列相等时的识别、使用常量来替换各列并且对只有一行返回的情况进行识别。在这之后的进一步的优化也可能会被阻止，因此会失去更多的优化机会。考虑下面的查询：

SELECT LASTNAME, CUST_ID, CUST_CODE FROM CUST
WHERE (CUST_ID * 100) + INT(CUST_CODE) = 123456 ORDER BY 1,2,3

你也可以用下面的语句：

SELECT LASTNAME, CUST_ID, CUST_CODE FROM CUST
WHERE CUST_ID = 1234 AND CUST_CODE = '56' ORDER BY 1,2,3

如果在 CUST_ID 上有一个唯一索引，重写的查询版本让查询优化器注意到最多一行记录将被返回。这避免了引入一个没有必要的排序操作。这也让 CUST_ID 和 CUST_CODE 列被 1234 和‘ 56 ’替代，从而避免了从数据页或索引页复制数据。最终，使得在 CUST_ID 上的谓词应用成一个索引开始或结束键。

当一个描述在谓词中时，它可能不是总这么明显。这种情况经常发生在涉及视图的查询中，尤其当视图列是通过描述定义的时候。比如，考虑下面视图定义和查询：

CREATE VIEW CUST_V AS
(SELECT LASTNAME, (CUST_ID * 100) + INT(CUST_CODE) AS CUST_KEY FROM CUST)
SELECT LASTNAME FROM CUST_V WHERE CUST_KEY = 123456

查询优化器把查询和视图定义合并了，产生下面的查询：

这是前面例子中有问题的谓词。你可以通过使用 explain 功能显示优化后的 SQL 语句来观察视图合并的结果。

如果相反的功能很难表达，考虑使用一个生成列。例如，如果你想找到一个满足 LASTNAME IN ('Woo', 'woo', 'WOO', 'WOo', and so on) 描述标准的姓氏，你可以创建一个生成列 UCASE(LASTNAME) = 'WOO' ，如下：

CREATE TABLE CUSTOMER
(LASTNAME VARCHAR(100),
U_LASTNAME VARCHAR(100) GENERATED ALWAYS AS (UCASE(LASTNAME)))
CREATE INDEX CUST_U_LASTNAME ON CUSTOMER (U_LASTNAME)

避免连接列上的数据类型不匹配

在某些情况下，数据类型不匹配将妨碍使用哈希连接。哈希连接在连接谓词上比其他连接方法要多一些约束。特别是连接列的数据类型必须完全相同。例如，如果一个连接列是 FLOAT 另外一个是 REAL，哈希连接将不支持。另外，如果连接列数据类型是 CHAR、GRAPHIC、DECIMAL 或 DECFLOAT 则长度必须一样。

在谓词中不要使用 no-op 描述来更改优化器评估

一个“ no-op ” coalesce() 谓词以“ COALESCE(X, X) = X ”会引入一个评估错误到使用它的所有查询的计划中。现在 DB2 查询编译器没有能力不去选则那个谓词并确定是否所有的行具体满足它。作为结果，这个谓词减少了对来自于部分查询计划的评估的行数。这个更小的行估计通常为后面的查询计划减少了行数和评估成本，而有时选择不同计划的结果是因为不同候选计划之间的相关评估条件改变了。

为什么这个什么都不做的谓词有时候却会提高查询性能？增加谓词“ no-op ” coalesce() 引入了一个错误掩盖了一些东西，要不然就是阻止了性能优化。

一些性能增强工具做了什么是一个强力测试：工具一再引入谓词到一个查询的不同位置，操作不同的列，通过引入一个错误来尝试找到一种情况，这个错误出现在哪里影响了一个更好的查询计划。这也是一个真正的查询语句开发人员在一个查询中手动。编写“ no-op ”谓词。通常，开发人员会得到一些对数据的了解来指导这个谓词的放置。

使用这个方法来提高查询性能是一个短期的解决方案，它并没有定位根本原因而且会有以下影响：

性能提升的潜力被掩盖了。

不能保证这个 workaround 会提供永久的性能提高，就像 DB2 查询编译器可能最终更好的处理这个谓词或者其他随机因素可能影响了它。

或许会有其他语句受到相同的原因影响而且对系统性能的影响通常会由你的系统承受。

避免不相等的连接谓词

连接谓词使用比较操作，除了相等其他都应该避免。因为不相等连接方法就会限制为嵌套循环。而且，优化器或许不能为连接谓词计算一个精确的可选评估。然而不等连接谓词不能永远规避。当它们是必须的时候，确保一个谓词索引存在于任何一个表中，因为连接谓词将应用嵌套内部连接。

不等连接谓词的一个简单例子是，为了精确反映维度数据在不同的时间点的状态，一个星型模式中的维度数据必须版本化。这常常作为一个‘慢慢改变的维度’来被参考。一类慢慢改变的维度包括每个维度行的有效的开始和结束日期。为了连接维度的主键，一个在事实表和维度表之间的连接需要检查和事实表相关的数据，包括维度的开始和结束日期。这常常作为一个‘第 6 类慢慢改变的维度’被参考。范围连接回到事实表通过一些实际事务日期以进一步限定维度版本，成本会很高。例如：

SELECT ... FROM
PRODUCT P, SALES F
WHERE
P.PROD_KEY = F.PROD_KEY AND F.SALE_DATE BETWEEN P.START_DATE AND P.END_DATE

在这个情况下，需要确保有一个索引在（F.PROD_KEY, F.SALE_DATE）列上

可以考虑创建一个统计视图来帮助优化器计算一个更好的可选评估。例如

CREATE STATISTICAL VIEW V_PROD_FACT AS
SELECT P.*
FROM PRODUCT P, SALES F
WHERE
P.PROD_KEY=F.PROD_KEY and
F.SALE_DATE BETWEEN P.START_DATE AND P.END_DATE
ALTER VIEW V_PROD_FACT ENABLE QUERY OPTIMIZATION
RUNSTATS ON TABLE DB2USER.V_PROD_FACT WITH DISTRIBUTION

指定星型模式连接，比如索引 ANDing 星型连接，并且如果在查询块中有任何不等连接谓词，集中连接不被考虑。（参见“如果你使用星型模式连接，确保你的查询和必须匹配在 12 页中的标准”）

避免出现多个 DISTINCT 关键字

避免使用在同一个 subselect 中运行多个 DISTINCT 集合的查询，它的运行成本非常高。考虑下面的例子：

SELECT SUM(DISTINCT REBATE), AVG (DISTINCT DISCOUNT) FROM DAILY_ SALES GROUP BY PROD_KEY;

为了判断 DISTINCT REBATE 值和 DISTINCT COUNT 值，来自 PROD_KEY 表的输入流需要进行两次排序。这个查询语句的查询计划就像这样 :

优化器重写了最初的查询语句，分成两个单独的集合，每个指定 DISTINCT 关键字，然后把多个集合用 UNION 关键字连接起来。内部重写的语句是：

SELECT Q8.MAXC0, (Q8.MAXC1 / Q8.MAXC2)
FROM
(SELECT MAX(Q7.C0) AS MAXC0, MAX(Q7.C1) AS MAXC1, MAX(Q7.C2) AS MAXC2
FROM
(SELECT SUM(DISTINCT Q2.REBATE) as C0 ,
cast(NULL as integer) AS C1, 0 AS C2, Q2.PROD_KEY
FROM
(SELECT Q1.PROD_KEY, Q1.REBATE
FROM DB2USER.DAILY_SALES AS Q1) AS Q2
GROUP BY Q2.PROD_KEY
UNION ALL
SELECT cast (NULL as integer) AS C0, SUM(DISTINCT Q5.DISCOUNT) AS C1,
COUNT(DISTINCT Q5.DISCOUNT) AS C2, Q5.PROD_KEY
FROM
(SELECT Q4.PROD_KEY, Q4.DISCOUNT
FROM DB2USER.DAILY_SALES AS Q4) AS Q5
GROUP BY Q5.PROD_KEY) AS Q7
GROUP BY Q7.PROD_KEY) AS Q8

如果你不能避免多个 DISTINCT 集合的话，就考虑使用带 ENHANCED_MUTIPLE_DISTINCT 选项的 DB2_EXTENDED_OPTIMIZATION 注册表变量。这个选项将使到多个 DISTICT 集合的输入流被读取一次然后被 UNION 的每一个分支重复使用。这个选项可以在数据库分区的处理器比率较低的时（例如，比率小于或等于 1）提高这类查询的性能。这个设置应该对没有对称的多处理器的 DPF（Database Partitioning Feature）环境（SMPs）很有用。这个优化扩展不能在所有环境中提高查询性能。应该进行测试来判断单个查询性能的提高。

避免多余的谓词

某些查询的语义需要外连接（不管左、右或全连接）。然而，如果查询语义不需要一个外连接并被用于处理不一致数据，那么最好是处理不一致数据的根本原因。例如，在一个数据集市中有一个星型模式，事实表可能包含事务数据，不过因为数据不一致的问题，造成与父维度行的一些维度不匹配。这是可能发生的，因为抽取、转换和装载（ETL）过程出于某些原因不与商业键不兼容。在这种情况下，事实表的行与维度左连接可以确保它们即使在没有父表的情况下得到返回。例如：

SELECT ... FROM DAILY_SALES F
LEFT OUTER JOIN CUSTOMER C ON F.CUST_KEY = C.CUST_KEY
LEFT OUTER JOIN STORE S ON F.STORE_KEY = S.STORE_KEY
WHERE
C.CUST_NAME = 'SMITH'

左外连接会阻止一些优化，也包括使用指定的星型模式连接访问方法。然而，在某些情况下左外连接可以自动被查询优化器重写成一个内部连接。在这个例子中，在 CUSTOMER 和 DAILY_SALES 之间的左外连接可以被转换成一个内部连接，因为 C.CUST_NAME= ’ SMITH ’谓词将排除这一列中所有 NULL 值的行，使得一个左外连接语义变得没有必要。所以由于外连接的出现导致那些优化损失可能并不会对所有查询产生负面影响。然而，注意到这些限制并避免外连接非常重要，除非它们是绝对必须的。

把 FETCH FIRST N ROWS ONLY 子句和 OPTIMIZE FOR N ROWS 子句放在一起使用

OPTIMIZE FOR N ROWS 子句表明对于优化器应用程序打算只获取 N 行，不过查询将返回完整的结果集。 FETCH FIRST N ROWS ONLY 子句显示查询只需返回 N 行。

DB2 数据服务器不会在为外部子查询指定了 FETCH FIRST N ROWS ONLY 而自动假设 OPTIMIZE FOR N ROWS 。尝试与 FETCH FIRST N ROWS ONLY 一起指定 OPTIMIZE FOR N ROWS 来鼓励查询计划直接从引用的表返回行，而不执行一个缓冲操作，比如插入一个临时表、排序或插入一个哈希连接的哈希表。

应用程序指定 OPTIMIZE FOR N ROWS 来鼓励查询计划避免缓冲操作，也不获取整个结果集，它们可能导致性能低下。这是因为快速返回前 N 行的查询计划可能不是对于获取整个结果集的查询计划。

如果你正在使用星型模式的连接，请确保你的查询满足需要的标准

优化器为星型模式考虑两个特殊的连接方法，叫做一个星型连接或集中连接，它们可以明显的提高性能。如果查询必须满足以下标准。

对每个查询块

最少 3 个不同的表被连接

所有连接谓词必须相等

没有子查询存在

在表与表之间或者查询块之间不存在相关性或依赖性

另外，对于索引 ANDing，必须没有不确定的函数，因为为方便 semi-joins 事实表的谓词必须通过索引来应用

事实表

是查询块中最大的表

少于 10000 行

被视为只有一个表

必须连接到至少两个维度表或叫做 snowflakes 的组。

维度表

不是事实表

可以单据连接到实施表或在 snowflakes 中

一个维度表或者一个 snowflake 。

必须过滤实施表（基于优化器的评估结果来过滤）

必须有一个连接谓词到实施表，它使用了实施表索引中主要的列。这个规范必须满足，不管是考虑星型连接或集中连接，即使集中连接将至需要使用一个单独的事实表索引。

一个查询块表现一个左外部链接或右外部连接，可以只涉及两个表，所以不符合一个星型模式的连接

不需要为优化器能发现一个星型模式连接而显示的声明参考完整性。

避免多余的谓词

避免多余的谓词，尤其是当他们发生在不同的表的时候。在某些情况下，优化器并不能判断多余的谓词。这可能导致基数被低估。

例如，在 SAP BI 应用程序中雪花模式和实施表以及维度表被作为一个查询优化数据结构被用到。在一些情况下会有多余的时间特征列（对于月的“ SID_0CALMONTH ”或对于年的 "SID_0FISCPER"）定义在事实表和维度表中。

SAP BI OLAP 处理器在维度和事实表的时间特征列上会产生多余的谓词。

这些多余的谓词可能产生很长的运行时间。

下面提供了一个例子，在一个 SAP BI 查询中有两个多余的谓词被定义在 WHERE 条件里。相同的位谓词定义在时间维度（DT）和事实（F）表。

AND ( "DT"."SID_0CALMONTH" = 199605
AND "F". "SID_0CALMONTH" = 199605
OR "DT"."SID_0CALMONTH" = 199705
AND "F". "SID_0CALMONTH" = 199705 )
AND NOT ( "DT"."SID_0CALMONTH" = 199803
AND "F". "SID_0CALMONTH" = 199803 )

DB2 优化器没有注意到相同的谓词，并对它们分别处理。这导致低估了谓词、生成不是最优的访问计划以及更长的查询运行时间。

处于这个原因多余的谓词从 DB2 数据库具体平台软件层面去掉了。

以上谓词转换成下面这一个。只有事实表“ SID_0CALMONTH ”列上的谓词被保留：

AND ( "F". "SID_0CALMONTH" = 199605
OR "F". "SID_0CALMONTH" = 199705 )
AND NOT ( "F". "SID_0CALMONTH" = 199803 )

应用 SAP 957070 和 1144883 备忘录来去掉多余的谓词。

设计并配置你的数据库

有很多数据库设计和配置选项可以影响查询性能。对数据库设计的更多建议参考“ Planning your Physical Database Design ”最佳实践文章。

使用约束来提高查询优化

考虑定义的唯一性，检查并参考一致性约束。这些约束提供了语义信息，允许 DB2 优化器重写查询来评估连接，通过连接来降低聚合和 FETCH FIRST N ROWS，去掉不必要的 DISTINCT 选项被和一些其它的优化。当应用程序可以保证它自己的关系时，信息约束也可以被用来检查并参考一致性约束。相同的优化也是可以的。当更新（插入或删除）行的时候，来自数据库管理器的强制约束可能导致很高的系统开销，尤其在更新很多有一致性约束的行的时候。如果一个应用程序在更新一行之前已经验证的信息，这样使用信息约束比起正常的约束更有效

例如，考虑 2 个表 DAILY_SALES 和 CUSTOMER 。在 CUSTOMER 表中的每一行都有一个唯一的客户键值（CUST_KEY）。 DAILY_SALES 包含一个 CUST_KEY 列并且每一行都引用一个 CUSTOMER 表中的客户键。可以创建一个参考一致性约束来防止在 CUSTOMER 和 DAILY_SALES 之间发生 1:N 的关系。如果应用程序要强制约束这个关系，可以创建一个信息化的约束。那么下面的查询避免了在 CUSTOMER 和 DAILY_SALES 之间进行连接，因为没有从 CUSTOMER 获取任何列，而且来自于 DAILY_SALES 的每一行都可以在 CUSTOMER 里面找到与之匹配的行，所以查询优化器将自动删除连接

SELECT AMT_SOLD, SALE PRICE, PROD_DESC
FROM DAILY_SALES, PRODUCT, CUSTOMER
WHERE
DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY AND
DAILY_SALES.CUST_KEY = CUSTOMER.CUST_KEY

应用程序必须执行信息约束，否则查询可能返回不正确的结果。在上面的例子中，如果行存在于 DAILY_SALES 中，在 CUSTOMER 表中却找不到相应的客户键，那么上面的查询返回的行可能不正确。

在复杂查询中使用 REOPT 绑定选项和输入变量

在一个在线事务处理（OLTP）环境的中输入变量有较好的语句准备时间是关键，在这样的环境中语句往往比较简单而且查询计划选择也很简单。使用不同的输入变量多次运行相同的语句可以复用在动态语句高速缓存中编译了的访问片段，避免了由于随时更改输入值而造成昂贵的 SQL 语句编译开销。

然而，输入变量对复杂的查询负载也会造成问题，它们的查询计划选择非常复杂，因此优化器需要更多的信息来做出好的决定。而且，语句编译时间通常是总运行时间中的一个很小组成部分。因为 BI 查询通常不会重复，所以并没有从动态语句高速缓存上得到好处。

如果在一个复杂查询工作负载中需要使用输入变量，请考虑使用 REOPT(ALWAYS) BIND 选项。当输入变量值是已知的，REOPT BIND 选项从 PREPARE 到 OPEN 或执行过程中推迟了语句编译。变量值被传递到 SQL 编译器中，这样优化器可以使用这些便利来计算一个更精确的选择评估。 REOPT(ALWAYS) 表示所有执行语句都应该被预编译。 REOPT(ALWAYS) 也可以被用于涉及特殊寄存器的复杂查询，比如 "WHERE TRANS_DATE = CURRENT DATE - 30 DAYS" 。如果输入变量对 OLTP 工作负载造成较差的访问计划选择，并且 REOPT(ALWAYS) 选项因为语句编译造成过多的开销，那么考虑对挑选过的查询使用 REOPT(ONCE) 。 REOPT(ONCE) 推迟语句的编译直到首个数据变量被绑定。使用这个首个输入变量值编译并优化 SQL 语句。后续使用不同的值来运行的语句将重用基于第一个输入编译的查询片段。这是一个好方法 , 如果首个输入变量代表了后续的输入值，并且在输入值未知的情况下比起优化器使用不同的值进行评估，它提供个了一个更好的查询访问计划 .

有很多方法来指定 REOPT：

对 C/C++ 应用程序中的嵌入式 SQL，使用 REOPT BIND 选项。这个 BIND 选项影响静态和动态 SQL 的再优化行为。

对 CLP 包，用 REOPT 绑定参数重新绑定 CLP 包。例如，使用 CS 隔离级别和 REOPT ALWAYS 来重新绑定 CLP 包，详细命令：

rebind nullid.SQLC2G13 reopt always;

对使用传统 JDBC 驱动的 CLI 应用程序或 JDBC 应用程序，在 db2cli.ini 中设置 REOPT 关键字。选项的值是：

2 - NONE

3 - ONCE

4 - ALWAYS

对于使用 JCC 通用驱动的 JDBC 应用程序，使用下面的方法之一：

使用 SQLATTR_REOPT 连接或语句属性。

使用 SQL_ATTR_CURRENT_PACKAGE_SET 连接或语句属性来制定 NULLID、NULLIDR1 或 NULLIDRA 包集合。 NULLIDR1 和 NULLIDRA 是保留的包集合名称。一旦使用就分别隐含了 REOPT ONCE 和 REOPT ALWAYS 。这些包集合需要于那个下面命令显示的创建：

db2 bind db2clipk.bnd collection NULLIDR1;
db2 bind db2clipk.bnd collection NULLIDRA;

对 SQL PL 存储过程使用下面的方法之一：

使用 SET_ROUTINE_OPTS 存储过程来为在当前会话中创建 SQL PL 存储过程设置绑定选项，例如调用 sysproc.set_routine_opts( ‘ reopt always ’ )

使用 DB2_SQLROUTINE_PREPOPTS 注册表变量在实例级别设置 SQL PL 存储过程选项。值设置为使用 SET_ROUTINE_OPTS 存储过程将覆盖 DB2_SQLROUTINE_PREPOPTS 指定的值

你可也能使用优化配置来为静态语句和动态语句设置 REOPT，如下面例子显示的：

为你的工作负载选择最佳的优化级别

设置优化级别可以获得显式指定优化技术的好处，尤其出于下面的原因：

为了管理非常小的数据库或者非常简单的查询语句

为了在你的数据库服务器编译时进行内存限制

为了减少查询编译时间，比如 PREPARE

大多数语句可以通过使用第 5 级优化得到充分的优化和合理的资源，这也是默认的查询优化级别。在一个给定的优化级别，查询编译时间和资源消耗是主要受查询复杂度的影响，尤其是连接以及子查询的数目。不过，编译时间和资源的使用同样受到执行优化的影响。

查询优化级别 1，2，3，5 和 7 适用于一般用途。只有你需要进一步减少查询优化时间而且在你知道 SQL 语句非常简单的情况下才考虑级别 0 。

Tip：要分析一个运行很长时间的查询，对查询运行 db2batch 来找出花了多少时间在编译上在运行上花费了多少时间。如果编译需要更多的时间，降低优化级别。如果执行需要更多的时间那么就考虑更高的优化级别

当你选择了一个优化级别，考虑下面的一般准则：

从使用默认查询优化级别开始，级别 5

要使用默认级别之外的级别，首先尝试级别 1，2 或 3 。级别 0，1 和 2 使用贪婪连接枚举运算法则。

如果你有很多表以及在同一列上有大量的连接谓词，在关心编译时间的情况下使用优化级别 1 或 2 。

对只有不到一秒的运行时间的查询使用一个低的优化级别（0 或 1）。比如查询往往有下面的特点：

只访问一个或很少的表

只获取一行或者几行

使用完全唯一的索引

在线事务处理（OLTP）事务是这种类型访问的很好例子

对长时间运行（超过 30 秒）的语句使用高一些的优化级别（3，5 或 7）。

优化级别 3 及其以上使用动态编程连接枚举算法。这个算法考虑更多的可选计划，并且可能招致比 0，1，和 2 更多的编译时间，尤其在表的数目增加后。

只有在你对一个查询有特别的优化需求时才使用优化级别 9 。

复杂查询需要不同数量的优化来选择最佳访问计划。对有下面特征的查询，请考虑使用更高的优化级别：

访问一个大表

谓词数目很多

大量的子查询

很多连接

很多集合操作，比如 UNION 和 INTERSECT

很多匹配的行

有 GROUP BY 何 HAVING 操作

嵌套表描述

大量的视图

决策支持查询或月底报告查询对于数据库是一个很常见的复杂查询的很好例子，对于这类查询优化级别至少应该使用默认值。

使用更高的查询优化级别的 SQL 语句是由查询生成器产生的。很多查询生成器创建效率低下的查询。写得很拙劣的查询，包括那些有查询生成器产生的查询，需要额外的优化以选择一个好的访问计划。使用查询优化级别 2 和更高的级别可以提高那些 SQL 查询。

对于 SAP 应用程序，总是使用优化级别 5 。这个优化级别启用了很多为 SAP 优化过的 DB2 功能，比如设置 DB2_REDUCED_OPTIMIZATION 注册表变量。

使用参数标记来减少动态语句的编辑时间

DB2 数据服务器可以通过在动态语句高速缓存中保存访问片段和语句文本来避免重复预编译一个前面运行过的动态 SQL 语句。对这个语句的一个后续 PREPARE 请求将尝试在动态语句高速缓存中查找访问片段来避免编译。然而，只要谓词在字面上有一点不同，这个语句高速缓存中的片段就不一致。例如，下面两个语句就在动态语句高速缓存中被看作不同的语句。

SELECT AGE FROM EMPLOYEE WHERE EMP_ID = 26790
SELECT AGE FROM EMPLOYEE WHERE EMP_ID = 77543

如果它们运行得太频繁，相关 SQL 语句的编译甚至会造成额外的系统 CPU 负担。在“ Monitoring and Tuning the System ”最佳实践文章中描述了如何检测这性能问题。如果你的系统遇到这类性能问题，应该考虑把应用程序改成使用参数标记来把谓词的值传递给 DB2 编译器，而不要显式的在 SQL 语句中包含它。不过，对于复杂的查询如果使用参数标记那么得到的访问计划可能不是最优的。更多信息请参见“在复杂查询中使用 REOPT 绑定选项和输入变量”。

设置 DB2_REDUCED_OPTIMIZATION 注册表变量

如果对你的应用程序设置的优化级别不能充分的减少编译时间，那么就尝试设置 DB2_REDUCED_OPTIMIZATION 注册变量。这个注册变量在优化器查找空间上比设置优化级别提供了更多控制。这个注册变量让你可以请求在指定的优化级别中减少优化功能或者严格使用优化功能。如果你减少了使用优化技术的数目，你同样减少了时间和优化过程中使用的资源。

注意：虽然优化时间和资源使用可能会减少，这也增加了产生的查询计划不是最优的风险。

首先，尝试设置注册表变量为 YES 。如果优化级别是 5（默认值）或更低，优化器将不会使用某些需要花费大量准备时间和资源的优化技术，但是通常也不会产生更好的查询计划。如果优化级别是 5，优化器会减少或取消一些额外的技术，这可能进一步减少优化时间和使用的资源，不过同样进一步增加了得到的查询计划不是最优的风险。对于低于 5 的优化级别，它们的一些技术可能在任何情况下都无效。

如果设置 YES 没能充分缩短编译时间，可以尝试设置这个注册变量为一个数字。效果是和 YES 一样，对于在级别 5 上的动态准备查询优化有后续的附加行为。如果在任何查询块中连接的总数目超过了这个设置，那么优化器就切换到一个贪婪连接枚举算法而不是取消额外的优化技术。这样的效果是查询将在一个类似优化级别 2 的级别上被优化。

对 SAP 应用程序设置 DB2_WORKLOAD 注册变量

对于 SAP 应用程序，总是会为 SAP 设 DB2_WORKLOAD 注册变量。这会触发一批其他的对 SAP 应用程序有特殊好处的 DB2 注册表变量设置。下面是在这些便利中和 DB2 优化器相关的注册变量设置

DB2_MINIMIZE_LIST_PREFETCH=YES

DB2_INLIST_TO_NLJN=YES

DB2_ANTIJOIN=EXTEND

DB2_REDUCED_OPTIMIZATION=<specific heuristics for SAP workloads>

DB2_VIEW_REOPT_VALUES=YES

DB2_REDUCED_OPTIMIZATION 的设置是专门针对 SAP 工作负载的。不要在其他应用程序下使用除非是 DB2 技术支持推荐的。

DB2_MINIMIZE_LIST_PREFETCH=YES 如没有足够可用信息来判断是否这个访问方法对查询有好处，将会阻止优化器考虑列出预取表访问方法。如果谓词包含参数标记，这可能对实例就是这种情况。在 SAP 应用程序中，参数标记在大多数应用程序中都有使用，除了 SAP NetWeaver BIreport 查询。

DB2_INLIST_TO_NLJN=YES 使优化器支持包含列表谓词 IN 的查询使用嵌套循环连接。 IN 列表被转换进一个表并像外部表的 NLJN 和通过索引访问的内部表使用。在 SAP 应用程序中经常有 IN 列表的查询，例如当使用 SAP 的 F4 帮助功能来从一个可能的值的列表中选择一个范围或一批不同的值。

即使在指定了 REOPT(ONCE) 的情况下，DB2_MINIMIZE_LIST_PREFETCH=YES 和 DB2_INLIST_TO_NLJOIN=YES 将依然保持活动。

DB2_ANTIJOIN=EXTEND 让优化器去寻找把 NOT IN 和 NOT EXISTS 子查询转换成 anti-joins 的机会。

DB2_VIEW_REOPT_VALUES=YES 让所有 SAP 用户去存储一个当语句被解释的时候在 EXPLAIN_PREDICATE 表中重新优化过的 SQL 语句的缓存的值。所有 SAP 用户使用相同的具有所需权限的数据库连接用户。

收集正确的编目信息，包括高级统计功能

精确的数据库统计信息是查询优化的关键。在所有对查询性能来说非常关键的表上有规律的运行 RUNSTATS 。如果一个应用程序直接查询这些表并且有大量的动态编目更新比如 DDL 语句，你可能也希望搜集系统编目表的信息。可以启用自动搜集统计信息功能来允许 DB2 数据服务器自动运行 RUNSTATS 。可以启用实时收集统计信息，通过立刻收集这些信息来让 DB2 数据服务器在优化查询之前能提供更及时的统计信息。

如果手动运行 RUNSTATS 来收集统计信息，你应该至少使用下面的选项。

RUNSTATS ON TABLE DB2USER.DAILY_SALES WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL

分发统计信息可以让优化器知道是否有数据倾斜。当使用特定索引访问表时，详细的索引统计信息提供更多的 I/O 需求细节来预取数据页。然而对大表收集详细的索引统计信息会消耗很多的 CPU 和内存。 SAMPLED 选项提供了详细索引统计信息和相同的精确性，却只需要一小部分 CPU 和内存。当并没有对一个表提供一个统计配置文件时，这些默认值同样会被自动收集统计信息使用。

为了提高查询性能，考虑收集更多更高级的统计信息，比如列组的统计信息，LIKE 统计信息或创建统计信息视图。

列组统计信息

如果你的查询不止一个连接谓词来连接两个表，在选择一个执行计划来运行查询之前 DB2 优化器将计算如何选择每个谓词。

例如，考虑以供厂商，他用有很多颜色的原料生产产品，弹性和品质。产品最后页是和原料一样的颜色。这个厂商执行了以下查询：

SELECT PRODUCT.NAME, RAWMATERIAL.QUALITY
FROM PRODUCT, RAWMATERIAL
WHERE PRODUCT.COLOR = RAWMATERIAL.COLOR
AND PRODUCT.ELASTICITY = RAWMATERIAL.ELASTICITY

这个查询返回了所有产品的名字和原材料品质。在这里有两个连接谓词：

PRODUCT.COLOR = RAWMATERIAL.COLOR
PRODUCT.ELASTICITY = RAWMATERIAL.ELASTICITY

优化器会假定这两个谓词是独立的，也就是说所有弹性对颜色的变化没有关系。然后通过建立每个表关于弹性等级数和不同的颜色数目的编目信息评估谓词的总的可选组合，并基于这个评估。例如，比起合并连接它可能更倾向于选择一个嵌套循环连接，反之亦然。

然而，这两个谓词可能并不独立。例如比较高的弹性材料可能只有几种颜色，而且弹性差的材料也可能不同于弹性好的材料只有剩下的其他颜色。然后组合这些谓词的选择消除一些行，所以查询将返回更多的行。没有这些信息，优化器也许不会选择最佳的计划。

为了在 PRODUCT.COLOR 和 PRODUCT.ELASTICITY 上收集列组统计信息 , 运行下面 RUNSTATS 命令：

RUNSTATS ON TABLE product ON COLUMNS ((color, elasticity ))

优化器使用这些统计信息来检测相关性，并动态调整相关的谓词选项组合，因此得到对连接的尺度和开销更精确的评估。

当一个查询需要数据以一个具体的方式（使用 GROUP BY 或 DISTINCT 关键字）编组时列组统计信息也会非常有用，因为优化器需要计算明确分组的数目。

考虑下面查询：

SELECT DEPTNO, YEARS, AVG(SALARY)
FROM EMPLOYEE
GROUP BY DEPTNO, MGR, YEAR_HIRED

没有任何索引或列组统计信息，优化器评估分组的数目（也包括在这种情况下返回的行数目）是 DEPTNO、MGR 和 YEAR_HIRED 的不同值的结果。这个评估假设分组键列是独立的。然而，如果每个管理员管理一个确定的部门，这个假设就可能是错误的。同样，不大可能每个部门每年都雇用新员工。因此，不同 DEPTNO、MGR 和 YEAR_HIRED 值得结果可能是对具体组的数目过高评估。

在 DEPTNO、MGR 和 YEAR_HIRED 上收集的列组统计信息将为上面的查询提供给优化器和有确定数目的分组：

RUNSTATS ON TABLE EMPLOYEE ON COLUMNS ((DEPTNO, MGR, YEAR_HIRED))

为了 JOIN 谓词关系，优化器也管理简单等价谓词，比如

DEPTNO = ” Sales ” AND MGR = “ John ”

在上面的 EMPLOYEE 表，在 DEPTNO 谓词上很可能与在 YEAR 上的谓词毫无关系。然而在 DEPTNO 和 MGR 上的谓词却肯定相关，因为每个单独的部门可能经常在一段时间受到同一个经理的管理。优化器使用列的统计信息来判断不同之的组合数目并对这两列的关系调整选择或计数评估。

子元素统计

如果你在模式结尾之外的任何位置对指定的 LIKE 谓词使用 % 通配符，你都应该搜集关于子元素结构的基本信息。

以及向通配符 LIKE 谓词（例如，SELECT .... FROM DOCUMENTS WHERE KEYWORDS LIKE '%simulation%'），这列以及查询必须满足某些标准来从子元素统计信息中获利。

表列应该包括由空格分开的子域或者子元素。例如，一个四行的 DOCUMENTS 表出于文本检索的目的有一个 KEWORDS 列和一系列相关的关键字。 KEYWORDS 的值是：

'database simulation analytical business intelligence'
'simulation model fruit fly reproduction temperature'
'forestry spruce soil erosion rainfall'
'forest temperature soil precipitation fire'

在这个例子中，每列的值有 5 个子元素组成，每个都是一个词（关键词），通过空格和其他关键词分隔开。

查询应该在 WHERE 子句中参考这些列。

优化器会评估每个谓词匹配多少行。对于这些通配符 LIKE 谓词，优化器假设 COLUMN 匹配了一系列彼此连接的元素，并且它基于字符串的长度来估算每个元素的长度，除了 % 开头或结尾的字符。如果你收集子元素的统计信息，优化器将有关于每个子元素和分隔符的长度信息。它可以使用这些额外的信息来更精确的评估可能有多少行匹配这个谓词。

为了连接子元素统计信息，运行有 LIKE STATISTICS 子句的 RUNSTATS 。

统计视图

DB2 基于成本的优化器把一个基于访问计划处理器处理的行数 – 或基数的评估作为这个操作的精确成本。这个基数评估是优化器成本唯一最重要的输入，而且它的精确度很大程度上取决于 RUNSTATS 实用工具从数据库收集的统计信息。对于计算一个精确的基数评估来说上面描述的统计信息是最重要的，然而有一些环境却需要非常成熟的统计信息。尤其是想要体现越复杂的关系就更需要成熟的统计信息，比如涉及比较的描述（例如，price > MSRP + Dealer_markup），关系跨了多个表（例如，product.name = 'Alloy wheels' and product.key = sales.product_key），或者其他谓词除了涉及独立属性以及简单比较操作的谓词。统计视图可以防止这些复杂关系类型，因为统计信息是在这个视图返回值的基础上收集的，而不是从这个视图相关的基础表上。

当一个查询被编译时，优化器把这个查询和可用的统计视图进行匹配。当优化器计算基数估算中间结果集时，它使用来自视图的统计信息来计算一个更好的评估。

查询不必因为优化器使用视图就直接参考统计视图。优化器可以使用和物化查询表（MQTs）相同的匹配机制来匹配到统计视图的查询。出于这个考虑，除了他们不被永久储存，统计视图和 MQTs 非常相似，因此它们也不消耗磁盘空间也不需要维护。

一个统计视图在第一次创建一个视图并使用 ALTER VIEW 语句允许优化。然后在这个统计视图上运行 RUNSTATS，用这个视图的统计信息填入系统编目表中。例如为了创建一个统计视图来表现在时间在一个星型模式中维度表和实施表之间的连接，执行下面语句：

CREATE VIEW SV_TIME_FACT AS (
SELECT T.* FROM TIME T, SALES S
WHERE T.TIME_KEY = S.TIME_KEY)
ALTER VIEW SV_TIME_FACT ENABLE QUERY OPTIMIZATION
RUNSTATS ON TABLE DB2DBA.SV_TIME_FACT WITH DISTRIBUTION

统计视图可以被用于对查询提高基数评估，以及访问计划和查询性能，例如：

SELECT SUM(S.PRICE)
FROM SALES S, TIME T, PRODUCT P
WHERE
T.TIME_KEY = S.TIME_KEY AND T.YEAR_MON = 200712 AND
P.PROD_KEY = S.PROD_KEY AND P.PROD_DESC = ‘ Power drill ’

没有统计视图，优化器假设所有事实表 TIME_KEY 的值对应一个特定的时间维度 YEAR_MON 值在事实表中是统一的。然而可能销售在 12 月特别强劲，产生比其他月份强劲多很多的交易。

在很多情况下，统计视图可以提高查询性能，并且有一些简单的最佳实践可以帮助判断需要创建哪些统计视图。请参考“更多阅读”章节。

自动收集统计信息当前不能用于统计视图。无论什么时候统计视图的基础表数据有了显著的改动都要收集统计视图的统计信息。

最小化 RUNSTAS 的影响

进一步改善 RUNSTATS 的时机：

通过 COLUMNS 子句来限制要收集统计信息的列。在查询工作负载中经常会有一些列永远都不会被谓词涉及到，因此它们不需要被统计。

如果倾向于一致的数据就限制搜集分布统计信息的列数。收集分布统计信息需要比收集基础列统计花费更多的 CPU 和内存。不过，要判断一列的值是否相同需要现有的统计信息或者对数据进行查询。因为表会发生改变，这个方法同样基于数据将保持一致的假设。

通过制定 TABLESAMPLE SYSTEM 或 BERNOULLI 子句使用页面或行级别的取样来显示处理的行数和页面的数目。从 10% 级别的样本开始例如 TABLESAMPLE SYSTEM(10) 。检查统计信息的精确性以及系统性能是否因为更改访问计划而有所下降。如果是的话，就尝试用 10% 行级别的样本来替换，比如 TABLESAMPLE BERNOULLI(10) 。如果统计信息精度不够，则增加取样数量。当使用 RUNSTATS 页面或行级别的取样，对进行连接的表使用相同样本率。这和确保连接列统计信息有相同级别的精度。

对一个配置了数据库分区功能（DPF）的数据服务器，RUNSTATS 从单个数据库分区搜集统计信息。如果 RUNSTATS 运行在表所在的这个数据库分区上，统计信息将在这里收集。如果不是，统计信息将从第这个表所在分区组的一个数据库分区上收集。为了保持一致的统计信息，就要确保进行连接的表的统计信息从相同的数据库分区搜集。

避免手动更新编目统计信息

DB2 数据服务器支持通过对 SYSSTAT 模式下的视图发起 UPDATE 语句手动来更新编目统计信息。这个功能对在测试系统中模拟一个生产数据库来检查查询计划，或许非常有用。为了在其他系统上重放，db2look 工具对抓取 DDL 和 UPDATE SYSSTAT 语句方面很有帮助。

然而，要避免手动更新统计信息 - 意味着为了强制执行一个特定的查询计划通过提供不正确的统计信息影响查询优化器。这个实践可能造成某些查询的性能提升，也可能导致其他的性能下降。在最终使用这个办法之前，请考虑其它之前考虑过的调优方法。如果不得不使用这个方法，在这种情况下一定要记录原始的统计信息，如果更新统计信息造成了性能下降就需要恢复它们。

对 SAP 应用程序，使用自动统计信息收集

在 SAP 安装过程中，自动和实施统计信息是默认打开的。这就是说你不需要手动发起 RUNSTATS 。

很多 SAP 使用情况是在第一步以及通过一个复杂的 SAP 文本查询来访问修改表内容。不要改变下面提到的默认设置 :

Automatic maintenance (AUTO_MAINT) = ON
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Automatic statement statistics (AUTO_STMT_STATS) = ON
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF

使用有不均匀数据的 SAP BI 表的统计视图

在 SAP BI snowflake 模式的表中不均匀的分布数据可能导致并不优化的查询访问计划并增加查询时间。大多数这样的问题是通过设置 SAP 工作负载变量 =SAP 来解决的。但是在一些例外的情况下，当很多表互相连接时，统计视图可以被创建来为 DB2 优化器提供更多关于值分布的信息。

下面的例子中，事实表和客户、请求、时间以及产品维度表进行连接。

大多数交易是跟 Customer #10200 完成的。因此，事实表的绝大多数行在 Dim C 列的值是 2 。这造成了在事实表的 Dim C 列上的不均匀的数据分布。

DB2 优化器可能会在对在客户维度有约束的查询时候估算出错误的基数

可以在维度和事实表上定义统计视图生成额外的统计信息，DB2 优化器利用这些信息来增强基数评估并减少查询运行时间。

在上面的例子中，下面在客户和事实表维度上的统计视图可能解决错误基数统计问题并减少查询响应时间：

CREATE VIEW stat_view as
( SELECT cust .c, cust.customer#, cust.region, f.dimC
FROM customer_ 维度 cust,
fact_table fact
WHERE cust.c = fact.dimC )

如果这个统计视图的一个基本表发生了改动，就需要在统计视图上更新数据库统计信息。统计视图不支持 DB2 自动统计信息搜集。

使用 SAP BI 聚集

SAP BI 实施了它们自己的物化查询表，叫做 SAP BI 聚集。使用 SAP 聚集在 SAP BI 应用程序中替代一般的 DB2 物化查询表。

如果其他调优选项不能产生可以接受的效果那么使用优化配置文件

如果你已经遵循了本文推荐的最佳实践，而你相信仍然没有达到最优的性能的话，你就可以向 DB2 优化器提供一个明确的优化指南。

优化指南包含在一个叫做优化配置文件的 XML 文档中。配置文件定义 SQL 语句和他们相关的优化指南。

如果你广泛的使用了优化配置文件，它们需要你付出很多的努力来维护。更重要的是，你只能使用优化配置文件来对现有 SQL 语句提高性能。下面的最佳实践对所有的查询持续稳定的提高查询性能，包括未来的某些查询。

使用解释工具来调优 SQL 语句

解释工具是用来显示被查询优化器用来运行一个 SQL 语句的查询访问计划。它包含了用于运行 SQL 语句关于相关操作非常广泛的信息，比如计划操作去、它们的 arguments、执行顺序和成本。因为查询访问计划是查询性能中最重要的因素之一，为了能诊断查询性能问题，能够理解解释工具的输出非常重要。

解释信息通常用于：

理解为什么应用程序性能发生了变化

评估性能调优的效果

分析性能变化

为了帮助你理解查询性能变化的原因，需要调优前后的解释信息，你可以通过执行下面步骤得到它们：

在你做任何更改之前，抓取查询的解释信息并保存解释表。另外，也可以保存 db2exfmt 解释工具的输出。然而，为了更成熟的分析，把解释信息保存在解释表中可以更方便用 SQL 查询，又提供了把数据保存在关系型 DBMS 中在维护上明显的优势。此外 db2exfmt 工具可以在任何时间运行。

如果你不想、或不能访问 Visual Explain 来查看这些信息，就保存或打印当前编目统计信息。你也可以使用 db2look 生产力工具来帮助执行这个任务。另外，如果是你用 DB2 9.5，在语句被解释的同时搜集解释快照。 Db2exfmt 工具将自动格式化包含在快照中的信息。这在使用自动或是统计信息收集的时候尤其重要，因为查询优化器使用的统计信息可能还没有存入系统编目表中，或者他们可能在语句从系统编目表中获取信息到被解释的过程中被更改了。

保存或打印数据定义语言（DDL）语句，包括这那些 CREATE TABLE、CREATE VIEW、CREATE INDEX、CREATE TABLESPACE 。 Db2look 同样可以执行这个任务。

通过这个方法收集的信息，为将来的分析提供了一个参考点。对动态 SQL 语句来说，当你第一次运行你的应用程序时，你可以搜集这个信息。对于静态语句，也可以在绑定的时候搜集这个信息。在一个主要系统更改之前搜集这个信息非常重要，比如安装一个新的服务级别或 DB2 版本或者一个很大的配置改动，比如增加或删除数据库分区和分布数据。这是因为这类系统更改可能造成访问计划的不利更改。虽然访问计划退步应该很少发生，但是有这些可用信息将允许更快的你解决性能退步的问题。要分析一个性能变化，把之前的信息和现在你开始分析的时候收集到的关于查询和环境的信息进行比较。

一个简单的例子，你的分析可能显示索引不再作为访问计划的一部分被用到。使用 Visual Explain 或 db2exfmt 显示的编目统计信息，你可能注意到 index 级别数远远高于查询第一次绑定到数据库的时候的值。然后你可以选择执行下面的某个操作：

重组索引

为你的表和索引搜集新的统计信息

在重新绑定的时候搜集解释信息。

在你执行其中某个操作之后，再检查一下查询计划。如果索引再一次被使用了，这个查询的性能可能不再是个问题。重复这些步骤直到问题被解决。

评估性能调整效果

你可以进行一系列的操作来帮助提高查询性能，比如校对配置参数、添加容器、和搜集刷新编目统计信息。

在你这些方面进行了更改，如果在被访问计划选择到的方面有更改的话，你可以使用解释工具来判断影响。例如，如果你基于索引指南添加一个索引或物化查询表（MQT），解释数据可以帮助你判断是否索引或物化查询表最终如你所期望的被用到了。

虽然解释输出提供了让你判断选中的访问计划的信息和成本，对一个查询来说精确测量性能提高的唯一方法是使用基准的是技术。

在 SAP 应用程序中提高 SQL 查询性能

SAP 提供了下面接口和数据库通讯：

应用程序写入 ABAP

SAP Open SQL

自然 SQL 通过 ADBC 接口（ABAP 数据库连接）

自然 SQL，以 EXEC SQL 语句开始并以 ENDEXEC 语句结束

JDBC 用于 SAP Java 应用程序

SAP ABAP 应用程序主要使用 SAP Open SQL 来访问数据库。 SAP Open SQL 由一组执行数据库操作的 ABAP 语句组成。被开发来为 SAP 支持的所有关系数据库平台提供一个通用接口。 SAP Open SQL 语句在 SAP 数据库接口中被转换为自然 SQL 。

SAP NetWeaver BI 使用 SAP BI 发布的 ADBC 。对于自然 SQL 接口 ADBC 是一个基于类的 API，它支持执行动态生成 SQL 语句。在 ABAP 环境中使用 ADBC 和在 Java 环境中使用 JDBC 类似。

用户他们自己写的 report 和事务可以使用任何一个可用接口来访问数据库。

在 SAP Open SQL 中使用 DB2 参数

SAP 支持一批 DB2 参数，它们可以添加到 Open SQL 语句中以影响从 Open SQL 生成的自然 SQL 语句如何在数据库层编译和运行。客户可以在他们自己写的 report 和事务中使用这些 DB2 参数来提高 SQL 查询性能。

注意，对前面描述的 DB2 SAP 指定参数设置，他应该只需要在特殊情况下使用这些参数。只有发生数据库性能问题需要快速解决方案和替代解决方案时才使用它们。

对 Open SQL ，SAP 支持下列 DB2 参数

OPT_FOR_ROWS N：如果 N > 0，一个 OPTIMIZE FOR N ROWS 子句就被添加到 SQL 语句中。对 ABAP 语句它包含 UP TO N ROWS 子句，OPTIMIZE FOR N ROWS 是自动生成的。 OPT_FOR_ROWS 0 被用于防止自动生成 OPTIMIZE FOR N ROWS 。

USE_OPTLEVEL X：这个参数可以被用来指定语句处理的 DB2 查询优化级别。默认情况下，SAP 使用优化级别 5，这为大多数情况提供了合理的语句编译和执行时间。在某些情况下，一个较低或较高的优化级别可能生成一个对特定语句并具有更高性能的访问计划。在这些情况下，参数可以被用作短期解决方案或替代方案。

&SUBSTITUTE LITERALS&：对于 SAP 内核版本 4.6，这个参数需要把 SAP 配置参数 dbs/db6/dbsl_substitute_literals 设置为 1 。这个参数使 ABAP 语句文本中的文字被作为 SQL 语句的文字实现（而不是作为参数标记）。如果这些文字被使用，优化器可以使用涉及这些列的表中的分布信息。注意，存在这种可能，没有对这个表搜集分布统计信息。

&SUBSTITUTE VALUES&：对于 SAP 内核版本 4.6，这个参数需要把 SAP 配置参数 dbs/db6/dbsl_substitute_literals 设置为 1 。这个参数使在 ABAP 语句中的所有输入值都被作为 SQL 语句文本的文字实现。就像 &SUBSTITUTE LITERALS&，DB2 优化器可以使用关于涉及表中的分布信息。然而，由于值的替换因为语句文本改变了，这个语句每次运行的时候都需要重新准备。

CONVERT_FAE_TO_CTE：对于内核版本早于 7.0 的 SAP，这个参数用来给控制有 FOR ALL ENTIRES 结构的 ABAP 语句生成 SQL 。

使用正确的 SAP 配置参数来控制为 FOR ALL ENTIRES Open SQL 查询生成 SQL

FOR ALL ENTRIES (FAE) 语句是一个通用的 SAP ABAP 语句结构。这个参数允许 ABAP 程序员用一个 ABAP 内部表连接一个或几个数据库表。 FAE 语句总是返回一个唯一的结果集。 SAP 数据库接口将删除任何从数据库返回的重复数据。

例如：

SELECT … FROM <dbtable> FOR ALL ENTRIES IN <itab>
WHERE <col> = <itab>~<col>

SAP 提供了下列配置参数来控制把多少语句被转化为 SQL：

rsdb/prefer_in_itab_opt：如果这个参数被设置为 1 而且如果 FAE 语句只在 WHERE 子句中涉及一个内部表列，那么一个有 IN 列表的自然 SQL 语句就被生成出来：

SELECT … FROM <dbtable> WHERE
<col> IN (<itab>[1]~<col>,
<itab>[2]~<col>, … , <itab>[N]~<col> )

<itab>[2]~<col>, … , <itab>[N]~<col> )

rsdb/prefer_join：这个参数是在 SAP 内核版本 7.0 或以后可用。如果这个参数被设置为 1 并且如果 FAE 语句不包含任何多表之间的连接，那么 FAE 语句就被实现为一个连接：

SELECT …
FROM <dbtable> t1,
( SELECT * FROM ( VALUES(<itab>[2]~<col>, <itab>[2]~<col>, … ,
<itab>[N]~<col> ) )
AS t2_tmp ( <coL> ) GROUP BY <col> )
AS T2
WHERE t1.<col> = t2.<col>

rsdb/prefer_union_all：如果这个参数被设置为 1，那么将为内部表中的每一个值都会生成一个 SQL 语句并且通过 UNION ALL 链接起来：

SELECT … FROM <dbtable> WHERE <col> = <itab> [1]~ <col> UNION ALL
SELECT … FROM <dbtable> WHERE <col> = <itab> [2]~ <col> UNION ALL
…
SELECT … FROM <dbtable> WHERE <col> = <itab> [N]~ <col>

如果这个参数被设为 0，那么将生成由 OR 链接的条件：

SELECT … FROM <dbtable> WHERE
<col> = <itab>[1]~<col> OR
<col> = <itab>[2]~<col> OR
…
<col> = <itab>[N]~<col> OR

这个参数在 rsdb/prefer_join 参数被设置为 1 的情况下不被使用。

如果上面的转换一个都没有发生，FAE 语句将被转换为带 OR 的自然 SQL 语句。

下面附加的 SAP 配置参数通过一个自然 SQL 语句控制内部 ABAP 表中输入的数目。

rsdb/max_blocking_factor

rsdb/max_in_blocking_factor

rsdb/prefer_fix_blocking

rsdb/min_blocking_factor

rsdb/min_in_blocking_factor

如果内部表包含更多输入，一些自然 SQL 语句会被生成出来而且结果搜集在 SAP 数据库接口中。更多细节，参见 SAP note 48230 。

SAP 用于控制 FAE 语句的执行的配置参数在整个 SAP 系统的很大范围内对关键数据库命令和性能影响上都有非常大的效果。因此，只有在进行了详细问题分析之后或是 SAP 技术支持推荐的情况下覆盖这些配置参数的默认设置。

对 SAP 内核 7.0 之后的版本，rsdb/prefer_join=1 选项是不可用的。可用 IN 列表，UNION ALL 语句或在 WHERE 子句中有 OR 链接条件的语句来替换。 SAP 的参数 CONVERT_FAE_TO_CTE 可以被添加到 ABAP FAE 语句中来转换成一个 DB2 通用表的表达式（CTE）。这提高了性能，因为 DB2 优化器可以产生更好的访问计划 – 因为内部表中的重复值已经在 CTE 中被删除了。

对 SAP BI 有问题的查询进行 SQL 性能调优

在 YDB6BWEXT 报告中，两个用户可用于提供一个更快的其他选择以提高长时间运行 SAP BI 有问题的查询的性能。

对 reporting 查询，SAP NetWeaver BI 使用 ADBC 和自然 SQL 语句。这个语句在 SAP NetWeaver BI Read Interface 中生成并运行，在 SQL 语句执行前后会有两个出口。例如，一个 SAP BI 查询的 DB2 优化级别或 SQL 查询级别可以在出口指定。

对可用出口而言，客户需要应用 YDB6BWEXT 报告的 queryoptbefore 与 queryoptafter 两个表和在 SAP NetWeaver BI 7.0 以及更高版本上的 SAP note 1143736 以及对早期版本应用 SAP note 872397 。

要获取如何实施在 YDB6BWEXT 中的出口指南请联系 SAP 开发支持。

1.避免查询条件中的复杂表达式

2.避免表达式中连接谓词

3.避免本地谓词中对列应用表达式

4.避免接列数据类型不匹配

5.避免不相等的连接谓词

6.避免 DISTINCT 关键字的聚集

7.避免不必要的外连接

8.把 FETCH FIRST N ROWS ONLY 子句和 OPTIMIZE FOR N ROWS 子句一起使用

9.如果你在使用星型模式连接，确保你的插叙满足标准要求

10.避免多余的查询约束

11.使用约束以提高查询优化

12.在复杂查询中 REOPT 绑定选项与输出变量同时使用

13.为你的工作负载选择最佳的优化级别

14.设置 DB2_REDUCED_OPTIMIZATION 注册变量

15.为 SAP 应用程序，设置 DB2_WORKLOAD 注册变量

16.收集正确的编目统计信息，包括高级统计功能

17.最小化 RUNSTATS 的影响

18.避免手动更新编目统计信息

19.对 SAP 应用程序，使用自动统计信息收集

20.对值分布不均匀的 SAP BI 表使用统计视图

21.使用 SAP BI 聚集

22.在 SAP Open SQL 中使用 DB2 参数

23.对 FOR ALL ENTRIES Open SQL 查询使用正确的 SAP 配置参数来控制 SQL 生成

24.如果其他调优选项没有得到可以接受的结果，就使用优化配置。

总结

本文中的最佳实践是为了帮助你最小化对系统资源的使用以及运行 SQL 语句的时间。避免潜在的性能问题，在你编写你的 SQL 语句和配置你的设置时使用这些最佳实践。

要编写可以被 DB2 优化器轻易优化的 SQL 语句，就要避免一下情况：

搜索条件中的复杂表达式

在本地谓词列上应用表达式

连接列的数据类型不匹配

不等连接谓词

多个聚集使用 DISTINCT 关键字

不必要的外连接

多余的查询谓词

如果你在使用星型模式连接，为了 DB2 数据服务器可以使用专门的星型连接方法以提高查询性能，要确保你的查询满足标准。

如果你指定 FETCH FIRST n ROWS ONLY 子句，你也应该指定 OPTIMIZE FOR n 子句来避免缓冲操作

在你设计并配置你的 DB2 数据库时，请考虑下列最佳实践：

使用约束

对在谓词中有输入变量的 SQL 语句使用 REOPT 绑定选项

对你的工作负载选择最优级别

设置 DB2_REDUCED_OPTIMIZATION 注册变量

确保准确的编目统计信息

考虑收集高级统计信息，比如列分组统计信息、子元素统计信息以及统计视图

最后，可以使用 DB2 解释功能来回顾潜在的查询访问计划并判断如何调整查询以获得最佳性能。

如果你开发 SAP 应用程序，你应该应用这些最佳实践，比如确保编目统计信息的正确。另外。，你应该应用 SAP 特定的最佳实践：

为 SAP 设置 DB2_WORKLOAD 注册变量

对有不平衡分布值的 SAP BI 表使用统计信息视图

使用 SAP BI 聚集来替代 MQTs

你也可以通过 SAP Open SQL 中 DB2 参数正确的配置提高在 SAP 应用程序中的 SQL 查询性能，并使用正确的 SAP 配置参数来控制 FOR ALL ENTRIES 构造的 Open SQL 查询的 SQL 风格。

次浏览