Session Cached Cursor tuning in Oracle

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;

3 thoughts on “Session Cached Cursor tuning in Oracle

  1. Danilo Neto

    In my sqlplus, the second query returns #### even formating with col usage for 999999…
    PARAMETER VALUE USAGE
    ———————- ——————– —–
    session_cached_cursors 50 ####%

    I’ve changed the decode(value, 0, ‘ n/a’, to_char(100 * used / value, ‘990’) || ‘%’) usage
    to decode(value, 0, ‘ n/a’, 100 * used / value)||’%’ usage, the result is still correct? what is 990?

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.