使用 DB2 语句集中器特性和 DB2 语句重新优化特性改进 SQL 执行时间

老枪 发布于 2011/07/12 09:43
阅读 368
收藏 1

简介

Database API(比如 JDBC 和 ODBC/CLI)允许数据库开发人员选择使用带有或不带参数标记(也称为主机变量)的 SQL 语句。通常,人们在进行选择时很少考虑其影响。当数据库应用程序稍后执行时,这种选择可能会导致意外的数据库性能问题。对于打包软件用户,这样的问题更加 严重,因为应用程序无法更改。本文将讨论何时应该使用参数标记,而何时使用文本更好。然后,本文将介绍两个 DB2 LUW 特性,它们分别用于帮助您解决由于文本和参数标记使用不充分所导致的性能问题,这两个特性是:DB2 语句集中器特性和 DB2 语句重新优化特性。

理解 SQL 执行阶段

SQL 语句的执行分为三个阶段:

准备
在准备阶段,DB2 优化器创建访问计划以执行 SQL 语句。
执行
在执行阶段,DB2 确定组成结果集的行( SELECT)、插入新行( INSERT)、删除行( DELETE)或更新现有行( UPDATE)。准备阶段创建的访问计划确定数据库内部操作和执行操作的顺序。
取回
在取回阶段,如果 SQL 语句是 SELECT,DB2 将结果集返回应用程序。

一个 SQL 语句的执行时间是完成上面三个阶段所需的时间。DB2 LUW 提供 db2batch 工具来确定一个 SQL 语句在三个阶段(准备、执行和取回)中的每个阶段中花费的时间。db2batch 从一个平面文件中读取 SQL 语句,执行语句,然后将结果写入一个输出文件。选项 -i complete 指示 db2batch 分别报告每个阶段需要的时间,如 清单 1 所示。


清单 1. 示例 db2batch 语句
				
db2batch -d <dbname> -f <sqlfile> -a <user>/<password> -r<outfile> -i complete

回页首

使用参数标记或文本

本节描述何时使用参数标记,何时使用文本。

使用参数标记的情况

如果一个 SQL 语句满足下面所有条件,则使用参数标记:

  • 此 SQL 语句被多次执行。
  • 准备阶段需要的时间占语句总执行时间的很大一部分。
  • 此 SQL 语句的最佳访问计划不取决于该语句的过滤条件中的显式值。

DB2 优化器将准备阶段中创建的访问计划存储在 SQL 语句缓存(在 DB2 中也称为包缓存)中以便重用。因此,当同一个 SQL 语句再次执行时,DB2 在语句缓存中找到此前创建的访问计划并重用它。这节约了准备阶段,因此节约了执行时间。避免准备阶段在下面两种情况下最有用:一是 SQL 语句被多次执行;二是准备阶段需要的时间占语句总执行时间的很大一部分。

考虑以下示例场景:

  • 一个 SQL 语句的执行时间为 4 毫秒。这 4 毫秒包含准备、执行和取回阶段需要的总时间:准备阶段 1 毫秒,执行阶段 2 毫秒,取回阶段 1 毫秒。
  • 如果该语句被执行 100,000 次,则总执行时间为 100,000 x 4 毫秒 = 400 秒 = 6 分 40 秒。
  • 如果访问计划被重用,则准备阶段所需的时间就节约了,总执行时间将减少 25%:1 x 4 毫秒 + 99,999 x 3 毫秒 = 300,001 秒 = 5 分钟。

访问计划重用的另一个好处是 CPU 负载减小。准备阶段的访问计划创建需要 CPU 资源。因此,如果一个访问计划被重用,则数据库服务器重复执行相应 SQL 语句所需的 CPU 资源将减少。

还记得吗,DB2 仅在 SQL 语句文本完全匹配时才能重用访问计划。例如,清单 2 中的 SQL 语句对于 DB2 而言不一致,尽管它们只有过滤条件略微不同。在这种情况下,DB2 不会重用访问计划。


清单 2. 过滤条件中使用文本的 SQL 语句
				
SELECT LASTNAME, FIRSTNME FROM EMPLOYEE WHERE EMPNO = '000040'
SELECT LASTNAME, FIRSTNME FROM EMPLOYEE WHERE EMPNO = '000120'

要实现访问计划重用,使用一个参数标记重写 SQL 语句,如 清单 3 所示。


清单 3. 过滤条件中使用一个参数标记的 SQL 语句
				
SELECT LASTNAME, FIRSTNME FROM EMPLOYEE WHERE EMPNO = ?

所有主要数据库接口都提供执行包含参数标记的 SQL 语句的功能。例如,JDBC 提供类 java.sql.PreparedStatement 来实现上述功能,如 清单 4 所示。


清单 4. JDBC java.sql.PreparedStatement 代码样例
				
java.sql.Connection con;
java.sql.PreparedStatement prepstmt;
java.sql.ResultSet rs;
String query;

// initialize the database connection
con = ...

// prepare the SQL statement (implicitly creates a prepared SQL statement object)
query = "SELECT LASTNAME, FIRSTNME FROM EMPLOYEE WHERE EMPNO = ?";
prepstmt = con.prepareStatement(query);

