解析分为硬解析和软解析和软软解析,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的实验可以参考