Re: SESSION_CACHED_CURSORS
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: oracle.tutorials@xxxxxxxxx
- Date: Thu, 30 Aug 2007 22:08:20 +0200
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@xxxxxxxxx> 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"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
