如何使用 Optim Query Tuner 进行数据库性能调优，第 3 部分

基于 Optim Query Tuner 的应用性能调优和监控

简介

在数据库应用实际上线运营之后，数据库管理员需要对应用的性能进行监控和维护。但是，由于应用的复杂性，一个应用中可能有成百上千的查询语 句，当应用真正发生性能问题之后，很难在短时间之内快速定位到引起性能问题的查询语句。同时，当你对一个查询语句进行局部优化之后，也许整个应用的性能并 没有得到改善，所以需要对应用进行整体的性能分析。对于一个复杂应用来说，这是一件非常困难的事情。

Optim Query Tuner，简称 OQT，是一个性能调优工具。OQT 为数据库管理人员和应用开发人员提供了一系列工具对单个查询语句或者一组查询语句进行性能的分析和调优，包括性能监控、查询语句的定位、图形化的性能分 析、智能的优化建议、生成报表等等，极大的提高企业对突发性能问题的处理能力和解决速度。

在本系列的前两篇文章中，我们分别介绍了 Optim Query Tuner 的功能概况以及使用 OQT 进行单个查询语句的性能调优。本文是针对 OQT 系列教程的最后一部分。本文分两个章节，完整介绍了使用 OQT 对应用进行整体性能调优和监控的详细过程。

回页首

应用性能调优

使用 OQT 获取应用的详细信息

使用 OQT 进行应用性能调优主要有两种应用场合，一是当应用真正发生性能问题之后，使用 OQT 快速定位到应用中的性能瓶颈并使用 OQT 的优化专家进行修正，二是周期性地对应用的性能进行健康检查，主动进行性能调优，提高应用的性能。不管是那种应用场合，在进行应用性能调优之前，首先需要 获取应用的详细信息，包括应用中的相关查询语句及其运行时的信息，比如执行次数和执行时间等。在 OQT 中，我们称这样的一组查询及其相关信息为“Workload”（工作负载）。 OQT 提供了一个“向导”带领用户一步步获取应用中的关键查询及其相关信息，并把它们存储到 OQT 自定义的一套表中以供接下来的分析处理。

启动“向导”

OQT 提供了一个“Workload List”视图用于管理工作负载。在这个视图中，用户可以点击“New Workload”->“Wizard”菜单项启动一个向导，从应用中获取详细信息并生成一个新的工作负载，见图 1。
图 1. 启动“向导”

指定查询“源”

应用中的查询语句一般可以分为两类，一类是动态查询，一类是静态查询。动态查询执行之后会被缓存到 dynamic statement cache（动态语句高速缓存）中。静态查询经过预编译、编译和绑定之后会以 package（包）或者 plan（计划）的形式存储到 DB2 的 catalog（编目）表中。在“向导”的第二步中，用户需要指定一个“源”类型以供 OQT 获取应用中的查询语句。如果应用中的查询为动态查询，则选择“statement cache”作为“源”；如果应用中的查询为静态查询，则选择“catalog”作为源，见图 2。
图 2. 指定查询“源”

指定过滤条件

数据库中可能存在很多应用，每个应用中可能有成百上千的查询，而真正引起性能问题的查询又可能是其中的一小部分。为了准确定位到应用中的相关 查询，需要指定一些过滤条件。比如，对于“statement cache”，可以通过指定用户的 ID 和执行次数来缩小获取查询的范围。图 3 展示了如何从“statement cache”中获取由 ADMF001 执行了不少于 3 次的查询语句。有关详细信息，可以参考本系列文章的第二部分：使用 OQT 来对 SQL 语句进行优化。
图 3. 指定过滤条件

指定抓取类型

如图 4 所示，用户可以指定不同的抓取类型，获取应用中的查询。

• 立即抓取：根据指定的过滤条件立即进行抓取。对于静态查询，只能选择立即抓取。对于动态查询，用于动态缓存中的查询语句会不断的变化，新的查询不断进入， 旧的查询经过一段时间之后可能被清除出去。所以除了“立即抓取”之外，用户还可以使用如下方式获取应用中的查询。
• 一次抓取：指定一个将来的时间进行抓取，OQT 会在指定的时间到来之际，启动后台线程进行抓取。
• 多次抓取：指定一个开始时间，结束时间和间隔时间进行多次抓取。比如，设置开始时间为上午 9 点，结束时间为上午 10 点，间隔时间为 15 分钟。OQT 将每隔一刻钟进行一次抓取，一共进行 5 次抓取。
• 连续抓取：指定一个开始时间和间隔时间进行无限次抓取，直到用户退出 OQT 或者取消抓取任务。

图 4. 指定抓取条件

