初始化引數OPEN_CURSORS的理解加深

以前一直有些迷惑，一直以為v$open_cursor裡查詢出來就是當前open的cursor，google收藏了一些討論得比較深入的帖，比如說這裡： 。[@more@]

今天看到Natalka Roshak寫的一篇關於open_cursors及session_cached_cursors的文章，頓時一目瞭然，參見：，摘錄部分精彩內容如下：

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000's or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached. There's no relationship between the two parameters.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)
這裡提到的softer soft parse與 這裡8#裡biti_rainy老大提到的軟軟解析就是一個意思了，之前我卡在這裡挺迷糊，現下算是弄明白了。

更加大快人心的句子來了：
I believe a lot of the confusion about open cursors vs. cached cursors comes from the names of the Oracle dynamic performance views used to monitor them. v$open_cursor shows cached cursors, not currently open cursors, by session. If you're wondering how many cursors a session has open, don't look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session:

--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
sigh,按這種思路做了幾個實驗，有點闊然開朗的感覺。
比如，用toad連線到一個庫，在Editor裡多開幾個tab(此時session sid均是一致)，分別在tab裡執行一些sql，在其中一個tab裡查詢open的cursor數量，發現此時open的cursor數量即等於開啟的tab數量。