SESSION_CACHED_CURSORS VS CURSOR_SPACE_TIME-Which When & Why?

They 2 considered to be the most important parameter for shared pool tuning, but I guess most of us generally don’t use them or sometimes use them incorrectly.

The idea to put them here to understand “what they do?”, “when to use them?”, “how to use them?” and finally “see the impact”.

SESSION_CACHED_CURSORS

In most of the environments, there are many SQL’s which are re-fired many a times within a session, and every time they are issued, the session searches the shared pool for the parsed state, if it doesn’t get the parsed version, it will “hard parse” it, and if it exists in shared pool, it will still do a “soft parse”.

As we know “hard parse” is a costly operation, even a “soft parse” requires library cache latch and CPU overhead, which if aggregated is a significant number.

This parameter if set to a non-zero value (default is 50), improves the “soft parse” performance by doing a softer soft parse.

How?

If enabled, oracle maintains a local session cache which stores recently closed cursors of a session.

To avoid this space getting misused or overused, oracle maintains the cursors for which there have been 3 parsed calls in the past, so all the SQL’s issued by a session are not here. Remember each cursor if pinned here, is not freeable and hence you may require more shared pool area.

A normal cursor in shared pool is sum of 2 components:
a) Heap 0 – size 1KB
b) SQL Area – size multiple of 4k

When we use session_cached_cursors only first component of cursor which is HEAP 0 is pinned in local session cache and if there is a call for re-parse for a statement, Oracle first checks the existence of the cursor in local cache and if found, it gets the address of the rest of the cursor which is in SQL Area (assuming if it is not aged out), so hereby saving CPU overhead and library cache latch contention.


How much it is getting used ?

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

MAX(VALUE)
----------
100

This shows maximum value for session_cached_cursors in past. If this value= “session_cached_cursors” (init.ora parameter), you should consider increasing it.

If you want to see how is your session cache doing?

SQL> select cache/tot*100 "Session cursor cache%"
2 from
3 (select value tot from v$sysstat where name='parse count (total)'),
4 ( select value cache from sys.v_$sysstat where name = 'session cursor cache hits' );

Session cursor cache%
---------------------
106.635284

A value near 100 is considered very good. But you may still consider increasing this parameter if MAX(VALUE) in query one shows you equal number of cached cursor which you have set.


Conclusion: In an OLTP application, where the same set of SQL is issued number of times, one must configure this parameter more than its default value (50).
Also increasing this parameter will mean extra memory required for shared pool, so you must increase your shared pool when you use this parameter.

CURSOR_SPACE_FOR_TIME

SQL can be aged out of shared pool in 2 cases:
a) When the cursor is closed: When the cursors are closed by application, they can still be in shared pool, until there comes a request for a new cursor and oracle needs to use LRU algorithm. SESSION_CACHED_CURSORS helps you in pinning (partial because it only pins HEAP 0) when the cursors are closed.

b) When the cursor is open: Oracle requires parsed state of SQL at PARSE and EXECUTE phase. If oracle parses (soft or hard) a statement, there is a likely hood that Oracle may age out your SQL out of shared pool after PARSE state if it requires to accommodate a new SQL coming its way. So in the EXECUTE state, there is a possibility that parsed information is lost and oracle parse it again.

CURSOR_SPACE_FOR_TIME if set to TRUE, ensures that SQL is not aged out before the cursor is closed, so in EXECUTE phase, you will have the PARSE information.

But this is generally a rare case and happens in a very highly active environment because to accommodate a new SQL, Oracle first check the free space and if it doesn’t get, it checks the closed cursors and see if any cursor can be aged out and when there is no space which can be reclaimed, Oracle comes to open cursors which are not EXECUTED.
This generally happens when the space of shared pool is too less.

I don’t suggest setting this parameter to TRUE in most of the cases.

There are some other serious tradeoffs also.
When a cursor is pinned, it cant be aged out and related memory cannot be freed for any new SQL and if you set this parameter to TRUE, you are telling Oracle to keep all the open cursors pinned and not freeable.
If you use this parameter, you are pinning the whole cursor not just the HEAP 0 which is 1k, you are pinning HEAP 0 (1k) + SQL Area (multiple of 4k) which makes shared pool life tough because of space issues.

Conclusion:

As I said, I don’t suggest setting this parameter to TRUE in most of the cases. An alternative to set this parameter is to increase shared pool size or/and check your code on how many numbers of cursors you are opening/closing. That will be a better approach. Setting this parameter is like taking paracetamol without knowing the cause of fever.