Caching Session Cursors

What’s the Caching Session Cursors? I would like to know about it.

If your applications repeat … repeat to parse calls on the same of SQL statements, then the reopening of the session cursor can affect system performance. Session cursors can be stored in a session cursor cache. These cursors are those that have been closed by the application and can be reused.

How can I enable?
You must set the initialization parameter SESSION_CACHED_CURSORS. The value of this parameter is a positive integer specifying the maximum number of session cursors kept in the cache. An LRU algorithm removes entries in the session cursor to make room for new entries when needed.

ALTER SESSION SET SESSION_CACHED_CURSORS = value;

To determine whether the session cursor cache is sufficiently large for your instance, You can examine the session statistic session cursor cache hints in the V$sysstat view.

How can I monitor?

select 'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from ( select max(s.value) used
from v$statname n,
v$sesstat s
where n.name = 'session cursor cache count'
and s.statistic# = n.statistic#
),
( select value
from v$parameter
where name = 'session_cached_cursors'
)
union all
select 'open_cursors', lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from ( select max(sum(s.value)) used
from v$statname n,
v$sesstat s
where n.name in ( 'opened cursors current',
'session cursor cache count')
and s.statistic# = n.statistic#
group by s.sid
),
( select value
from v$parameter
where name = 'open_cursors');

PARAMETER VALUE USAGE
---------------------- ----- -----
session_cached_cursors 150 100%
open_cursors 300 98%

- If You found > 100% You should increase initialization parameters.