Re: SESSION_CACHED_CURSORS

Hi Alberto

I think I didnt explain poperly.

I mean

Version < 9.2.0.5: No fragmentation caused by session_cached_cursors because it was simply a PGA thing
Version >= 9.2.0.5:Possible fragmentation because PL/SQLCursor Caching is now controlled by this parameter so the parameter now affects both PGA and SGA Thanks

--
LSC




On 8/31/07, Alberto Dell'Era <alberto.dellera_at_gmail.com> wrote:


>

> No, it's actually the opposite - session_cached_cursors controls the

> PL/SQL Cursor Cache only for versions >= 9.2.0.5 (again, I don't

> know about 11g).

>

> It's not the fact that the Session Cursor Cache is in the PGA that makes

> it not causing (possible) fragmentation, but rather that cursors there

> "cached"

> do not "lock" the cursors in the library cache. The Session Cursor Cache

> is composed by simple "pointers", and the "pointed" cursor can disappear

> (or be replaced by another cursor) at will. So the library cache can

> discard

> them if pressured for space.

>

> On 8/31/07, LS Cheng <exriscer_at_gmail.com> wrote:

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

> > >

> > >

> > >

> >

> >

>

>

> --

> Alberto Dell'Era

> "the more you know, the faster you go"

>


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