// bind a value to the parameter marker and execute the SQL statement
prepstmt.setString(1, "000040");
rs = prepstmt.executeQuery();
// process the result set...

// bind another value to the parameter marker and execute the SQL statement again
prepstmt.setString(1, "000120");
rs = prepstmt.executeQuery();
// process the result set...

很适合使用参数标记的 SQL 语句(因为它们遵守上述三条规则)通常非常简单,因此通常适合在线事务处理(OLTP)。在大多数情况下,非常适合使用参数标记的 SQL 语句要么是非常简单的 SELECT 语句(只引用一个表),要么是被多次执行的 INSERTUPDATEDELETE (UID) 操作。清单 5 显示了这样的 SQL 语句的低复杂度。


清单 5. 适合使用参数标记的简单 SQL 语句
				
SELECT LASTNAME, FIRSTNME FROM EMPLOYEE WHERE EMPNO = ?
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT) VALUES (?, ?, ?)
UPDATE EMPLOYEE SET JOB = ? WHERE WORKDEPT = ?

使用文本的情况

如上节所述,SQL 语句中的参数标记使用可促进访问计划重用。这样,当 SQL 语句被多次执行时,准备阶段的时间和 CPU 资源将得以节约。但使用参数标记并不总是比使用文本(显式值)更好。如果 SQL 语句的最优访问计划根据过滤条件中的文本变化,则应总是使用文本。 通常,这种情况适用于用于报告目的的应用程序执行的复杂查询。这种应用程序很少两次执行同一个查询。因此,即使使用参数标记,准备阶段的时间和 CPU 节约也很少。

在简单 SQL 语句中,文本也可能是更好的选择,如 清单 6 所示。


清单 6. 不适合使用参数标记的简单 SQL 语句
				
SELECT SUM(SALES) FROM SALES WHERE SALES_DATE BETWEEN ? AND ?

对于 清单 6 中的示例进行以下假定:

  • SALES 中的所有行都有一个位于 2010-01-01 和 2010-12-31 之间的 SALES_DATE
  • 每天都大约包含 1,000 行,因此 2010 年全年总共包含约 365,000 行。
  • SALES_DATE 上有一个索引。

根据准备阶段之后为参数标记插入的值,使用 SALES_DATE 索引或一个完整的表扫描的数据访问更有意义。例如,如果只有一天需要计算(约 1,000 行),使用 SALES_DATE 索引访问行可能更好。但是,如果需要计算 2010 年全年,则使用索引访问行会导致处理开销,因为无论如何都需要读取整张表。在这里,使用一个表扫描来访问行是更好的选择。由于 DB2 优化器在准备阶段不知道参数标记的值,因此生成了一个在所有情况下都不是最优的万能(one-size-fits-all)访问计划。对于这个示例查询, 最好使用文本而不是参数标记,并接受重复的准备阶段,如 清单 7 所示。


清单 7. 上面的 SQL 语句的更好版本,使用文本而不是参数标记
				
SELECT COUNT(SALES) FROM SALES WHERE SALES_DATE BETWEEN '2010-05-26' AND '2010-05-26'
SELECT COUNT(SALES) FROM SALES WHERE SALES_DATE BETWEEN '2010-01-01' AND '2010-12-31'

在 JDBC 应用程序中,使用 java.sql.Statement 而不是 java.sql.PreparedStatement 来执行这类查询,如 清单 8 所示。


清单 8. JDBC java.sql.Statement 代码样例
				
java.sql.Connection con;
java.sql.Statement stmt;
java.sql.ResultSet rs;
String query;

// initialize the database connection
con = ...

// create an SQL statement object
stmt = con.createStatement();

// execute the SQL statement with literals
query = "SELECT COUNT(SALES) FROM SALES " +
        "WHERE SALES_DATE BETWEEN '2010-05-26' AND '2010-05-26'";
rs = stmt.executeQuery(query);
// process the result set...

// execute the same SQL statement but with different literals
query = "SELECT COUNT(SALES) FROM SALES " +
        "WHERE SALES_DATE BETWEEN '2010-01-01' AND '2010-12-31'";
rs = stmt.executeQuery(query);
// process the result set...

使用 java.sql.PreparedStatement 生成的源代码通常比使用使用 java.sql.Statement 生成的源代码的可读性更好,应为后者可能需要丑陋的 字符串连接。但是,如果文本对于获取最优访问计划很重要,那么更短的 SQL 语句执行时间比应用程序源代码的优雅性 更重要。

回页首

修复由于数据库级别的参数标记使用不充分而导致的性能问题

当您理解参数标记和文本的优缺点后,就能在应用程序开发过程中在这两个选项之间做出明智选择,实现最优的 SQL 语句执行性能。但是,最常见的情况是,当 SQL 性能问题发生时,往往不能影响应用程序执行 SQL 语句的方式。下面就是一些这样的情况:

