Oracle 11g新特性学习之---Oracle 11g bind peek

Oracle 11g Bind Peek

Oracle 11g下的绑定变量和Peeking说明:

      在Oracle 11g 以后在绑定变量这块有所以改变，会生成一个范围值的执行计划。  然后每次传变量进去就对比范围，选择最优的执行计划。与这个功能相关的参数保存在v$sql视图中：is_bind_sensitive,is_bind_aware,is_shareable。 这几个字段，在Oracle 10g的v$sql 视图里是没有的。

15:33:49 SYS@ test1 >desc v$sql;

Name                                                              Null?    Type

----------------------------------------------------------------- --------

IS_BIND_SENSITIVE                                                          VARCHAR2(1)

IS_BIND_AWARE                                                              VARCHAR2(1)

IS_SHAREABLE                                                               VARCHAR2(1)

1、 Adaptive Cursor Sharing

      AdaptiveCursor Sharing 特性允许一个使用绑定变量的SQL语句使用多个执行计划。

      对于同一个SQL, 为了得到合适的查询，oracle 会监控使用不同绑定变量的情况，已确保对不同绑定变量值的cursor（执行计划）都是最优的。比如因为数据倾斜的原因对绑定变量值A 使用执行计划A，对绑定变量值B 使用执行计划B。 虽然他们的SQL 是相同的，但执行计划不同。

      AdaptiveCursor Sharing 默认启动的。 不过要注意的是，该特性只有在绑定变量的参数个数不超过14个的情况才有效。

2、Bind-Sensitive Cursors

      Bind-SensitiveCursor是根据绑定变量值得到的最优执行计划的一个cursor。Oracle 会根据不同绑定变量值来进行判断，如果之前的cursor 已经不是最优的了，那么Oracle 会重新创建一个child cursor，并将bind-sensitive标价为Y.

优化器会根据以下两个条件来判断对应的cursor 符合Bind-Sentive Cursor：

（1）使用某个具体的变量值进行peek得到的估算选择性。

（2）如果对应绑定列上存在直方图，则使用直方图信息。

      当对应字段上的数据有倾斜时，Oracle 会创建对对应的直方图。 并创建新的cursor，并将IS_BIND_SENSITIVE 标记为Y。

      当每次查询新的绑定变量值时，数据库会记录每次执行的统计信息，然后与之前的进行比较，如果这次的统计信息比上次好，那么数据库会重新创建一个child cursor并将bind-aware标记为Y。

3、Bind-Aware Cursors

      当一个cursor 被标记为bind-aware， optimizer 会根据Bind value 和selectivity estimate来选择新的执行计划。

      如果cursor的bind-aware标记为Y，那么在下次执行时，Oracle 做如下操作：

      （1）根据新的bind value 来生成新的执行计划

      （2）标记原来的cursor 为非共享，即V$SQL.IS_SHAREABLE 设置为 N，当这种cursor 长期不被使用时， 就会被移除shareed SQL area.

4、Cursor Merging

      当optimizer 为bind-aware cursor创建一个新的执行计划（plan）时，如果这个cursor 和之前存在的cursor 一样，那么optimizer 会进行cursor merging。

      如果绑定变量值对应的plan 不能和已经存在的plan匹配，那么Oracle 会进行一次硬解析来生成新的执行计划和cursor。如果这个新的执行计划被已经存在的cursor 使用，那么数据会将这2个cursor 进行合并并删除老的cursor。

