DB2 V9.7 语句集中器的使用

核心提示：简介在 DB2 数据库中存在两种类型的 SQL 语句，一种为动态 SQL，DB2 V9.7 语句集中器的使用，一种为静态 SQL ，静态 SQL 的执行计划是在 bind 包到数据库时就已经确定，比较适用于有大量小 SQL 执行的 OLTP 环境，对 OLAP 环境或者一个语句中的不同字面值导致执行计划显著不同，执行时

简介

在 DB2 数据库中存在两种类型的 SQL 语句，一种为动态 SQL，一种为静态 SQL 。静态 SQL 的执行计划是在 bind 包到数据库时就已经确定，执行时只需要把执行计划调出来即可；动态 SQL 的执行计划需要每次执行时进行编译，如果下次执行时，执行计划已经不在包缓存中则需要重新编译该语句。

在 OLTP 环境下，每秒钟需要执行的 SQL 非常多，如果这些 SQL 语句都是动态语句，则都需要大量的 CPU 时间进行编译。 DB2 判断一个动态 SQL 语句的执行计划是否在包缓存中时采用的是 HASH 算法，该算法根据 SQL 语句的文本进行 HASH，SQL 文本即使只有一个字母的大、小写不同，也会造成 HASH 值不同如果 HASH 值不同，则认为是两个不同的 SQL 语句。

对下面的两个语句 DB2 就认为是不同的 SQL 。

select　firstnme,lastname　from　employee　where　empno='000020'　
　select　firstnme,lastname　from　employee　where　empno='000070'

上面两个语句虽然在在 Where 条件处只有一个数字差异，DB2 HASH 算法也会认为这是两个不同的 SQL 。但是 DB2 为他们生成的执行计划都是一样的，我们使用 db2expln 工具获得执行计划如下。执行计划显示上面两个 SQL 语句都是使用索引 PK_EMPLOYEE 先获取 RID，然后根据 RID 再读取具体的数据。

　　　　　　　　　　　　　　　　　　　　　　　　Rows　
　　　RETURN　
　　　(　　1)　
　　　　Cost　
　　　　I/O　
　　　　|　
　　　　　1　
　　　FETCH　
　　　(　　2)　
　　　7.58163　
　　　　　1　
　　/----+----\　
　　1　　　　　　42　
　　　IXSCAN　　TABLE:　DB2INST1　
　　　(　　3)　　　　EMPLOYEE　
　　　0.0165581　　　　Q1　
　　0　
　　　　|　
　　　　42　
　INDEX:　DB2INST1　
　　PK_EMPLOYEE　
　　　　Q1

虽然两个 SQL 的执行计划是相同的，但是 DB2 为了获取执行计划需要对两个 SQL 都要进行编译，消耗了 CPU 。这种消耗在每秒钟执行成千条 SQL 语句的 OLTP 环境下，对性能的影响是比较大的。

语句集中器的启用

DB2V9.7 推出了语句集中器的功能，语句集中器在数据库服务器上修改动态 SQL 语句，以使类似而不等同的 SQL 语句可以共享同一个执行计划。如果启动了语句集中器，上述两个 SQL 只需要编译一次即可。

在联机事务处理（OLTP）系统中，可能会反复生成包含不同字面值的简单语句。在此类工作负载中，重新编译语句的成本会导致开销大幅增加。语句集中器通过允许重复使用已编译的语句（而不考虑字面值）来消除此开销。

缺省情况下，语句集中器处于禁用状态。如果希望对数据库中的所有动态语句启用语句集中器，我们需要将 stmt_conc 数据库配置参数设置为 LITERALS 。不过 DB2 只会将前 100000 个字面值才进行替换；其余字面值保持不变，一般情况下这也能满足我们的要求。

db2　get　db　cfg　for　sample　|grep　"CON"　
语句集中器　(STMT_CONC)　=　OFF

如果并不希望对所有 SQL 启动语句集中器，只是希望指定连接在执行动态 SQL 时进行语句集中，则我们可以在客户机上启动语句集中器，需要在 db2cli.ini 配置文件中设置：

StmtConcentrator　=　WITHLITERALS

默认情况下连接的语句集中器是否启动由 Server 的配置决定。如果设置 StmtConcentrator 的值为 OFF，表示连接的语句集中器关闭；如果 StmtConcentrator 为 WITHLITERALS 表示启动语句集中器。当语句集中器启动后，所有 Server 支持集中的语句将共享执行计划。 db2cli.ini 中的参数 StmtConcentrator 影响的是连接的 SQL_ATTR_STMT_CONCENTRATOR 属性，我们也可以在 ODBC、JDBC 程序中直接设置连接的这个属性。

