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

赵 旭芹, 软件工程师, IBM

赵旭芹，IBM 中国开发中心的软件工程师，从事 Query Tuner 产品的安装开发和软件功能开发，已完成 3 个版本的安装开发工作，熟悉 IBM Installation Manager 的使用。

杨 锐, 软件工程师, IBM

杨锐，软件工程师，2008年加入 IBM，一直从事 DB2 JCC 驱动的测试工作。

高 远, 软件工程师, IBM

高远，IBM 中国开发中心的软件工程师，他 2008年加入 IBM，主要从事 Optim Query Tuner 产品开发工作，在基于 Eclipse 的插件开发开发、Web 开发方面有一定的经验。

简介： 当前企业用户面临的一些挑战，应用开发人员缺乏足够的性能调优知识，而数据库管理员所需要管理的企业级应用日益复杂，本 文从一个用户实际案例入手，介绍了如何使用 OQT 进行查询调优的大致过程和期间用到的各种组件。

发布日期： 2010 年 4 月 29 日
级别： 初级

平均分 （共 4 个评分 ）

前言

一个数据库性能调优的真实案例！

A 银行是一家中等规模的银行，提供多种金融产品和服务，近年来发展迅速，业务量增长很快。A 银行开发构建了一套业务应用系统。这套业务应用系统自从上线以来，一直运行状况良好，未曾出现性能问题。数据库对交易的平均响应时间为 3 秒左右。对这一性能状况，客户比较满意，能够满足当时业务应用的需要。

一天早上，柜员们发现终端的处理速度显著变慢，很多业务最后提交后迟迟得不到响应，最终无法办理。由于查询性能急剧下降，对交易的平均响应时 间已经升高到了 5 分钟。这意味着此业务应用系统已经完全无法使用，数据库监控程序显示系统已经完全瘫痪。必须马上解决这一问题。用户数据库应用较为复杂，经过努力，找出了 严重影响数据库性能的查询，却 a 发现这条查询并没有人为的修改记录。

是什么原因导致了这条查询执行速度缓慢 ? A 银行请来数据库专家来协助解决问题。经过专家的分析和研究，发现与这条查询执行速度密切相关的数据库统计信息严重缺失或不准确。用户收集了数据库的对象的 统计信息以后，查询的平均响应时间恢复到了事发前的水平。专家在深入了解了 A 银行业务应用系统的数据库物理结构，发现了与查询性能密切相关的索引设计也存在问题，一些可以提高查询性能的索引没有建立，而有些现有的索引也不是最佳 的。优化了索引设计以后，数据库的响应时间降低到 10 毫秒，性能有了非常大的提升。上述数据库性能的调优过程及性能指标可用下图表示。
图 1. 用户应用性能

从 5 分钟到 10 毫秒，这一过程经历了 5 天，A 银行的业务应用系统处于完全中断状态，这一损失是巨大的。由此可见，数据库的性能调优对于系统的正常运行是非常重要和必要的。目前，很多数据库应用程序不 实施或者很少实施数据库性能调优，主要原因有两个：

• 数据库开发人员更关注如果通过查询实现业务逻辑，对查询的性能问题重视不足，由于开发时间紧迫，很少进行性能相关的测试。此外开发人员对数据库调优所需的 专业知识也了解有限；
• 因为数据库调优这一领域专业性较强，如果没有专家参与，很难获得好的效果。

尽早进行数据库性能的优化，就会越多地降低成本和风险。人工解决性能问题也存在响应较慢，对目标数据库专业调优知识要求较高，若数据库应用较 为复杂则需要较长时间分析性能瓶颈，不利于调试查询语句较多和系统设计复杂的应用。若采用自动调优工具，则在降低开发和维护人员对数据库专业调优知识的同 时，快速解决数据库应用的性能问题。IBM Optim Query Tuner 针对上述问题提供了一整套解决方案，包括各种调优工具，方便用户快速定位解决应用中的性能问题。

IBM Optim Query Tuner 能够帮助用户进行查询调优以及性能监控。IBM Optim Query Tuner 能够针对查询提供综合的专家建议，例如索引建议、统计信息建议、访问信息建议等等。

使用 IBM Optim Query Tuner 调优的工作流程。
图 2. 调优的工作流程

回页首

获取查询语句

IBM Optim Query Tuner 系列产品支持从多种数据源定位问题 SQL 查询并进行性能调优。这些 SQL 查询可以是手工输入的，也可以是从文本文件获得的，还可以从 IBM Optim Query Tuner 自带的 SQL 分类管理器选择的。此外，对于 DB2 for Linux, Unix and Windows 还可以从 DB2 包（package）中获得 SQL 查询；对于 DB2 for z/OS 还支持下列数据源：