5、总结

      当我们第一去执行一个带有绑定变量的SQL时，Oracle 会进行硬解析，但是硬解析不能确定最优的执行计划，所以这时候有了Peek。 也可以说是偷窥，即把实际值带入，来生成一个selectivity estimate。 然后来选择最优的一个执行计划来执行。

      这是第一次执行SQL语句。以后执行时就会使用已经存在的plan和cursor。 Oracle 通过Adaptive Cursor Sharing特性允许同一个SQL 可以使用多个执行计划。

      在每次执行时，Oracle会根据Peek 的selectivity estimate 值和直方图（如果存在）来判断已经存在的cursor 是否是最优的，如果不是，就重新创建一个child cursor，并讲Bind-Sensitive 标记为Y。

      而且Oracle在SQL 每次执行时，都会收集相关的统计信息，然后根据统计信息进行判断，如果比上次的更好，就在创建一个child cursor，并将Bind-Aware 标记为Y。

      当标记为bind-aware cursor 的cursor在下次执行时，Oracle根据新的bind value 来生成新的plan和cursor，并将原来的cursor标记为非共享，即V$SQL.IS_SHAREABLE 设置为 N，当这种cursor 长期不被使用时， 就会被移出shared SQL area.

      在bind-aware cursor创建新的cursor 之后，如果这个cursor 和之前某个存在的cursor一样，那么Oracle 会对他们进行合并。

      如果在cache里不能找到bind-aware对应的plan，那么就会重新进行一次硬解析，来生成plan 和cursor，如果这个plan 以后被新的cursor 使用，那么Oracle 会将这2个cursor 进行合并。

自适应游标共享（Adaptive Cursor Sharing） 说明

1.1 ACS概述

绑定变量使Oracle DB 可以为多条SQL 语句共享单个游标，以减少分析SQL 语句所使用的共享内存量。然而，游标共享和SQL 优化是两个相互冲突的目标。用文字编写SQL 语句为优化程序提供了更多的信息，这无疑会导致更好的执行计划，但大量的硬分析会导致内存和CPU 开销增加。

Oracle9i Database首次尝试推出了一个折衷的解决方案:允许共享使用不同文字值的相似SQL 语句。对于使用绑定变量的语句，Oracle9i 还引入了绑定扫视(Bind Peek)概念。使用绑定扫视，优化程序会在首次执行语句时查看绑定值。然后，它使用这些值来确定一个执行计划，语句的所有其它执行均共享该执行计划。为了从绑定扫视中受益，假定使用游标共享，且假定语句的不同调用使用相同的执行计划。如果语句的不同调用从不同的执行计划中获得很大收益，则绑定扫视对生成有效的执行计划就不再有用。

一个计划并不总是适用于所有绑定值,为了尽可能解决此问题，Oracle Database 11g 引入了自适应游标共享。此功能是一项更复杂的策略，它并不会盲目地共享游标，如果与分析时间和内存使用量开销相比，使用多个执行计划所带来的收益更重要，则会为使用绑定变量的每条SQL 语句生成多个执行计划。然而，由于使用绑定变量的目的是共享内存中的游标，因此对于需要生成的子游标数目必须采取一种折衷的方法。

Adaptive Cursor Sharing 作用如下：

通过自适应游标共享，可以仅针对使用绑定变量的语句智能地共享游标。

自适应游标共享用于协调游标共享和优化之间的矛盾。

自适应游标共享具有如下优点：

– 自动检测不同执行受益于不同执行计划的时间

– 将生成的子游标数限制到最小

– 是自动机制，无法关闭

1.2 ACS体系结构

1.2.1示例

1. 游标照常随硬分析启动。如果发生绑定扫视(BindPeek)，且使用直方图计算包含绑定变量的谓词选择性，则该游标将被标记为对绑定敏感的游标。此外，还会存储一些有关包含绑定变量的谓词的信息，包括谓词选择性。在上图中，所存储的谓词选择性是一个以(0.15,0.0025) 为中心的立方体。由于进行了初始硬分析，将使用已扫视的绑定确定初始执行计划。执行游标后，绑定值和游标的执行统计信息存储在该游标中。

当使用新的一组绑定值执行下一语句时，系统会执行常规软分析，并查找供执行使用的相匹配的游标。执行结束时，会将执行统计信息与当前存储在游标中的执行统计信息进行比较。然后，系统观察所有先前运行的统计信息模式并确定是否将游标标记为能识别绑定的游标。

2. 下一次对此查询进行软分析时，如果游标能够识别绑定，则会使用能识别绑定的游标匹配。假定具有新的一组绑定值的谓词选择性现在是(0.18,0.003)。由于选择性用作能识别绑定的游标匹配的一部分，并且该选择性位于现有立方体中，因此该语句使用现有子游标的执行计划运行。

