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;
Wow great!You nailed it:)Many thanks
LikeLiked by 1 person
Thanks for your support
LikeLike
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?
LikeLike
thanks for wonderful solution
LikeLike