• 查询缓存（Statement Cache）
• DB2 系统编目中的查询计划（Plan）和包（Package）
• Query Management Facility
• Query Management Facility HPO
• 存储过程
• EXPLAIN 输出的查询计划表、查询表和用户自定义函数表（Plan Table/Statement Table/Function Table）
• IBM Query Monitor for z/OS
• IBM Optim Development Studio 2.2 with pureQuery
• IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS

回页首

单条查询（single query）语句调优

Query Tuner 提供一系列的调优专家和调优工具对 SQL 语句进行可视化分析，从而帮助用户修正性能问题。下图是 Query Tuner 对一条查询语句分析结果的总览。
图 3. 调优结果总览

Query Tuner 提供了内容丰富，功能强大的调优专家组件。包括：查询调优专家（Query Advisor）、访问路径调优专家（Access Path Advisor）、统计信息调优专家（Statistics Advisor）和索引调优专家（Index Advisor）。下面对其逐一介绍：
图 4. 调优专家

查询调优专家（Query Advisor）：

DBA 和数据库程序开发人员通常很难了解到 DB2 内部处理每一条 SQL 语句的细节。某些情况下，用户的 SQL 查询语句写的不够好也可能导致 DB2 运行时性能不佳。基于一系列最佳实践规则提供查询重写建议。
图 5. 查询调优专家

访问路径调优专家（Access Path Advisor）：

Access Path Advisor 用于帮助用户进行查询调优以避免非最优访问路径。访问路径分析是基于 Explain Model 内的信息进行的，Access Path Advisor 除提供给用户相应警告及解释说明信息，同时也呈现了 PLAN_TABLE 信息，令分析结果更明了清晰。
图 6. 访问路径调优专家

统计信息调优专家（Statistics Advisor）

DB2 优化器会基于统计信息来创建访问计划。过时的甚至错误的统计信息可能会让 DB2 优化器产生不好的访问计划。统计信息调优专家能够分析查询语句，发现可能存在问题的统计信息，生成重新收集统计信息的 RUNSTATS 命令，并通过执行 RUNSTATS 命令来收集准确的统计信息，让 DB2 优化器产生最优的访问计划。
图 7. 统计信息调优专家

索引调优专家（Index Advisor）

索引调优专家能够帮用户自动分析查询语句的特征，并且根据当前数据的实际状况，迅速提供出满足用户要求的最优索引方案。
图 8. 索引调优专家

除了上面介绍的调优专家组件之外，Query Tuner 还提供了一些实用的调优工具帮助用户方便、清晰地对查询进行展现和调优。

访问路径图（Access Plan Graph）

Access Plan Graph 通过图形化的方式表现 DB2 的数据访问路径，展现各 mini-plan 的组成及连接细节，并可查阅各节点的日志和统计信息。
图 9. 访问路径图

查询注释工具（Query Annotation）

Query Annotation 不仅可以帮助用户对 SQL 语句进行格式化整理，而且可以对 SQL 语句中所涉及的表（Table），列（Column）以及谓词（Predicate）提供统计信息和估计代价信息，用户可以方便地对该 SQL 语句进行性能分析。
图 10. 查询注释工具

可视化优化器提示（Visual Plan Hint）

优化器提示 (Optimization Hint) 是 DB2 为有经验的 DBA 提供的一种机制 , 它允许 DBA 可以按照自己期望定制执行计划 , 例如 , 在某些情况下 , 当优化器 (Optimizer) 可能选择不是最佳的执行计划时 , DBA 通过优化器提示来调整优化器 , 例如通过选择更好的索引等 , 以获得更好的数据库性能。在没有借助工具情况下使用优化器提示，DBA 常常会发现这是一个比较繁琐而且非常容易出错的过程。借助 Visual Plan Hint，使用优化器提示将变得更加容易、高效。
图 11. 可视化优化器提示

查询报告（Query Report）

用户在使用查询语句过程中，如果遇到问题需要解决，势必要了解相关的信息，比如涉及到的表，索引，谓词以及相应的统计信息。Query Report 就为用户提供了这个功能，将给定的一条或者一组查询语句所涉及到的信息进行解析整理，给出完整有序地分析报告。
图 12. 查询表报告

图 13. 查询索引报告

图 14. 查询谓词报告

回页首

工作负载（Workload）性能调优