查看已抓取到的查询语句

根据用户指定的过滤条件和抓取类型，OQT 会启动后台线程对应用中的查询进行抓取，抓取到的查询语句及其相关信息会被存储到一个工作负载中。用户可以从“Workload List”视图中双击打开一个已有的工作负载，所有被抓取到的查询语句及其相关信息将被显示在一个叫做“Workload Tuning Editor”的界面中。这些信息包括查询语句的文本信息，执行次数，累计执行时间和平均执行时间等，见图 5。如果已抓取到的查询数目特别多，OQT 还提供了翻页功能，每页缺省显示 50 个查询语句。同时，用户还可以定制过滤条件，来减少显示的查询数目。
图 5. 查看已抓取到的查询语句

合并查询语句

这是一个可选的步骤。当工作负载中的查询语句除了“文本值”（literal value）之外完全相同，可以通过“合并查询语句”将这些查询语句合并成一条，见图 6。例如，下面的三条查询语句除了“文本值”（粗体部分）之外，其他部分完全相同。

 SELECT * FROM SYSIBM.SYSTABLES WHERE NAME= ’ T1 ’;
 SELECT * FROM SYSIBM.SYSTABLES WHERE NAME= ’ T2 ’;
 SELECT * FROM SYSIBM.SYSTABLES WHERE NAME= ’ T3 ’;

通过定义一个“合并文本值”的任务，这三条查询将被合并成一条查询。文本值部分将被替换成问号（question marker）

 SELECT * FROM SYSIBM.SYSTABLES WHERE NAME=?;

图 6. 合并查询语句

生成解释信息（EXPLAIN）

在对工作负载进行性能分析之前，还需要为工作负载中的每一个查询语句生成解释信息。如果这些查询语句是静态查询，并且在绑定的时候指定了 EXPLAIN YES 这个选项，那么在抓取这些查询语句的时候，对应的解释信息也会同时被抓取出来存储到工作负载中。否则，用户必须通过“EXPLAIN”对话框定义一个 EXPLAIN 任务，如图 7 所示。用户可以选择对所有的查询语句都重新生成解释信息，或者只对解释信息缺失的查询语句生成解释信息。
图 7. 生成解释信息

使用存储过程

如果应用中的查询数目特别大，无论是抓取查询语句、合并查询语句还是生成 EXPLAIN 信息等任务都比较耗时，所以 OQT 提供了对这些功能的存储过程实现。使用存储过程可以减少网络开销，从而提高处理速度。为了使用存储过程，需要进行如下配置：

1. 在主机端安装 Java 环境和 JCC 驱动。有关详细信息，可以参考 OQT 的在线文档
2. 从客户端或者主机端配置存储过程及其相关包。有关详细信息，可以参考本系列文章的第一部分：Optim Query Tuner 概述
3. 从“首选项”中选择“使用存储过程”，见图 8。

图 8. 使用存储过程

使用 OQT 分析应用的统计信息并生成优化建议

基于 workload 的 Statistic Advisor 是 OQT 用来分析应用中统计信息的一个有力工具，简称 WSA。相比于用来分析单条查询的 Statistic Advisor，WSA 会逐条分析每条查询，并将所有的推荐的 RUNSTATS 命令进行合并。此外，WSA 还会根据每条查询在 workload 之中的比重，推荐被引用次数最多的一些列和列的组合。

图 9 是 WSA 分析完一个 workload 后获得的 RUNSTATS 推荐方案：
图 9. WSA 的推荐

可以看到，推荐方案分为高低两个优先级。可以注意到此处的优先级顺序与 SA 正好相反，维护级别处于高优先级。之所以这样是因为 WSA 更偏向于对系统的维护，鼓励用户定期收集 workload 所有相关的统计信息。

在 WSA 给出的推荐方案报告之中，最左面的是 RUNSTATS 推荐栏。在高优先级的推荐之中，WSA 给出了两条 RUNSTATS 命令。点击右面的“Run…”按钮，就能执行这两条 RUNSTATS 命令。在执行完毕之后，用户还可以选择去验证新生成的统计信息。图 10 是 RUNSTATS 后的结果。
图 10. RUNSTATS 结果

在推荐界面点击“Details”按钮后，就能看到关于整个 workload 的统计信息报告。这个报告中包含了在 workload 里被引用过表及表上的列、列的组合和索引的统计信息。假如部分统计信息存在冲突，那么将会在统计信息报告文本框下面的统计信息冲突文本框之中显示出来。统 计信息冲突的含义是指，在数据库之中存在的几个统计信息存在互相矛盾的情况。比如在系统编目中，表的记录显示某个表一共有 1000 条记录；但是在有关列的记录之中，这个表的某一个列却包含了 1500 条不同的记录。显然这两者至少有一个值是错误的，这就是统计信息冲突的一个典型的例子。图 11 是 WSA 详细的报告。
图 11. WSA 详细报告

