如何使用 Optim Query Tuner 进行数据库性能调优，第 2 部分,使用 Optim Query Tuner 对 SQL 语句进行查询优化

使用 Optim Query Tuner 对 SQL 语句进行查询优化

秦 玮, 高级软件工程师, IBM

秦玮，IBM 中国软件开发中心，高级软件工程师，从事数年 DB2 Universal Database 性能调优工作，目前为 IBM Optim Query Tuner 开发人员。

梁 高中, 高级软件工程师, IBM

梁高中，高级软件工程师，2005 年加入 IBM CDL， 一直从事 Data Studio Optimization Query Tuner 产品研发工作， 具有一定的 DB2 系统性能调优专长，并擅长 Java 和基于 Eclipse 的软件开发和测试，以及其他开源技术。

赵 国斌, 软件工程师, IBM

赵国斌，IBM 软件工程师， 2007 年加入 IBM CDL，一直从事和 DB2 优化相关的技术工作，对调优 SQL 的运行效率积累了一些经验，目前从事 OQT 的开发工作。

简介： 本文介绍了如何使用 OQT 来对 SQL 语句进行调优，其主要功能包括：Statistics Advisor（统计信息调优专家），Query Advisor（SQL 语句优化专家），Access Path Advisor（访问路径优化专家），Index Advisor（索引优化专家），Visual Plan Hint（可视化优化提示），Capture SQL（数据源定义），Query Annotation（SQL 语句注释）和Access Plan Graph（访问路径图）。

发布日期： 2010 年 5 月 06 日
级别： 初级

平均分 （共 3 个评分 ）

在对 OQT 进行了基本的配置之后，就可以开始使用 OQT 来对 SQL 语句进行各种调优工作。OQT 的功能可以分为几个部分：针对特定问题提供各种优化建议的专家系统工具；以及提供诊断信息的报告工具。

提供优化建议的专家系统工具包括：Statistics Advisor（统计信息调优专家），Query Advisor （SQL 语句优化专家），Access Path Advisor（访问路径优化专家），Index Advisor（索引优化专家）和 Visual Plan Hint（可视化优化提示）。

诊断信息报告工具包括：Capture SQL（数据源定义），Query Annotation（SQL 语句注释）和 Access Plan Graph（访问路径图）。

下面我们将逐一介绍上面提到的各个 OQT 功能。本文里的 SQL 语句所引用到的表，均来自 TPC-D 标准中所定义的表。关于 TPC-D 标准的详情，请参见参考资源。

使用 Capture SQL 识别查询性能低的语句

在数据库应用开发和性能调优的过程中，用户首先要做的就是定位存在问题的查询语句，然后才能对其进行分析以期提高该查询语句的性能。但由于很 多开发人员往往缺乏数据库调优方面的经验，因此需要借助有效的工具来辅助其定位问题。

OQT 的 Capture SQL 模块（捕获 SQL 模块）可以帮助数据库应用开发者和数据库管理员准确地定位问题查询。Capture SQL 模块可以从多种数据源 Catalog（数据库系统表）、Statement Cache（数据库的 SQL 语句缓存容器）和 SQL 存储过程等中抽取查询语句，并使用 Capture SQL 分析功能定位存在问题的语句。从而减少用户的调优成本，提高工作效率。

借助 OQT 的 Capture SQL 模块来定位问题查询主要分为两个步骤。首先用户需要选择一种数据源，即可能存在问题查询的地方。目前 OQT 支持的数据源有用户输入、Statement cache、Catalog、SQL 存储过程和操作系统普通文件等。

选择好数据源之后，用户就可以定义一些过滤条件来查找问题查询。需要注意的是，不同的数据源分别对应于一组不同的过滤条件供用户选择定义。用 户需要理解这些过滤条件的含义，才能很好的应用它们来定位问题查询。

下图 1 和 2 定义了一组过滤条件，目的是查找包 (Catalog Package) 名字为’ DSNTEST ’中估计消耗 CPU（处理器）执行时间大于 500 毫秒的问题查询。
图 1. 定义过滤条件示例 1

图 2. 定义过滤条件示例 2

