做优化的朋友都知道oracle提供了共享游标这么个开关:cursor_sharing,当我们设定cursor_sharing=force 时对于绑定变量用的很差的系统确实能起到一定的优化效果。我们要清楚一个事实,如果一个功能百利而无一害时,oracle自然就会将其设为默认值,而不会让我们手动去打开这个开关!!!所以使用这个功能肯定有它的弊端,我们来看看
1. 执行个简单的查询,只要求返回一行
SELECT/*zhuge*/ object_id FROM test t WHERE ROWNUM=1
2. 打开共享游标开关
ALTER SESSION SET cursor_sharing=Force;
3.执行同样的查询(为了不共享sql,改下别名)
SELECT/*zhuge*/ object_id FROM test WHERE ROWNUM=1
首先有个明显的感觉,第二个sql执行的时间远远超过第一个sql,为什么了,我们先把2个sql找出来
SELECT * FROM v$sql t WHERE t.SQL_TEXT LIKE 'SELECT/*zhuge*/%';
在sql_text中显示2个sql的情况如下:
SELECT/*zhuge*/ object_id FROM test t WHERE ROWNUM=1
SELECT/*zhuge*/ object_id FROM test WHERE ROWNUM=:"SYS_B_0"
可以看到对于共享游标打开的sql,oracle会自动将sql的字面量用变量来代替,用于实现绑定变量,这表明看起来不错,不要要命的是,oracle并不知道哪些字面量应该用变量代替,像上面的sql rownum=1这样的关键信息是完全不应该用绑定变量来代替的,这样就造成了oracle在分析sql采用执行计划时,它就会茫然,不知道你需要返回多少行,这样就很可能采用错误的执行计划。。。
我们来看看这两个sql的执行计划就一目了然了:
第一个:
1 SQL_ID 4axzqdybg46ms, child number 0
2 -------------------------------------
3 SELECT/*zhuge*/ object_id FROM test t WHERE ROWNUM=1
4
5 Plan hash value: 3931117773
6
7 ---------------------------------------------------------------------------
8 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
9 ---------------------------------------------------------------------------
10 | 0 | SELECT STATEMENT | | | | 2 (100)| |
11 |* 1 | COUNT STOPKEY | | | | | |
12 | 2 | TABLE ACCESS FULL| TEST | 1 | 4 | 2 (0)| 00:00:01 |
13 ---------------------------------------------------------------------------
14
15 Predicate Information (identified by operation id):
16 ---------------------------------------------------
17
18 1 - filter(ROWNUM=1)
第二个:
1 SQL_ID 2mzqdacqm3r11, child number 0
2 -------------------------------------
3 SELECT/*zhuge*/ object_id FROM test WHERE ROWNUM=:"SYS_B_0"
4
5 Plan hash value: 2091529335
6
7 ----------------------------------------------------------------------------
8 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
9 ----------------------------------------------------------------------------
10 | 0 | SELECT STATEMENT | | | | 1801 (100)| |
11 | 1 | COUNT | | | | | |
12 |* 2 | FILTER | | | | | |
13 | 3 | TABLE ACCESS FULL| TEST | 4980K| 18M| 1801 (7)| 00:00:22 |
14 ----------------------------------------------------------------------------
15
16 Predicate Information (identified by operation id):
17 ---------------------------------------------------
18
19 2 - filter(ROWNUM=:SYS_B_0)
可以看到两者有明显的差别,所以大家在写sql的时候能有绑定变量就尽量用绑定变量,不要事后希望通过oracle提供的这个共享游标的开关来实现,有时候会适得其反的。。。