DB2性能调优：设计并配置你的数据库

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

    使用约束来提高查询优化

   考虑定义的唯一性，检查并参考一致性约束。这些约束提供了语义信息，允许 DB2 优化器重写查询来评估连接，通过连接来降低聚合和 FETCHFIRST 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 选项。当输入变量值是已知的，REOPTBIND 选项从 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，如下面例子显示的：

• DB2性能调优工程师常遇二十大难题
• 全面解析DB2数据库的锁等待
• IBM DB2数据库性能调整相关命令整合

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

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

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

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

    为了减少查询编译时间，比如 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 theSystem ”最佳实践文章中描述了 如何检测这性能问题。如果你的系统遇到这类性能问题，应该考虑把应用程序改成使用参数标记来把谓词的值传递给DB2 编译器，而不要显式的在 SQL语句中包含它。不过，对于复杂的查询如果使用参数标记那么得到的访问计划可能不是最优的。更多信息请参见“在复杂查询中使用 REOPT绑定选项和输入变量”。

    设置 DB2_REDUCED_OPTIMIZATION 注册表变量

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

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

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

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