RE: session_cached_cursors

Caching Session Cursors
If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the session cursors can affect system performance. Session cursors can be stored in a session cursor cache. This feature can be particularly useful for applications designed using Oracle Forms because switching between forms closes all session cursors associated with a form.
Oracle uses the shared SQL area to determine if more than three parse requests have been issued on a given statement. If so, Oracle assumes the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session will then find the cursor in the session cursor cache.
To enable caching of session cursors, you must set the initialization parameter SESSION_CACHED_CURSORS. This parameter is a positive integer that specifies the maximum number of session cursors kept in the cache. A least recently used (LRU) algorithm ages out entries in the session cursor cache to make room for new entries when needed. You can also enable the session cursor cache dynamically with the ALTER SESSION SET SESSION_CACHED_CURSORS command. To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic "session cursor cache hits" in the V$SESSTAT view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, you should consider setting SESSION_CACHED_CURSORS to a larger value.

-
Chris J. Guidry P.Eng.
ATCO Electric, Metering Services
Phone: (780) 420-4142
Fax: (780) 420-3854
Email: chris.guidry_at_atcoelectric.com

> -----Original Message-----
> From: Benhayoune khalid [SMTP:benhayoune_at_maroclear.co.ma]
> Sent: Thursday, May 18, 2000 12:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: session_cached_cursors
>
> Hi All,
>
> Excuse my poor English !
>
> What are parameters and statistics should i consider to size the value of
> the initialization parameter SESSION_CACHED_CURSORS ?
>
> Is there any rule about this ?
>
> I use the following SQL query and compare the values of the statistics
> "parse count" and "session cursor cache hits", but i am not sure :
>
> select name,value
> from v$sysstat
> where statistic# in (131,142);
> | |------------------- parse count
>
> |-------------------------- session cursor cache hits
>
> Thanks in advance.
>
> ------------------------------------------------------------------
> K.Benhayoune
> System engineer/DBA
> MAROCLEAR
> Phone : 212-2-48-49-49
> Fax : 212-2-48-67-03
> Address : 216, BD ZERKTOUNI, 5 eme etage
Received on Thu May 18 2000 - 17:09:04 CDT