Hello,
Recently I have noticed high session cached cursor usage in the database. I got this below query to check the usage percentage of Session Cached Cursors.
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 sys.v_$statname n, sys.v_$sesstat s
where n.name = 'session cursor cache count'
and s.statistic# = n.statistic#),
(select value
from sys.v_$parameter
where name = 'session_cached_cursors')
The current value is set to 750, and the usage is showing 100%. Earlier the value was 600 due to application login issue changed it to 750.
Why does this situation arise? And Is there a way to control this?
How do I identify the optimal value that needs to be set on the database?
Thanks