在推荐界面点击“Save into Profile Table”按钮后，OQT 会将当前在 RUNSTATS 命令推荐框中的 RUNSTATS 命令保存到数据库的表中。而当用户点击“Retrive from Server”按钮后，会从服务器得到以前保存过的相关表的 RUNSTATS 命令，并显示在右边的 Profile 框之中。

使用 OQT 分析应用的查询并生成优化建议

基于 workload 的 Query Advisor 是 OQT 用来分析应用中查询的一个有力的工具，简称 WQA。相比于单条查询，WQA 能够逐个分析应用中的每条查询，并且根据优先级生成一个综合报告，便于用户能够迅速定位到重要的问题查询。图 12 是一个综合报告的例子：
图 12. WQA 的报告

在这个报告中，我们能看到所有分析的查询条数共 22 条，有警告信息的查询条数 4 条，较低严重程度的警告信息 7 个。另外，我们还能知道较低严重程度的查询条数 4 条。因为一条查询里面可能包括了多个警告，所以这里 4 条查询里面包括了 7 条较低严重程度的警告信息。一般来说，我们只需要显示包括警告信息的查询。图 13 是包括警告信息的查询条目：
图 13. WQA 的警告信息

我们可以很容易的看到这些查询中包括的警告条数，以及它们相关的执行信息，例如执行次数，累计的执行时间，CPU 时间等等。选中任何一条查询，点击 Details 可以显示实际的 Query Advisor 的推荐，如何去重写查询以便获得更优性能等。

使用 OQT 分析应用的索引设计并生成优化建议

基于 workload 的 Index Advisor 是 OQT 用来分析应用中索引设计的一个有力工具，简称 WIA。相比于用来分析单条查询的 Index Advisor，WIA 具有更加强大的功能，能针对不同类型用户的需求，采用不同的策略，根据用户所制定的条件，推荐出更优的索引设计方案。图 14 是 WIA 分析完一个应用程序后获得的索引推荐方案：
图 14. WIA 的索引推荐方案

在这个方案中，我们可以看到 WIA 给这个应用中所涉及的 5 个表推荐了 13 个新的索引，并且估计了这些索引所需要的磁盘空间共是 380.01MB，能够获得的性能提高是 69 ％。这个数据是在 WIA 按照缺省设置运行后给出的推荐。一般来说这个设置适用于帮助现有的应用获得最优的性能，所以没有限制新的索引能够使用的磁盘空间。如果用户只希望获得可接 受的性能提高，不希望占用过多的磁盘空间，可以通过 WIA 所提供的 What-If 分析功能来实现。只需要点击 What-If Analysis 这个按钮，然后修改磁盘空间的限制，WIA 就会推荐出一组修正的索引设计，如图 15，我们限制磁盘空间为 200MB：
图 15. WIA 通过 What-If 分析限制磁盘空间后的推荐

在这个方案中，我们发现只有 4 个表推荐了索引，并且索引总数也下降为 8 个。当然因为限制了磁盘空间，所以最终的磁盘空间为 198.7MB，下降了 47.7%，性能提高为 61 ％，比原来 69 ％下降了 8%。所以通过这个调整，WIA 帮我们丢弃了那些消耗空间多，但性能提高不显著的索引，在某些情况下，特别是系统磁盘空间紧张的时候，不失为一个折衷的方案。

回页首

应用性能监控

在实际的数据库应用中，SQL 语句的执行效率很大程度上决定着整个应用的效率，因此对应用中 SQL 语句的运行进行监控对发现问题和优化性能都有重要意义。OQT 在 DB2 Profile 的基础上提供了监控解决方案，能够对数据库系统中的静态或者动态查询并进行监控。本文将详细介绍如何使用 OQT 监控器对数据库中运行的 SQL 进行监控和调优。

使用 OQT 监控数据库应用的日常运行以进行健康检查

创建一个日常运行的监控器

１． OQT 提供了一个“Monitor List”视图用于管理监控器。在这个视图中，用户可以点击“Monitor Profile”->“New”菜单项启动一个监控器向导，选择监控器类型为正常（Normal）来创建一个正常的监控器，见图 16。
图 16. 启动向导

