oracle cursor_sharing 参数设置测试

题目：Oracle的cursor_sharing参数告诉Oracle数据库何时重用SQL语句，有Exact、force和similar三种设置，请阐述三种设置的区别，并通过一个例子距离说明三者之间的区别。最后请设计一个实际应用场景，执行一系列操作，说明这个参数设置的意义（提示，可以设计一个循环操作语句来验证），分别说明在什么情况下用何种参数设置。

 

 

答：

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

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

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

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

 

比如执行三条sql语句分别为：

Select * from dba_objects where object_id=7000；

Select * from dba_objects where object_id=7001；

Select * from dba_objects where object_id=7002；

 

在参数为exact的情况下，数据库将执行3次硬解析，因为每一条语句并不是完全相同，所以每一条语句都会被硬解析一次。

而在参数为force的情况下，Oracle对输入的SQL值，会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时，会进行cursor sharing共享游标。所以该情况下只会对第一条语句执行1次硬解析，然后对后面两条语句执行软解析。

而在本例的情况下，因为谓词条件并不会影响到执行计划，所以结果和force是一样的，oracle执行一次硬解析，两次软解析。

 

 

合适的cursor_sharing参数设置能够让数据库在执行相似的sql操作时显著提高效率，从而提高数据库的性能。下面是不同参数设置下的oracle数据库执行1000条相似的select语句所花时间的对比。

 

 

测试方法：

让数据库在不同的参数设置下执行一个相同的sql脚本，观察执行完成所需要的时间。

测试使用的是oracle12 c数据库 用sys用户登入，在cdb$root中查询dba_objects表中的一千条纪录。

 

 

Sql脚本如下：

alter system flush shared_pool;//清空shared_pool

alter system flush buffer_cache;//清空buffer_cache

alter session set cursor_sharing='exact';//设置cursor_sharing参数为exact

show parameter cursor_sharing;//显示当前的参数值

 

SET TERMOUT OFF;//关闭查血结果显示

SET ECHO OFF;//关闭回显

 

select * from dba_objects whereobject_id=1000;

select * from dba_objects whereobject_id=1001;

select * from dba_objects whereobject_id=1002;

select * from dba_objects whereobject_id=1003;

select * from dba_objects whereobject_id=1004;

select * from dba_objects whereobject_id=1005;

select * from dba_objects whereobject_id=1006;

select * from dba_objects whereobject_id=1007;

select * from dba_objects whereobject_id=1008;

 

此处省略n行。。。。。。。。。。。。。。。。。。。。。。。。。。。

 

select * from dba_objects whereobject_id=1995;

select * from dba_objects whereobject_id=1996;

select * from dba_objects whereobject_id=1997;

select * from dba_objects whereobject_id=1998;

select * from dba_objects where object_id=1999;

 

 

SET TERMOUT on;//显示查询结果

SET ECHO on;//打开回显

Select count(*) from dba_objects whereobject_id>=1000 and object_id<=1999;

//输出有效记录条数

 

Cursor_sharing参数为Exact时的执行结果和执行时间

通过截图可以看到:

从23:58:39开始执行，到最终00:00:12执行完成。

查询1000条记录所用的时间为93s

Cursor_sharing参数为force时的执行结果和时间

通过截图可以看到在参数为force的情况下

开始执行时间为00:14：49，执行完1000条语句的时间为00：14：51

总共用时为2s。相较于exact的时候缩短了91s。

 

Cursor_sharing参数为similar时的执行结果和时间

通过截图可以看到：

开始执行时间为00:19:53，而执行完1000条语句的时间为时间为00:19:53

总用时为2s，和force的结果一样。

由于测试脚本的条件分部是均匀的，所以此时的similar实际上和force是一样的模式，于是使用的时间相同。

 

 

 

 

通过以上的测试可以发现，在某些特定的情况下，使用合适的cursor_sharing参数能够显著的提高数据库的效率和性能。

 

通过总结和查询资料我们可以得到以下结论：

1.在cursor设置为exact时，两条sql语句如果存在一点不同，就不会共享cursor，而进行两次硬解析。

2.对与参数cusor_sharing设置为force时，根据实验，我们可以得出下列结论：

