共享池之九：绑定变量与session_cached_cursors

解析分为硬解析和软解析和软软解析，SQL语句第一次解析时必须进行硬解析

还有一种是结果集缓存—无解析，详见：结果集缓存

一句话说明硬解析与软解析的区别是：

硬解析=需要生成执行计划   软解析=不需要生成执行计划

在Oracle中存在两种类型的SQL语句，一类为DDL语句，不共享使用，也就是每次执行都需要进行硬解析。还有一类就是DML语句，会进行硬解析或软解析。

硬解析变成软解析：绑定变量

软解析变成软软解析：设置session_cached_cursors，详见本文最后。

 

软软解析参数session_cached_cursors的参考值

select owner,name from v$db_object_cache where child_latch=1;显示library cache中那些对象被缓存，以及对象的尺寸

session_cached_cursors占用的内存：会话退出会释放，或者达到达到最大值后，最早、早少使用的会被释放。

session_cached_cursors参数的设置： --来自周亮--ORACLE DBA实战攻略。

SYS@ bys3>show parameter session_cached  11G中默认值是50

NAME                                TYPE        VALUE

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

session_cached_cursors              integer    50

SYS@ bys3>select a.name,b.value from v$statname a,v$sesstat  b where a.statistic#=b.statistic# and a.name in('session cursor cache hits','session cursor cache count','parse count (total)') and  b.sid=(select c.sid from v$mystat c where rownum=1);

NAME                                                                 VALUE

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

session cursor cachehits                                               32

session cursor cachecount                                               4

parse count(total)                                                     43

session cursor cache count表示指定会话缓存的游标数，session_cached_cursors参数是系统当前每个会话最多能缓存的游标数。

session cursor cache count小于session_cached_cursors，不用增加session_cached_cursors大小。如相等，则有可能需要增加。

session cursor cache hits 表示从UGA中命中的次数--软软解析次数。

parse count (total)指定会话的总解析次数。

如果session cursor cache hits接近parsecount (total)，无需调整session_cached_cursors。

如果session cursor cache hits远小于parsecount(total)，则可能需要调整session_cached_cursors。

session_cached_cursors对所有会话生效，如果需要调优的会话占所有会话比例很小，调整意义不大。

--注意session_cached_cursors是静态参数，调整后要重启库才生效。

 SYS@ bys3>alter system set session_cached_cursors=11;
alter system set session_cached_cursors=11
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

 绑定变量

oracle 能够重复利用执行计划的方法就是采用绑定变量。

绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

绑定变量只是起到占位的作用，同名的绑定变量并不意味着在它们是同样的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。

绑定变量是在通常情况下能提升效率，非正常的情况如下：

在字段(包括字段集)建有索引，且字段(集)的集的势非常大（也就是有个值在字段中出现的比例特别的大）的情况下，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。这种情况最好不要使用绑定变量。

但是并不是任何情况下都需要使用绑定变量，下面是两种例外情况：

1．对于隔相当一段时间才执行一次的SQL语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消

2．数据仓库的情况下。

绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。不能用绑定变量来代替表名、过程名、字段名等.

从效率来看，由于oracle10G全面引入CBO，因此，在10G中使用绑定变量效率的提升比9i中更为明显。

绑定变量窥视：Bind Peeking--字段分布均匀时有利

Bind Peeking 就是当在WHERE条件中使用绑定变量的时候，CBO会根据第一次使用的真实变量值来生成一个执行计划。在这个cursor的整个生命周期中，CBO不会再对相同的SQL进行hardparse。

优点：如果索引字段的值是均匀分布的，hardparse就降低了，性能提高。

缺点：如果字段分布不均匀，并且第一次使用值不具有普遍性，那么执行计划就将非常糟糕。

 

Oracle11g 提供了一个新特性，AdpativeCursorSharing，或者叫 Extended Cursor Sharing，来解决这个问题。他的核心思想是，当某个字段的histogram提供了数据不均匀的信息，CBO会在实际使用不同值的时候，尝试重新生成更合适的执行计划。