打包软件
当软件作为一个产品购买时,软件开发人员决定应用程序如何访问数据库,更改实现是不可能的。
持久性框架
当第三方持久性框架用于自定义应用程序开发时,不可能影响持久性层生成和执行 SQL 语句的方法。
遗留应用程序
对于自定义遗留应用程序,更改实现是可能的,但这种更改耗费的时间和金钱通常太多。

如果出现上面任一情况,由于参数标记和文本使用不适当,数据库性能可能很差。DB2 提供下面两个特性来改进数据库端性能:

DB2 语句集中器
DB2 语句集中器特性能在应用程序不恰当地使用文本而不是参数标记时支持访问计划重用。
DB2 语句重新优化
DB2 语句重新优化特性能在应用程序不恰当地使用参数标记而不是文本时支持在准备阶段之后重新优化访问计划。

回页首

使用 DB2 语句集中器特性强制实施访问计划重用

本节通过一个示例展示如何使用 DB2 语句集中器特性。

表 1 描述了示例表 DEMO.TAB1 的内容,该表包含 10,000 行和 3 列。


表 1. 样例表 DEMO.TAB1 的内容
ID COL1 COL2
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
... ... ...
99 99 1
100 1
101 1 1
... ... ...
199 99 1
200 1
201 1 1
... ... ...
10000 1

其中:

  • ID 是从 1 到 10,000 的连续 ID。
  • COL1 包含运算结果。
  • ID MOD 100ID 除以 100 之后的余数。
  • COL2 在所有行开始处包含值 1。

除列 ID 上定义的主键外,还定义了两个索引:列 COL1 上的索引 DEMO.IDX1 和列 COL2 上的索引 DEMO.IDX2

清单 9 中的 DB2 CLP 脚本创建并填充了这个 DB2 样例数据库中的表 DEMO.TAB1(及其索引)。


清单 9. DB2 CLP 脚本在 SAMPLE 数据库中创建表 DEMO.TAB1 及其索引
				
!db2sampl;

CONNECT TO SAMPLE;

CREATE TABLE DEMO.TAB1
(
    ID INT NOT NULL PRIMARY KEY,
    COL1 INT,
    COL2 INT
);

INSERT INTO DEMO.TAB1 (ID, COL1, COL2)
WITH TMP (ID, COL1, COL2) AS
(
    VALUES (1, 1, 1)
    UNION ALL
    SELECT (ID + 1), MOD((ID + 1), 100), 1
    FROM TMP
    WHERE ID < 10000
)
SELECT ID, COL1, COL2 FROM TMP;

COMMIT;

CREATE INDEX DEMO.IDX1 ON DEMO.TAB1 (COL1 ASC);
CREATE INDEX DEMO.IDX2 ON DEMO.TAB1 (COL2 ASC);

RUNSTATS ON TABLE DEMO.TAB1 AND INDEXES ALL;

CONNECT RESET;

假设一个应用程序反复读取 COL1 值相同的所有行的 IDs。对应的 SELECT 语句在过滤条件中使用文本,如 清单 10 所示。


清单 10. 在过滤条件中使用文本访问表 DEMO.TAB1 的 SELECT 语句
				
SELECT ID FROM DEMO.TAB1 WHERE COL1 = 42;
SELECT ID FROM DEMO.TAB1 WHERE COL1 = 73;

可以预见,DB2 在执行第二个语句(COL1 = 73)时不能重用为第一个语句(过滤条件 COL1 = 42)生成的访问计划。这些值通过过滤条件中的文本生成,DB2 认为这两个语句不同。一个 DB2 语句缓存快照确认每个语句在语句缓存中都有自己的条目,每个语句都只显示一次执行(Number of executions = 1)和一次编译(Number of compilations = 1),如 清单 11 所示。


清单 11. 包含文本的 SELECT 语句的动态 SQL 快照
				
GET SNAPSHOT FOR DYNAMIC SQL ON SAMPLE;

      Dynamic SQL Snapshot Result

 Database name              = SAMPLE

 Database path              = C:\DB2\NODE0000\SQL00001\

 Number of executions       = 1
 Number of compilations     = 1
 ...
 Statement text             = SELECT ID FROM DEMO.TAB1 WHERE COL1 = 42


 Number of executions       = 1
 Number of compilations     = 1
 ...
 Statement text             = SELECT ID FROM DEMO.TAB1 WHERE COL1 = 73

由于 COL1 中的值分布均匀,因此参数标记应该是正确的选择,这是因为在本例中,每个这样的查询都会返回 100 行。由于无法更改示例应用程序(由于上述任一原因),因此应通过使用 DB2 语句集中器特性改进 SQL 语句的性能。可以通过下面的方法在数据库级别激活语句集中器:将 DB CFG 参数 STMT_CONC 的值设置为 LITERALS,如 清单 12 所示。


清单 12. 在数据库配置中激活 DB2 语句集中器
				
UPDATE DB CFG USING STMT_CONC LITERALS IMMEDIATE;

激活语句集中器后,再次执行这两个 SELECT 语句。生成的 DB2 语句缓存的内容如 清单 13 所示。


清单 13. DB2 语句集中器激活时 SELECT 语句的动态 SQL 快照
				
