今天看到Natalka Roshak写的一篇关于open_cursors及session_cached_cursors的文章,顿时一目了然,参见:
http://www.orafaq.com/node/758
,摘录部分精彩内容如下:
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与
http://www.oracle.com.cn/viewthread.php?tid=4410
这里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数量。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14517718/viewspace-1007327/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14517718/viewspace-1007327/