cursors.sql

#cursor_sharing = EXACT #MP
#open_cursors = 600
#session_cached_cursors = 500
--alter system set open_cursors=6000           scope=both sid='*';
--alter system set session_cached_cursors=5000 scope=spfile sid='*';

--cursors.sql
--IF: How to Monitor and Tune the OPEN_CURSORS and SESSION_CACHED_CURSORS Parameters (Doc ID 2091529.1)
--What is the purpose of the Database Parameter 'SESSION_CACHED_CURSORS'? (Doc ID 2462046.1)
--SCRIPT - to Gauge the Impact of the SESSION_CACHED_CURSORS Parameter (Doc ID 208918.1)
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
gv$statname n,
gv$sesstat s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
gv$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
gv$statname n,
gv$sesstat s
where
n.name in ('opened cursors current') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
gv$parameter
where
name = 'open_cursors'
)
/
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 value calls from v$sysstat where name = 'parse count (total)' ),
  ( select value hard  from v$sysstat where name = 'parse count (hard)' ),
 ( select value sess  from v$sysstat where name = 'session cursor cache hits' )
/