SCRIPT – to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters

Script:

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'
  )
/

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_parses
from
  ( 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_parses
from
  ( 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' )
/