DB2 for i5/OS 上的 SQL 性能优化目标评测

核心提示：简介DB2 for i5/OS on V5R4 提供了一些新方法来通过一些流行的动态 SQL 接口提高数据库查询的性能，SQL Call Level Interface (CLI) 为用户提供了一个新的连接属性，DB2 for i5/OS 上的 SQL 性能优化目标评测，用于调优数据库查询使用的优化目标，用于 IBM&

简介

DB2 for i5/OS on V5R4 提供了一些新方法来通过一些流行的动态 SQL 接口提高数据库查询的性能。SQL Call Level Interface (CLI) 为用户提供了一个新的连接属性，用于调优数据库查询使用的优化目标。用于 IBM® Developer Kit for Java 的 Java Database Connectivity (JDBC) 接口（也称 Native JDBC）和 Toolbox 也提供了一个新的可控制查询优化目标的连接属性。那些熟悉 System i 和数据库查询性能分析和调优的人就会理解控制优化目标是优化性能的关键步骤。从 V5R4 开始，这些动态接口也可利用 System i 其他地方出现的那种直接程序性控制。本文主要讨论用于设置优化目标的新的 CLI 属性。

优化目标接口

在过去，执行 SQL 语句的应用程序可以选择通过几种机制来调优优化目标：

Query Options File (QAQQINI) -- 可以通过将 OPTIMIZATION_GOAL 选项指定为以下几个值之一，来控制整个系统或一个特定连接（任务）的优化目标。由于缺省的优化目标值取决于使用的 SQL 接口，因此该选项没有缺省的设置。对于用于每种接口的缺省值，请参考表 1。

*ALLIO

*FIRSTIO

表 1. 每种 SQL 接口的缺省优化目标

接口	缺省优化目标
CLI	ALLIO
Native JDBC 驱动程序	ALLIO
Toolbox JDBC 驱动程序	FIRSTIO - 如果使用扩展的动态包，则缺省值为 All I/O
iSeries access for Windows Open Database Connectivity (ODBC)、Object Linking and Embedding (OLE) DB 和 .NET 客户机	FIRSTIO - 如果使用扩展的动态包，则缺省值为 All I/O
嵌入式静态 SQL	FIRSTIO
嵌入式动态 SQL	ALLIO
QSQPRCED API	FIRSTIO
STRSQL 实用程序	FIRSTIO
RUNSQLSTM 实用程序	ALLIO

OPTIMIZE FOR N ROWS 子句 -- 通过使用 OPTIMIZE FOR N ROWS 子句，可以直接将优化构建到 SQL 请求中。若 N 值较小，则可能导致查询优化器使用 FIRSTIO 目标，反之，如果该值较大（例如 ALL ROWS），则会导致查询优化器使用 ALLIO 目标。

CLI SQLSetConnectAttr() API 和 SQL_ATTR_QUERY_OPTIMIZE_GOAL 属性

SQL_ALL_IO （缺省）

SQL_FIRST_IO

Toolbox JDBC 连接属性 "query optimize goal" 和 DataSource 方法 setQueryOptimizeGoal()

0 = 使用缺省目标 *FIRSTIO，但是如果使用扩展的动态包，则使用目标 *ALLIO

1 = *FIRSTIO

2 = *ALLIO

Native JDBC 连接属性 "query optimize goal" 和 DataSource 方法 setQueryOptimizeGoal()

0 = 使用缺省目标 *ALLIO

1 = *FIRSTIO

2 = *ALLIO

新的 CLI 和 JDBC 接口

新的 CLI 和 JDBC 选项提供了一种更程序性、在某些情况下也是更细粒度的方法来调优优化目标。由于新的 CLI 连接属性的作用范围是整个连接，因此它将影响在设置该属性之后执行的所有 SQL 查询。这对于以服务器模式运行很多线程的环境很有利，因为这种环境必要时可以在多个连接上使用不同的优化目标。此外，这也使得设置优化目标更加有助于动态调优策略。注意，这些 JDBC 和 CLI 接口只对到 i5/OS V5R4 或更高版本上运行的 System i 服务器的连接有影响。

本文的目的不是对优化目标的设置进行深入的讨论，但是对此作一个简要的描述可能对某些读者有用。通过将优化目标指定为 FIRSTIO，应用程序可以迫使查询更快地返回第一页结果的输出。若优化目标为 ALLIO，则可以以最短的时间完成整个查询。为了直观地理解这些选项，可以将 FIRSTIO 选项比作自行车，而将 ALLIO 选项比作飞机。如果是去很近的地方，那么自行车也许是最好的选择，因为基本上没有启动成本。但是，如果要去比较远的地方，虽然启动成本较高，飞机仍然是更好的解决方案。对距离的理解，或者说对查询的输出行为的理解，对于为不同查询选择最有效的选项是至关重要的。

