cursor_sharing参数！

cursor_sharing这个参数用来告诉Oracle在什么情况下可以共享游标，即SQL重用。它有3个值可以设置：CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
EXACT：SQL必须绝对一样，才可以共享游标，否则将作为新的SQL语句处理。
SIMILAR：如果CBO发现被绑定变量的谓词还有其他的执行计划可以选择时，如果谓词条件的值有变化，就将会产生一个新的子游标，而不是重用之前的sql语句；如果谓词条件没有其他的执行计划可选择，则忽略谓词的值，重用之前的sql语句。
FORCE：CBO将sql语句的所有谓词用变量替换，只做一次硬分析，之后所有的sql都重用第一个sql语句。

EXACT

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL> select id,owner,object_id,object_name from t set_exact where id = 1;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         1 SYS                                    20 ICOL$

SQL> select id,owner,object_id,object_name from t set_exact where id = 2;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         2 SYS                                    44 I_USER1

SQL> select id,owner,object_id,object_name FROM t set_exact where id = 2;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         2 SYS                                    44 I_USER1

SQL> select id,owner,object_id,object_name   FROM t set_exact where id = 2;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         2 SYS                                    44 I_USER1

SQL> select sql_text from v$sql where sql_text like '%set_exact%';

SQL_TEXT
------------------------------------------------------------------------------------------------
select id,owner,object_id,object_name from t set_exact where id = 1
select id,owner,object_id,object_name   FROM t set_exact where id = 2
select id,owner,object_id,object_name from t set_exact where id = 2
select id,owner,object_id,object_name FROM t set_exact where id = 2

可以看见sql必须完全一样，才会被重用，仅仅是谓词、大小写、空格的差别都会导致sql不被重用。

SQL> alter system flush shared_pool;

系统已更改。

SQL> variable x number;
SQL> exec :x:=1;

PL/SQL 过程已成功完成。

SQL> select id,owner,object_id,object_name from t set_exact where id = :x;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         1 SYS                                    20 ICOL$

SQL> exec :x:=2;

PL/SQL 过程已成功完成。

SQL> select id,owner,object_id,object_name from t set_exact where id = :x;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         2 SYS                                    44 I_USER1

SQL> select sql_text from v$sql where sql_text like '%set_exact%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------------
select id,owner,object_id,object_name from t set_exact where id = :x

可以看见使用了绑定变量后，sql被重用了。

SIMILAR

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter session set cursor_sharing = 'similar';

会话已更改。

SQL> show parameter cursor_sharing;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           similar

SQL> create index t_ind on t(id);

索引已创建。

SQL> select id,owner,object_id,object_name from t set_similar where id = 1;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         1 SYS                                    20 ICOL$

SQL> select id,owner,object_id,object_name from t set_similar where id = 2;

        ID OWNER                           OBJECT_ID OBJECT_NAME
---------- ------------------------------ ---------- --------------------
         2 SYS                                    44 I_USER1

SQL> select sql_text from v$sql where sql_text like '%set_similar%';

SQL_TEXT
----------------------------------------------------------------------------------------------------
select id,owner,object_id,object_name from t set_similar where id = :"SYS_B_0"
select id,owner,object_id,object_name from t set_similar where id = :"SYS_B_0"

可以看见，两条sql语句看起来是一样的，但是很显然，尽管他们看起来一样，但是Oracle依然会把它作为2条sql语句来处理。

FORCE

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter session set cursor_sharing = 'force';

会话已更改。

SQL> show parameter cursor_sharing;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           force

SQL> select object_name from t set_force where id = 1;

OBJECT_NAME
--------------------
ICOL$

SQL> select object_name from t set_force where id = 2;

OBJECT_NAME
--------------------
I_USER1

SQL> select sql_text from v$sql where sql_text like '%set_force%';

SQL_TEXT
-------------------------------------------------------------------------------------------------------
select object_name from t set_force where id = :"SYS_B_0"

这回sql被重用了。