DB2 Performance Expert 简化性能管理和调优 （2）

　您需要具体分析使您能够对 DB2 和 DB2 应用程序进行控制和调优的一些要害性能因素吗？您希望提前诊断性能和可用性问题吗？或者您曾在运用 DB2 服务器时遭遇某一问题，但却无法使用当前的快照判定造成该问题的原因，因此希望使用历史的监视数据？IBM DB2 Performance Expert 就是一个能够帮助您完成这些任务的工具。使用场景下面这些使用场景可以展示如何分析和解决各种性能问题，并在 DB2 Performance Expert V2.1 的帮助下完成故障检修任务：确定索引是否可以改进性能　;;;;;重新回顾排序的性能检查对表进行重构的需要确保有足够的 DB2 代理可以处理工作负载解决锁冲突的问题使用 cache 包中提供的 SQL 语句经常检查数据库分析缓冲池监视系统的健康状况确定是否需要索引DB2 PE 步骤在 System Overview 面板中选择 Application Summary。图 1. System Overview在 Application Summary 视图中选择适当的应用程序（在本例中是 db2bp.exe）。图 2. Application Summary在 Application Details 视图中选择 SQL Activity。图 3. Application Details方法图 3中给出的 SQL Activity 界面显示了有关应用程序执行的语句的信息，其中包括任务单元（UOW）、光标、读取的行、选择的行等等。要判定我们是否需要索引，需要查看读取的行与选择的行的比率。;读取的行与选择的行读取的行与选择的行的比率说明了为了要找到目标记录行，一共要读取多少行数据。假如读取的行数与选择的行数的比值大于推荐值，那么我们就应该对查询进行分析，并对可能的索引进行检查。计算：(读取的行数) / (选择的行数)理想值：对于 OLTP 来说，该值为 2 到 3结论DB2 读取了 99,145 行，但只选择了 2,000 行。这就是说，它读取了整个表的内容，却只选择了 2,000 行。因此，创建索引可能会提高性能。重新回顾排序性能　　DB2 PE 步骤在 System Overview 面板中选择 Application Summary。在 Application Summary 视图中选择适当的应用程序（在本例中是 db2bp.exe）。在 Application DetailsSelect 视图中选择 Sort，如 图 4 所示。图 4. Application Details方法Sort 界面中显示了有关排序操作的具体信息，其中包括所有排序、所有排序时间、排序溢出、hash 连接等。排序溢出这个数字说明了排序时用光排序堆而需要磁盘空间临时进行存储的行数。在数据库或应用程序级，使用这个元素可以计算溢出到磁盘上的排序的百分比。假如这个百分比很高，那么您可能希望通过增加排序堆来调整数据库的配置。在语句级上，可以使用该元素判定需要大型排序的语句。这些语句可以从减少所需排序数量的其他调优中获益。在出现排序溢出情况时，可能导致其他开销，因为假如需要将数据写入磁盘，那么排序需要一个合并阶段，这可能需要更多的 I/O。该元素为一条语句、一个应用程序或访问一个数据库的所有应用程序都提供了有用的信息。实质上，要排序的数据都会从缓冲池溢出到 TEMPSPACE 表空间中。;计算：(排序溢出行数) / (总排序行数)理想值：对于非 DSS 型的任务来说，该值为零或接近零的值结论在出现排序溢出的情况时，可能会造成额外的开销，因为假如要将数据写入磁盘，那么排序就会需要一个合并阶段，这可能需要更多的 I/O。为了避免出现这种溢出，可以增加排序堆的大小，并对查询进行分析，以确定查询是否需要使用索引。检查对表进行重构的需要DB2 PE 步骤在 System Overview 面板中选择 Statistic Details。图 5. System Overview在 Statistic Details 视图中选择 Tables，并选中 Receive table information。图 6. Statistic Details　　方法 Statistic Details 中的 Table 视图给出了有关表的具体信息，其中包括表名、数据库名、写入的行数、读取的行数、溢出的行数、表的文件 id、表的类型、页面重构等。访问溢出的行这个数字说明了对该表中溢出行进行存取（读和写）的数目。溢出行说明了数据中的碎片情况。假如这个数字很高，那么您可以通过使用 REORG 工具对表进行重构，从而提高表的性能，这会清理数据中的碎片。当一行数据被更新并且不再适合原来写入的数据页时，就会出现行溢出的情况。这通常是对 VARCHAR 列进行更新的结果，或者是执行 ALTER TABLE 语句的结果。页面重构：这个数字说明了需要对一个表进行重构的页数。对太多页进行重构可能会导致性能比优化插入方式的性能还要低。您可以使用 REORG TABLE 工具对表进行重构，从而消除数据碎片。您还可以使用 ALTER TABLE 语句的 APPEND 参数来说明插入某个表的所有数据都要附加到该表的末尾，以避免页面重构问题。在对行进行更新导致该行的长度增加的情况下，虽然页面可能有足够的空间来容纳新行，但是为了整理这段空间的碎片，可能需要对页面进行重构。或者，假如页面中没有足够的空间来容纳更大的行，就会创建一条溢出记录。您可以使用固定长度而不是可变长度的列来避免这两种情况。结论对太多页进行重构可能会导致性能比优化插入方式的性能还要低。假如您有大量的页面需要重构，可以使用 REORG TABLE 工具对表进行重构，并消除数据碎片。调优 DB2 代理的个数现在，您的目标是确保有足够多的 DB2 代理来处理工作负载。DB2 PE 步骤在 System Overview 面板中选择 Statistic Details。在 Statistic Details 视图中选择 Instance Information，如 图 7 所示。图 7. Statistic Details方法Statistic Details 中的 Instance Information 视图给出了有关当前实例的具体信息，其中包括实例名、当前的连接数、已注册的代理数、已注册最大代理数、等待令牌的代理数、从缓冲池中分配的代理数、已窃取的代理数等。已注册的代理该值说明了在正被监视的数据库治理实例中注册的代理（协调代理和子代理）个数。您可以使用这个元素来帮助评价最大代理配置参数的设置。已注册的最大代理该值是从数据库启动以来，曾经同时在数据库治理程序中注册的代理（协调代理和子代理）的最大个数。您可以使用这个元素来帮助评价最大代理配置参数的设置。等待令牌的代理该值是等待令牌以便在数据库治理程序中执行事务的代理的个数。您可以使用这个元素来帮助评价最大代理配置参数的设置。已窃取的代理：该值是从应用程序中窃取的代理的次数。当一个与应用程序关联的空闲代理被重新分配给一个普通的应用程序执行任务时，就会出现代理窃取。可以用该元素来评价应用程序对系统的负载情况。; 结论假如您发现有"等待令牌的代理" 或 "已窃取的代理"，就增大数据库治理程序中可用代理的个数（MAXAGENTS 和/或 MAX_COORDAGENTS）。解决锁冲突的问题DB2 PE 步骤在 System Overview 面板中选择 Locking Conflicts。图 8. System Overview在 Locking Conflicts 视图中选择适当的锁冲突。图 9. Locking Conflicts要分析等待某个锁的应用程序，请选择 Waiter（等待锁的）应用程序。图 10. 锁冲突的应用程序 —— waiter 应用程序在 Application Details 视图中选择 SQL Statement and Package。图 11. waiter 应用程序的 SQL Statement and Package　　在 Application Details 视图中选择 Locks。图 12. waiter 应用程序的 Locks　　为了对持有锁的应用程序进行分析，请选择一个 Holder（持有锁的） 应用程序。图 13. 锁冲突中的应用程序 —— holder 应用程序在 Application Details 视图中选择 SQL Statement and Package。;图 14. holder 应用程序的 SQL Statement and Package在 Application Details 视图中选择 Locks。图 15. holder 应用程序的 Locks　　要找到哪个用户正在运行 holder 应用程序，可以选择 Application Details 视图中的 Identification。图 16. holder 应用程序的 User Identification方法在 System Overview 面板中选择 Applications in Lock Conflicts，显示锁冲突所设计的所有应用程序，当您选择 Locking Conflicts时，这些应用程序都与一个非凡的资源相关联。Application in Lock Conflicts 面板显示了 holder 和 waiter 应用程序，其中包括应用程序的状态、锁的模式、锁等待时间等。Application Details 视图中的 SQL Statement and Package 展示了加锁的应用程序的 SQL 语句。Application Details 视图中的 Locks 显示了具体的加锁信息，例如应用程序所持有的锁、检测到的死锁、锁升级、等待锁的代理等。Application Details 视图中的 Identifcation 显示了有关正在运行该应用程序的用户的具体信息。应用程序所持有的锁：这个数字说明当前的应用程序持有多少个锁。假如监视信息是在数据库级上进行的，那么该值就是数据库中所有应用程序所持有的锁的总数。假如监视信息是应用程序级的，那么该值就是这个应用程序的所有代理目前持有的锁的总数。从连接以来等待的锁：这个数字是应用程序或连接已经等待锁的次数。在数据库级上，该值是应用程序在这个数据库上等待锁的次数。而在应用程序连接级上，该值是在某个连接请求一个锁但由于另外一个连接正持有该数据的锁而必须等待的次数。可以使用该元素计算在数据库级上等待一个锁的平均等待时间。这种计算可以在数据库级或应用程序连接级上进行。假如锁的平均等待时间很长，那么您应该查看一下持有很多锁的应用程序；或者假如是这种情况导致等待时间过长，就对该锁进行升级，从而重点对应用程序进行调优，以改进并发性。假如升级是导致锁平均等待时间很长的原因，那么可能是 locklist 或 maxlocks 这两个配置参数值中的一个太小了，也可能这两个参数值都太小了。锁升级这个数字说明了某个锁作为锁升级的一部分被升级的次数。升级的范围可以从（一个表中的）许多行锁到单独某个表锁。可以使用这个元素更好地理解死锁的原因。假如您曾经碰到过有关应用程序执行锁升级而导致死锁的情况，那么就可能希望增加锁内存的数量（locklist）或修改一个应用程序可以请求某个锁的百分比（maxlocks）。检测到的死锁该值是已经出现死锁的总数。可以用该元素来判定应用程序正出现争用问题。这些问题可能是由于以下情形引起的：数据库中正在进行锁升级。应用程序可能在系统生成足够多的行锁时显式地对表进行锁定。应用程序可能在绑定时使用了不恰当的隔离级别。为了可以重复读而锁定目录表。应用程序正在对不同的订单使用相同的锁，从而导致死锁。您可以通过判定死锁是在哪个应用程序（或应用程序进程）上产生的来解决问题。然后可以修改应用程序，使其能够更好地并行执行。然而，有些应用程序可能不能并行运行。您可以使用连接的时间戳监视元素，判定死锁的严重性。例如，在 5 分钟之内出现 10 次死锁就比在 5 小时内出现 10 次死锁严重得多。对上面列出的这些相关元素的描述还提供了其他一些调优建议。;等待锁的代理：该值说明了等待某个锁的代理个数。这个元素是应用程序等待某些锁的百分比的一个指示器。假如这个数字很大，那么您的应用程序可能存在并行问题，您应该对现在持有锁或者长期持有互斥锁的应用程序进行分析。结论:检查 waiter 和 holder 应用程序的 SQL 语句，确定诸如应用程序中频繁进行提交操作而导致释放锁或检查应用程序使用的隔离级别之类的操作。当执行很多更新时，在更新之前，要在整个事务的持续时间内锁定整个表。虽然这样可以只使用一个锁，同时还可以防止其他锁妨碍更新操作，但是这样做会减少数据对于其他用户的并发能力。经常使用 cache 包中的 SQL 语句进行检查DB2 PE 步骤在 System Overview 面板中选择 Statistic Details。在 Statistic Details 视图中选择 Dynamic SQL Statements。图 17. Dynamic SQL Statements下拉滚动条，查看语句的具体资料。图 18. 语句的具体资料方法Statistic Details 中的 Dynamic SQL Statements 视图给出了有关 SQL 语句的具体信息，其中包括访问的数据库、执行次数、已经过去的执行时间、最差和最佳的预备时间、排序，以及在选中 Receive statement cache information时每条语句占用的 CPU 时间等。通过点击如 图 17所示的标题中的 Executions列，可以按照执行次数对 SQL 语句进行降序排列，这样就可以看到执行最频繁的 SQL 语句。;执行次数该值是一条 SQL 语句已经被执行的次数。您可以使用该元素来判定系统中执行最频繁的 SQL 语句。每条语句占用的 CPU 时间这个数字说明了一条 SQL 语句占用的所有 CPU 时间。可以将该元素与“已经过去的执行时间和“每条用户语句占用的 CPU 时间一起使用，来评价语句的最大花费。最佳预备时间：该值是预备一条特定的 SQL 语句所需要的最短时间。可以用该值来判定编译耗时的 SQL 语句。最差预备时间：该值是预备一条特定的 SQL 语句所需要的最长时间。可以用该值来判定编译耗时的 SQL 语句。结论这个执行监视元素可以您帮助判定系统中执行最频繁的 SQL 语句。在本例中，某个查询运行了 500 次，并且进行了 500 次排序。这是进行查询优化的很好的一个选择，可以检查排序值，并验证是否需要创建新的索引。分析缓冲池DB2 PE 步骤在 System Overview 面板中选择 Buffer Pool Analysis。图 19. System Overview在 Buffer Analysis 中选择 File-> Generate new report。图 20. 缓冲池分析图 21显示了缓冲池跟踪报告的结果。图 21. 缓冲池跟踪报告下拉滚动条，查看缓冲池分析的具体内容。图 22. 缓冲池分析的具体内容方法 上一页12345678910下一页 Buffer Pool Analysis 中提供了缓冲池跟踪报告，它以 HTML 的格式显示，或者以可选的图形交互式报告格式显示。缓冲池命中率这个比率说明了为页面请求提供服务时，数据库治理器不需从磁盘装入页（即该页已经在缓冲池中）就能处理页请求的时间百分比。计算：BPHR = (1 - ((缓冲池数据物理读 + 缓冲池索引物理读) /(缓冲池数据逻辑读 + 缓冲池索引逻辑读) ) ) * 100%索引命中率这个比率表明了可以在缓冲池中找到的页面能够满足的对索引页的所有读请求所占的百分比。计算：IHR = (1 - (缓冲池索引物理读 / 缓冲池索引逻辑读) ) ) * 100%数据命中率这个比率说明了可以在缓冲池中找到的页面能够满足的对数据页的所有读请求所占的百分比。计算：DHR = (1 - (缓冲池数据物理读 / 缓冲池数据逻辑读) ) ) * 100%结论缓冲池命中率大于 80% 被认为是理想的。对于 OLTP 系统来说，该值的理想情况是尽可能接近于 100% （索引命中率更是如此）。要提高缓冲池的命中率，可以增加缓冲池的大小，也可以考虑分配多个缓冲池，可以为每个经常访问的具有自己的表空间的大型表使用一个缓冲池，也可以为一组小型表使用一个缓冲池。监视系统的健康状况DB2 PE 步骤在 System Overview 面板中选择 System Health。图 23. System overview在导航器中选择 Data View。图 24. Data view　　右击 Open Predefined Data View。图 25. Open Predefined Data View　　选择您要监视的数据库。图 26. Open Predefined Data View - 选择数据库图 27给出了系统健康状况视图的一个例子。图 27. System Health View方法 System Health 视图以图形化的方式在数据视图中显示了很多重要的性能计数器。您可以使用预定义的数据视图，也可以定制自己的数据视图。结论 System Health 是一个理想的图形化监视重要性能指标的工具。一旦定义之后，它们就可以用来在 System Overview 面板中显示系统性能数据。结束语 本系列文章的第1部分对 DB2 Performance Expert 进行了简介，第2部分又展示了可以用来简化数据库调优任务和系统治理工作的具体方法。您可以使用 DB2 PE 来帮助理解影响性能的多个因素，例如索引、缓冲池的使用、语句缓存、锁、重构要求等等。另外，它还可以用来存储性能数据，供以后分析，并根据您定义的各种因素产生警报。致谢 非凡感谢 IBM 开发实验室的 DB2 性能专家 Ute Baumbach，是他审校了这篇文章；感谢 IBM 美国高级技术支持中心的 Cintia Y Ogura，是他为本文提供了教学材料。;