SELECT ID FROM DEMO.TAB1 WHERE COL1 = 42;
SELECT ID FROM DEMO.TAB1 WHERE COL1 = 73;

GET SNAPSHOT FOR DYNAMIC SQL ON SAMPLE;

      Dynamic SQL Snapshot Result

 Database name              = SAMPLE

 Database path              = C:\DB2\NODE0000\SQL00001\

 Number of executions       = 2
 Number of compilations     = 1
 ...
 Statement text             = SELECT ID FROM DEMO.TAB1 WHERE COL1 = :L0


 Number of executions       = 0
 Number of compilations     = 0
 ...
 Statement text             = SELECT ID FROM DEMO.TAB1 WHERE COL1 = 42


 Number of executions       = 0
 Number of compilations     = 0
 ...
 Statement text             = SELECT ID FROM DEMO.TAB1 WHERE COL1 = 73

这两个 SELECT 语句在语句缓存中仍有自己的条目,但执行和编译次数(Number of executions/compilations = 0)表明这些 SELECT 语句不是以它们包含文本的原始表示形式执行。相反,DB2 语句集中器自动生成了一个更通用的 SELECT 语句表示,将文本替换为一个参数标记:SELECT ID FROM DEMO.TAB1 WHERE COL1 = :L0。语句缓存中的对应条目显示,这个通用 SELECT 语句执行了两次(Number of executions = 2),但只编译了一次(Number of compilations = 1)。通过激活语句集中器,可以节约这种 SELECT 语句的重复的准备阶段。

访问计划信息也证明了 DB2 重新编写了原始 SELECT 语句。访问计划信息的生成方法是:输入 清单 14 中的语句,然后使用 db2exfmt 工具进行格式化。


清单 14. 在 DB2 语句集中器激活时生成访问计划
				
EXPLAIN ALL FOR
SELECT ID FROM DEMO.TAB1 WHERE COL1 = 42;

除了常见的部分 Original Statement 和 Optimized Statement 之外,访问计划的 db2exfmt 表示还包含一个额外的 Effective Statement 部分,该部分反映了使用一个参数标记替换文本,如 清单 15 所示。


清单 15. 访问计划摘要显示原始和有效语句
				
Original Statement:
------------------
SELECT ID 
FROM DEMO.TAB1 
WHERE COL1 = 42


Effective Statement:
-------------------
SELECT ID 
FROM DEMO.TAB1 
WHERE COL1 = :L0


Optimized Statement:
-------------------
SELECT Q1.ID AS "ID" 
FROM DEMO.TAB1 AS Q1 
WHERE (Q1.COL1 = :L0)

如果一个应用程序执行多个包含文本的相似 SQL 语句,且如果使用参数标记替代文本,那么 DB2 通常能够重用那些语句的访问计划,则 DB2 语句集中器就能改进数据库性能。

为了在下一节中分析参数标记使用不当的问题,现在先取消语句集中器的激活状态,如 清单 16 所示。


清单 16. 在数据库配置中取消 DB2 语句集中器激活状态
				
UPDATE DB CFG USING STMT_CONC OFF IMMEDIATE;

回页首

使用 DB2 语句重新优化特性获取最优访问计划

要确定某些 SQL 语句种类的最优访问计划,DB2 优化器需要关于 WHERE 子句中指定的条件的关联过滤因子的非常准确的信息。这时,使用文本替代参数标记对于获得良好的 SQL 性能很重要。当一个表中的某些列中的数据分布不均时,这种情况尤其适用(请参阅 参考资料 部分关于分布统计数据的文章)。要在样例表 DEMO.TAB1COL2 列中生成不均衡的值分布,可以执行 清单 17 中的 DB2 CLP 脚本。


清单 17. 更新表 DEMO.TAB1 的 DB2 CLP 脚本
				
CONNECT TO SAMPLE;

UPDATE DEMO.TAB1 SET COL2 = 0 WHERE ID = 1;

RUNSTATS ON TABLE DEMO.TAB1 WITH DISTRIBUTION ON COLUMNS (COL2) AND INDEXES ALL;

COMMIT;

CONNECT RESET;

在此脚本执行之前,表 DEMO.TAB1 中的 10,000 行的每一行的 COL2 列的值都为 1。执行脚本后,只有一行(ID = 1 的行)的 COL2 列的值为 0。COL2 列中的值分布非常不均衡:值 0 只出现 1 次,而值 1 出现 9,999 次。此脚本还为表 DEMO.TAB1 生成一些新统计数据。在统计数据生成过程中,将针对 COL2 列中的值生成分布信息。这样,数据库的统计数据信息中就反映了这种不均衡的值分布。

清单 18 展示了一个过滤条件中使用参数标记的样例 SELECT 语句。


清单 18. 使用参数标记的样例 SELECT 语句
				
SELECT ID FROM DEMO.TAB1 WHERE COL1 = ? AND COL2 = ?;

为分析 DB2 如何执行这个语句,在一些解释表中生成访问计划信息(如 清单 19 所示),然后使用 db2exfmt 工具格式化访问计划。


清单 19. 为包含参数标记的样例 SELECT 语句生成一个访问计划
				
