Oracle9i,10g,11g 使用绑定变量的区别及与cursor_sharing的关系[final]

转自 http://blog.itpub.net/35489/viewspace-683646/

绑定变量的好处，大家都比较熟悉，使用绑定变量可以限制需要存储在库缓存
中返回相似结果集的SQL语句游标的数量。也就是为了减少硬解析。但是并不是任
何时候使用绑定变量都是最优的。有时候会出现绑定变量使性能变差的情况。

 

1. 扫描成本和OPTIMIZER_INDEX_COST_ADJ

在CBO模式下，Oracle会根据统计信息计算各个访问路径的代价，采用最小代价的访问路径
作为语句的执行计划。而对于索引的访问代价的计算，需要根据一个系统参数OPTIMIZER_INDEX_COST_ADJ
来转换为与全表扫描代价等价的一个值。

这是什么意思呢？我们先稍微解释一下这个参数：OPTIMIZER_INDEX_COST_ADJ。它的值是一
个百分比，默认是100，取值范围是1～10000。当估算索引扫描代价时，会将索引的原始代
价值乘以这个百分比，将换算后的值作为与全表扫描代价比较的值。也就是说，当这个值为
100时，计算出的索引扫描代价就是它的原始代价：
COST (Index Scan)= COST_ORIGINAL (Index Scan) * OPTIMIZER_INDEX_COST_ADJ/100

在使用绑定变量时，参数OPTIMIZER_INDEX_COST_ADJ对于是否选择索引会有重要的影响。在
Oracle9i之前使用绑定变量不会将绑定变量的值考虑进去，而是计算出平均成本。

特别是类似索引字段的集的势非常高的时候(比如1000笔记录，995笔为'A',2笔为'B',3笔
为'C')，平均代价与实际扫描某个值代价相差非常远。这种情况下，OPTIMIZER_INDEX_COST_ADJ
对不使用绑定变量查询影响就非常小（因为索引代价不是比全表扫描成本大很多就是小
很多），不管扫描哪个值，不使用绑定变量将更加容易选择到合理的查询计划。

 

 

2.  绑定变量窥视(Bind Variables Peeking)及cursor_sharing

在了解了参数OPTIMIZER_INDEX_COST_ADJ的作用后，再了解一个对查询计划，特别是使用
绑定变量时会产生重大影响的特性： 绑定变量窥视（Bind Variables Peeking）。

绑定变量窥视是9i以后的一个新特性。它使CBO优化器在计算访问代价时，将绑定变量传入
的值考虑进去，从而计算出更合理的成本（否则，将会计算平均成本）。看下面例子：

此时OPTIMIZER_INDEX_COST_ADJ是60，根据上面的结论，似乎查询计划应该选择扫描索引。
但是，这里给绑定变量赋了值"A"，这时，优化器会“窥视”到这个值，并且在计算扫描成
本时按照这个值的成本来计算。因此，得出的查询计划是全表扫描，而不是扫描索引.

但是，绑定变量窥视对一条语句只会使用一次。就是说，在第一次解析语句时，将绑定变
量的实体值考虑进去计算成本生成查询计划。以后在执行该语句时不再发生Bind Variables Peeking，
而是都采用这个第一次执行时的执行计划，不管绑定变量中赋予是什么值 。

因此，这种情况下使用绑定变量也会导致无法选择最优的查询计划。

综上所述，在对建有索引的字段（包括字段集），且字段（集）的集的势非常大时(数据分
布非常不均匀)，使用绑定变量可能会导致查询计划错误。

------------------------------------------------------------------------
存在较多因素导致SQL语句不能在shared_pool中共享：
1. SQL文本大小写不一致
2. SQL语句的绑定变量的类型不一致或长度有很大区别
3. SQL语句涉及的对象名称虽然一致但是位于不同的schema下
4. SQL语句的优化模式不一致(比如添加hint,修改了optimizer_mode参数等)
------------------------------------------------------------------------

 

 

-----------------------------------
以下来自网络文章(来自itpub及it168)：
-----------------------------------
http://tech.it168.com/a2009/0108/262/000000262531_2.shtml

 

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的，
但是也永远的无法解决前面提到的绑定变量所固有的执行计划不准确的问题。

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.

当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是最好的。

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

 

 

3. 11g新特性 - 自适应游标共享 

Oracle 11g提供自适应游标共享(ACS)以克服不该共享时的游标共享，ACS使
用了两个新的度量机制 ： 绑定敏感度和绑定感知。

