概述

大家都知道硬解析，软解析对数据库sql的执行效率影响是很大的。在Oracle中，用户输入的SQL语句要进行所谓的Parse解析过程，用于生成执行计划，这也就是Query Optimizer的主要工作。在Parse中，有两种具体类型，被称为“hard parse”（硬解析）和“Soft parse”（软解析）。

“实现执行计划shared cursor共享，减少硬解析”是我们OLTP系统优化一个重要方向。但是，让Oracle真正实现SQL共享不是一件容易的事情，受到很多其他因素的影响。最常用的方式是使用绑定变量，让SQL字面值保持一致。如果应用端没有使用绑定变量，其中一种做法是设置系统参数cursor_sharing，将SQL语句中的条件进行绑定变量替换。

一、Cursor_sharing简介：

这个参数是用来告诉Oracle在什么情况下可以共享游标，即SQL重用。

Cursor_sharing参数有3个值可以设置：

1)、EXACT：通常来说，exact值是Oracle推荐的，也是默认的，它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。

2)、SIMILAR：similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时，才会被重新分析，否则将重用SQL。

3)、FORCE：force是在任何情况下，无条件重用SQL。

备注：上面所说的SQL重用，仅仅是指谓词条件不同的SQL语句，实际上这样的SQL基本上都在执行同样的业务操作。

二、在Cursor_sharing参数值不同时对SQL的影响：

1、创建实验环境：

 ----首先创建一张hwb表----
 SQL> create table hwb (id int,name varchar2(10));
 ----产生一些数据----
 SQL> insert into hwb values(1,'aa');
 SQL> insert into hwb values(2,'bb');
 SQL> insert into hwb values(3,'cc');
 SQL> insert into hwb values(4,'dd');
 SQL> commit;
 SQL> select * from hwb;

IT知识分享网

IT知识分享网 ----创建下面实验将要用到的三张表----
 SQL> create table hwb_exact as select * from hwb;
 SQL> create table hwb_similar as select * from hwb;
 SQL> create table hwb_force as select * from hwb;
 ----查看该session的trace文件的路径----
 SQL> oradebug setmypid
 SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/nwppdb/nwppdb/trace/nwppdb_ora_3553.trc

2、 cursor_sharing=exact的情况：

 ----将cursor_sharing设置为exact----
 SQL> alter session set cursor_sharing=exact;
 SQL> alter session set sql_trace=true;
 SQL> select * from hwb_exact where id=1;
 SQL> select * from hwb_exact where id=3;
 SQL> select * from hwb_exact where id=1;
 SQL> alter session set sql_trace=false;

IT知识分享网 ----从下面的查询可以看出执行了两次硬解析----
 SQL> select sql_text from v$sql where sql_text like 'select * from hwb_exact where%';

----查看trace文件，通过tkprof工具
[oracle@nwppdb:/u01/app/oracle/diag/rdbms/nwppdb/nwppdb/trace]$tkprof nwppdb_ora_3553.trc nwppdb_ora_3553.txt aggregate
内容比较多，就不介绍了，这里可以通过输出文件看到有两次硬解析一次软解析

总结：当cursor_sharing=exact时，只有当SQL语句是完全一样的情况下才能被重用。

3、 cursor_sharing=similar的情况：

 ----将curor_sharing设置为similar----
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/nwppdb/nwppdb/trace/nwppdb_ora_3671.trc
SQL> alter session set cursor_sharing=similar;
SQL> alter session set sql_trace=true;
SQL> select * from hwb_similar where id=1;
SQL> select * from hwb_similar where id=4;
SQL> select * from hwb_similar where id=8;

---下面查询中可以看到Oracle将SQL中的谓词条件用同一个名词的一个变量替代，尽管看起来是一样的，但是Oracle依然把它们作为两条SQL来处理----
 SQL> select sql_text from v$sql where sql_text like 'select * from hwb_similar where%';
 SQL> alter session set sql_trace=false;
[oracle@nwppdb:/]$tkprof /u01/app/oracle/diag/rdbms/nwppdb/nwppdb/trace/nwppdb_ora_3671.trc /home/oracle/nwppdb_ora_3671.txt sys=no

从trace文件中可以清楚看到上面的结论—-

对于SIMILAR的情况，如果CBO发现被绑定变量的谓词还有其他的执行计划可以选择时，如果谓词条件的值有变化，就将会产生一个新的子游标，而不是重用之前的SQL；如果谓词没有其他的执行计划可选择，则忽略谓词的值，重用之前的SQL。

4、 cursor_sharing=force的情况

 ----设置cursor_sharing=force----
 SQL> alter session set cursor_sharing=force;
 SQL> alter session set sql_trace=true;
 SQL> select * from hwb_force where id=1;
 SQL> select * from hwb_force where id=4;
 SQL> select * from hwb_force where id=1;
 SQL> alter session set sql_trace=false;

 ----从下面的查询中可以看出只进行了一次硬解析，而且使用了绑定变量----
 SQL> select sql_text from v$sql where sql_text like 'select * from hwb_force where%';
 ----查看trace文件内容----
[oracle@nwppdb:/home/oracle]$tkprof /u01/app/oracle/diag/rdbms/nwppdb/nwppdb/trace/nwppdb_ora_3697.trc /home/oracle/nwppdb_ora_3697.txt aggregate=no sys=no

总结：force是在任何情况下，无条件重用SQL。

总结：

FORCE和SIMIALR最大的区别在于，FORCE会把所有的谓词用变量代替，并且不管变量的值如何，一律重用第一条SQL语句，而SIMILAR会根据谓词的不同，来重新选择SQL的执行计划。

如果一个系统，它存在变量绑定的问题，并且这种问题已经影响到了系统的性能，这时候可以考虑将参数cursor_sharing的值设置为SIMILAR或FORCE来改善这种局面，不过在改成SIMILAR或FORCE都可能带来一些Bug以及很多未知的东西，所以需要慎用。

最后，对于OLTP系统，如果绑定变量情况不好的话，也许可以考虑通过设置这个参数来缓解一下问题；对于是在OLAP系统上，这个参数应该设置成EXACT，并且不应该使用绑定变量，因为在OLAP系统中，SQL的解析对于SQL的执行来看，花费的代价几乎可以忽略，而正确的SQL执行计划才是OLAP数据库最需要关注的。