EXPLAIN ALL FOR
SELECT ID FROM DEMO.TAB1 WHERE COL1 = ? AND COL2 = ?;

清单 20 显示了生成的 db2exfmt 输出。


清单 20. 包含参数标记的样例 SELECT 语句的访问计划
				
Access Plan:
-----------
	Total Cost: 		39,6529
	Query Degree:		1

            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
             50 
           FETCH 
           (   2)
           39,6529 
           32,9772 
         /---+----\
       100         10000 
     RIDSCN   TABLE: DEMO    
     (   3)        TAB1
     7,63471        Q1
        1 
       |
       100 
     SORT  
     (   4)
     7,63438 
        1 
       |
       100 
     IXSCAN
     (   5)
     7,61811 
        1 
       |
      10000 
 INDEX: DEMO    
      IDX1
       Q1

用于格式化访问计划的 db2exfmt 工具

可以通过以下步骤创建一个 SQL 语句的详细访问计划:

  1. 定位生成解释表的脚本。此脚本位于 <DB2 instance owner>/sqllib/misc/EXPLAIN.DDL 目录(对于 UNIX 或 Linux)或 <DB2 install dir>\SQLLIB\MISC\EXPLAIN.DDL 目录(对于 Windows)。
  2. 使用 DB2 CLP 执行脚本,创建解释表(只需要一次)。
    db2 CONNECT TO <dbname> USER <userid>
    db2 -tf EXPLAIN.DDL
  3. 以 EXPLAIN 模式执行 SQL 语句,使用以下任一选项:
    • db2 SET CURRENT EXPLAIN MODE EXPLAIN
      db2 <sqlstmt>
      db2 SET CURRENT EXPLAIN MODE NO
    • db2 EXPLAIN ALL FOR <sqlstmt>
  4. 读取解释表,使用 以下命令将访问计划写入一个文件:
    db2exfmt -d <dbname> -u <userid> <pwd> -g -o <outfile> -w -1 -n % -s % -# 0

访问计划在准备阶段生成。此时,DB2 优化器不知道 SELECT 语句的过滤条件中的参数标记的值,这是因为应用程序在语句执行阶段之前(准备阶段之后)才提供过滤值。访问计划显示,DB2 优化器基于下面几个原因使用 COL1 列上的索引(索引 DEMO.IDX1)访问表 DEMO.TAB1 中的行:

  • 独立于最终为参数标记插入的值,过滤条件 COL1 = ? 总是返回 100 行,原因是 COL1 列中的值分布均匀(DB2 优化器精确地处理过滤因子)。
  • 另一方面,根据为参数标记插入的值(1 或 0),条件 COL2 = ? 的过滤因子变化极大。如果插入 1,则过滤因子不好,因为有 9,999 行满足条件。但是,如果插入 0,则过滤因子非常好,因为只有一行满足条件。
  • 由于参数标记在过滤条件中使用,因此 DB2 使用 COL1 列上的索引,尽量减小不良访问计划的风险,选择一个保守的数据访问模式。这个访问模式能确保一个良好的执行时间,该时间独立于为过滤条件 COL2 = ? 中的参数标记插入的值(1 或 0)。

通过检查过滤条件中包含文本的相同 SELECT 语句,可以看到,DB2 优化器使用准确的过滤因子来为 COL2 = 0 情况生成一个更好的访问计划,如 清单 20 所示。


清单 21. 为包含文本的样例 SELECT 生成一个访问计划
				
EXPLAIN ALL FOR
SELECT ID FROM DEMO.TAB1 WHERE COL1 = 1 AND COL2 = 0;

生成的 db2exfmt 输出如 清单 21 所示。


清单 22. 包含文本的样例 SELECT 的访问计划
				
Access Plan:
-----------
	Total Cost: 		15,139
	Query Degree:		1

            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
            0,01 
           FETCH 
           (   2)
           15,139 
              2 
         /---+----\
        1          10000 
     IXSCAN   TABLE: DEMO    
     (   3)        TAB1
     7,57519        Q1
        1 
       |
      10000 
 INDEX: DEMO    
      IDX2
       Q1

通过在过滤条件中使用文本,DB2 优化器意识到,使用 COL2 列上的索引(索引 DEMO.IDX2)访问表 DEMO.TAB1 比参数化查询选择使用 COL1 列上的索引(索引 DEMO.IDX1)更高效。文本访问计划的成本(Total Cost)更低,这确认了优化的数据访问:15 timerons,而不是参数标记访问计划的 39 timerons。

如果某些 SQL 语句的最优访问计划取决于在过滤条件中使用文本,而应用程序使用参数标记,则 DB2 能提供重新优化 SQL 语句的可能性。重新优化意味着 DB2 仍然根据包含参数标记的原始 SQL 语句文本在准备阶段生成访问计划。但是,当应用程序为参数标记插入值后,DB2 将重新评估此前生成的访问计划 ,如果必要,在访问计划执行之前重新优化访问计划。与 DB2 语句集中器不同,语句重新优化特性不能通过设置一个 DB CFG 参数简单地激活或取消激活。重新优化在数据库接口级别控制。