此外，为了方便用户管理已经定义的过滤条件组合，用户可以将其保存成试图（View），以便于日后的再次查阅和修改。

回页首

使用 Query Annotation 格式化标注 SQL 语句

当用户分析比较复杂的 SQL 语句时，通常要采用手工的方式对其进行格式化整理，这样才能便于用户理解该 SQL 语句的含义。然而手工整理的方式不仅繁琐、低效，而且受人为因素制约容易出错。因此，OQT 提供了自动化工具 Query Annotation（SQL 标注工具）。该工具不仅可以帮助用户对 SQL 语句进行格式化整理，使其便于阅览；而且可以对 SQL 语句中所涉及的 Table、Column 以及 Predicate 进行注释。借助这些注释信息 ( 包含统计信息和估计代价信息 )，用户可以方便地对该 SQL 语句进行性能分析。

下图 3 展示的是采用该工具对一个较复杂的 SQL 查询语句进行处理的结果：
图 3. Query Annotation 结果示例

结果分为两部分：一部分是格式化后的 SQL 语句；另外一部分是对该 SQL 语句中所引用的 Table、Column 和 Predicate 的注释部分。可以发现，SQL 语句按照其各组成部分分别进行了整理、对齐，各组成部分一目了然，该查询所要表达的含义很容易就被展现出来。

同时，为了便于用户分析 SQL 语句，该工具提供了高亮显示其它相关行的功能。用户可以在格式化后的 SQL 语句中点击任意一行，其相关行将被高亮显示。如用户可以点击 FROM 子句中的一个表，则 SELECT 子句中以及 WHERE 子句中引用到该表的行将被高亮显示。如下图 4 所示：
图 4. Query Annotation 高亮功能示例

OQT 自动化工具 Query Annotation 处理结果的另外一部分内容是对 SQL 的注释部分，即对该 SQL 语句中所引用的 Table、Column 和 Predicate 的注释部分，如 Table 中总共有多少行、Table 中满足查询条件的行数、Predicate 的估计过滤率等一些 CATALOG 中的统计信息和 SQL 执行时的估计代价信息。通过这些注释信息，用户可以方便的分析该 SQL 的性能。

同时，该工具还提供了另外一项高级功能，即格式化显示经过数据库引擎转换后的 SQL 语句。感兴趣的用户可以参阅自动化工具 Query Annotation 关于转换后（Transformed）的部分。

回页首

使用 OQT 图形化展示数据访问计划

Access Plan Graph( 图形化数据访问计划 )，简称 APG，是 OQT 的一项重要功能部件。它通过图形化的方式展现 DB2 的数据访问计划，各 mini-plan 的组成及连接细节，并可查阅各节点的数据字典和统计信息。可视化的数据访问计划能够帮助更好地理解优化器所选择的数据访问计划，更加容易地发现性能的瓶 颈。
图 5. 图形化数据访问计划示例

上图 5 是 APG 展示的数据访问计划。

APG 的界面基本上分成两个部分，第一个部分（如图示 1 所示）是图形化数据访问计划显示区，从图中我们看到数据访问计划是以树状的结构进行展现，不同类型的节点会以不同形状及颜色表示，同时 APG 中对这些显示属性提供了定制功能。为了方便浏览图形及图形节点中的数据，APG 还提供丰富的图形操作功能，例如放大、缩小、图形变换、节点浮动信息提示、以及节点上丰富的右键菜单等。

例外 APG 还提供灵活的打印功能，如图 6 所示，例如你可以选择打印所有查询块（Query block）中的图形，打印指定查询块（Query block）或打印选定的图形区域以及格式化后的 SQL 文本等。
图 6. 打印界面

界面的第二部分（如图示 2 所示）是功能选择卡。位于最上层的功能卡是 APG 概要显示区，这个区域显示当前打开的数据访问计划的概要信息。位于中间的是节点详细信息显示区，如下图 7 所示：
图 7. 节点详细信息显示界面

当在图形显示区选中某一个节点时，该节点的详细信息例如数据字典及统计信息，将会被显示在这个选择卡中。为方便信息的浏览，这些信息以树状的 结构进行归类和组织。

