Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SESSION_CACHED_CURSORS


From: LS Cheng <>
Date: Fri, 31 Aug 2007 00:27:56 +0200
Message-ID: <>

Hi Alberto

I guess when you say session_cached_cursors can cause fragmentation you mean it happens whe we are talking in the context of PL/SQL cursor cache? Because the old session_cached_cursors are located in PGA as you have mentioned. If so strictly speaking this possible fragmentation cause only applies for those versions <



On 8/30/07, Alberto Dell'Era <> wrote:

> 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 and
> by session_cached_cursors from (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.
> Al
> On 8/30/07, DBA Deepak <> 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"
> --
Received on Thu Aug 30 2007 - 17:27:56 CDT

Original text of this message