为执行 JDBC 或 ODBC/CLI 发送的 SQL 语句,DB2 在内部使用所谓的 CLI 包。这些包通常通过 BIND 命令绑定到数据库,如 清单 23 所示。


清单 23. . 绑定 CLI 包
				
BIND "C:\PROGRA~1\IBM\SQLLIB\bnd\@db2cli.lst"
    BLOCKING ALL GRANT PUBLIC;

由于 BIND 命令没有包含任何显式架构名称,CLI 包将被绑定到默认架构 NULLID。当 DB2 通过 JDBC 或 ODBC/CLI 接收 SQL 命令时,DB2 默认使用架构 NULLID 中的 CLI 包。架构 NULLID 中的 CLI 包不为 SQL 语句的执行使用重新优化特性。

要重新优化,将 CLI 包重新绑定到另一个架构 NULLIDRA。架构名称在 BIND 命令的 COLLECTION 选项中指定,如 清单 24 所示。


清单 24. . 使用选项 COLLECTION 绑定 CLI 包
				
BIND "C:\PROGRA~1\IBM\SQLLIB\bnd\@db2cli.lst"
    BLOCKING ALL COLLECTION NULLIDRA GRANT PUBLIC;

NULLIDRA 是 DB2 中的一个专用架构名称,表明 CLI 包应该使用额外选项 REOPT ALWAYS 绑定。由于这个选项针对架构名称 NULLIDRA 隐式使用,因此不必显式指定 REOPT ALWAYS 选项。

仅仅将 CLI 包绑定到 NULLIDRA 架构中还不够。JDBC 或 ODBC/CLI 应用程序必须明确告知数据库,它愿意使用 NULLIDRA 架构中的 CLI 包而不是 NULLID 架构中的默认 CLI 包(别忘了 NULLIDNULLIDRA CLI 包在数据库中同时存在)。对于 Java 应用程序,CLI 包的架构可以通过设置 JDBC 属性 currentPackageSet 指定。这个 JDBC 属性可以附加到数据库 URL,或者在数据库的数据源定义中设置。向数据库 URL 附加 JDBC 属性如 清单 25 所示。


清单 25. . JDBC 数据库 URL 将属性 currentPackageSet 设置为架构 NULLIDRA
				
jdbc:db2://localhost:50000/sample:currentPackageSet=NULLIDRA;

对于包含参数标记的样例查询,Java 源代码如 清单 26 所示。


清单 26. . 使用通过 REOPT ALWAYS 选项绑定的 CLI 包的样例 Java 类
				
public class ReoptAlwaysSample {

    public static void main(String[] args) {
        try {
            // load driver
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();

            // set database URL, user, and password

            // use REOPT ALWAYS CLI packages
            String databaseUrl =
                "jdbc:db2://localhost:50000/sample:currentPackageSet=NULLIDRA;";

            String user = "userid";
            String password = "password";

            // get connection
            java.sql.Connection con =
                java.sql.DriverManager.getConnection(databaseUrl, user, password);

            // execute query
            String query = "SELECT ID FROM DEMO.TAB1 WHERE COL1 = ? AND COL2 = ?";
            java.sql.PreparedStatement prepstmt = con.prepareStatement(query);
            prepstmt.setInt(1, 1);
            prepstmt.setInt(2, 0);
            java.sql.ResultSet rs = prepstmt.executeQuery();

            // in the real world, result set evaluation would occur here
            // ...

            rs.close();
            prepstmt.close();

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

您可以在 Java 类 ReoptAlwaysSample 中识别 SELECT 语句的访问计划中的几个地方,那些地方表明该语句在执行过程中受到重新优化。

db2exfmt 输出的头部区域显示 NULLIDRA 架构中的 CLI 包用于语句执行,如 清单 27 所示。


清单 27. db2exfmt 头部显示 CLI 包 SYSSH200 的架构 NULLIDRA
				
******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       09.07.2
SOURCE_NAME:       SYSSH200
SOURCE_SCHEMA:     NULLIDRA
SOURCE_VERSION:      
EXPLAIN_TIME:      2011-01-07-18.01.01.921001
EXPLAIN_REQUESTER: FECHNER

访问计划树发生了变化。它显示表 DEMO.TAB1 中的行通过使用 COL2 列上的索引 DEMO.IDX2 访问。换句话说,包含参数标记的 SELECT 语句的访问计划树现在等同于过滤条件中使用文本的 SELECT 语句的最优访问计划树,如 清单 28 所示。


清单 28. 访问计划显示 SQL 语句重新优化发生时最优索引被选择
				
Original Statement:
------------------
SELECT ID 
FROM DEMO.TAB1 
WHERE COL1 = ? AND COL2 = ?

Optimized Statement:
-------------------
SELECT Q1.ID AS "ID" 
FROM DEMO.TAB1 AS Q1 
WHERE (Q1.COL2 = :?) AND (Q1.COL1 = :?)

Access Plan:
-----------
	Total Cost: 		15,139
	Query Degree:		1

            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
            0,01 
           FETCH 
           (   2)
           15,139 
              2 
         /---+----\
        1          10000 
     IXSCAN   TABLE: DEMO    
     (   3)        TAB1
     7,57519        Q1
        1 
       |
      10000 
 INDEX: DEMO    
      IDX2
       Q1

索引扫描操作(3)和取回操作(2)的细节部分也显示对 SELECT 语句进行了重新优化。两部分细节都包含重新优化过程中为参数标记插入的文本值,如 清单 29 所示。


清单 29. 明确显示 SQL 语句重新优化所使用的值的 db2exfmt 输出的细节部分
				
	2) FETCH : (Fetch)
            ...
		Predicates:
		----------
		3) Sargable Predicate, 
			Comparison Operator: 		Equal (=)
			Subquery Input Required: 	No
			Filter Factor: 			0,01