最后一个选择卡是数据访问计划数据查询区，如下图 8 所示，当打开的数据访问计划非常庞大时（可能包含上百个节点），我们常常需要很快定位到我们关心的节点，例如我需要快速查看所有表扫描类型的节点，这时数 据查询功能将带来便利。
图 8. 数据访问计划查询界面

回页首

使用 Query Advisor 优化 SQL 语句

数据库管理员（DBA）和数据库程序开发人员通常很难了解到 DB2 内部处理每一条 SQL 语句的细节。某些情况下，用户的 SQL 查询语句写的不够好也可能导致 DB2 运行时性能不佳。那么什么样的 SQL 语句算是“好”的，什么样的是“不好”的？针对这种问题，OQT 中的 Query Advisor 提供相应的功能，来指出用户使用的 SQL 语句中可能存在的性能问题，并给出相应的解决建议。

下面介绍一下 OQT 中 Query Advisor 的使用。首先在 Query Tuner Project 中的 Query Text 里输入 SQL 查询语句（如图 9 所示）：
图 9. 在 OQT 中输入 SQL 语句

然后在右侧菜单栏中点击“Choose Tuning Activities”，选择“Query Advisor”，此时“Query Format and Annotation”会自动被选中。点击“OK”就可以运行 Query Advisor。
图 10. 运行 Query Advisor

对于之前给定的 SQL 语句，Query Advisor 给出的分析结果如图 11 所示。我们可以看到，在“Advisor Recommendation Overview”中列出了 Query Advisor 给出的若干个建议。按照可能对性能造成的影响程度，这些建议可以划分为“高”、“中”、“低”三个等级。
图 11. Query Advisor 分析结果

当我们在“Advisor Recommendation Overview”中点击其中某一条分析建议时，其对应的详细分析结果会显示在下方的“Query Recommendation Detail”窗口当中，如图 12 所示。
图 12. Query Advisor 分析结果详情

在“Query Recommendation Detail”中，Query Advisor 用下划波浪线在 SQL 语句中明确标记了可能带来性能问题的可疑点；另外，Query Advisor 对每一条建议给出了详细的说明（Recommendation Details）和理论上的解释（Explanation），以及相关的示例（Example）。

Query Advisor 是一个基于 DB2 数据库优化经验法则的专家系统工具，根据用户输入的 SQL 查询语句，它自动的给出修改建议，用户可以根据这些建议来修改他们的 SQL 语句，从而得到查询性能上的提升。

回页首

使用 Access Path Advisor 优化访问路径

DB2 中的访问路径（Access Path）是分析 DB2 性能问题时的主要信息来源。经验丰富的数据库管理员在遇到运行性能不佳的 SQL 语句时，往往是通过查看 DB2 的 PLAN_TABLE 中的信息来分析该条 SQL 语句的访问路径，找出其中的性能瓶颈。但是这要求用户具有非常丰富的 DB2 调优经验才能做到，而且这样人工分析的方式耗时耗力，在处理非常复杂的 SQL 语句时候可能会力不从心。

针对这样的问题，OQT 提供了 Access Path Advisor，它可以分析给定 SQL 语句的访问路径，自动进行分析并找出其中可能存在性能问题的访问计划（Access Plan），提醒用户注意这些潜在的问题。

下面介绍 OQT 中 Access Path Advisor 的使用。在 Query Tuner Project 中的 Query Text 里输入 SQL 查询语句之后，在右侧菜单栏中点击“Choose Tuning Activities”，选择“Access Path Advisor”，点击“OK”就可以运行 Access Path Advisor。
图 13. 运行 Access Path Advisor

对于之前给定的 SQL 语句，Access Path Advisor 给出的分析结果如图 14 所示。我们可以看到，在“Advisor Recommendation Overview”中列出了 Access Path Advisor 给出的若干个建议。按照可能对性能造成的影响程度，这些建议可以划分为“高”、“中”、“低”三个等级。
图 14. Access Path Advisor 分析结果

当我们在“Advisor Recommendation Overview”中点击其中某一条分析建议时，其对应的详细分析结果会显示在下方的“Query Recommendation Detail”窗口当中，如图 15 所示。
图 15. Access Path Advisor 分析结果详情