·      Oracle对输入的SQL值，会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时，会进行cursor sharing共享游标；

·      在第一次进行自动替换绑定变量的时候，Oracle会依据bind peeking取值，获取到一个执行计划，对应成子游标；

·      在以后的SQL语句中，只要出现父游标可共享的情况，Oracle都会强制使用生成的唯一子游标进行sharing。不去在乎是不是对SQL是最优的执行计划；

·      FORCE取值的规则思想很简单，对SQL语句进行强制的绑定变量替换。使用第一次的bind peeking值生成执行计划，之后全部使用这个执行计划。这种方式实现了游标共享，避免出现大量的library cache硬解析，限制一个父游标的version_count数量。

·      如果这种SQL语句本身是“Good SQL”，也就是条件列分布比较平均，没有出现过大的偏移分布。我们认为这种FORCE是很有益的。但是如果数据列分布不平均，这样借用第一次输入的bind peeking生成并且共享执行计划就很成问题。我们说，在cursor_sharing取定FORCE遇到的潜在问题，和我们使用绑定变量时候使用的bind peeking值问题是相同的。

3.当cursor_sharing设置为SIMILAR的时候，Oracle对没有使用绑定变量的SQL字面语句都会进行处理，将where条件后自动替换为绑定变量；

·      在执行语句是，对每一个条件设置值，都会生成一个新的child cursor子游标，与父游标相对应。当一个语句输入的时候，如果之前存在过相同条件值的SQL子游标，就共享该子游标。否则生成一个新的child cursor，生成一个匹配的执行计划；

·      SIMILAR是FORCE的进化版。在SIMILAR模式下，Oracle对游标共享的条件变得比较敏感。如果绑定变量值发生变化，就意味着执行计划可能存在不匹配的情况。所以索性Oracle对每一个新的值都bind peeking一下，生成执行计划。而执行计划游标的共享只在相同绑定变量的时候才发生。

·      这个与FORCE相比，cursor_sharing=SIMILAR的确缓解了由于bind peeking单次带来的执行计划不匹配问题。但是会引入两个新问题：

·      如果对应条件列的取值相对较少，这样生成执行计划的个数起码是可以控制的。如 果是一种连续取值情况或者对应取值很多，必然引起parent cursor对应的child cursor数目增多，每次从child cursor列中遍历的时间增加，latch和pin发生的时间增多。这也是similar取值是一个常见的问题

·      生成child cursor的标准不是是否执行计划相同，而是绑定变量值相同。这样如果数据分布较为平均，所有值对应的执行计划都是相同的。那么生成很多的子游标执行计划必然是相同的。这样又会带来性能和其他一些问题。

·      在谓词条件的列后没有直方图时，其作用和force类似，会共享子游标（可以删除where条件后列上的直方图重复最后一步实验验证）

Cursor_sharing设置为similar，存在一些BUG，即使是force也同样有BUG存在。例如导致EXPDP的导出时间变成，物化视图的更新变慢等。Oracle 并不建议你设置此参数。在12C中ORACLE将废除similar的设置。

 

 

如何选择合适的参数：

1.  通过上面的总结我们可以发现如果相似sql语句中的条件分布列比较均匀，那么一般可以使用force参数，因为这样能够显著的提高语句的执行效率。

2.而如果sql语句中的条件分部不均匀甚至有很大的偏差的时候，使用force就会有很大的问题。例如对于select * from awherea<10(1万条里面找10条)和select * from a where a<9000 (1万条里面找9000条)这样类型的SQL使用绑定变量,可能会带来负面影响。

下面的截图可以证明这一点（将前面的测试用例换为select * from dba_objects where object_id<xxxx; 执行1000条相似语句）

3.  还有就是对于OLTP系统，若绑定变量情况不好的话，重新修改应用程序代价太大, 可以考虑通过设置这个参数来稍微缓解一下问题；但对于OLAP系统，这个参数应该设置成EXACT，且不应该使用绑定变量，因为在OLAP系统中，SQL的解析对于SQL的执行来看，花费的代价几乎可以忽略，而正确的SQL执行计划才是OLAP数据库最需要关注的。