Script:
select 'session_cached_cursors' parameter, lpad(value, 5) value, decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usagefrom ( 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 allselect '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' )/ Sample Output:
PARAMETER VALUE USAGE---------------------- ---------- -----session_cached_cursors 20 100%open_cursors 300 16%select to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits, to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses, to_char(100 * hard / calls, '999990.00')||'%' hard_parsesfrom ( select sum(value) calls from v$sysstat where name in ('parse count (total)', 'parse count (failures)') ), ( select value hard from v$sysstat where name = 'parse count (hard)' ), ( select value sess from v$sysstat where name = 'session cursor cache hits' )/select to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits, to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses, to_char(100 * hard / calls, '999990.00')||'%' hard_parsesfrom ( select sum(value) calls from v$mystat m, v$statname s where m.statistic# = s.statistic# and name in ('parse count (total)', 'parse count (failures)') ), ( select value hard from v$mystat m, v$statname s where m.statistic# = s.statistic# and name = 'parse count (hard)' ), ( select value sess from v$mystat m, v$statname s where m.statistic# = s.statistic# and name = 'session cursor cache hits' )/