CURSOR_SHARING = SIMILAR !

遭遇cursor_sharing = similar的问题,跟GSD在SR中多次沟通总算解决了问题,国外的同事shift了几轮,实在是我跟进最长的一个SR了。

问题发生在客户在压力测试的时候,数据库版本是RAC 10.2.0.3 on AIX,客户使用Loadrunner模拟10000用户在系统中做业务,当虚拟用户增加到1200左右的时候,明显看到事务的响应时间开始上升,从原本的40s升到70s,然后继续上升,到最后2000用户的时候,已经开始产生大量的失败事务,响应时间也升高到400s,而用户也无法再login了。

AWR报告显示:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
—————————— ———— ———– —— —— ———-
cursor: pin S wait on X 13,153,838 133,733 10 49.8 Concurrenc
latch: library cache 365,244 54,514 149 20.3 Concurrenc
kksfbc child completion 579,210 29,477 51 11.0 Other
cursor: mutex X 383,841,637 20,095 0 7.5 Concurrenc
CPU time 13,549 5.0

SQL ordered by Elapsed Time中显示一条SQL占据了90%以上的数据库时间,而同样的这条SQL消耗了将近1G的共享池,Version Count值也高达19000!

这条SQL非常简单,从一张表中通过索引获取1行数据,表中有超过1亿条数据,分区,单独执行SQL效率非常好,也只有15左右的一致读,但是执行次数巨多,而且应用中是写死了的literal sql,通过设置cursor_sharing = similar而让Oracle转换为绑定变量的样式,但是每一次执行却因为某种原因都无法共享SQL,都要产生一个新的cursor,因此随着执行次数的增多,version count就越来越多了。因为太多的Version Count,所以导致library cache竞争剧烈,系统性能急剧下降。

导致性能问题的罪魁祸首很明显,但是为什么会有这么多Version Count?为什么Oracle不能共享这个SQL的cursor?又如何避免这个问题?

通过长时间在SR以及AIM上跟GSD的同事交流,做各种各样的trace,systemstate dump,甚至将统计信息export出来让他们在那边搭建环境,最后GSD都认为是一个bug而提交到BDE去了,然而最终的结果是这是Oracle的正常举动。

产生这种情况的条件是:
1. cursor_sharing = similar
2. 收集了列上的histogram
3. SQL中使用到了此列作为条件,并且条件是“等于”
4. 这个SQL是没有绑定变量的

这时候,oracle会认为每个送上来的literal变量都是unsafe的,因此就不重用以前的cursor而新产生一个version。

解决方法是:
1. cursor_sharing = force
2. 删除列上的histogram

实际上Oracle也建议在OLTP系统中不要对所有列都收集histogram,只对那些distinct值比较少而且数据分布非常倾斜的列才去收集。

有兴趣的朋友可以去metalink上查看SR 6470259.992 [Download it locally],这个SR中不但对这个问题有详细的说明,而且涉及了很多诊断问题的方法,包括
1. 如何生成hanganalyze的trace
2. 如何生成systemstate dump
3. 如何在普通SQL*Plus命令已经无法登录数据库的时候还能登录数据库 (sqlplus -prelim “/ as sysdba”)
4. 如何禁用mutex而使用以前版本的library cache latch机制
5. 如何跟踪cursor (使用cursortrace事件)
6. 如何导出统计信息而迅速生成另外一个测试环境

结论:
各位写应用程序的哥们儿还是尽量从一开始就绑定变量吧,不要依靠Oracle的cursor_sharing帮你作这件事情。
不是收集越多的信息给Oracle就越好,有时候不需要的信息反而容易引起问题,比如这里的列上的histogram。