设置CURSOR_SHARING=SIMILAR需要注意的事情
时间：2010-07-23 来源：TOMSYAN
有时候我们的应用程序没有使用绑定变量，而修改程序可能有点困难，我们可能需要设置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
相关阅读 更多 +