Workload 就是指一组在语法、语义或是在其他方面都联系紧密的一组查询语句。 比如来自同一个应用程序的查询语句，他们访问同一套数据库定义，可以在逻辑上组成一个 workload。这样对这组关联查询进行调优，实际上就提高整个应用程序的性能。也可以监测访问数据库的全部查询， 这样对此 workload 进行调优，则提高了整个数据库的性能。Optim Query Tuner 帮助 DBA 和应用开发者可以快速的定义管理 workload，并提供了一整套优化专家在 workload 的基础上分析索引性能，收集关键统计信息，优化资源配置。

应用 workload 合进行性能调优时，并不是简单针对单个查询调优的累加。而是在 workload 基础上综合考虑。比如数据库目前的索引设计不够完善，新建索引可以提高查询语句的性能， workload index advisor 推荐出的索引，不只是提高一句查询的性能，而是 workload 里面尽可能多的查询语句提高性能。而这样以 workload 为基础的优化专家，扩展了优化范围和优化能力，可以帮助改进整个应用的性能。

工作负载控制中心（Workload Control Center）

提供一组快捷的管理操作。可以方便的建立 workload， 维护 workload，导入 / 导出 workload， 收集 workload 运行时信息。
图 15. 管理指定数据库实例上的 workload

图 16.  用 workload 编辑器管理查询语句和进行性能调优

性能监控（Profile Monitor）

可以监控对指定数据库访问的静态查询和动态查询，并可以将监控结果生成 workload。
图 17.  监控数据库

工作负载统计信息专家（Workload Statistics Advisor）

探测统计信息是否丢失， 过期，冲突， 并给出需要收集统计信息的数据库对象以及 RUNSTATS 语句。
图 18. Workload Statistics Advisor 给出的优化建议

工作负载索引专家（Workload Index Advisor）

根据不同的应用场景给出建立索引，或删除不需要的索引的建议。提高数库应用性能。 并生成 DDL 语句，方便用户部署。
图 19. Workload Index Advisor 给出的优化建议

工作负载改写专家（Workload Query Advisor）

根据一组最佳实践规则，指出查询语句的潜在问题，给出查询语句的改写建议。提高编写查询语句技巧。
图 20. Workload Query Advisor 给出的优化建议

工作负载报告（Workload Query Report）

可以提供 workload 先关联的 access path 信息，关键的统计信息， 数据库物理模型设计信息。
图 21. Workload Query Report

回页首

配置数据库

要使用 OQT 的功能，首先需要在 OQT 中连接到 DB2，并且完成对 DB2 的配置。

1. 新建 DB2 数据库别名并连接到数据库

新建 DB2 数据库别名并连接到 DB2 是在 Data Source Explorer 中完成的。

从 Data Source Explorer 中，在数据库连接（Data Connection）上点击鼠标右键，选择新建（New）。
图 22. 新建 DB2 数据库别名并连接到数据库

新建连接向导被打开，在对话框中选择数据库类型，并填入连接属性，结束后数据库子系统别名就建立好了。子系统别名建好后，在其上点击鼠标右 键，可以选择连接到数据库。
图 23. 连接到数据库

在数据库上点击鼠标右键，选择 Query Tuner – →  Configure for Tuning，开始配置数据库。
图 24. 配置数据库

对于 DB2 z/OS，OQT 提供了配置向导辅助用户逐步的完成数据库配置。配置向导包括以下步骤：

• 在数据库上绑定 OQT 包
• 启用 EXPLAIN
• 建 workload 表
• 新建统计表

对于 DB2 LUW，没有配置向导，OQT 一步完成配置工作。

2. 查看 DB2 的配置情况

用户可以随时浏览某个数据库的配置状态，查看启用了哪些功能。在 data source explorer 中 DB2 子系统别名上点击右键选择属性（Property），打开属性视图，选择 Query Tuner Status 标签页，可以看到当前启用的功能状态，并且在此页面上也可以完成某些配置工作。

图 25. 查看 DB2 的配置情况

回页首

结束语

作为 Optim 产品家族的成员， OQT 可以与其他 Optim 产品进行集成，并且可以从与 Optim Data Studio Developer 相同的 Eclipse shell 中调用。这样，它就使开发人员能够更轻松地调用和使用查询调优功能。

IBM Optim Query Tuner 提供了用来识别和调优表现不佳的 SQL 语句的环境。通过监视您的数据库工作负载，OQT 可以快速找到可能会引发性能问题的 SQL 语句。完善的专家建议程序不仅可以调优特定的 SQL 语句，还可以推荐能够改善完整工作负载的整体性能的调优行为。帮助您提高数据库应用性能。

参考资料

学习

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

作者简介