监控 DB2 活动之Visual Explain详细介绍(3)

　　影响查询性能的因素

　　数据库环境的配置方式和用于准备查询的查询优化级别对于查询的准备方式、执行方式有着重大的影响。

　　配置参数值

　　Visual Explain 可迅速汇总影响查询编译的所有参数，并在一个汇总窗口中显示出来。这个窗口就称为 Optimization Parameters 窗口，通过在 Access Plan Graph 窗口的主菜单中选择 Statement > Show Optimization Parameters 可调用此窗口。图 14 展示了 Optimization Parameters 窗口在激活时的外观。

　　图 14. Optimization Parameters 窗口

　　Optimization Parameters 窗口中包含的部分配置参数包括：

　　AVG_APPLS(平均应用程序)：此参数表示为数据库并发运行的应用程序平均数量。DB2 使用此信息来确定排序空间和缓冲池使用得有多么频繁，并确定查询能够使用的空间有多少。

　　SORTHEAP(排序堆)：排序堆是执行排序时可用的内存空间数量。若排序需要的内存多于排序堆中可用内存，则部分排序数据将不得不分页到磁盘上(这会对性能造成严重的负面影响)。

　　LOCKLIST(锁列表)：该参数表示 DB2 可用于存储各应用程序的锁定信息的内存数量。若锁列表空间过小，则 DB2 可能必须逐步提交(escalate)部分锁，以便为应用程序具有的所有锁腾出空间。

　　MAXLOCKS(最大锁列表百分比)：该参数控制整个锁列表空间中有百分之多少的空间可为一个应用程序所有。若一个应用程序具有过多的开放锁，从而试图占用过多的内存，DB2 将提交部分锁，以释放锁列表中的空间。

　　NUM_FREQVALUES(频繁值数)：DB2 Runstats 实用工具使用频繁值数来控制 DB2 将在内存中保留多少使用频率最高的值。优化器使用该信息来确定 WHERE 子句中的一个谓词将消耗结果集的多少百分比。

　　NUM_QUANTILES(数据分位数)：DB2 Runstats 实用工具使用分位数来控制为列数据捕获多少分位。增加分位数将给予 DB2 关于数据库中数据分布情况的更多信息。

　　DBHEAP(数据库堆)：数据库堆控制数据库对象信息的可用内存量。对象包括索引、缓冲池和表空间。事件监控器和日志缓冲区信息也存储在这里。

　　CPUSPEED (CPU 速度)：计算机的 CPU 速度。若此值设置为 -1，则 DB2 使用 CPU 速度度量程序来确定恰当的设置。

　　BUFFPAGE 和缓冲池大小：优化器可在优化数据中使用的缓冲池大小。增加或减少缓冲池大小会对访问计划产生显著影响。

　　所用优化级别

　　影响为查询生成访问计划的方式的最重要因素就是优化级别，优化级别用于为此任务做准备。该信息告诉 DB2 Optimizer 要付出多少努力、使用什么技术来确定解决查询的最佳访问计划。较高的级别将使优化器使用更为复杂的算法和代数分析 —— 因而也需要花费更多的时间 —— 来生成最终计划。

　　有 7 个可用的优化级别，各级别使用所有可用规则和统计信息的不同子集。可用的优化级别包括：

　　0 —— 使用最少的优化

　　1 —— 所用的优化程度大致与 DB2/6000 V1 相同，此外还增加了一些 V1 中没有的低成本特性

　　2 —— 使用优化级别 5 的特性，但简化了连接算法

　　3 —— 执行中等数量的优化，与 DB2 for MVS/ESA 的查询优化特征相似

　　5 —— 使用大量优化，利用 Heuristic Rules(若未另行指定，这将是默认优化级别)

　　7 —— 使用无 Heuristic Rules 的大量优化

　　9 —— 使用所有可用优化技术

　　在确定要使用的最佳优化级别时，以下指导原则可给予您很大的帮助：

• 　　为需要很少的优化、极其依赖主键索引搜索或极简单的连接(例如，非常简单的 OLTP)的查询使用优化级别 0 或 1。
• 　　若查询比较简单，仅包含少量表和设计表的索引的连接(例如，OLTP)，则为查询使用优化级别 1。
• 　　对包含复杂 OLTP 的工作负载或包含多个表上许多复杂连接的报告(例如，混合的 OLTP 和报告)，应使用优化级别 5。

　　对于需要大量数据统计数据分析、且运行时间较长(超过 1 分钟)的查询(例如，非常复杂的数据研究或决策支持)，使用优化级别 9。DB2 Optimizer 需要花费较长的时间来生成访问计划，但访问计划中改进的价值往往超出产生计划所花费的额外时间。

　　对 SQL 进行故障排除的最终思考

　　已经有许多书籍专门探讨如何改进 SQL 性能这个话题，本文不可能面面俱到地介绍查询性能调优。但在您开始使用 Visual Explain 对执行状况不佳的查询进行故障排除时，应牢记一些要点：

　　未充分地利用索引。查询是否按您的希望使用了索引?应确保未在您理所当然地认为应该具有索引的表上进行表扫描。此问题可通过查看查询的访问计划图表轻松应对。若确实存在索引，则检查基数或索引键的顺序。情况未必与您预计的相同。

　　表基数和 'SELECT *' 的使用。有时，由于您要返回的列数，DB2 Optimizer 会判定扫描整个表的速度更快。有可能表非常小，也有可能扫描索引并返回大量行(即返回表中的所有列)的效率很低。尝试仅返回那些您确实需要的列。查看查询各部分返回的列，观察您是否确实需要这些列，并观察这是否是表扫描发生的原因。同样，考虑使用索引中包含的列。

　　优化级别设置过低。许多 DBA 将优化级别降低到 1，以缩短查询准备所需时间。有的时候，将优化级别增加到 5 将允许优化器找到更佳的访问计划，而您不必创建一个新索引来改进性能。您在 Explain Query Statement 窗口中选择为查询生成解释信息时(参见图 9)，可轻松在 Visual Explain 工具中调整该值。还可在 Command Line Processor 中设置该值，执行以下命令即可：

　　SET CURRENT QUERY OPTIMIZATION [0|1|2|3|5|7|9]