session_cached_cursor另类用途

版本11.1.0.7
最近生产库的一个sql的version_count达到了七千多，占用的shared pool内存也达到了400多M。非常吓人。除BUG的因素外，还有就是update后面set了非常多的字段，由于varchar2字段的绑定变量分级，造成child cursor过多。当时根据经验，这个SQL的解析，不管是软解析还是硬解析消耗的时间肯定都会比较长，如果执行频繁，肯定会造成library cache latch争用。因此就推动公司架构那边去优化，只UPDATE必须的字段。优化后，查看了一下，v$sqlarea版本数没变。可是v$sql里明显已经只剩6个子child cursor.占用的shared pool也已经降到了不到300k.
ops$admin@CRMG>select sql_id,version_count from v$sqlarea where sql_id='56uscp6kdg24a';

SQL_ID                     VERSION_COUNT
-------------------------- -------------
56uscp6kdg24a                       7196

ops$admin@CRMG>select count(*) from v$sql where sql_id='56uscp6kdg24a';

  COUNT(*)
----------
         6
ops$admin@CRMG>select SHARABLE_MEM from V$SQLarea where sql_id='56uscp6kdg24a';

SHARABLE_MEM
------------
      295075

可是另我非常疑惑不解的是，这个SQL的版本一直就比较多，而且执行频率也非常高，可是数据库的等待事件library cache latch也还算正常，不至于看出存在性能问题。为什么呢？


周末在家终于想明白了。session_cached_cursor导致的，这个SQL由于执行次数多，被cached了起来，每次软解析的时候，根本不需要扫描很长的cursor列表，因为cached 的cursor保留指向library cache的lco的指针，这样即使列表再长，只要是软解析，就不会造成持有library cache latch时间过长。但是是硬解析的话，就无能为力了。
通过如下语句，可以基本确定这个SQL被cursor了起来。（我执行了N多次，这个CURSOR一直存在在各个SESSION里）。当然v$open_cursor并不是用来看那些cursor被cached的，奇怪ORACLE没有一个视图去查，那些cursor被cache起来。由于update语句执行时间非常短，因此如果没cache起来，执行完就会被关闭，就不会出现在v$open_cursor里了。

select sid,count(*) from v$open_cursor where sql_id='56uscp6kdg24a' group by sid;
至此问题基本搞明白了。
ops$admin@CRMG> show parameter session
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
session_cached_cursors               integer                100

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-681186/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-681186/