			Predicate Text:
			--------------
			(Q1.COL1 = :? /* REOPT VALUE=1*/)

	3) IXSCAN: (Index Scan)
            ...
		Predicates:
		----------
		2) Start Key Predicate, 
			Comparison Operator: 		Equal (=)
			Subquery Input Required: 	No
			Filter Factor: 			0,0001

			Predicate Text:
			--------------
			(Q1.COL2 = :? /* REOPT VALUE=0*/)

		2) Stop Key Predicate, 
			Comparison Operator: 		Equal (=)
			Subquery Input Required: 	No
			Filter Factor: 			0,0001

			Predicate Text:
			--------------
			(Q1.COL2 = :? /* REOPT VALUE=0*/)

关于这个样例的一些其他说明:

  • CLI 包被绑定到架构 NULLIDRA 中(RA = REOPT ALWAYS),这样 SQL 语句每次执行时都将被重新优化。在某些情况下,SQL 语句在首次执行时重新优化可能就足够了。如果这是理想的行为,那么 CLI 包将被绑定到架构 NULLIDR1 中(R1 = REOPT ONCE)。当架构 NULLIDR1 被指定时,BIND 命令隐式使用选项 REOPT ONCE 来绑定 CLI 包。 REOPT ONCE 是最优访问计划和访问计划重用之间的平衡,因为在这种情况下,只对 SQL 语句的首次执行进行重新优化。对于后续执行,访问计划重用将独立于绑定到参数标记的值。
  • 要为 Java 类中的 SELECT 语句生成访问计划信息,可以将 JDBC 属性 currentExplainMode 设置为 YES,如 清单 30 所示。

    清单 30. 设置属性 currentPackageSetcurrentExplainMode 的 JDBC 数据库 URL
    						
    jdbc:db2://localhost:50000/sample:currentPackageSet=NULLIDRA;currentExplainMode=YES;

  • CLI 关键字 CurrentPackageSet 等同于 ODBC/CLI 应用程序中(用于设置 CLI 包的架构的)JDBC 属性 currentPackageSet。可以在 CLI 配置文件 db2cli.ini 中设置数据源(=数据库)的 CurrentPackageSet

对于 JDBC 或 ODBC/CLI 应用程序而言,将 currentPackageSet 设置为 NULLIDRA 将为应用程序中的每个 SQL 语句的执行激活重新优化特性。但是在大多数情况下,只有几个 SQL 语句应该被重新优化,这是因为重新优化每个语句可能会造成负面性能影响。可以使用一个优化配置文件控制语句级别的重新优化。

使用配置文件控制重新优化

参见 参考资料 中一篇介绍优化配置文件的文章的链接。

