https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:865497961356

http://www.orafaq.com/node/758

https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#LNOCI091

https://m.baidu.com/from=844b/bd_page_type=1/ssid=0/uid=0/pu=usm%402%2Csz%401320_2001%2Cta%40iphone_1_11.0_3_604/baiduid=F584A35ACA9260303DC1F27EA50003B3/w=0_10_/t=iphone/l=3/tc?ref=www_iphone&lid=8709951824705805360&order=1&fm=alop&tj=www_normal_1_0_10_title&vit=osres&m=8&srd=1&cltj=cloud_title&asres=1&title=SESSION_CACHED_CURSORS%E7%9C%9F%E7%9A%84%E8%83%BD%E9%81%BF%E5%85%8Dsoftparse%E5%90%97%3F-...&dict=32&w_qd=IlPT2AEptyoA_yiGI5WtWzA8uR_XQaC5FR4VVjlGATWg8UW7hPTjJNBsZzKvL8iP&sec=25911&di=7d45a1979fc7efe2&bdenc=1&tch=124.0.205.311.0.0&nsrc=IlPT2AEptyoA_yixCFOxXnANedT62v3IEdiURiNN1zmymEytxP4kHREsRCP8QnqXJ9ibczDXvB9QwWbzKzlq&eqid=78dff7098a5adc00100000015a256950&wd=&clk_info=%7B%22srcid%22%3A1599%2C%22tplname%22%3A%22www_normal%22%2C%22t%22%3A1512401248447%2C%22xpath%22%3A%22div-div-div-a-p%22%7D&sfOpen=1

https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#LNOCI16658

OPEN_CURSORS sets the maximum number of cursors each session can have open,per session.

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have.

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.) In the session cursor cache,Oracle manages the cached cursors using a LRU list.


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_cursorshowscachedcursors,notcurrently open cursors,by session. To monitor open cursors,query v$sesstat where name='opened cursors current'. --total cursors open,by session select a.value,s.username,s.machine,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';