WINDOWS下的程序员出身，偶尔也写一些linux平台下小程序， 后转行数据库行业，专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群：58308065，23618606
全部博文（599）
分类： Oracle
2010-07-23 14:09:03
有时候我们的应用程序没有使用绑定变量，而修改程序可能有点困难，我们可能需要设置CURSOR_SHARING=SIMILAR来强制ORACLE使用绑定变量。不过这个设置并不是一定起作用的，
下面几种情况设置CURSOR_SHARING=SIMILAR不起作用。
SQL> CONN YSP/YSP
Connected.
SQL> CREATE TABLE TEST(ID ,NAME) AS SELECT ROWNUM,OBJECT_NAME FROM ALL_OBJECTS WHERE ROWNUM<=10;
Table created.
SQL> SHOW PARAMETER CURSOR_SHARING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> COL NAME FORMAT A20
SQL> ALTER SESSION SET CURSOR_SHARING=SIMILAR;
Session altered.
SQL> SHOW PARAMETER CURSOR_SHARING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string SIMILAR
1、如果不收集统计信息，那么ORACLE每次还是单独生成子游标
SQL> SELECT * FROM TEST WHERE ID=1;
ID NAME
---------- --------------------
1 ICOL$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SQL> SELECT * FROM TEST WHERE ID=2;
ID NAME
---------- --------------------
2 I_USER1
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 1
SQL> SELECT * FROM TEST WHERE ID=3;
ID NAME
---------- --------------------
3 CON$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 1
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 2
可以看到，执行3次，ORACLE并没有共享第一次的执行计划。
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',method_opt=>null);
PL/SQL procedure successfully completed.
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
no rows selected
SQL> SELECT * FROM TEST WHERE ID=1;
ID NAME
---------- --------------------
1 ICOL$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SQL> SELECT * FROM TEST WHERE ID=2;
ID NAME
---------- --------------------
2 I_USER1
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SQL> SELECT * FROM TEST WHERE ID=3;
ID NAME
---------- --------------------
3 CON$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
可以看到，在收集了统计信息后，ORACLE共享了前面的执行计划。
2、即使收集了统计信息，如果用了范围查找，那么ORACLE每次还是单独生成子游标
SQL> alter system flush shared_pool;
System altered.
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
no rows selected
SQL> SELECT * FROM TEST WHERE ID>=1 AND ID<=3;
ID NAME
---------- --------------------
1 ICOL$
2 I_USER1
3 CON$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID>=:"SYS_B_0" 0
AND ID<=:"SYS_B_1"
SQL> SELECT * FROM TEST WHERE ID>=1 AND ID<=2;
ID NAME
---------- --------------------
1 ICOL$
2 I_USER1
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID>=:"SYS_B_0" 0
AND ID<=:"SYS_B_1"
SELECT * FROM TEST WHERE ID>=:"SYS_B_0" 1
AND ID<=:"SYS_B_1"
SQL> SELECT * FROM TEST WHERE ID>=2 AND ID<=4;
ID NAME
---------- --------------------
2 I_USER1
3 CON$
4 UNDO$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID>=:"SYS_B_0" 0
AND ID<=:"SYS_B_1"
SELECT * FROM TEST WHERE ID>=:"SYS_B_0" 1
AND ID<=:"SYS_B_1"
SELECT * FROM TEST WHERE ID>=:"SYS_B_0" 2
AND ID<=:"SYS_B_1"
3、如果相应列上有直方图，那么ORACLE每次还是单独生成子游标
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
no rows selected
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',method_opt=>'for columns id size 128');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TEST WHERE ID=1;
ID NAME
---------- --------------------
1 ICOL$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SQL> SELECT * FROM TEST WHERE ID=2;
ID NAME
---------- --------------------
2 I_USER1
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 1
SQL> SELECT * FROM TEST WHERE ID=3;
ID NAME
---------- --------------------
3 CON$
SQL> SELECT SQL_TEXT,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM TEST WHERE%';
SQL_TEXT CHILD_NUMBER
---------------------------------------- ------------
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 0
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 1
SELECT * FROM TEST WHERE ID=:"SYS_B_0" 2