本文的目的之一是执行一些查询并测试两种优化设置（FIRSTIO 和 ALLIO）的性能，从而来证明 CLI 环境中新设置的实际实现。可以肯定的是，iSeries Navigator 的 SQL Performance Monitor 特性对于测量查询的启动时间、取数据时间和总响应时间以及优化器的实现是一个有用的工具。我编写了一个 CLI 程序，用于以任意一种优化目标设置来运行 SQL 查询。程序中为新的连接属性提供了 CLI 常量、SQL_FIRST_IO 和 SQL_ALL_IO。

清单 1. 控制优化目标的 CLI 示例代码

attr = SQL_FIRST_IO;
rtnc = SQLSetConnectAttr(hdbc,SQL_ATTR_QUERY_OPTIMIZE_GOAL,&attr,0);

iSeries Navigator 的 SQL 性能工具

可以通过在 图 1 所示的下拉菜单中选择 Monitor > Start SQL Performance Monitor 任务，从 iSeries Navigator Run SQL scripts 界面中启动 SQL Performance Monitor。然后，使用 CL 前缀调用包含 CLI 调用的 C 程序，以发出一个 i5/OS CALL 命令。

图 1. Run SQL Scripts 界面

对于一个包含大约 1 GB 数据的数据库，第一个测试的查询返回一个只包含 12 行的记录。如下面的图 2 所示，当使用 Performance Monitor 时，界面中将通过比较特性可视化地并列显示 FIRSTIO 和 ALLIO 的性能数字。

图 2. SQL 性能监视器比较

图 3 显示了测试这两种优化目标时 SQL Monitor 比较的输出，其中包含时间方面的数字。在这种情况下，对于第一个查询，与使用 FIRSTIO 优化目标相比，使用 ALLIO 时返回全部 12 行所需的时间明显更短。有趣的是，使用 FIRSTIO 时的启动时间比 ALLIO 更短，但由于取数据的时间方面的差别，在整体响应时间方面还是 ALLIO 占优。这是因为在使用 ALLIO 的情况下，优化器在启动阶段额外耗费了一些时间，以选择能快速取到所有数据的计划，而不是像 FIRSTIO 那样只急于获取前几行数据。

图 3. 对于查询 1 的 SQL Monitor 比较输出

测试的第二个查询使用了一条将返回包含数千行数据的结果集的 SQL 语句。但是，在这个例子中，CLI 应用程序在初始处理阶段只关心前 40 行。应用程序最终也会处理剩下的行，但要稍后才会处理。现实中的一个例子就是 Web 页面搜索引擎。Web 页面希望尽快显示前面数条结果，虽然剩下的结果也可能要显示出来，但是通常情况下用户并不急于第一眼或者在第一个页面上看到它们。从图 4 中的性能结果可以看出，对于第二个查询，在将前 40 行返回到应用程序的过程中，使用 FIRSTIO 目标构建的访问计划比使用 ALLIO 目标的访问计划所花的时间要少一半。

图 4. 对于查询 2 的 SQL Monitor 比较输出

还可以使用 SQL Performance Monitors 的 Analyze 功能来调查研究对查询性能有影响的设置。有两种方法可以开始这样的分析，一种方法是在 Run SQL Scripts 界面中，从图 1 所示的 Monitor 下拉菜单中选择 Analyze 任务；另一种方法是在 SQL Performance Monitors 视图中（见图 2），在 iSeries Navigator 树中右键单击一个监视器并选择 Analyze 任务。 通过运行 Analyze 功能，可以得到 图 5 所示的监视器总结。注意，图 5 中突出显示了 FIRSTIO 优化目标设置。

图 5. 对优化设置的分析的输出

使用 Visual Explain 进行优化分析

理解在计划创建期间 DB2 for i5/OS 查询优化器使用的优化目标的另一种方法是使用 Visual Explain 工具，该工具是 iSeries Navigator 的一部分。可以在一个监视器集合中，通过右键单击一个监视器并选择 Show Statements 任务，在 SQL 语句上应用 Visual Explain 工具。通过分析 图 6 中对于第二个查询的 Visual Explain 输出，可以证实优化器将达到 FIRSTIO 的预期优化目标。

图 6. Visual Explain 分析

iSeries Navigator DB2 Performance 工具集中新的 V5R4 分析和比较特性为研究查询性能提供了新的、令人激动的方法。此外，CLI 和 JDBC 提供的附加功能允许对优化目标进行程序性控制，从而为应用程序提供了更大的威力。