CURSOR_SHARING = SIMILAR !

on

遭遇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。

29 Comments Add yours

  1. Fenng says:

    Force 了?

    估计你接下来就该遇到更多的Bug了

  2. 木匠 says:

    Binding Variable + Thinking in Set

    这是我找工作必须提出的两点主张.

    也许我换工作太频繁了, 嘻嘻.

  3. victor666666 says:

    如果我没记错的的话,biti_rainy 在cnoug上有个关于similar/histogram/version count/soft parse的讨论贴

  4. kamus says:

    Fenng on August 16, 2007 at 10:15 pm said:

    Force 了?

    估计你接下来就该遇到更多的Bug了

    没有,我是建议删除列上的histogram了。

  5. kangbiao says:

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

    这四个条件是必要的吗?
    我记得似乎就是绑定了变量,还是会出现这种情况的

  6. linyiyong says:

    请问需要什么样级别的CSI才能看这个SR?

  7. kamus says:

    @kangbiao
    在我们的系统上是未绑定变量的,我不确认如果变量绑定了并且cursor_sharing = exact仍然会出现这个问题

    @linyiyong
    需要能够浏览其他SR的权限,我不知道Customer的CSI会不会有这个级别,也许不会吧。。。

  8. 马艺桐mayitong@hotmail.com says:

    开不到你的sr detial,不同CSI号没办法看,不如将SR 粘贴出来,过滤到客户的信息即可!

  9. ynhoo says:

    直接查6470259.992

  10. boypoo says:

    这个SR好长!
    看到了内部员工与普通partner和最终用户在SR服务上的区别了。
    内部员工的SR随时有人回,而且可以通过AIM(内部交流似乎都用这个).

  11. boypoo says:

    还有其中给的文章连接都是直接指向WEBIV的,比如Note 377847.1,Note 390249.1 How To Quickly Add/Remove Column Statistics (Histograms)

  12. kamus says:

    @boypoo
    不是内部员工和最终用户有区别,实际上Oracle的SR服务是更看重最终用户的,这个SR之所以随时有人回复,是因为我开了Sev 1,也就是所谓的一级TAR,因此是24小时有人应答的。

    Add/Remove Column Statistics其实就是DBMS_STATS.DELETE_COLUMN_STATS函数的普通功能,单单这个倒还真不用WebIV,呵呵。

  13. truezxd says:

    叹气,我还是看不到!没有权限。。。

  14. Fishyu says:

    我的metalink帐号没有权限那,强烈建议kamus能够贴出来看看,如果不方便的话发一个到我邮箱撒!:)谢谢!

  15. boypoo says:

    呵呵,我看是Quickly,以为还有特殊方法呢:)

  16. goodhawk says:

    看来loadrunner测试还真是好用。本来不想用了,看来这把我也好好测试一把。

  17. 马艺桐mayitong@hotmail.com says:

    看不到这些信息呀,楼主能不能帖出来
    sqlplus -prelim “/ as sysdba”)
    如何禁用mutex而使用以前版本的library cache latch机制
    如何跟踪cursor (使用cursortrace事件)

  18. kamus says:

    现在外地出差,metalink我自己都很难登录上。。。
    等回到公司,我把这个SR保存成html,然后当附件上传上来好了。
    等几天,我会更新这个帖子的。

  19. kamus says:

    已经把完整的SR保存为htm页面上载到本文章最后了,有兴趣的朋友可以下载去看看

  20. koko says:

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

    这四个条件是必要的吗?
    我记得似乎就是绑定了变量,还是会出现这种情况的

    我这里就是这个现象
    1. cursor_sharing = exact
    2 .未收集列上的histogram
    3. insert sql
    4.绑定了变量

    version_count>32767 因此oracle在查一个sql有没有一个可用的执行计划的时候 child table >32767 抱了一个ora600的错

  21. kamus says:

    @koko
    如果是这样的话,那应当是另外一个问题。
    能否给出你的SQL,执行计划以及相关表信息。
    你的情况是最普通的绑定变量应用,如果仅仅是因为绑定变量就导致version count过高,那这个世界早就乱了。。。

  22. koko says:

    早些时候我在论坛发过一个帖子 你也参与了的 你可能忘记了

    http://www.itpub.net/763469,0.html

  23. kamus says:

    @koko
    你说的那个帖子我有印象,那其实也是varchar2变长的问题,而且还必须是后出现的SQL中绑定的变量长度要大于前面一个SQL,所以也并不仅仅是你说的只要满足那4个条件就可以,你说的那4个条件太普遍了。

  24. koko says:

    恩 而且我觉得这种情况太容易发生了
    一个sql如果出现了多个varchar2(4000)很有可能在将来的莫个时候
    出现问题
    我就在怀疑
    这种varchar2节约空间的机制 真的有必要吗?

  25. ma.qy says:

    我来说说这个问题。当cursor_sharing=similar
    1:从metalink 377847.1来看,不论有无histgram,都不会共享游标。
    2:这个问题与是否有histogram无关。而在如“377847.1”所说的dynamic sampling,是否有关?从我的测试结果来看,应该是有关的。但是在tom的文章里面,tom明确说明dynamic sampling只在hard parse才发生。
    3:我已经测试到没有histogram的时候也会发生bind peek导致游标不共享。而且肯定可以重复

  26. kamus says:

    @ma.qy
    你提到的这篇文档并没有指出histogram的存在与此问题无关。相反地,倒是我这篇文章一份很好的注脚 😀
    比如你说dynamic sampling会导致游标不共享,原因在于“even if there are no histograms (or even statistics) on a table (since dynamic sampling will create these in the background.”
    当然,还有其它情况也会导致CBO认为bind is unsafe,但是正如你提到的文档中所说,“This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.”
    同时,这篇文档也说明了为什么设置CURSOR_SHARING = FORCE也可以解决这个问题。

Leave a Reply to kamus Cancel reply

Your email address will not be published. Required fields are marked *