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