SESSION_CACHED_CURSORS - how to figure out if it can be increased to see performance gains

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

PARAMETER VALUE USAGE
---------------------- ----- -----
session_cached_cursors 30 100%
open_cursors   65535 0%

ALTER SYSTEM SESSION_CACHED_CURSORS= requires a bounce. 

Alternatively, a database level trigger can be created like below to automatically set this value.

create or replace trigger ssc_trig after logon on database
begin
    execute immediate 'alter session set session_cached_cursors = 100';
end;
/