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

语句集中器的启用

　　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=offSTMT_CONC=LITERALS

　　Col2=10.607 秒0.732 秒

　　Col2=517.73 秒21.65 秒

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

　　结束语

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