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_sharingNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cursor_sharing                       string      EXACTSQL> 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_USER1SQL> select id,owner,object_id,object_name FROM t set_exact where id = 2;        ID OWNER                           OBJECT_ID OBJECT_NAME---------- ------------------------------ ---------- --------------------         2 SYS                                    44 I_USER1SQL> select id,owner,object_id,object_name   FROM t set_exact where id = 2;        ID OWNER                           OBJECT_ID OBJECT_NAME---------- ------------------------------ ---------- --------------------         2 SYS                                    44 I_USER1SQL> 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 = 1select id,owner,object_id,object_name   FROM t set_exact where id = 2select id,owner,object_id,object_name from t set_exact where id = 2select 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_USER1SQL> 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                           similarSQL> 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_USER1SQL> 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                           forceSQL> 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_USER1SQL> 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被重用了。