参数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” 。这种写法很通用吧？语句出现问题的 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 ”的条件，www.linuxidc.com这是开发人员在条件不定时，动态添加条件的常用写法。这种写法在很多类型的应用中都出现过。这个 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 ，语句执行也正常。测试结果显示，如果没有占位符，就正常了。这是怎么回事呢？看来，必须去他们各自的分析执行计划才能明白了。归档不能删除报RMAN-8137错解决过程Oracle游标存储过程语句相关资讯 SQL语句

如何定位SQL语句在共享池里用到了（03月17日）
Java 注解入门自动生成SQL语句（07/28/2015 16:08:34）
Oracle 通过sql profile为sql语句（05/03/2015 19:43:07）

MySQL 存储过程动态执行sql语句（10/13/2015 19:10:08）
画图解释 SQL join 语句（07/17/2015 15:16:27）
MySQL数据库sql语句调优（03/21/2015 17:42:45）

本文评论查看全部评论（0）