关于oracle 11g acs的一点总结：

今天谈谈下面这几个参数对数据库性能和稳定性的影响：

cursor_sharing：游标共享

_optim_peek_user_binds：绑定变量窥视

_optimizer_adaptive_cursor_sharing：自适应游标共享（简称ACS），一般还包括另外两个_optimizer_extended_cursor_sharing和_optimizer_extended_cursor_sharing_rel 参数）

_optim_peek_user_binds和ACS：

在10g，因为没有ACS，一般建议客户关闭绑定变量窥视功能的情况多一些。

在11g，很多客户还是将绑定变量窥视和ACS都关闭了，原因有的是数据库从1g升级而来，升级后没有改，还有就是因为ACS早期版本有一些bug。其实这是两个很好的参数，可以在代码写的不是太好的情况下，也能获得比较好的性能。虽然ACS可能还有一些小bug没有解决（有的bug是在很特殊的情况下才会触发），到了11204版本应该都不是大问题了。老虎刘建议还是都开启比较好。

最重要的参数还在下面，如果做到了下面这两点，上面两个参数就显得不是那么重要了：

首先，cursor_sharing这个参数对系统性能和稳定性都非常重要，可惜经常被忽略，建议使用该参数的默认值：

即 cursor_sharing=EXACT （而不是FORCE或similar）

这要求应该使用绑定变量的地方，必须使用绑定变量。这个对于OLTP系统来说是铁律，不容置疑，cursor_sharing=FORCE通常就是为了解决该使用绑定变量而没有使用绑定变量的情况。前年在网上看到一个广为流传的某水果公司的AWR报告，居然设置 cursor_sharing= FORCE ，令人感叹啊。

其次，还有一个重要的补充条件：

不该使用绑定变量的地方，不用绑定变量：对那些唯一值较少的字段，特别是数据分布不均的情况，不建议使用绑定变量。如type、status等字段，我们建议使用常量：where type=1 and status=2。

这种情况如果使用了绑定变量，就是绑定变量窥视和ACS发挥作用的时候。

如果cursor_sharing=FORCE；或者cursor_sharing=EXACT，但是在数据分布不均的字段上也使用了绑定变量（两者基本上是等同的，虽然后一种略好于前一种情况），那么就要考虑“绑定变量窥视”和“自适应游标”两个参数的影响了。

看下面几种情况：

1、关闭“绑定变量窥视”（默认是开启）：

ACS同时失效，这时系统的稳定性好（不会因为绑定变量的不同，发生执行计划改变），但是整体性能会下降：因为不能窥视绑定变量，只能按照字段是数据分布均匀的情况来计算，在能否使用索引，返回行源的估值上，都会出现较大的偏差，有时可能会配合使用hint来提高SQL性能。

2、如果开启了“绑定变量窥视”而不开启ACS（默认是开启）：

那么系统就会极不稳定：比如硬解析窥视到一个绑定变量适合全表扫描的执行计划，不管接下来的绑定变量是否能使用索引，都会一直全表扫描下去，直到下次硬解析时再次窥视绑定变量才可能重新生成新的执行计划。

3、如果开启“绑定变量窥视”，同时开启ACS：

这种情况在解决了一部分稳定性的同时，兼顾了性能。也是11g新增的ACS比10g没有ACS进步的地方：执行计划不再从一而终，而是会根据绑定变量的不同，不是很及时的做出调整：比如第一次窥视到的绑定变量适合全表扫描，那么第二次即使使用的绑定变量适合走索引，也还是会使用全表扫描的执行计划，下一次再次执行就会纠正为使用索引的执行计划（具体请参考ACS的实现原理）。

绑定变量窥视和ACS这两个参数是与直方图信息紧密联系在一起的，关闭直方图收集，也就相当于关闭了绑定变量窥视和ACS，即使开启了这两个参数。

直方图能较为准确的反映数据分布不均字段的数据分布情况，一般使用默认选项（auto），某些特殊情况可以补充或去掉某些字段的直方图信息。一些客户在数据库级关闭收集直方图的做法是不建议的。

总结：

最佳实践：

cursor_sharing=EXACT + 合理使用绑定变量（合理就是：类似ID、account_no等唯一值等于或接近表行数的字段，必须使用绑定变量；而type、status等唯一值少且数分别不均的字段，不使用绑定变量）。

绑定变量窥视和ACS保持默认开启状态。

特殊情况：

1、字段唯一值有一定的数量（介于少与多之间），比如1000个，如果数据分布均匀，则可以使用绑定变量。如果字段分布不均，则把占比多的几个值，使用常量，其他值使用绑定变量。

2、字段唯一值少，还有经常互相转变的情况，比如常见的工单处理表：没有处理的状态是0，处理后的状态是1，夜间统计信息收集后，由于字段值的不稳定，统计信息经常不能反映表的实时数据分布情况，这种情况谈是否使用绑定变量已没有意义，涉及这类表的SQL，可以关闭字段上的直方图收集，再配合rownum和hint 来提高SQL效率和稳定性，必要时还可以使用dynamic_sampling（动态采样）来辅助优化器做出正确的执行计划。

最差组合：

cursor_sharing=FORCE

_optim_peek_user_binds=TRUE（开启绑定变量窥视）

_optimizer_adaptive_cursor_sharing=FALSE（关闭ACS，还有其他两个参数也要一起设置）

执行计划不稳定的同时还会带来低性能。

使用ACS的前提条件：
1.绑定变量使用变量窥视；
2.绑定变量的列上使用直方图；

关闭acs步骤：

我们先来看看跟ACS相关的三个隐藏参数，是用来控制是否启用ACS
col ksppinm for a30
col ksppstvl for a20
col ksppdesc for a35
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = ‘_optimizer_adaptive_cursor_sharing’;
KSPP免费云主机域名INM KSPPSTVL KSPPDESC
—————————— ——————– ———————————–
_optimizer_adaptive_cursor_sha TRUE optimizer adaptive cursor sharing
ring

SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = ‘_optimizer_extended_cursor_sharing’;
KSPPINM KSPPSTVL KSPPDESC
—————————— ——————– ———————————–
_optimizer_extended_cursor_sha UDO optimizer extended cursor sharing
ring

SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = ‘_optimizer_extended_cursor_sharing_rel’;
KSPPINM KSPPSTVL KSPPDESC
—————————— ——————– ———————————–
_optimizer_extended_cursor_sha SIMPLE optimizer extended cursor sharing f
ring_rel
所以如果我们要关闭ACS，使用如下的命令
alter system set “_optimizer_extended_cursor_sharing_rel”=none;
alter system set “_optimizer_extended_cursor_sharing”=none;
alter system set “_optimizer_adaptive_cursor_sharing”=false;

参考：
http://mp.weixin.qq.com/s?__biz=MzIzMTQ3OTE4Mw==&mid=2247483871&idx=1&sn=06a86ac02f4f63e339979588308ea386&scene=1&srcid=09140h8P90bBFNlYiDgaEojG#rd

相关推荐: latch:library cache lock等待事件

This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either: …