Session Cached Cursor tuning in Oracle

SESSION_CACHED_CURSORS is used to improve the performance of “soft parse”.
With this parameter Oracle maintained a local session cache which stores recently closed
cursors of a session and maintains the cursors which have 3 parsed calls.

It basically improve the performance of SOFT PARSE by maintaining the session cache of queries.

Check the value of Session Cached Cursors

show parameters SESSION_CACHED_CURSORS

NAME                     TYPE        VALUE
------------------------ ----------- -------
session_cached_cursors   integer     50

Check the maximum value used by Oracle for value of Session Cached Cursors parameter

select max(value)
from v$sesstat
where STATISTIC# in
(select STATISTIC#
from v$statname
where name = 'session cursor cache count');

Note: If value is reached to maximum limit then you can increase the value of this parameter.

Check the usage percentage of Session Cached Cursors

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 sys.v_$statname n, sys.v_$sesstat s
where n.name = 'session cursor cache count'
and s.statistic# = n.statistic#),
(select value
from sys.v_$parameter
where name = 'session_cached_cursors')

Note: If usage is above 95% percent than you can also increase the value.

Increase or Decrease the value by setting with alter command
Change this parameter need to restart the Database.

ALTER SYSTEM SET session_cached_cursors = 200 scope=spfile;
Shutdown immediate
startup


At Session level change the value of parameter

ALTER SESSION SET session_cached_cursors = 50;


More detail about SQL Query present in Session cached cursors

select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id = sql.sql_id and c.sid = &sid;