绑定敏感度：  无论何时，当包含绑定变量的SQL语句首次执行时，优化器在
偷窥了绑定变量的值后，会为其标记一个绑定敏感度，以确定语句的谓词，但
偷窥结束时也类似，因为它也为后面相同语句相同绑定变量不同值时进行对比，
以确定是否要产生新的执行计划。

自适应游标共享元数据 ：Oracle 11g提供了三个新的视图，并在v$sql视图中
添加了两个新列以便让Oracle DBA确定优化器是否已经决定SQL语句是否适合自
适应游标共享，优化器使用业务规则将SQL语句的执行计划进行分类以便共享：

自适应游标共如果享视图：

V$SQL  --  增加了两列，IS_BIND_SENSITIVE, 表示SQL语句是否绑定敏感，如果
这一列值是Y,意味着优化器已经偷窥了绑定变量的值，以便确定每个谓词的选择。
IS_BING_AWARE列，表示优化器执行额外的语句后决定SQL语句的游标是否有绑定感
知。

V$SQL_CS_HISTOGRAM -- Oracle11g使用分配的频率决定SQL语句是否绑定敏感，
包括执行的次数，特别是子游标被执行的次数。

V$SQL_CS_SELECTIVITY -- 包括有关SQL语句谓词的相对选择性信息，包括谓词自身
及高值，低值范围，这些值也称为游标的选择性立方体。

V$SQL_CS_STATISTICS -- 列出自适应游标是否被共享以及/或如何共享的统计信息，
如果绑定设置已经用于构建自适应游标，PEEKED列会显示一个Y值。

绑定感知：  一旦SQL语句的游标被标记为绑定敏感，优化器可能还会决定将其视为
绑定感知，优化器是通过检查提供给绑定变量的值是否与相同查询后面的执行计划匹配
来实现的，如果优化器决定它可以使用现有的执行计划，那就只需要更新游标执行直方
图以反应语句的执行情况，  换句话说，如果绑定变量值发了重大变化，优化器可能会
决定创建一个全新的子游标和执行计划，如果是这样的话，Oracle 11g也会存储自适应
游标共享元数据中的子游标的相对选择性。

我觉得它有助于把这些选择性评级作为“电子云”或影响范围的中心点，Oracle文档
了使用的术语是“选择性立方体”，在随后游标的执行过程中，优化器会使用游标最
近执行的统计信息与现有的选择性统计信息进行比较，如果它观察到大多数执行都使
用系统的选择性范围，游标将会被标记为绑定感知。

当绑定变量的值超出了现有绑定感知游标影响的范围时，执行包含这个绑定变量的查询
会发生什么?在语句的硬解析期间，优化器可能只会选择扩大选择范围，以包括新的绑定
值，这是通过创建新的子游标结合这两套绑定变量值，然后删除旧的、范围小的游标来
实现的，显然，这样只会产生几个的确需要的几个子游标。

　　那么如何激活这一新功能呢?好消息是在Oracle 11g中默认就已经启动了，它完全
与CURSOR_SHARING初始化参数无关，这大大增加了在OLTP/DSS系统中SQL语句使用绑定
变量的机会。

　　对SQL计划管理(SPM)的影响：如果你读过我之前写的SQL计划管理方面的文章，你
可能会疑惑自适应游标共享是否会影响SQL计划管理捕获和保存SQL执行计划到SQL管理
基础库中的功能，下面列出它们之间交互的摘要信息：
　　如果初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES被设置为TRUE以激活自
动捕获执行计划，那么带有绑定变量的SQL语句也会被标记为启用和接收执行计划。

　　如果同一个语句构建了第二个执行计划 – 并不是自适应游标共享 – 那么该计划
只会简单地添加到语句的计划历史中，但它不会立即被使用，因为SPM首先会要求校验这
个新的执行计划。

　　不幸的是，这意味着一个很好的执行计划会被忽略，解决这个问题的一个好办法是
将自动捕获计划设置为FALSE，然后在库缓存中将所有子游标捕获到SMB中，这样将会强
制所有子游标的计划被标记为SQL计划基线。

 结语
　　Oracle 11g的新特性自适应游标共享为包含有绑定变量的SQL语句有效共享执行计
划提供了一个更简单的方法，但只有绑定变量有值时才有意义，自适应游标共享有时也
会产生新的执行计划，但共享的游标会保持相对小的数量。