在“Query Recommendation Detail”中，Access Path Advisor 对每一条建议给出了详细的说明（Recommendation Details）和理论上的解释（Explanation），以及相关的示例（Example），除此之外，Access Path Advisor 还给出了该条建议对应的 PLAN_TABLE 的记录，便于用户对照 Access Path Advisor 给出的建议查看相应的访问计划。

Access Path Advisor 是一个基于 DB2 数据库优化经验法则的专家系统工具，根据用户输入的 SQL 语句的访问路径，它自动的分析查找其中的可疑点并提醒用户注意。需要指出的是，Access Path Advisor 并不会直接给出修改建议。如果用户想直接对 SQL 语句的访问路径进行修改，可以使用 OQT 当中的其他专家系统工具，比如 Visual Plan Hint（可视化优化提示）。

回页首

使用统计信息调优专家分析查询语句

说到数据库优化问题，DBA 的第一反应就是想到统计信息，RUNSTATS，统计信息的完整性与否，很大程度上决定了查询语句性能的好坏，针对一条有性能问题的查询语句，他面对的是 这条查询语句相关的很多对象，以及这些对象的统计信息，如何能准确的发现当前的哪些统计信息是被 DB2 优化器用到的，并且和实际数据不一致？如何能准确用 RUNSTATS 语句来收集这些必要的统计信息呢？ OQT 统计信息调优专家能够帮助 DBA 完成这些工作。

下面是 OQT Query Text 窗口，这是 OQT 分析查询语句的主窗口，用户可以把之前找到的有性能问题的查询语句粘贴到 Query Text 窗口，在窗口右侧，选择执行 OQT 统计信息调优专家“Choose Tuning Activities”->“Statistics Advisor”，如图 16 所示。
图 16. 运行 Statistic Advisor

等执行完毕，在弹出的“Advisor Recommendation Overview”内容页中， OQT 统计信息调优专家给出了针对该查询语句的统计信息方面的建议，如图 17 所示，可以看到，在列表中有两个建议，它们的建议级别分别是 HIGH 和 MAINTAINANCE 级别 , 列表的第三列是对建议的简单描述信息。
图 17.Statistic Advisor 分析结果

用户通过点击任何一个建议，“Advisor Detail”窗口将会弹出，如图 18 所示，图中最上面的部分编号 1，是统计信息调优专家给出的完整的 RUNSTATS 语句，中间部分 2 描述了这条查询语句所涉及到的表，索引，列和列组所搜集到的详细的统计信息和分析结果，比如哪些统计信息时缺失的，哪些是过时了的，由于这部分信息非常的 多，OQT 在部分 2 左侧为用户提供了对这些信息和分析结果的搜索查询的功能，在最下面部分 3，是对当前系统中的统计信息有矛盾的地方进行的一个总结。

下图 18 所示：
图 18.Statistic Advisor 分析细节

下面来介绍一下部分 4 的内容，用户可以通过工具条，方便的查看表，索引，列和列组的详细的或者概括的统计信息和分析结果；可以通过运行操作按钮直接运行这条 RUNSTATS 语句，对相关统计信息进行补全；并且将相应信息保存到 PROFILE 表中；相反，可以获得数据库端保存在 PROFILE 表中的之前执行的 RUNSTATS 命令。

回页首

使用 OQT 索引顾问分析查询语句

索引设计在数据库性能调优中发挥着重要的作用，好的索引经常能够大幅度的提高查询性能。然而，随着数据库查询越来越复杂，即使是有经验的 DBA，要设计出适合查询的最优索引也是一件耗时、耗力并且容易出错的工作。OQT 索引顾问能够帮用户自动分析查询语句的特征，并且根据当前数据的实际状况，迅速提供出满足用户要求的最优索引方案。本文将会通过实际的例子介绍如何使用 OQT 索引顾问以及相关的重要功能，例如结合 What-if 分析来评估索引推荐和用户自定义索引等。

在 OQT 的 Preference 页面中，可以对索引顾问进行全局参数的设置，如下图 19 所示。
图 19.Index Advisor 的参数设置

在一个 Query Tuner Project 中，将查询语句拷贝到 Query Text 窗口，在窗口右侧，选择执行 OQT 索引顾问“Choose Tuning Activities”->“Index Advisor”，如图 20 所示。
图 20. 运行 Index Advisor