3. 下一次对此查询进行软分析时，假设具有一组新绑定值的谓词选择性是(0.3,0.009)。由于该选择性不在现有立方体中，所以找不到子游标匹配项。因此，系统会执行硬分析，在本例中生成了一个具有第二个执行计划的新子游标。此外，新选择性立方体将存储为该新子游标的一部分。执行该新子游标后，系统会将绑定值和执行统计信息存储在该游标中。

4. 下一次对此查询进行软分析时，假设具有一组新绑定值的谓词选择性是(.28,0.004)。由于该选择性不在现有的某个立方体中，系统将执行硬分析。假设此时硬分析生成与第一个执行计划相同的执行计划。因为该计划与第一个子游标相同，所以将合并这两个子游标。也就是说，这两个立方体将合并为一个较大的新立方体，并删除其中一个子游标。下次执行软分析时，如果选择性位于该新立方体中，子游标将匹配。

1.2.2 说明

在Oracle 10g 和11g中对绑定变量的处理，已经有所不同， 在Oracle 10g中，绑定变量相对比较简单，当使用绑定变量的SQL 第一次执行时，会进行硬解析，生成plan 和cursor。 在这个过程中，Oracle 会使用bind peeking，即将绑定变量的值带入，从而选择最优的一个plan。以后每次执行都使用这个plan。

在以后的执行时，如果因为其他原因导致cursor 不可重用，那么就会生成一个child_cursor. 这个cursor 不可重用的原因可以查看：v$sql_shared_cursor视图。

那么这就有一个问题。如果列上有列上有严重的数据倾斜，某个字段中99%是值1，1%是值0. 当我们用0 来进行peeking的时候，这时候会走索引，并且以后的所有plan 都是使用这个。 如果我们的绑定值变成了1. 这个时候，明显走全表扫描比索引划算。

但是Oracle 10g 下还是会使用第一次的plan，即使这个plan 不是最优的。所以在Oracle 10g下，如果数据存在数据倾斜，那么最好不要使用绑定变量。

在Oracle 11g 以后在绑定变量这块有所以改变，会生成一个范围值的执行计划。然后每次传变量进去就对比范围，选择最优的执行计划。与这个功能相关的参数保存在v$sql视图中：is_bind_sensitive,is_bind_aware,is_shareable。这几个字段，在Oracle 10g的v$sql 视图里是没有的。

我们这里要说明的Adaptive Cursor Sharing特性，其允许一个使用绑定变量的SQL语句使用多个执行计划。对于同一个SQL, 为了得到合适的查询，oracle 会监控使用不同绑定变量的情况，已确保对不同绑定变量值的cursor（执行计划）都是最优的。比如因为数据倾斜的原因对绑定变量值A 使用执行计划A，对绑定变量值B 使用执行计划B。虽然他们的SQL 是相同的，但执行计划不同。

Adaptive Cursor Sharing 默认启动的。不过要注意的是，该特性只有在绑定变量的参数个数不超过14个的情况才有效。

1.3自适应游标共享视图

1.3.1 V$SQL 中已新增了两个新列

（1）IS_BIND_SENSITIVE：指示游标是否为对绑定敏感，值为YES | NO。符合以下情况的查询称为对绑定敏感的查询：计算谓词选择性时优化程序为其扫视绑定变量值，并且绑定变量值的更改可能导致不同计划。

（2）IS_BIND_AWARE：指示游标是否为能标识绑定的游标，值为YES | NO。游标高速缓存中已标记为使用能识别绑定的游标共享的游标称为能标识绑定的游标。

1.3.2 V$SQL_CS_HISTOGRAM

显示跨三个存储桶执行历史记录直方图的执行计数的分布情况。

1.3.3 V$SQL_CS_SELECTIVITY

显示为包含绑定变量且在游标共享检查中使用了其选择性的每个谓词存储在游标中的选择性立方体或范围。它包含谓词文本和选择性范围的下限值和上限值。

1.3.4 V$SQL_CS_STATISTICS

自适应游标共享监视查询的执行，并在一段时间内收集相关的信息，使用此信息可确定是否切换到对该查询使用能识别绑定的游标。该视图汇总了所收集的信息以让您作出以下决定：对于执行示例，它跟踪已处理的行数、缓冲区获取数和CPU 时间。如果使用绑定集来构建游标，则PEEKED 列的值为YES，否则为NO。