２．制定被监控 SQL 语句的类型。监控器向导中提供了 SQL 语句类型的选项，分别提供对动态 SQL 语句和静态 SQL 语句的监控。在这里，选择监控动态 SQL 语句需要提供应用程序的 AUTHID 和 IP 地址；选择监控静态 SQL 语句需要提供已绑定的 Plan 的名称，或者提供完整的 Plan、Collection ID 和 Package 的名称，见图 17。
图 17. 选择监控 SQL 语句的类型

３．指定过滤条件。在这里可以选择是否抓取解释信息，还可以选择监控基本信息（执行时间和累计的 CPU 时间）或者监控全部运行时信息。监控基本信息对性能影响较小，如果监控全部信息则会增加 10%CPU 消耗，见图 18。
图 18 指定过滤条件

４．选择何时启动监控器

• 立即启动，监控器定义完成后立即启动。
• 按计划运行，手工指定监控器的启动时间，停止时间。
• 不启动，监控器定义完成后不启动，需要时通过“Monitoring”->“Start Monitoring …”来启动。
• 禁止，监控器定义完成后即被禁止，使用时需要先激活才能启动。见图 19。

图 19. 选择何时启动监控器

５．获取快照。创建好 Monitor 之后，OQT 自动创建一个同名的工作负载。在“Monitor List”界面上，通过“Schedule”->“snapshot”可以获取监控器快照，见图 20。在获取快照时，会将监控器抓取的 SQL 语句和运行时信息写入工作负载中。这样就可以通过工作负载来查看这些语句的详细信息，见图 21。
图 20. 获取快照

图 21. 监控器抓取的工作负载

使用 OQT 监控数据库应用中的异常查询并进行性能调优

OQT 还提供只监控性能异常的 SQL 语句的功能。

１． 启动向导并选择监控器类型为异常，这样就可以创建监控满足特定异常条件的 SQL 语句，见图 22。
图 22. 启动向导

２． 选择监控 SQL 语句的类型。

３． 指定过滤条件

在指定过滤条件时，我们可以选择使用 SQL 语句执行过程中的 CPU 耗费作为过滤条件，也可以选择相对的 CPU 时间耗费或者同时指定两个过滤条件，见图 23。
图 23. 指定过滤条件

４． 选择何时启动监控器。

５． 打开监控器对应的工作负载。在“Monitor List”界面上，选定监控器，通过“Open”按钮可以打开对应的工作负载，并将已捕获的异常 SQL 信息写入工作负载中，用户可以通过工作负载检查和分析这些信息，见图 24。
图 24. 打开工作负载

使用 OQT 监控与其他监控方案的比较

１． 与 DB2PM 的 IFI ACCTG trace 比较

ACCTG 是 DB2PM 产生的数据，集合了所有 SQL 语句的运行信息。从 ACCTG 数据中很难发现究竟是哪一条 SQL 语句造成了效率问题。而使用 OQT 监控却可以跟踪每一条 SQL 单独的运行信息，从而很容易找出造成问题的语句加以分析和修复。

２． 与 DB2 的 Dynamic Statement Cache 比较

对动态 SQL 语句来说，OQT 监控与 Dynamic Statement Cache 使用相同的 DB2 机制。与 Dynamic Statement Cache 相比，使用 OQT Monitor 可以只监控基本信息从而减少资源耗费；可以通过限定条件来控制监控对象的范围，比如指定 AUTHID 和 IP 地址；可以监控静态 SQL；可以监控特定的异常并生成报告。我们只有在收集所有的动态 SQL 语句信息时，才推荐使用 Dynamic Statement Cache.

３． 与 Query Monitor 或同类产品比较

类似 IBM Query Monitor 这样的外部工具，会提供更多的监控情景和更完整的信息。

回页首

结束语

Optim Query Tuner 提供了应用程序性能监控和调优的完整解决方案，适用于基于主机的应用开发，测试以及生产系统的管理，调优等。通过这个解决方案，传统的应用程序调优逐渐从 被动转为主动，从而更大程度的规避风险和降低成本。

参考资料

学习

• 通 过 IBM Optim 产品专题，获取更多 Optim 产品家族和集成管理的技术资源。
• 通过访问 IBM Optim Data Privacy Solution，了解 Optim 数据隐私保护解决方案的更多信息。
• 通 过访问 IBM Optim Solution，了解 Optim 集成数据管理的更多信息。
• 通过 Optim： 数据隐私：了解数据隐私的需求和 Optim 数据隐私技术的特点。

获得产品和技术

• 下 载 IBM Optim Developer 和 pureQuery Runtime的试用版本，开始开发您的应用程序。
• 下载 Optim Database Administrator 的试用版本。

讨论

• 查 看 Optim Development Studio 和 pureQuery Runtime 讨论论坛。
• 查看 Integrated Data Management 社区空间。

作者简介