等待执行完毕，OQT 弹出 Advisor Recommendation Overview 页面，如图 21 所示，这个内容页将列出 OQT 索引顾问的推荐项。通过该页面的右侧工具项，用户可以调用其它的专家模块对该查询语句进行分析。
图 21.Index Advisor 运行结果

用户可以单击任一建议，来查看这个推荐项的详细信息，“Advisor Detail”窗口将会弹出，如图 22 所示，中间的部分 2 是索引顾问推荐的索引的详细情况，包括每个索引包含的关键列，以及可能占用的磁盘空间大小等信息，用户也可用对这些推荐的索引进行添加，删除和修改，最上 面的部分 1 是一组预测值，如果系统创建了推荐的索引，该索引能给查询语句带来的性能提升百分比和该索引需要占用的磁盘空间大小，最下面的部分 3 描述了当前系统中存在的与查询语句相关的索引信息。
图 22.Index Advisor 分析细节

下面我们来介绍一下图 22 中右边部分 4 工具条的用法。

“Show DDL”， 用户可以查看左侧中选中的推荐索引的 DDL。

“Run DDL”, 用户可以创建左侧选中的推荐索引。

“Run What-if”， 用户可以运行 What-If 功能，查看 DB2 优化器对存在索引和推荐索引是否采用，该功能运行的结果，将会以图形化的方式展现出来，如图 23 所示。
图 23.What-if 运行

回页首

使用 OQT 优化器提示定制执行计划

优化器提示 (Optimization Hint) 是 DB2 为有经验的 DBA 提供的一种机制 , 它允许 DBA 可以按照自己期望定制执行计划 , 例如 , 在某些情况下 , 当优化器 (Optimizer) 可能选择不是最佳的执行计划时 , DBA 通过优化器提示来调整优化器 , 例如通过选择更好的索引等 , 以获得更好的数据库性能。在没有借助工具情况下使用优化器提示，DBA 常常会发现这是一个比较繁琐而且非常容易出错的过程。本文将详细介绍如何使用 OQT 中的 Visual Plan Hint（可视化优化提示）可视化地使用优化器提示。该工具将提供对优化器提示整个生命周期的支持，从创建到验证最后到部署。在它的帮助下，使用优化器提 示将变得更加容易、高效。

优化提示的可视化定制

在 Visual Plan Hint 中提供了可视化的优化提示定制界面，可以基于现有的执行计划定制优化提示，例如下图 24 所示：
图 24. 可视化的优化提示定制界面

对于给定的 SQL，在初始状态下，Visual Plan Hint 会自动生成当前执行计划的图形化表示，一方面它会帮助你更好地理解你的 SQL 以及当前的优化器所选择的执行计划，另一方面在它的基础上，你可以很方便地定制优化提示，例如如图 25 所示，当你在【 Join Graph 】中双击一个表节点时， 在弹出的对话框中便可对该表的访问方式进行定制 :
图 25. 可视化的优化提示定制实例 1

又如通过鼠标拖拽的方式就可以定制你自己的表连接顺序，如下图 26 所示：
图 26. 可视化的优化提示定制实例 2

优化提示的验证

当你完成优化提示的定制后，下面一步就是验证你的优化提示是否真的发布到数据库上后可被优化器采纳。在这一步骤中 Visual Plan Hint 会帮助将你在界面中定制的优化提示发送到数据库进行验证，并生成验证分析报告。验证分析报告如下图 27 所示：
图 27. 验证分析报告界面

优化提示的部署

经验证，当在界面中定制的优化提示可以满足要求时，便可进入最后一个步骤，Visual Plan Hint 可以帮你将优化提示真正地发布到数据系统中，并最终生成发布分析报告。

回页首

结束语

OQT 提供的多种功能可以帮助数据库管理员和开发人员定位性能不佳的 SQL 查询语句，分析其在 DB2 中的访问路径，并给出相应的提示和优化方法，从而有效的解决生产和开发过程中出现的数据库查询性能问题。

参考资料

学习

• 通 过 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 社区空间。

作者简介