Re: SESSION_CACHED_CURSORS

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 < 9.2.0.5.

Thanks

--
LSC

On 8/30/07, Alberto Dell'Era <alberto.dellera_at_gmail.com> 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 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

>

>

>


--
http://www.freelists.org/webpage/oracle-l