DB2性能优化之设计DB2配置数据库很好用！

在这里我们主要向大家讲述的是DB2性能优化之设计DB2配置数据库的正确操作步骤，在实际操作中有很多数据库设计与配置选项时直接影响查询DB2数据库的性能。对数据库设计的更多建议参考“ Planning your Physical Database Design ”***实践文章。

使用约束来提高查询优化

考虑定义的唯一性，检查并参考一致性约束。这些约束提供了语义信息，允许 DB2 优化器重写查询来评估连接，通过连接来降低聚合和 FETCH FIRST N ROWS，去掉不必要的 DISTINCT 选项被和一些其它的DB2性能优化。

当应用程序可以保证它自己的关系时，信息约束也可以被用来检查并参考一致性约束。相同的优化也是可以的。当更新(插入或删除)行的时候，来自数据库管理器的强制约束可能导致很高的系统开销，尤其在更新很多有一致性约束的行的时候。如果一个应用程序在更新一行之前已经验证的信息，这样使用信息约束比起正常的约束更有效

例如，考虑 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 的再DB2性能优化行为。

对 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，如下面例子显示的：

<STMTPROFILE ID="REOPT example "> <STMTKEY> 
<![CDATA[select acct_no from customer where name = ? ]]> 
</STMTKEY> 
<OPTGUIDELINES> 
<REOPT VALUE='ALWAYS'/> 
</OPTGUIDELINES> 
</STMTPROFILE> 

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

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

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

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

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

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

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

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

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

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

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

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

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

只访问一个或很少的表

只获取一行或者几行

使用完全唯一的索引

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

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

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

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

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

以上的相关内容就是对的介绍，望你能有所收获。

【编辑推荐】

1. 正确提高DB2数据备份和恢复的效率要用到哪些项目？
2. DB2恢复删除表的实际操作技巧演示
3. 3个方案实现备份恢复DB2数据库，很简单！
4. DB2数据迁移哪几种方案可以实现？
5. DB2在线备份，以及重定向恢复实战演习！