Hello,
Let’s talk about the SESSION_CACHED_CURSORS parameter. It might have significant affect on your system so understanding how it works and what it cause is important.
This parameter limits the number of cached cursors on the session. As you know there is 2 kind of parse SOFT and HARD. Hard parse is creating the cursor from the scratch. Checking grants, syntax and semantics, creating execution plans etc. Soft parse is using a previously created cursor. So, if Oracle has already calculated all information for a query (sql statement, cursor) then, it stores it in the shared pool and can use it over and over again. not calculating everything from scratch is a big performance increment.
Even in a soft parse, Oracle still need to search through library cache and find the cursor first. SESSION_CACHED_CURSOR allows you to cache a cursor in your sessions PGA (and still in SGA). This way if you run a query over an over, it will be found and execute even faster. Basically, SESSION_CACHED_CURSOR is kind of “Softer” parse. Starting from 11g its default value is 50 and in 21c it is the same.
You can see cached cursors via V$OPEN_CURSOR view. Let’s open a new sqlplus session and check its cached information. to check cache information I will use a separate session because I don’t want to affect the main session. here is main session:
|
1 2 3 4 5 6 |
sqlplus mustafa/mustafa@orcl select userenv('SID') from dual; USERENV('SID') -------------- 1470 |
so my main sessions SID is 1470. from monitoring session I will check cached objects in the session:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Monitoring session: select cursor_type, last_sql_active_time, sql_text from v$open_cursor where sid =1470 order by cursor_type desc, LAST_SQL_ACTIVE_TIME desc; CURSOR_TYPE LAST_SQL_ACTIVE_TIM SQL_TEXT ---------------------------------------------------------------- ------------------- ------------------------------------------------------------ SESSION CURSOR CACHED 02/07/2022 12:07:50 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;SESSION CURSOR CACHED 02/07/2022 12:07:50 select text from view$ where obj#=:1 SESSION CURSOR CACHED 02/07/2022 12:07:50 select spare6 from user$ where user#=:1 SESSION CURSOR CACHED 02/07/2022 12:07:50 select privilege# from sysauth$ where (grantee#=:1 or grante SESSION CURSOR CACHED 02/07/2022 12:07:50 SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION', SESSION CURSOR CACHED 02/07/2022 12:07:50 select to_char(userenv('INSTANCE')) my_inst, to_char(s.sid) SESSION CURSOR CACHED 02/07/2022 12:07:50 select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, SESSION CURSOR CACHED 02/07/2022 12:07:50 select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U SESSION CURSOR CACHED 02/07/2022 12:07:50 select /*+ connect_by_filtering index(sysauth$ i_sysauth1) * OPEN-RECURSIVE 02/07/2022 12:07:50 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0 OPEN 02/07/2022 12:09:15 select userenv('SID') from dual |
I won’t be able to put all output because it will be much bigger in a minute. simple explanation for cursor types:
OPEN is currently opened (last run) sql statement.
OPEN-PL/SQL is currently opened cursors in plsql. it could be more than one, you can open many cursor remember that.
OPEN-RECURSIVE is recursive sql statements for your actual statement (for example Oracle needs a privilege check if you have access to those objects, it runs a query to find that)
SESSION CURSOR CACHED is half of what we are looking for here. direct sql statements which has been run in this session and cached in session PGA.
PL/SQL CURSOR CACHED is the other big half of what we are looking for here. Sql/plsql statements which has been run in a plsql object which is called in this session.
there are few more types but I won’t go detail all of them right now. if session runs a sql statements directly (select, insert, update, delete, merge etc) it will be cached as SESSION CURSOR CACHED. if session calls a plsql objects, all sql statements in it will be cached as PL/SQL CURSOR CACHED. Remember, to cache an sql statement, it must be run at least 3 times, only then it will be cached. by saying “run” cursor must be opened and closed. if you just run a query 3 times, you will not see it in cache because last one is still opened. run something else and then it will be in the cache.
you can see session cached cursors count via session statistics:
|
1 2 3 4 5 6 7 8 9 |
select s.sid, n.name, value from v$statname n, v$sesstat s where n.name like 'session cursor cache %' and s.statistic# = n.statistic# and s.sid=1470; NAME VALUE ---------------------------- ---------- session cursor cache hits 46 session cursor cache count 31 |
“session cursor cache hits” is how many times a new statement has been found in session cursor cache. if you run a query 100 times repeatedly, after first 3 (after 3 runs it will be cached) next 97 will be read from cache and session cursor cache hits will increase by 97. “session cursor cache count” is currently number of cached objects (all sql, plsql and dictionary object queries).
as I said by default SESSION_CACHED_CURSORS is 50 so my session can cache up to 50 different statements and if there is more? Oracle will use a Least Recently Used (LRU) algorithm to keep most used (and recently) queries. Previously issued statements will be deleted from the session cache (not from library cache).
Let’s start some tests and run 3000 different queries 3 times (so they can be cached). of course instead of calling 3000 different queries I will use a simple plsql. those queries will be cached as PL/SQL CURSOR CACHED. Also, I will create a tmp table as copy of dba_objects to do my tests:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- main session MUSTAFA@orcl-1> create table tmp as select * from dba_objects; MUSTAFA@orcl-1> begin for i in 1..3000 loop execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; end loop; end; / |
before continue, you might wonder why I used dynamic sql. if I use a static sql as “select count(*) from t where object_id = i” then i would be a bind variable and every query I run would be same. so, we couldn’t see the difference.
From the monitoring session let’s check V$OPEN_CURSOR for main session (a little bit long output)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
--monitoring session select cursor_type, last_sql_active_time, sql_text from v$open_cursor where sid =1470 order by cursor_type desc, LAST_SQL_ACTIVE_TIME desc; CURSOR_TYPE LAST_SQL_ACTIVE_TIM SQL_TEXT -------------------- ------------------- ------------------------------------------------------------ PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2987 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2964 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2972 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2952 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2998 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2994 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2951 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2999 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2991 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2961 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2995 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2984 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2977 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2986 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2982 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2983 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2971 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 3000 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2978 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2974 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2990 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2954 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2956 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2965 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2981 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2985 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2953 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2966 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2955 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2969 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2979 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2980 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2992 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2975 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2962 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2957 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2976 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2958 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2960 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2996 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2989 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2968 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2997 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2988 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2970 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2993 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2959 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2973 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2967 PL/SQL CURSOR CACHED 02/07/2022 19:57:32 select count(*) from tmp where object_id = 2963 OPEN-RECURSIVE 02/07/2022 12:07:50 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0 OPEN 02/07/2022 19:57:32 begin for i in 1..3000 loop execute immediate 'select |
as you see, 50 different select statement has been cached which IDs starting from 2951 to 3000. out last 50 queries. also OPEN cursor is our initial plsql code.
so what would happen if we increase the cache? is it a good thing or a bad thing? well, it depends. it is a good thing because you will be doing “softer” parse. your session will access to repeated queries faster but since this information is stored in PGA (session specific memory area) it will increase the usage of PGA. if you don’t have enough PGA it could be a pain for your database. PGA is automatically managed by Oracle. it grows or shrinks based on session needs. So, you might end up too much pga resize operation and even cannot cache that much queries at all.
Let’s check current PGA size for our main session now, from monitoring session:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
col name for a30 select name, round(value/1024/1024) size_in_mb from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and name = 'session pga memory' and s.sid = 1470; NAME SIZE_IN_MB ------------------------------ ---------- session pga memory 3 |
so our main (testing) session is using 3 MB pga size. now, I will increase the size of session_cached_cursors to 1000 and run previous 3000 queries again.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- main session MUSTAFA@orcl-1> alter session set session_cached_cursors=1000; MUSTAFA@orcl-1> begin for i in 1..3000 loop execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; end loop; end; / |
check the pga size now:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--monitoring session select name, round(value/1024/1024) size_in_mb from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and name = 'session pga memory' and s.sid = 1470; NAME SIZE_IN_MB ------------------------------ ---------- session pga memory 8 |
now it uses 8MB of PGA. Numbers could be small but think it this way; we are using 2.5 times higher PGA then previous. Also, if you have 10.000 active sessions, this could be a problem. it is a high usage. if you have enough memory then, there is nothing to be afraid of or at least you should be adjusting your memory components accordingly. there is no correct size or ratio because this is highly depend on your application coding. You must test the values because if your application uses same queries during a business job then, you can have a smaller session_cached_cursors but if not then, you must increase the size based on your memory.
how about the good side? Yes, let’s measure that. Assume we have a business process running with 400 different queries for every run and run this process 1000 times in a day. how it would affect the performance increasing or decreasing the parameter. I will start with 100.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
--main session MUSTAFA@orcl-1> set timing on MUSTAFA@orcl-1>alter session set session_cached_cursors=100; Session altered. MUSTAFA@orcl-1> begin for k in 1..1000 loop for i in 1..400 loop execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; end loop; end loop; end; / PL/SQL procedure successfully completed. Elapsed: 00:00:29.38 |
it took 29.38 seconds to run all queries. Now increasing the parameter value to 400 (which is almost sufficient to store all queries I run):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
--main session MUSTAFA@orcl-1> set timing on MUSTAFA@orcl-1>alter session set session_cached_cursors=400; Session altered. MUSTAFA@orcl-1> begin for k in 1..1000 loop for i in 1..400 loop execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; execute immediate 'select count(*) from tmp where object_id = ' ||i; end loop; end loop; end; / PL/SQL procedure successfully completed. Elapsed: 00:00:24.63 |
24.63 seconds. 5 seconds shorter which means around %17 percent faster. which is not bad and actually good. think your system is running faster by %17 percent without doing a complex sql tuning or changing the hardware. if you set session_cached_cursors parameter between 100 and 400, elapsed time would be between 24 and 29 accordingly, pga usage too.
finally, you can also query how many times your session used session cached cursors :
|
1 2 3 4 5 6 7 8 9 |
select n.name, value from v$statname n, v$sesstat s where n.name like 'session cursor cache %' and s.statistic# = n.statistic# and s.sid=1470; NAME VALUE ------------------------------ ---------- session cursor cache hits 2011646 session cursor cache count 399 |
as you see currently, 399 different queries (sql statements) has been cached in the session and those queries has been accessed 2.011.646 times.
This is a quite nice trick to me but must be used cautiously because of the increase of PGA usage.
I hope this helps to increase your databases efficiency.
Wish you all healthy days.