Prayer

db2缺省的优化器级别为5，级别越高优化程度越高，大多数情况下为应用程序绑定时指定高级别的优化器会提高性能，但有时因机器硬件或内存分配不合适，高级别的优化会消耗更多的资源，有时会优化报错。本人碰到过几次因优化器不能正常工作反而使sql的性能急剧下降的案例。

先转载一下关于db2优化器的官方资料：

优化级别 n=0,1,2,3,5,7,9

可以在编译 SQL 查询时指定下列其中一个优化器级别： 

0 - 
此级别指导优化器使用最少的优化来生成存取方案。此优化级别具有下列特征： 
优化器不考虑任何非均匀分布统计信息。 
仅应用基本的查询重写规则。 
发生贪婪联合枚举。 
仅允许使用嵌套循环连接及索引扫描存取方法。 
[9] 在生成的存取方法中不使用列表预取。 
不考虑星型连接策略。 
此级别应该只用于需要最低的查询编译开销的情况。查询优化级别 0 适用于以下应用程序：完全由存取经过良好索引表的很简单的动态 SQL 语句组成。 

1 - 
此优化级别具有下列特征： 
优化器不考虑任何非均匀分布统计信息。 
只应用查询重写规则的一个子集。 
发生贪婪联合枚举。 
[9] 在生成的存取方法中不使用列表预取。 
除了“合并扫描”连接及表扫描也可用以外，优化级别 1 类似于级别 0。 

2 - 
此级别指导优化器使用比级别 1 显著高的优化程度，而使复杂查询的编译成本显著低于级别 3 及更高级别。此优化级别具有下列特征： 
利用了所有可用的统计信息，包括频率和分位数非均匀分布统计信息。 
除只在极少情况下才适用的计算密集型规则外，将应用所有其它查询重写规则，包括路由对具体查询表的查询。 
使用了贪婪联合枚举。 
考虑各种存取方法，包括列表预取和具体查询表路由。 
如果适用的话，考虑星型连接策略。 
优化级别 2 除了使用“贪婪”联合枚举而不是“动态规划”以外，类似于级别 5。在所有使用“贪婪”联合枚举算法的级别中，此级别具有最高的优化程度，与级别 3 及更高级别相比，它对复杂查询的替代方案考虑较少，因而消耗的编译时间也少。建议将级别 2 用于决策支持或联机分析处理（OLAP）环境中非常复杂的查询。在这种环境下，特定查询很少完全重复，因此查询存取方案不大可能在高速缓存中停留到出现下一个查询为止。 

3 - 
此级别请求中等优化。此级别与 DB2 MVS/ESA 版、OS/390 或 z/OS 版的查询优化特征基本匹配。此优化级别具有下列特征： 
使用非均匀分布统计信息（如果可用的话），该统计信息跟踪频繁出现的值。 
应用大部分查询重写规则，包括子查询至连接的变换。 
动态规划连接枚举，如下所示： 
组合内部表的有限使用 
涉及查找表的星型模式的笛卡尔乘积的有限使用 
考虑各种存取方法，包括列表预取、索引 AND 运算和星型连接。 
此级别适用于大量应用程序。此级别改进具有 4 个或更多连接的查询的存取方案。但是，优化器可能无法考虑使用缺省优化级别选择的更好方案。 

5 - 
此级别指导优化器使用相当大量的优化来生成存取方案。此优化级别具有下列特征： 
使用所有可用的统计信息，包括频率和分位数分布统计信息。 
除只在极少情况下才适用的那些计算密集型规则外，将应用所有其它查询重写规则，包括路由对具体查询表的查询。 
动态规划连接枚举，如下所示： 
组合内部表的有限使用 
涉及查找表的星型模式的笛卡尔乘积的有限使用 
考虑各种存取方法，包括列表预取、索引 AND 运算和具体查询表路由。 
当优化器检测到不能保证用于复杂动态 SQL 查询的附加资源和处理时间时，将减少优化。减少的范围或大小取决于机器大小和谓词数目。 

当查询优化器减少查询优化量时，它继续应用正常时应用的所有查询重写规则。但是，它的确使用了贪婪联合枚举法并减少了考虑的存取方案的组合数。 

对于由事务和复杂查询组成的混合环境，查询优化级别 5 是一个很好的选择。此优化级别设计成可以用高效的方式应用最有价值的查询变换和其它查询优化技术。 

7 - 
此级别指导优化器使用相当大量的优化来生成存取方案。级别 7 除了不减少用于复杂动态 SQL 查询的查询优化量以外，它与查询优化级别 5 是相同的。
 
9 - 
此级别指导优化器使用所有可用的优化技术。这些技术包括： 
所有可用的统计信息 
所有查询重写规则 
联合枚举的所有可能性，包括笛卡尔乘积和任意多种组合的内部结构 
所有存取方法 
此级别可以大大扩展由优化器考虑的可能的存取方案数量。对于使用大表的很复杂且运行时间很长的查询，可以使用此级别来确定更全面优化是否将生成更好的存取方案。使用“说明”和性能测量来验证是否实际上已找到更好的方案。 

---

调整优化器级别案例：

1、中试所fmis3上线后发现业务模块性能很慢，在调优过程中发现大部分复杂的sql都报警告：
   SQL0437W  此复合查询的性能可能不是最优的。原因码为："3"。  SQLSTATE=01602
   经查帮助得知原因：3 优化器成本下溢
   估计是服务器的资源所限(CPU、内存)，降低优化级别，调整数据库参数(DFT_QUERYOPT = 3)后问题解决。
   相关语句：
  

$ db2 update db cfg for cwgl using DFT_QUERYOPT 3
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
$
  

  

2、一个修正数据的脚本，含有大量的复杂sql如join、union、group等操作，在执行时同样报sql0437W的警告，经降低优化器级别后，问题解决。
相关语句：

d:/>db2 -z test.log -std@ -f test.sql
SQL0437W  此复合查询的性能可能不是最优的。原因码为："3"。  SQLSTATE=01602


d:/>db2 ? 01602

SQLSTATE 01602: 优化级别已降低。


d:/>db2 set current query optimization=3
DB20000I  SQL 命令成功完成。

d:/>db2 -z test.log -std@ -f test.sql
SQL0437W  此复合查询的性能可能不是最优的。原因码为："3"。  SQLSTATE=01602


d:/>db2 set current query optimization=2
DB20000I  SQL 命令成功完成。

d:/>db2 -z test.log -std@ -f test.sql
SQL0437W  此复合查询的性能可能不是最优的。原因码为："3"。  SQLSTATE=01602


d:/>db2 set current query optimization=1
DB20000I  SQL 命令成功完成。

d:/>db2 -z test.log -std@ -f test.sql
DB20000I  SQL 命令成功完成。


d:/>