Re: SESSION_CACHED_CURSORS

There are a lot of cursor caches nowadays.

The first is the Session Cursor Cache, which is simply an array (or an hash table) in the server process memory (assuming dedicated connections), whose max dimension is dictated by session_cached_cursors. This cache contains a pointer into the library cache where the cursor was when it was closed; when a parse for the same statement is requested, the process follows the pointer hoping to find the cursor still intact in the library cache (if that is the case, a lot of latching is avoided since the pointer is basically a shortcut that avoids reading -and thus latching - some memory structures).
But being in this cache doesn't prevent the cursor from being erased by the library cache if needed (the library cache knows nothing about this cache, it's a process thing), hence it cannot influence fragmentation.

Another cache is the PL/SQL Cursor Cache, which is different - a cursor in this cache is still open as far as the library cache is concerned, hence it cannot be removed from the library cache - so it can influence fragmentation. This cache is an "array" as well, contained in the process memory as well, whose max dimension was dictated by open_cursors before 9.2.0.5 and by session_cached_cursors from 9.2.0.5+ (never understood why they didn't use a different parameter). BTW if session_cached_cursors=0 (the default), the actual size is 20. Oh, I don't know about 11g.

Then there is the JDBC 3.0 Cursor Cache, that behaves similarly to the PL/SQL one but is controlled by the Java client (and resides in the Java client process memory) using its own policies; this, too, influences fragmentation.
Other languages might have their own cursor caches, and one can certainly write an application that keeps the cursors open in between executions "manually".

But - it is important to know that a cursor in the library cache is composed by many memory fragments, and some can be removed from the library cache if the cursor is open but not currently executing. One such example is the "plan", that is, the compiled form of the Sql Statement, that can be recreated by simply recompiling the statement at the next execution.

And recent versions of Oracle (especially 10g AFAIK) try to use standard sizes for the memory "pages" allocated to cursors, hence the fragmentation is less likely to occur.

That is qualitatively how it works - so your papers are correct in saying that an excessively high session_cached_cursors *might* cause some fragmentation,
but not necessarily in a measurable way if any.

HTH
Al

On 8/30/07, DBA Deepak <oracle.tutorials_at_gmail.com> wrote:
>
>
> Hi All,
> Have a doubt on the SESSION_CACHED_CURSORS parameter. If we set this
> parameter to some higher value is there a chance that the shared pool will
> get fragmented because of this parameter setting? If yes, then am just
> curious to know how?
>
> Some articles say if we have a fragmented shared pool then we may consider
> decreasing this parameter value. In this case why Oracle will not be able to
> free-up the memory occupied by the cached cursors when space is required in
> the shared pool for incoming SQLs.
>
> --
> Regards,
>
> Deepak
> Oracle DBA

-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l