通过隐含的参数来调整数据库默认的bind peeking行为：_OPTIM_PEEK_USER_BINDS。

关闭Bind Variable Peeking，可以设置该参数为False ----要重启数据库。

SQL>alter sessionset"_optim_peek_user_binds"=false

 

绑定变量分级--bind graduation及bind-mismatch导致高版本问题

bind_mismatch一般是由于bind value的长度不同导致bindbuffer无法重用，最终导致cursor无法重用。
根本的原因在于：Oracle数据库引擎应用了绑定变量分级，即根据绑定变量的长短划分为4级，比如varchar2(32)和varchar2(33)属于不同级别。
四个级别的划分是：
0~32
32~128
129~2000
2001~
当表上有数十上百个varchar2类型的列时候，会比较常见因为bind graduation导致的bind-mismatch,即产生N多无法共享的子游标。
子游标过多会对SQL parse有影响。
针对一些特别的表或者查询列特别多的SQL,可以通过给字符串变量绑定固定的长度，如to_char(4000),来避免因为bind graduation导致child cursor过多的问题．

例 如:对于字符类型的字段，进行绑定变量的时候，第一次会使用32字节的BUFFER，如果该值小于32字节的话，第二次执行这个SQL的时候，如果小于 32字节，那么可以共享这个CURSOR,如果大于，就无法共享，原因就是BIND_MISMATCH，此时会产生一个子CURSOR，同时分配128字节的BIND BUFFER，以此类推。
select count(*) from v$sql_shared_cursor where sql_id='9rwd4wkwm4bsy' andBIND_MISMATCH='Y' ;
 COUNT(*)
----------
  120
 
可以通过v$sql_bind_capture视图查看一下每次绑定变量的值：
select position,LAST_CAPTURED,datatype_string,value_string fromv$sql_bind_capture where sql_id='9rwd4wkwm4bsy' and rownum<50;

正常情况不会产生这么大量的子CURSOR。但是由于一些BUG，会导致问题。
如果没有补丁，一个临时性的解决方案，设置一个较大的BUFFER
SQL>ALTER SESSION SET EVENTS '10503 trace name context level<bufferlength>, forever';
 
而这些具体的内容，可以通过视图来查看。查看上述的共享父游标对于的bind_metadata
selects.child_number,m.position,m.max_length,
decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype)as datatype
from v$sql s,v$sql_bind_metadata m
where s.sql_id='94ka9mv232yxb'
and s.child_address=m.address;
CHILD_NUMBER   POSITION MAX_LENGTH DATATYPE
------------ ---------- ----------------------------------------------
         0         3         32 VARCHAR2
         0         2         32 VARCHAR2
         0         1        22 NUMBER
         1         3         32 VARCHAR2
         1         2        128 VARCHAR2
         1         1         22 NUMBER
从绑定变量四个级别来看，最多生成4个子游标。但是为什么AWR报表中SQL ordered by Version Count的version count能高达好几十，甚至几百呢？而且不能共享的原因都是因为bind_mismatch.

关于:cursor_sharing

根据oracle官方建议在11g中不推荐使用cursor_sharing=SIMILAR,其实在所有版本中都不推荐,设置为该值很容易导致高版本问 题.而且该值会出现莫名其妙的,无法解释的高版本问题.而且根据oracle相关文档,在即将发布的12c版本中,将除掉SIMILAR值.对于客户库的 该问题,因为很多sql未绑定参数,为了减少硬解析,建议在业务低谷时设置cursor_sharing=FORCE,并刷新sharedpool.

如果cursor_sharing 参数是设置为similar的，这样会将SQL 中的谓词值自动用变量来代替。这样会增加cursor的数量。
为了减少cursor对library cache的占用，先将cursor_shring 参数改成了默认的exact模式。
这样version_count 会减少很多，但是硬解析的次数也会增加，可能会增加Library Cache Latch等待。
cursor_sharing的实验可以参考DAVE的博客：http://blog.csdn.net/tianlesoftware/article/details/6551723