我们应优先考虑在客户机级别启用语句集中器，首先它允许在最精细的级别控制语句集中器，其次，它是在整个 DB2 产品系列中启用语句集中器的唯一一致方式。

语句集中过程导致修改动态语句，那么原始语句和修改后的语句都将显示在说明输出中。如果语句集中器已修改原始语句文本，那么事件监视器逻辑监视元素以及 MON_GET_ACTIVITY_DETAILS 表函数的输出都将显示原始语句。其他监视器界面将仅显示修改后的语句文本。

我们修改数据配置参数 STMT_CONC 对所有连接启动语句集中器。

db2　update　db　cfg　for　sample　using　STMT_CONC　LITERALS

然后我们分别执行：

select　firstnme,lastname　from　employee　where　empno='000020'　
　select　firstnme,lastname　from　employee　where　empno='000070'

我们使用下面语句获取 SQL 语句的编译、执行情况：

db2　get　snapshot　for　dynamic　sql　on　sample　
　
执行数　=　0　
编译数　=　0　
最差预编译时间（毫秒）　=　0　
最佳预编译时间（毫秒）　=　0　
　----------------------------------　省略　------------------------------　
语句文本　=　select　firstnme,lastname　from　employee　where　empno='000020'　
　
执行数　=　0　
编译数　=　0　
最差预编译时间（毫秒）　=　0　
最佳预编译时间（毫秒）　=　0　
　----------------------------------　省略　------------------------------　
语句文本　=　select　firstnme,lastname　from　employee　where　empno='000070'　
　
执行数　=　2　
编译数　=　1　
最差预编译时间（毫秒）　=　218　
最佳预编译时间（毫秒）　=　218　
　----------------------------------　省略　------------------------------　
语句文本　=　select　firstnme,lastname　from　employee　where　empno=:L0

我们看到两个原始的 SQL 语句编译次数、执行次数、编译时间均为 0，同时有个用” :L0 ”参数标识的语句编译次数为 1，执行次数为 2，编译时间为 218 毫秒。

语句集中器限制

由于语句集中过程将更改语句文本，因此会对执行计划的选择产生影响。如果程序包高速缓存中的类似语句具有大量类似的执行，那么应该使用语句集中器。如果一个语句中的不同字面值导致执行计划显著不同，那么不应对该语句启用语句集中器。

下面我们看语句集中器的对性能产生影响的例子。

我们将创建一个表包含 10 万行， Col1 数据顺序增长，Col2 中值为 5 的行数 90001，Col2 的其他行在 10000 之内均匀分布，同时在 Col2 上存在一个索引。我们将考查当 col2 数据分布的不均衡时，语句集中器是否启动对执行计划的影响。

测试表创建脚本：

drop　table　test;　
　CREATE　TABLE　test　
　(　
　col1　int,　
　col2　int,　
　padding　char(50)　
　);　
　
　create　index　idx_test_col2　on　test(col2);　
　
　INSERT　INTO　test　(col1,　col2,padding)　
　WITH　TEMP　(COUNTER,　col1,　col2,padding)　AS　
　(　
　VALUES　(0,　0,MOD(INT(RAND()　*　10000),　10000),'A')　
　UNION　ALL　
　SELECT　
　(COUNTER　+　1),(COUNTER　+　1),MOD(INT(RAND()　*　10000),　10000),'A'　
　FROM　
　TEMP　
　WHERE　
　(COUNTER　+　1)　<　10000　
　)　
　SELECT　
　col1,　col2,padding　
　FROM　
　TEMP;　
　　
　INSERT　INTO　test　(col1,　col2,padding)　
　WITH　TEMP　(COUNTER,　col1,　col2,padding)　AS　
　(　
　VALUES　(10000,　10000,5,'A')　
　UNION　ALL　
　SELECT　
　(COUNTER　+　1),(COUNTER　+　1),5,'A'　
　FROM　
　TEMP　
　WHERE　
　(COUNTER　+　1)　<　100000　
　)　
　SELECT　
　col1,　col2,padding　
　FROM　
　TEMP;　
　　　　
　runstats　on　table　db2inst1.test　with　DISTRIBUTION　ON　all　COLUMNS　and　indexes　all　;

STMT_CONC 关闭时的执行计划

我们执行一下命令确认 STMT_CONC 关闭：

db2　update　db　cfg　for　sample　using　STMT_CONC　off

我们准备比较下面两个 SQL 的执行计划。

Select　*　from　test　where　col2=1;　
　Select　*　from　test　where　col2=5;

首先我们看到 Col2=1 的执行计划如下，我们看到 DB2 首先对 IDX_TEST_COL2 进行扫描，然后根据 RID 去表中读取数据，整个语句的成本是 15.1589，其中 IO 成本是 2，返回的结果行数估计为 1.56909 。

