SESSION_CACHED_CURSORS

In a typical OLTP environment, most of the SQLs will be repeatedly fired. If SESSION_CACHED_CURSORS are set to 0 and even if SQLs are found in library cache, soft parse will happen, which requires Library cache latch and few CPU cycles. This can aggregate to huge number of CPU cycles as time goes on.
Setting value greater than 0 to SESSION_CACHED_CURSORS will cache the queries which are executed three times or more. This helps oracle in doing “softer” soft parse which is less resource intensive then soft parse (in terms of Latch and CPU cycles).
Following query helps to find out session cursor cache count and number of times cached cursor was utilized.
select name, value from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name in ('session cursor cache hits', 'session cursor cache count') and sid =

Note before changing this parameter:

• Setting SESSION_CACHED_CURSORS may mislead the data in view V$OPEN_CURSOR.
• Be sure SHARED_POOL_SIZE is set appropriately.