I am receiving a recommendation from the addm repport that says to increase the value of the cached cursors per session, so I make a query to find the statistics of the sessions and decide how much it is necessary to increase the value, but when processing queries I get these values:
We can say that the current cached is much above the maximum allowed cacheable value per session, there is a wey to know if the current cached is an instantaneous value or a history of all cached cursors.
The query used to get that response was:
select sess.username usr, sess.sid , hits.value CACHE_HITS,
cached.value curr_cached, par.value max_cacheable,
prs.value all_parses, hprs.value hard_parses,
prs.value - hprs.value Soft_parses
from v$session sess, v$parameter2 par,
v$sesstat cached, v$statname cached_stat,
v$sesstat hits, v$statname hits_stat,
v$sesstat prs, v$statname prs_stat,
v$sesstat hprs, v$statname hprs_stat
where sess.sid = &sid
and par.name = 'session_cached_cursors'
and hits.statistic# = hits_stat.statistic#
and hits_stat.name = 'session cursor cache hits'
and hits.sid=sess.sid
and cached.statistic# = cached_stat.statistic#
and cached_stat.name = 'session cursor cache count'
and cached.sid=sess.sid
and prs.statistic# = prs_stat.statistic#
and prs_stat.name = 'parse count (total)'
and prs.sid=sess.sid
and hprs.statistic# = hprs_stat.statistic#
and hprs_stat.name = 'parse count (hard)'
and hprs.sid=sess.sid;