jboss7 oracle连接池,JBOSS连接池inlist查询优化

部分同学可能不明白什么是in list的SQL，这里先说明一下，所谓in list的SQL就是指使用了in来进行查询，绑定变量个数不确认的SQL，如:

select * from test where id in (:1,:2,:3)

对于这一类的查询，由于in的查询条件中绑定变量个数的不同，会导致SQL版本变多，从而导致PreparedStatementCache的命中率下降。(因为JBOSS/oracle中都是以SQL文本完全一致来匹配PreparedStatementCache)。

因为PreparedStatementCache在MYSQL中没有作用(根本原因是MYSQL不支持绑定变量)，所以MYSQL不需要考虑in list在PSCACHE中的优化，下面给出oracle中两种in list sql的解决方案：

方案1：使用oracle中的pipelined function(可以认为是一种性能较好的存储过程)，实现如下函数：

create or replace type t_array

as table of varchar2(4000)

/

--The PIPELINED keyword on line 4 allows this function

-- to work as if it were a table:

create or replace function

str2varlist(p_string in varchar2)

return t_array

PIPELINED

as

v_str VARCHAR2 (4000) DEFAULT p_string || ',';

v_n number(11);

begin

LOOP

v_n := INSTR (v_str, ',');

EXIT WHEN (NVL (v_n, 0) = 0);

pipe row(LTRIM (RTRIM (SUBSTR (v_str, 1, v_n - 1))));

v_str := SUBSTR (v_str, v_n + 1);

END LOOP;

return;

end;

/

这个函数的作用是将以逗号分隔的字符串转换成一个table，如下:

zhoucang@zhoucang>select * from TABLE(str2varlist('123,456,789,012,345'));

COLUMN_VALUE

---------------------------------------------------------------------------

123

456

789

012

345

5 rows selected.

这样，每次我们的SQL执行时，只要传入一个固定长度的字符串即可,如：

select * from test where id in(select * from TABLE(str2varlist(:1)))

或者写成join的方式：

select /*+ use_nl(a b) ordered*/ b.* from

TABLE(str2varlist(:1)) a,test b

where a.column_value = b.id

这也不失为一种好方法，至少使用起来非常的方便。

方案2：这是一种看起来很土的方法，同事建议的，真的很土，即固定in后面条件的个数，每次传入不同的值，不足的可以使用一个不存在的值来进行补充。如：

select * from test where id in(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)

固定10个ID的查询，不足10个以-1来补充(如果ID不为负数)。

因为oracle中的in限制最多为999个，所以，极端情况下，也只需要固定999个ID即可。

如何选择方案1还是方案2？

专门测试了一下，测试过程主要逻辑如下：

方案1，创建pipelined function,执行20000次查询：

sql = " select /*+ use_nl(a b) ordered*/ b.* from

TABLE(str2varlist(:1)) a,test b

where a.column_value = b.id ";

for (int i = 0; i < 20000; i++) {

stmt1.setString(1,"1111,2222,3333");

}

方案2，固定绑定变量个数，执行20000次查询：

sql = "select * from test where id in(?,?,?,?,?,?,?,?..........) ";

for (int i = 0; i < 20000; i++) {

stmt.setString(1, "1111");

stmt.setString(2, "2222");

stmt.setString(3, "3333");

stmt.setString(4, "-1");

stmt.setString(5, "-1");

stmt.setString(6, "-1");

……

}

测试主要以数据库的性能损耗为主要考虑指标(因为应用上带来的好处对我们来说诱惑力实在太大了，基本上没什么坏处，性价比很高)，测试结果如下(给出数据库中单个SQL主要的性能参考指标)：

说明:

应用总执行时间：秒

内存读(块)

返回记录数

单条SQL CPU时间(us)

单条SQL响应时间

3个绑定变量，传3个id

38

9

3

75.84365

75.84365

20个绑定变量，传3个id

39

10.06

3

94.48995

94.48995

100个绑定变量, 传3个id

43

10.05955

3

112.8007

112.8007

PPLINE,3个id

41

9.01435

3

149.5074

149.5074

100个绑定变量，传10个id

53

31.05955

10

220.55765

220.55765

10个绑定变量,传10个id

43

30

10

174.7577

174.7577

PPLINE,10个id

51

30.03795

10

358.46375

358.46375

PPLINE方案(方案1)：

1． CPU：会增加CPU的消耗1倍左右，原因是数据库需要对字符串进行解析。

2． IO:对IO基本无损耗。

3． 响应时间:数据库响应时间增加一倍。

固定绑定变量个数(方案2) ：

1． CPU：CPU的消耗，增加幅度在20%~60%，之间，具体因in的个数而定。

2． IO:增加了一个逻辑读，这一个逻辑读是由于查询-1时产生的，主要是由于索引branch节点查询引起，由于root节点一定需要查询，索引一般为3-4层，可以认为，这个逻辑读开销在1-3个左右，基本上可以认为在1个左右(这个可以结合B树索引结构得知)。并且这个branch节点非常热，IO开销肯定是逻辑读。

3． 响应时间: 增加幅度在20%~60%，之间，具体因in的个数而定。

显然，选择方案2会更加节省数据库的资源。

从测试结果可以看出，当传入3个ID的时候，使用100个绑定变量和20个绑定变量，还是存在一定的差异。因此，对于in的SQL我们可以再进行分级，根据执行频率，评估适当的多给出几个版本，如：

Id=:1 :适用于1个ID的查询

Id in(20个绑定变量) :适用于ID个数据在2-20个之前的查询。

Id in (1000绑定变量) :适用于ID个数在20个以上的查询。