转自：http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0912yanlh/index.html
Optim Query Tuner，简称 OQT，作为 Optim （原 Data Studio）家族的新成员，是一款可应用于 DB2 for z/OS 和 DB2 for Linux，UNIX，及Windows 平台的查询优化工具。本文通过多平台上对查询语句进行实际调优的例子，展示如何有效安排各功能模块的优先次序，如何在尽量降低系统整体开销的前提下达到快速发现问题症结和高效解决问题的目的，以显著提高数据库访问效率，为数据库管理员和数据库开发人员使用该工具提供了一种有用的指导和借鉴。
IBM Optim （原 Data Studio）软件家族作为 IBM 软件产品组合的新成员，有效集成了数据库应用程序的设计、开发、部署、操作、优化、治理这几个数据生命周期阶段，并实现不同 IT 角色之间的统筹协作。
在为应用程序和工作负载进行前摄性的规划和优化领域，Optim 家族提供了 Optim Query Tuner、DB2 Performance Expert、Optim pureQuery Runtime 等调优工具。而 Optim Query Tuner，简称 OQT，是一款可应用于 DB2 for z/OS 和 DB2 for Linux，UNIX，及 Windows 平台的查询优化工具。它提供一套全面专业的专家建议（advisor），支持针对单个查询及含有成百上千个查询的工作负载（workload）的调优，帮助用户识别和改进有问题的查询。
其商业价值在于，在数据库的开发和应用当中，往往出现某些纷繁复杂的数据查询和访问环节存在设计上的缺陷，以至于严重影响了应用的效率，此时即使是有丰富经验的 DBA 也无从下手。它面向的用户对象不仅仅是有丰富数据库应用经验的数据库管理员（DBA），也可方便数据库之上的开发人员（developers）进行数据库查询和设计方面的优化，将最佳实践和顾问功能集成于一体。也因此，该工具的可操作性与优化方案的高效明晰性也经得起检验。
它具备诸如以下性能优势：
- 通过使开发人员在开发阶段调优 SQL 以降低开销和风险。
- 在广为熟悉的 Eclipse 开发环境下操作，与 Optim Development Studio 实现无缝集成。
- 通过提供专家建议和推荐以加速查询优化分析。
- 鼓励开发人员（developers）与数据库管理员（DBAs）之间的协作。
OQT 的功能十分强大，能提供关于数据库访问路径的图形化和表格化信息，利用 catalog 目录统计信息和代价估计信息对 SQL 文本进行格式重组和注解分析，对 Query 和执行计划进行优化的建议（Query Advisor and Access Path Advisor），对表和索引等的统计信息进行收集和更新的建议（Statistics Advisor），对创建合理索引的建议（Index Advisor），给出 query 的相关信息如表、索引、谓词相关的报告。
功能模块如此包罗万象，参考信息如此丰富，也会使数据库应用经验不足的用户眼花缭乱，无所是从。如何安排其优先级，如何在尽量降低系统整体开销的前提下对查询做出高效的优化，显著提高数据库访问效率，是众多数据库开发人员和 DBA 们十分关注的问题。
这里针对两个有代表性的查询问题进行性能调优的例子，展示如何有效利用 OQT 工具以达到快速发现问题症结和高效解决问题的目的。
在一个用户 DB2 V9 for z/OS 的真实数据库应用之上，他们通过一个查询监控工具发现，如下的查询语句存在性能上的问题。此查询访问 DB2 catalog 表存取数据需要的时间不可接受，已严重影响了应用的效率，因此用户希望以最小的系统更改代价来改进设计。
SELECT HEX(SUBSTR(T1.STMT, 48, 1)) AS HEXMRIC, CAST(SUBSTR(T1.STMT, 48, 1) AS CHAR(1) CCSID EBCDIC FOR SBCS DATA) AS DBRMMRIC, HEX (SUBSTR(T1.STMT, 47, 1)) AS HEXPDRM, CAST(SUBSTR(T1.STMT, 47, 1) AS CHAR (1) CCSID EBCDIC FOR SBCS DATA) AS DBRMPDRM FROM SYSIBM.SYSPACKSTMT T1, SYSIBM.SYSPACKAGE T2 WHERE T1.COLLID = :H :H AND T1.NAME = :H :H AND T1.VERSION = :H :H AND T1.COLLID = T2.COLLID AND T1.NAME = T2.NAME AND T1.VERSION = T2.VERSION AND T2.TYPE = ' ' AND T1.SECTNO = 0 AND T1.SEQNO = 0 AND T1.STMTNO = 0 |
已知其上现有的索引结构为：
- INDEX on SYSPACKSTMT T1：DSNKSX01 (LOCATION, COLLID, NAME, CONTOKEN, SEQNO)
- INDEX on SYSPACKAGE T2：DSNKKX01 (LOCATION, COLLID, NAME, VERSION)；DSNKKX02 (LOCATION, COLLID, NAME, CONTOKEN)
如果按照 OQT 提供的功能模块，选择所有的调优动作，对应图 1 的操作“Select All”，我们将得到如图 2 所示的结果。OQT 给出的建议以优先级顺序排列。但对查询的调优并非是依次地执行这些建议，任何查询问题也并非只有一个最优解。秉着尽量减少系统开销的原则进行有效的优化操作，以改善查询的响应时间，此案例中给出的分析问题解决问题的思路，对用户来讲将不失为一种有效的指导和借鉴。
Query Format and Annotation 模块可以根据 SQL 语法特点，将输入的查询语句进行规范化的排列和展示，特别是对于复杂难理解的查询语句，此功能十分有帮助。
要找到问题的本质，即要找到查询在数据库中执行过程中的瓶颈，我们首先需要借助图形化工具展示其数据库访问细节，Access Path Graph（简称 APG）工具首当其冲。下图 4 展示了此查询在 DB2 数据库中的访问路径。APG 可以使用户直观地理解 SQL 语句的执行方案，有助于发现导致低性能的可疑 mini plan。相比执行工具 db2exfmt 产生的格式化文本的访问计划，它展现的信息更丰富，更明晰。
从图 4 的 APG 结果可以看出，此查询中对表 T1 和表 T2 的访问方式都是索引扫描，但都为 screening，即是非 matching 的。matching 匹配是指如果对输出的排序需求可以与索引列相匹配，那么按列顺序扫描索引将允许按正确顺序检索行而不需要排序。在应用索引扫描时，我们希望其尽可能是 matching 的，检索的效率可以大大提高。
此外，由于出现了“HIGH”等级的 Statistics Advisor，表明此时采用的统计信息都为缺省值，DB2 优化器已不能较为正确地估计出最好的执行计划，需要重新由 OQT 调用 RUNSTATS 存储过程来更新 DB2 catalog 表中的统计信息。但同时，执行 Statistics Advisor 给出的 Recommendation 建议需要视具体应用而定，因为重新收集 Statistics 统计信息也需要耗费较大的代价，特别是在 z/OS 平台上。因 DB2 优化器使用目录统计信息来确定最佳的访问路径，而要更新这些目录统计信息所采用的主要方法就是运行 RUNTSATS 实用程序，它会重新收集数据库中表和索引的统计信息，以获取与实际情况较为接近的数据环境。
图 5. Statistics Advisor 推荐结果
图 6. Statistics Advisor 推荐 RUNSTATS 结果
此后，在新的环境下重新调优 SQL，OQT 给出了较高优先级的 Query Advisor 的建议，如图 7 所示。
其分析结果显示，SYSIBM.SYSPACKSTMT 和 SYSIBM.SYSPACKAGE 表在列 LOCATION 和 CONTOKEN 上存在外键关系，可考虑加入一些 join 连接谓词来增加查询的过滤性，避免代价较大的笛卡儿连接。即隐含的提示为： 添加连接谓词 T1.LOCATION = T2.LOCATION AND T1.CONTOKEN = T2.CONTOKEN 后，查询仍是等价的。于是，重写查询语句为如下形式：
SELECT HEX(SUBSTR(T1.STMT, 48, 1)) AS HEXMRIC, CAST(SUBSTR(T1.STMT, 48, 1) AS CHAR(1) CCSID EBCDIC FOR SBCS DATA) AS DBRMMRIC, HEX (SUBSTR(T1.STMT, 47, 1)) AS HEXPDRM, CAST(SUBSTR(T1.STMT, 47, 1) AS CHAR (1) CCSID EBCDIC FOR SBCS DATA) AS DBRMPDRM FROM SYSIBM.SYSPACKSTMT T1, SYSIBM.SYSPACKAGE T2 WHERE T1.COLLID = :H :H AND T1.NAME = :H :H AND T1.VERSION = :H :H AND T1.COLLID = T2.COLLID AND T1. LOCATION = T2. LOCATION AND T1. CONTOKEN = T2. CONTOKEN AND T1.NAME = T2.NAME AND T1.VERSION = T2.VERSION AND T2.TYPE = ' ' AND T1.SECTNO = 0 AND T1.SEQNO = 0 AND T1.STMTNO = 0 |
应用此修改后的 SQL，此时再用 OQT 进行调优的环境已经较之前发生了变化，查看其执行计划如图 8：
图上可见，应用了连接谓词 T1.LOCATION = T2. LOCATION 之后，表 T2 上应用索引是 matching 的，而 T1 上是非 matching 的，仍不能最大限度地应用其上的索引的优势。重新运行 OQT 的“Tune Query”得到较高优先级的 Access Path Advisor 推荐的结果，如图 9 所示。
如上 APA 的结果显示，系统目录统计表 SYSIBM.SYSPACKSTMT 采用的是非 matching 的访问方式，这不是一种高效率的执行计划。此时，我们查看系统 catalog 表 T1 和 T2 的定义，看能否看出一些端倪：
由图 10 可知，两表都有的 LOCATION 列可以为空值，即可增加两个本地谓词来增加查询的有效性。于是在上述结果的基础上改写查询语句为如下形式，成为最终的优化结果：
SELECT HEX(SUBSTR(T1.STMT, 48, 1)) AS HEXMRIC, CAST(SUBSTR(T1.STMT, 48, 1) AS CHAR(1) CCSID EBCDIC FOR SBCS DATA) AS DBRMMRIC, HEX (SUBSTR(T1.STMT, 47, 1)) AS HEXPDRM, CAST(SUBSTR(T1.STMT, 47, 1) AS CHAR (1) CCSID EBCDIC FOR SBCS DATA) AS DBRMPDRM FROM SYSIBM.SYSPACKSTMT T1, SYSIBM.SYSPACKAGE T2 WHERE T1.LOCATION =' ' AND T2.LOCATION =' ' AND T1.COLLID = :H :H AND T1.NAME = :H :H AND T1.VERSION = :H :H AND T1.COLLID = T2.COLLID AND T1. LOCATION = T2. LOCATION AND T1. CONTOKEN = T2. CONTOKEN AND T1.NAME = T2.NAME AND T1.VERSION = T2.VERSION AND T2.TYPE = ' ' AND T1.SECTNO = 0 AND T1.SEQNO = 0 AND T1.STMTNO = 0 |
图 11. 最终的执行结果图
由调优后的 APG 图 11 可以看出，对两个表的访问都采用的是 matching 的索引扫描方式。因此在 OQT 的帮助和提示下一步步地发现问题并解决了问题，达到了我们初始期望的目标。
此案例涉及表上索引的设计和更改。用户工作环境上安装了用于 Windows 的 DB2 V9.5.1，发现有以下查询性能较差：
SELECT T1.CASEID,T1.PACKAGENAME,
T1.CLASSNAME,T1.METHODNAME,T1.BEGINTIME,T1.STATUS,
T1.MEMORY0,T1.MEMORY1,T1.MEMORY2,T1.TOTALTIME,
T1.DYNAMICSQL_TIME,T1.STATICSQL_TIME
FROM (SELECT PACKAGENAME,CLASSNAME,METHODNAME, MAX(BEGINTIME) AS MAX_BEGINTIME
FROM PERFORMANCE_TESTCASES_TABLE
WHERE PACKAGENAME LIKE ? AND CLASSNAME LIKE ? AND METHODNAME LIKE ?
GROUP BY PACKAGENAME,CLASSNAME,METHODNAME) AS T2, PERFORMANCE_TESTCASES_TABLE AS T1
WHERE T1.PACKAGENAME = T2.PACKAGENAME AND T1.CLASSNAME = T2.CLASSNAME
AND T1.METHODNAME = T2.METHODNAME AND T1.BEGINTIME = T2.MAX_BEGINTIME
FETCH FIRST 1000 ROW ONLY
|
首先，我们可以借助 Query Format and Annotation 功能模块对输入 SQL 进行格式重组和注解分析，如图 12。由于其中发现表上和某些列上的基数（以 CARD 表示的）是缺失的，这就表明，统计数据信息的收集将必不可少。而我们运行“Tune Query”时得到的推荐建议中也将 Statistics Advisor 给出的建议设为了最高优先级。
图 12. 对 SQL 进行格式重组和注解分析
图 13. 各 Advisor 推荐汇总
在接受了 Statistics Advisor 的推荐并执行了 RUNSTATS 命令之后，最新的目录统计信息被收集。之后，要充分分析此查询问题的症结所在，寻根溯源，先检查其 APG 图的步骤必不可少。在如图 14 表现的执行计划中，我们发现对数据库表的访问采用的都是表扫描的方式，而未利用到索引检索数据效率高的优势，这势必会影响到查询的性能。
Index Advisor 是创建索引的专家，其遵循的原则主要有：对于主键字段，自动创建主键的索引；将在查询中用来连接表的字段作为索引的键值；将经常用来作为排序 ( 如 order by 的字段 ) 基准的字段作为索引的键值；会考虑数据的创建方式来创建索引等。此例中它给出了两种索引的推荐，一种是应用了连接谓词中的字段作为键值，一种是采用了 index-only 的方式。索引的创建并非越多越好，索引在加快查询效率的同时也会占用磁盘空间，用户需要综合考虑到数据库的应用效率，权衡利弊。
本例中当我们采纳了 Index Advisor 的建议（图 15），新建了两个索引之后，再次运行 APG（图 16），查看优化后的效果，发现对表的访问都是索引扫描的方式，达到我们预期的设想，经实际验证，可以有效解决此查询问题。
由以上两个案例，可以了解各主要模块的功用和适用阶段，也能总结出使用 OQT 进行查询优化时一般采用的流程，即先用 Query Format and Annotation 对 SQL 格式重组后，辅助 Access Path Graph 查看执行计划和数据存取细节，找到问题瓶颈或可改进的余地；然后根据目录统计信息状况运行 Statistics Advisor 得到实际数据环境，之后可根据 OQT 给出的建议优先级，或使用 Access Path Advisor 或 Query Advisor 对查询语句进行重写修改，或在数据库允许的情况下，使用 Index Advisor 建立合适的索引，提高查询效率；最后查看 APG 对修改效果加以印证核实。OQT 中的功能模块既能单独运行，亦能多个结合，相互辅助，数据库管理员和开发人员的经验也将对优化效果起到重要作用。
总之，数据库上的查询优化是一个复杂的过程，除了应用层次的操作，还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计等，涉及较广，操作复杂。而借助于 OQT 工具的帮助，遵循数据库查询优化的原则，使得用户可以较为轻松地找到问题的症结并得出有效的解决方案，便达到了我们的最终目的。这里通过 DB2 z/OS 和 DB2 LUW 平台上利用 OQT 发现、分析并解决查询问题的两个实例过程，展现了用 OQT 处理问题的一种思路和方法，对于数据库管理员和数据库开发人员将是一种有用的指导和借鉴。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15082138/viewspace-621817/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15082138/viewspace-621817/
546
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
