Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

SESSION_CACHED_CURSORS Parameter and How can It Affect the Database

Mustafa, 2022-07-02

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:

Oracle PL/SQL
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:

Oracle PL/SQL
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:

Oracle PL/SQL
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:

Oracle PL/SQL
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)

Oracle PL/SQL
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:

Oracle PL/SQL
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.

Oracle PL/SQL
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:

Oracle PL/SQL
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.

Oracle PL/SQL
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):

Oracle PL/SQL
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 :

Oracle PL/SQL
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.

11g 12c 18c 19c 21c caching queriespga usagequery performancesession cursor cache countsession cursor cache hitssession pga memorySESSION_CACHED_CURSORSV$OPEN_CURSOR

Post navigation

Previous post
Next post

Comments (2)

  1. Alaa says:
    2023-12-12 at 20:04

    Hi Mustafa

    Thank you for the enlightening details about cursor types. I would greatly appreciate if you can provide additional clarification on the “OPEN-RECURSIVE” cursor type.

    Reply
    1. Mustafa says:
      2023-12-14 at 11:14

      Hi Alaa,

      OPEN-RECURSIVE cursors are created internally by oracle to run your command. For example, when you run a select statement Oracle will do some checks like “is that table exist”, “do you have privilege to select” or “what are the columns of the table” etc. all those checks are internal select statements and they will be mark as OPEN-RECURSIVE. another example is audit, if there is an enabled audit on the statement you run, Oracle will insert audit records into audit tables, that insert statement will be marked as OPEN-RECURSIVE.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes