参数cursor_sharing的设置导致含占位符的SQL执行变慢问题

我们的应用程序开发人员发现这样一个异常现象，某 SQL 语句在使用绑定变量时，执行的时间比不使用绑定变量时要慢很多，甚至慢到数十倍。

在应用程序中，执行的 SQL 如果没有绑定变量，那么可能会导致共享池挣用等待事件的出项。而且，这种情况在很多应用系统的程序开发中很常见。对此， Oracle 数据库系统提供了一种折中解决方法，将初始化参数 cursor_sharing 的值设置为 force 或 similar 。这样，在 Oracle 中运行的 SQL ，其常量如字符、数字等则会自动转换为变量，从而使得类似 SQL 变得一样，减少共享池挣用。

在出现该问题的数据库系统中，它的初始化参数 cursor_sharing 的值是 force 。

从问题现象看，使用绑定变量的 SQL 执行计划和不使用绑定变量的不一样，前者走的执行计划不合理。

这个 SQL 也比较复杂， where 子句中既有自定义的变量语句，也有很多常量语句。在常量条件中，有个占位符子句，紧随 where 关键字。

这类写法在 JAVA 中拼装 SQL 语句时很常见。在需要新加条件判断语句时，直接加上 ”and xx=yy” ，变成 ”where 1=1 and xx=yy” 。

这种写法很通用吧？

（miki西游 @mikixiyou 的文档，原文链接: http://mikixiyou.iteye.com/blog/1552021 ）

语句

出现问题的 SQL 语句如下：

SELECT *

   FROM (SELECT row_.*, ROWNUM rownum_

           FROM (select count(*)

                   from (select t1.id as id,

                                'sms' as type,

                                t1.empid as empid,

                                t1.deptno as deptno,

                                t1.content as title,

                                to_char(t1.send_time, 'yyyy-MM-dd hh24:mi:ss') as plantime,

                                t1.sysuid as sysuid,

                                t1.custid as custid,

                                t1.mobile as contact,

                                t1.mark as mark

                           from liantong_send_back t1

                          where 1 = 1

                            and t1.send_time >=

                                to_date('2011-11-01 00:00:00',

                                        'yyyy-mm-dd hh24:mi:ss')

                            and t1.send_time <

                                (to_date('2011-12-06 00:00:00',

                                         'yyyy-mm-dd hh24:mi:ss') + 1)

                            and t1.deptno = '3400'

                         union all

                         select t1.id as id,

                                'sms' as type,

                                t1.empid as empid,

                                t1.deptno as deptno,

                                t1.content as title,

                                to_char(t1.send_time, 'yyyy-MM-dd hh24:mi:ss') as plantime,

                                t1.sysuid as sysuid,

                                t1.custid as custid,

                                t1.mobile as contact,

                                t1.mark as mark

                           from liantong_send t1

                          where 1 = 1

                            and t1.send_time >=

                                to_date('2011-11-01 00:00:00',

                                        'yyyy-mm-dd hh24:mi:ss')

                            and t1.send_time <

                                (to_date('2011-12-06 00:00:00',

                                         'yyyy-mm-dd hh24:mi:ss') + 1)

                            and t1.deptno = '3400') T

                  where rownum < 10001

                  order by T.plantime desc, T.id) row_

          WHERE ROWNUM <= :b)

 WHERE rownum_ > :a

这个 SQL 的内部 where 条件中有一个“ 1=1 ”的条件，这是开发人员在条件不定时，动态添加条件的常用写法。这种写法在很多类型的应用中都出现过。

这个 SQL 很明显去掉 rownum 的条件判断使用的绑定变量，其他条件都是常量赋值。这是因为内部那个结果集的 SQL 是应用程序拼出来的，条件很灵活，不容易实现带变量的写法。因此我们让数据库系统在执行之前自动去修改这些常量为变量，从而实现不同常量的 SQL 能共享游标（ cursor ），减少硬分析。

设置 cursor_sharing=force ，就实现了这种自动转换。但占位符（“ 1=1 ”）也会被系统自动替换成 :"SYS_B_02" = :"SYS_B_03" 。

但在 cursor_sharing=exact 时，系统的优化器则是做了另一种操作。它将占位符（“ 1=1 ”）忽略掉，因为也确实不需要去判断，从而节省 CPU 执行时间。很聪明吧！

分析

我先做了一些简单的测试：

测试一、将 SQL 中设置的变量取消，让 SQL 完全由系统生成绑定变量，语句执行正常；

测试二、将 cursor_sharing 修改成默认值（ exact ）， SQL 使用绑定变量，语句执行也是正常；

测试三、将 SQL 中占位符 (“1=1”) 去掉，应用使用绑定变量， cursor_sharing 设置为 force ，语句执行也正常。

 

测试结果显示，如果没有占位符，就正常了。

这是怎么回事呢？

看来，必须去他们各自的分析执行计划才能明白了。

 

--待续