Re: PGA & Open_Cursors

hpuxrac wrote:
> qAnand wrote:
> > Hi All,
> > I was going throught the Oracle Concepts guide where in it was
> > described that every server process has its own PGA and PGA consists of
> > Private SQL area (persistent & runtime area). The persistent area of
> > PGA deals with cursors (implicit & explicit).
> >
> > My question is, whenever a cursors are opened in a particular session,
> > does it take space in the same persistent area of Private SQL area, or
> > new areas are alloted for each cursor?
> > If this is true, can we have more than one Private SQL area for a
> > session based on Open_Cursors parameter?
> >
>
> Oracle supports sessions having multiple open cursors each open cursor
> uses some part of the PGA. Each new open cursor is handled by oracle
> code. Since multiple simultaneous open cursors are supported (even
> mandatory ) each cursor uses it's own small chunk of memory ... whether
> oracle has pre-allocated a big chunk then keeps parceling out parts of
> that until it needs a new big chunk ... probably you need someone from
> oracle internals to answer that question.

Partially explained in the performance manual: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i38400

I've found in a particularly cursor-intensive app that required many thousands of cursors, session_cached_cursors let me cut open_cursors by an order of magnitude.

Some googling finds things like
http://www.freelists.org/archives/oracle-l/02-2004/msg01349.html and http://www.vldb.org/conf/2002/S29P03.pdf I see references to a pga paper by Joze Senegacnik but haven't been able to find it online.

This all suggests the answers to the questions are "it depends" (on version, init parameters, server dedication and coding) and "yes," since each cursor is a private area.

jg

--
@home.com is bogus.
http://www.everwonder.com/david/wizardofoz/
Also try watching "The Missles of October" with King Crimson's first
album.