(转载ITPUB)关于CURSOR_SHARING三个取值的区别-优快云博客

关于CURSOR_SHARING三个取值的区别

看到资料上说：
exact : 只令完全相同的 SQL 语句共享一个游标。（默认选项）
similar: 允许相似的SQL可以共享一个游标。
force: 强制执行和similar基本上一样，但force会影响执行计划。
所以一般不建议用。

请问以上是对的吗？这三种模式应该在什么样的情况被设置？

=======================================================

我喜欢从最简单的概念开始讨论，本来这些都是最基本的概念

首先需要明确这两种最基本的语句的区别:
1, 使用文字常量，如select * from t1 where x=1;
这种情况缺点是如果使用不同常量，如select * from t1 where x=2; sql语句无法共享，产生大量硬解析。耗费过多CPU.
这种情况的优点是，相对于使用绑定变量，使用常量更容易得到比较合理的执行计划。

2. 使用绑定变量，如select * from t1 where x=:b1; (:b1=1)
这种的优点是可以避免大量的硬解析，节省CPU.
这种的缺点是可能得到不好的执行计划。
当Oracle对带有绑定变量的SQL语句最解析的时候，如果这个语句是第一次执行，解析的时候会做一次bind variable peeking，也就是根据当时绑定变量的值决定合适的执行计划。这个计划对于这个绑定变量（:b1=1）自然是合适的。
但是如果后来我们再执行select * from t1 where x=:b1; (这次:b1=2)，很多情况下，Oracle是不会再做bind variable peeking，而是直接使用b1=1的时候的执行计划，那么现在的执行计划你能确认他是合理的么？显然不能。

现在我们可以来看cursor_sharing这个参数。楼主对参数的解释应该是差不多对的，我再把英文原文补充进来：

1) exact : 只令完全相同的 SQL 语句共享一个游标。（默认选项）

Only allows statements with identical text to share the same cursor.

这个设置是默认的，我觉得也是最好的。
这时候如果为了实现sql的共享，从应用程序的角度就需要使用绑定变量。但是如果我们发现使用绑定变量会导致不太合适的计划，应用就应该采用文字常量，当然这时候在相应的列上使用柱状图也是必须的了。我觉得这个设置再结合好的应用，是最好的。

2) force: 强制执行和similar基本上一样，但force会影响执行计划。
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

简单的说，这时候oracle会把select * from t1 where x=1; 改写成select * from t1 where x=:b1; (:b1=1)，这在无法修改应用的情况下确实是可以大量减少hard parse的，但是也永远的无法解决前面提到的绑定变量所固有的执行计划不准确的问题。这里我不清楚的地方是到底什么时候常量的值会影响到sql语句的含义。

3) similar: 允许相似的SQL可以共享一个游标。
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

这时候oracle也是会把select * from t1 where x=1; 改写成select * from t1 where x=:b1; (:b1=1)，但加了个条件：
unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

也就是说如果oracle认为the literals affect either the meaning of the statement or the degree to which the plan is optimized. CBO会重新做bind variable peeking，不管这是第几次执行这个语句。

我不知道什么时候literals affect the meaning of the statement，但什么时候literals affect the degree to which the plan is optimized.是很明显的，当然就是当sql所涉及的列上收集了柱状图(histogram)的时候。

也就是说，当cursor_sharing=similar，我们执行select * from x=1; 这时候语句会被改写为select * from t1 where x=:b1; (b1=1), 然后CBO会做bind variable peeking，得到计划plan A，
然后我们执行select * from x=2; 这时候语句会被改写为select * from t1 where x=:b1; (b1=2), 然后取决于x上有没有收集柱状图，如果有收集，CBO会重新生成新的计划plan B。

那么这三个设置那个更好，自然是看具体情况更合适的更好。不过如果应用可以修改的话，我始终觉得exact是最好的。

我猜想一下exact和similar的区别应该是这样的（和force的区别不用说了吧。。。），不过下面的部分我还没有验证，
情况一，使用cursor_sharing=exact，表t1.x上有柱状图且x分布是skew的。
我们执行
select * from t1 where x=1;
select * from t1 where x=2;
select * from t1 where x=3;
select * from t1 where x=4;
select * from t1 where x=5;
select * from t1 where x=6;
select * from t1 where x=7;
select * from t1 where x=8;
select * from t1 where x=9;
...................

在library cache里的内存结构应该是
select * from t1 where x=1;
plan 1
select * from t1 where x=2;
plan 2
select * from t1 where x=3;
plan 3
select * from t1 where x=4;
plan 4
select * from t1 where x=5;
plan 5
select * from t1 where x=6;
plan 6
select * from t1 where x=7;
plan 7
select * from t1 where x=8;
plan 8
select * from t1 where x=9;
plan 9
...................

情况二：
使用cursor_sharing=similar，表t1.x上有柱状图且x分布是skew的。
select * from t1 where x=1;
select * from t1 where x=2;
select * from t1 where x=3;
select * from t1 where x=4;
select * from t1 where x=5;
select * from t1 where x=6;
select * from t1 where x=7;
select * from t1 where x=8;
select * from t1 where x=9;
...................

在library cache里的内存结构应该是
select * from t1 where x=:b1
plan 1 for :b1=1
plan 2 for :b1=2
plan 3 for :b1=3
plan 4 for :b1=4
plan 5 for :b1=5
plan 6 for :b1=6
plan 7 for :b1=7
plan 8 for :b1=8
plan 9 for :b1=9
..................

我个人的观点是，cursor_sharing=similar的时候，library cache里的结构不合理，会产生严重的latch争用。------因为我猜想这时候所有不同version的计划都在一个sql下，从而受到同一个latch的保护。