ORACLE cursor_sharing的不同取值对SQL的影响

一、创建测试表
 SQL> create table  testcursor as select 3333 as id, t.* from dba_objects t;
 
Table created
 二、创建索引
SQL> create index idx_testcursor on testcursor(id);
 
Index created
 三、查看当前参数 
SQL> show parameter cursor_sharing;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT


SQL> select * from testcursor test_exact where id=1;
 
SQL> select * from testcursor test_exact where id=2;
 四、查询SQL_TEXT
SQL> select sql_text from v$sql where sql_text like ' select * from testcursor test_exact where id=%';
 
SQL_TEXT
--------------------------------------------------------------------------------
 select * from testcursor test_exact where id=1
 select * from testcursor test_exact where id=2
 
 默认情况下，cursor_sharing=exact,Oracle没有处理谓词，而是把其当作两个SQL来处理，每一条SQL都要进行一次硬分析。

五、声明变量
 SQL> variable n number;
SQL> exec :n :=1;
 
PL/SQL procedure successfully completed
n
---------
1
 
SQL>  select * from testcursor where id=:x;
 
SQL> exec :n :=2;
 
PL/SQL procedure successfully completed
n
---------
2
 
SQL>  select * from testcursor where id=:x;
六、查看SQL_TEXT
SQL> select sql_text from v$sql where sql_text like '  select * from testcursor where id=%';
 
SQL_TEXT
--------------------------------------------------------------------------------
  select * from testcursor where id=:x
  
上述语句，只执行了一次硬分析，其他都为软分析，OLTP系统中 ，此种方法最优。如果绑定变量不好，则cursor_sharing会增加硬分析次数，效率会降低。

七、设置cursor_sharing=similar
SQL> alter session set cursor_sharing=similar;
 
Session altered
 
SQL> select * from testcursor test_similar where id=1;
 
 
SQL> select * from testcursor test_similar where id=2;
 八、查看SQL_TEXT
SQL> select sql_text from v$sql where sql_text like '%test_similar%';
 
SQL_TEXT
--------------------------------------------------------------------------------
 select * from testcursor test_similar where id=:"SYS_B_0"
 select * from testcursor test_similar where id=:"SYS_B_0"
 
 设置cursor_sharing=similar后，ORALCE会将SQL语句中的谓词条件用同一名称的一个变更代替__SYS_B_0，尽管如此，ORALCE依旧会作为2条SQL处理。 如果CBO发现绑定变量的谓词还有其他可执行计划时，如果谓词的值有变化，则会产生一个新的子游标，而非重用之前的SQL,如果谓词没有其它的计划选择，则重用之前的SQL。 假定某一谓词ID有索引列，select * from t where t.id = '' and name='';如果id的值改变，则会发生一次硬分析，而name上没有索引，则不会 发生硬分析。当然，如果ID的列的范围过大，则会发生大量的硬分析。
 
九、设置 cursor_sharing=force
 SQL> alter session set cursor_sharing=force;
 
Session altered
 
SQL> select * from testcursor test_force where id=1;
 
SQL> select * from testcursor test_force where id=2;
 
SQL> select sql_text from v$sql where sql_text like '%test_force%';
 
SQL_TEXT
--------------------------------------------------------------------------------
 select * from testcursor test_force where id=:"SYS_B_0" 
 
强制使用force后，ORACLE会当作同一条语句进行处理。此种情况可能会影响正确的执行计划。

只有OLTP中，绑定变更才会有性能的提升，OLTP系统表的特点是ID列上有索引，重复率低。 

在OLTP中，建议使用EXACT，如果变量绑定不好，可考虑使用SIMILAR和FORCE， 在OLAP系统中，则一直建议使用EXACT.