session_cached_cursors sizing Oracle Database Tips by Donald Burleson

As we know, as new SQL enters the database Oracle checks the library cache for parsed SQL statements, and the session_cached_cursors can be used to ?pin? the cursors for the most popular SQL.

Pinning SQL with session_cached_cursors is especially useful for reentrant SQL that contains hoist variables, and there are many incarnations of the SQL, each with a different host variable value.  The session_cached_cursors parameter is used to reduce the amount of parsing with SQL statements that use host variables and with PL/SQL cursors. 

The session_cached_cursors parameter has a default value of 50, and increasing the value of session_cached_cursors will requires a larger shared_pool_size to cache the cursors. 

Many shops double the default value for session_cached_cursors to 100, and some use values as high as 1,000, depending on the application.

Oracle notes that the session_cached_cursors is related to the open_cursors parameter and if you are concerned that cursors are being paged-out of the library cache, increasing session_cached_cursors up to the value of open_cursors can improve performance.

To monitor the benefit increasing session_cached_cursors, we look for the session cursor cache hits value in our STATSPACK or AWR reports.  We can also see this metric with data dictionary scripts.

col c1 heading 'sID?
col c2 heading ?Cache|Hits?
col c3 heading ?All Parsing?
col c4 heading Un-used Session|Cached Cursors?

select
   sid         c1,
   stat1.value c2,
   stat2.value c3,
   stat2.value c4
from
   v$sesstat  stat1,
   v$sesstat  stat2,
   v$statname name1,
   v$statname name2
where
   stat1.statistic# = name1.statistic# 
and
   name1.statistic# = name2.statistic#;
and
   name1.name = 'session cursor cache hits'
and
   stat2.statistic#=name2.statistic#
and
   name2.name= 'parse count (total)'
and
   stat2.sid= stat1.sid
 

The session_cached_cursors parameter is used to reduce the amount of parsing with SQL statements that use host variables. 

The session_cached_cursors parameter has a default value of 50, and increasing the value of session_cached_cursors will requires a larger shared_pool_size to cache the cursors. 

Many shops double the default value for session_cached_cursors to 100, and some use values as high as 1,000, depending on the application.

From the Oracle 9.2.0.5 patchset notes:

However, if you have session_cached_cursors set to zero, or set at a value significantly lower than the open_cursors parameter, and you are concerned that PL/SQL cursors need to be cached for optimal performance, then you should ensure that the session_cached_cursors parameter is increased appropriately.

This suggests a starting point for session_cached_cursors at 50:

 If you do not have this parameter set already (session_cached_cursors) then it is advisable to set it to a starting value of about 50.

The statistics section of the bstat/estat report includes a value for 'session cursor cache hits' which shows if the cursor cache is giving any benefit.

Sizing session_cached_cursors

Here are some great tips by Steve Adams for sizing your session_cached_cursors:

"The session cursor cache is an important facility for reducing load on the library cache. In our opinion, the session_cached_cursors parameter should always be set to at least 2.

However, a larger value is normally beneficial.

The session cursor cache can be constrained by either the session_cached_cursors parameter, or the open_cursors parameter.

This script reports the current maximum usage in any session with respect to these limits.

If either of the usage figures approaches 100%, then the corresponding parameter should normally be increased."