Cursor Sharing in Oracle Database 11g

遭遇过一次由于cursor_sharing = similar导致的系统问题，大量游标无法共享，产生巨大的version count，最终让整个系统崩溃。

在这个案例中我提到有4个条件导致了问题的发生：
1. cursor_sharing = similar
2. 收集了列上的histogram
3. SQL中使用到了此列作为条件，并且条件是“等于”
4. 这个SQL是没有绑定变量的

在最近Optimizer Development Group的Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g文章中又再次提到这个现象。

This is in fact the expected behavior. when
1. CURSOR_SHARING is set to similar
2. Bind peeking is in use
3. And a histogram is present on the column used in the where clause predicate of query

在Oracle10g中这是正常的现象，如果在某列上收集了histograms信息，那么就等于告诉CBO这一列上的数据是不平衡的，如果都使用同一个执行计划那么就可能产生问题，因此对于每一个distinct值，CBO都会产生一个child cursor，这一点无法避免。当然，由于这是child cursor，因此比cursor_sharing = exact时候产生的parent cursor还是要节省内存空间，至少SQL语句本身不需要重复存储了。

在Oracle10g中解决方法是：
1. 去掉这列上的histograms统计信息，或者
2. 将CURSOR_SHARING = FORCE

虽然源于对Oracle Database的热爱，我们无条件接受了10g中的这个方式，并且不认为这是bug，但是实际上心里一定暗暗骂过，傻啊，我收集了histogram你就要每个值产生1个cursor吗？自己再分析一下不行吗？都是一样的执行计划何必要用不同的cursor空间呢？

我想Oracle自己也一定意识到了这点，于是在Oracle11g中这一切有了变化，Oracle推出了称为Adaptive Cursor Sharing（自适应游标共享）的游标共享机制，可以阅读这几篇相关文章。

Optimizer Development Group：Adaptive Cursor Sharing
Optimizer Development Group：Update on Adaptive Cursor Sharing
Arup Nanda：Adaptive Cursors and SQL Plan Management
Tim Hall：Adaptive Cursor Sharing in Oracle Database 11g Release 1

什么是Adaptive Cursor Sharing就不重复叙述了，上面的几篇文章说的非常清楚，那么ACS机制对于之前在10g中碰到的child cursor过多的情况有何种改善呢？

简单地说，在Oracle11g中我们可以保留cursor_sharing=similar并且也保留列上的histograms统计信息，ACS机制将不会对每一个distinct值都产生一个child cursor，而是对每一个不同的执行计划产生一个child cursor，这大大减少了子游标的数量。这种处理方式无疑是合理的，只有在执行计划确实需要不相同的时候才产生额外的child cursor，当然，在bind peeking之后，CBO是否确实能够选择一个最优的执行计划那另当别论，是另外的话题。

注意，实际上产生的child cursor数量仍然是会大于execution plan数量的，也就是加入对于一个绑定变量的SQL一共有2种执行计划，那么child cursor数量会大于2，因为CBO始终在监控SQL的执行效率，如果认为变量的某一个真实值跟其它值的分布情况有很大的不同，那么CBO就会让这个SQL再做一次hard parse，这样就会产生出来一个新的child cursor，即使最终这个cursor的执行计划还是跟之前的相同。但是我们不用担心这些多余的cursor，因为这些cursor被标志为无法共享（可以通过v$sql.is_shareable字段得知），在需要的时候将会被age out出去。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/730796/viewspace-607397/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/730796/viewspace-607397/