本节描述如何使用一个优化配置文件控制本示例中的 SQL 语句的重新优化。

  1. SELECT 语句创建一个优化配置文件,如 清单 31 所示。样例优化配置文件名为 profile1.xml

    清单 31. 为某个查询指定 REOPT ALWAYS 的优化配置文件
    						
    <?xml version="1.0" encoding="UTF-8"?>
    <OPTPROFILE VERSION="9.1.0.0">
        <STMTPROFILE ID="REOPT_QUERY_1">
            <STMTKEY>
                <![CDATA[SELECT ID FROM DEMO.TAB1 WHERE COL1 = ? AND COL2 = ?]]>
            </STMTKEY>
            <OPTGUIDELINES>
                <REOPT VALUE="ALWAYS"/>
            </OPTGUIDELINES>
        </STMTPROFILE>
    </OPTPROFILE>

    优化配置文件包含针对一个或多个 SQL 语句的 DB2 优化器提示。每个 SQL 语句在 XML 文档中都有自己的 <STMTPROFILE> 部分。这个部分包含 SQL 语句的文本(<STMTKEY> 子部分)和一些优化器提示(<OPTGUIDELINES> 子部分),DB2 优化器在为语句生成访问计划时应该考虑这些提示。样例优化配置文件包含一个 SELECT 语句和对应的提示 <REOPT VALUE="ALWAYS"/>。这个提示告知 DB2 优化器使用 NULLIDRA 架构中的 CLI 包来执行这个 SELECT 语句,这意味着该语句每次执行都要重新优化。

  2. 将 DB2 注册表变量 DB2_OPTPROFILE 设置为值 YES,在 DB2 中显式激活优化配置文件的使用,如 清单 32 所示。

    清单 32. 通过激活 DB2 注册表变量 DB2_OPTPROFILE 激活优化配置文件支持
    						
    db2set DB2_OPTPROFILE=YES
    
    db2stop
    db2start

  3. 重新启动 DB2 实例,以便 DB2 注册表更改生效。
  4. 调用存储过程 SYSPROC.SYSINSTALLOBJECTS 创建 SYSTOOLS.OPT_PROFILE 表来存储优化配置文件,如 清单 33 所示。

    清单 33. 创建 SYSTOOLS.OPT_PROFILE 表来存储优化配置文件
    						
    CALL SYSPROC.SYSINSTALLOBJECTS('OPT_PROFILES', 'C',
        CAST(NULL AS VARCHAR(128)), CAST(NULL AS VARCHAR(128)));

  5. 使用 DB2 IMPORT 工具将包含优化配置文件定义的 XML 文件(profile.xml)插入 SYSTOOLS.OPT_PROFILE 表。
    1. 创建一个输入文件(比如 profile1.del),以便 IMPORT 工具引用 XML 文件,并为优化配置文件提供一个架构(DEMO)和一个名称(PROFILE1),如 清单 34 所示。

      清单 34. . 用于导入优化配置文件的输入文件 profile1.del
      								
      "DEMO","PROFILE1","profile1.xml"

    2. 为输入文件调用 IMPORT 工具,如 清单 35 所示。

      清单 35. 将优化配置文件导入 SYSTOOLS.OPT_PROFILE
      								
      IMPORT FROM profile1.del OF DEL MODIFIED BY LOBSINFILE 
           INSERT INTO SYSTOOLS.OPT_PROFILE;

  6. 刷新 DB2 优化配置文件缓存,确保优化配置文件的更改生效,如 清单 36 所示。

    清单 36. 刷新 DB2 优化配置文件缓存
    						
    FLUSH OPTIMIZATION PROFILE CACHE ALL;

由于样例应用程序不再对所有 SQL 语句进行重新优化,因此 currentPackageSet 属性被移除。该属性被替换为 optimizationProfile 属性,后者定义优化配置文件的完全限定名 (= DEMO.PROFILE1),该配置文件应该用于应用程序执行的 SQL 语句,如 清单 37 所示。除将一个 Java 属性替换为另一个属性外,源代码不需要其他任何更改。


清单 37. . 使用优化配置文件的样例 Java 类
				
public class ReoptAlwaysSample {

    public static void main(String[] args) {
        try {
            // load driver
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();

            // set database URL, user, and password

            // specify optimization profile to use
            String databaseUrl = "jdbc:db2://localhost:50000/sample"
                                 + ":optimizationProfile=DEMO.PROFILE1;";

            ...
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

db2exfmt 输出的头部显示,应用程序现在再次使用 NULLID 架构中的默认 CLI 包(没有重新优化),如 清单 38 所示。


清单 38. db2exfmt 头部显示 CLI 包 SYSSH200 的默认架构 NULLID
				
******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       09.07.2
SOURCE_NAME:       SYSSH200
SOURCE_SCHEMA:     NULLID  
SOURCE_VERSION:      
EXPLAIN_TIME:      2011-01-21-19.10.55.500001
EXPLAIN_REQUESTER: FECHNER 

注意,有一个新部分 Profile information。该部分显示应用程序使用的优化配置文件的名称,以及包含 SELECT 语句的 SQL 语句文本的语句键(= <STMTKEY>)。出现正确的 <STMTKEY> 这个事实证明 SELECT 语句的 SQL 语句文本匹配取得成功,如 清单 39 所示。


清单 39. db2exfmt 输出中的 Profile Information 部分
				
Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
	DEMO.PROFILE1
STMTPROF: (Statement Profile Name)
	REOPT_QUERY_1

访问计划树证实使用优化配置文件的选择性重新优化生效。执行 SELECT 语句的最优索引(DEMO.IDX2)被选中,如 清单 40 所示。


清单 40. . 访问计划显示优化配置文件选择了最优索引
				
Access Plan:
-----------
	Total Cost: 		15,139
	Query Degree:		1

            Rows 
           RETURN
           (   1)
            Cost 
             I/O 
             |
            0,01 
           FETCH 
           (   2)
           15,139 
              2 
         /---+----\
        1          10000 
     IXSCAN   TABLE: DEMO    
     (   3)        TAB1
     7,57519        Q1
        1 
       |
      10000 
 INDEX: DEMO    
      IDX2
       Q1

回页首

结束语

本文阐述了如何在 SQL 语句中使用参数标记和文本来影响 SQL 执行时间,如何根据应用程序类型和数据库数据分布特征确定是使用参数标记还是文本。本文还描述了如何使用两个 DB2 for Linux, UNIX, and Windows 特性改进 SQL 执行时间,特别是无法更改数据库应用程序时。您了解了如何在不宜使用文本时使用 DB2 语句集中器特性,以及如何在不宜使用参数标记时使用 DB2 语句重新优化特性。


参考资料

学习

原文: http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1104db2statementconcentratorreoptimization/index.html?ca=drs-
加载中
返回顶部
顶部