db2　set　current　explain　mode　explain　
　db2　select　*　from　test　where　col2=1　
　db2exfmt　-d　sample　-w　-1　-n　%　-s　%　-#　0　–　t　
　
　Total　Cost:　15.1589　
　Query　Degree:　1　
　
　　　Rows　
　　RETURN　
　　(　　1)　
　　　Cost　
　　　I/O　
　　　|　
　　1.56909　
　　FETCH　
　　(　　2)　
　　15.1589　
　　　　2　
　　/---+----\　
　　　1.56909　　　100000　
　　　IXSCAN　　TABLE:　DB2INST1　
　　　(　　3)　　　　TEST　
　　　7.59362　　　　Q1　
　　　　1　
　　　　|　
　　　100000　
　INDEX:　DB2INST1　
　IDX_TEST_COL2　
　　　　Q1

首先我们看到 Col2=5 的执行计划如下，我们看到 DB2 不再对 IDX_TEST_COL2 进行扫描，而是直接进行表扫描，整个语句的成本是 953.761，其中 IO 成本是 878，返回的结果行数估计为 90001 。

db2　set　current　explain　mode　explain　
　db2　select　*　from　test　where　col2=5　
　db2exfmt　-d　sample　-w　-1　-n　%　-s　%　-#　0　–　t　
　
　Total　Cost:　953.761　
　Query　Degree:　1　
　
　　　　Rows　
　　　　RETURN　
　　　　(　　1)　
　　　　Cost　
　　　　　I/O　
　　　　　|　
　　　　90001　
　　　　TBSCAN　
　　　　(　　2)　
　　　　953.761　
　　　　　878　
　　　　　|　
　　　　100000　
　　TABLE:　DB2INST1　
　　　　TEST　
　　　　　Q1

DB2 使用上面两个执行计划获取数据时，SQL 花费的时间分别时

STMT_CONC 开启时的执行计划

我们执行一下命令确认 STMT_CONC 打开：

db2　update　db　cfg　for　sample　using　STMT_CONC　LITERALS

首先我们看到 Col2=1 和 Col2=5 时执行计划相同，这符合我们对 STMT_CONC 设置为 LITERALS 后的预期。执行计划显示 DB2 首先对 IDX_TEST_COL2 进行扫描，然后根据 RID 去表中读取数据，整个语句的成本是 20.461，其中 IO 成本是 2.69924，返回的结果行数估计为 15.748 。

这个执行计划与上面的执行计划比较后，我们发现在 Col2=1 时结果集大小被高了，而 col2=5 时结果集大小有被严重低估了，而且由于成本、结果集的误估计导致执行计划也发生了变化，变成了先扫描 IDX_TEST_COL2 进行扫描，然后根据 RID 去表中读取数据。

db2　set　current　explain　mode　explain　
　db2　
　　　"select　*　from　test　where　col2=5"　
　db2exfmt　-d　sample　-w　-1　-n　%　-s　%　-#　0　–　t　
　
　Total　Cost:　20.461　
　Query　Degree:　1　
　
　　　　　　　　　　　　　　　　　　　　　　　Rows　
　　　RETURN　
　　　(　　1)　
　　　Cost　
　　　　I/O　
　　　　|　
　　　15.748　
　　　FETCH　
　　　(　　2)　
　　　20.461　
　　　2.69924　
　　/---+----\　
　　　15.748　　　　100000　
　　　IXSCAN　　TABLE:　DB2INST1　
　　　(　　3)　　　　TEST　
　　　7.60197　　　　Q1　
　　1　
　　　　|　
　　　100000　
　INDEX:　DB2INST1　
　　IDX_TEST_COL2　
　　　　Q1

执行时间比较

我们使用下面脚本测试在 STMT_CONC 打开或者关闭的情况下，查询时间的变化：

db2　"connect　to　sample"　
　db2　"values　current　timestamp"　
　db2　"select　*　from　test　where　col2=$i"　>/dev/null　
　db2　"values　current　timestamp"

其中 i 取值分别为 1 和 5，执行脚本后获得以下结果。

	STMT_CONC=off	STMT_CONC=LITERALS
Col2=1	0.607 秒	0.732 秒
Col2=5	17.73 秒	21.65 秒

测试结果表明在 col2=5 时由于启动了连接集中器导致执行计划选择错误，执行时间增加了约 3 秒钟

结束语

DB2 通过配置参数 STMT_CONC 决定是否在数据库级别上启动语句集中器。如果我们希望精确控制语句集中器的使用，可以在客户端上进行配置。语句集中器减少了动态 SQL 语句的编译次数，比较适用于有大量小 SQL 执行的 OLTP 环境，对 OLAP 环境或者一个语句中的不同字面值导致执行计划显著不同，那么需要慎重考虑语句集中器的启用。