[oracle-l] Re: pga_aggregate_target and a memory leak

Hi,

The bug I saw on the course was 3194895, but I am not able to see this one
Myself with my account, maybe some internal use only, but take a look at
Docs 3156574 or 2790318 this looks similar
The teacher also mentioned a patch to lift the 1GB pga limit to 5Gb
But I am not able to find this also. I will email him
To ask for details. Anybody else experience with this or this patch?

Regards,

Jeroen

-----Oorspronkelijk bericht-----
Van: Arnold, Sandra [mailto:ArnoldS@xxxxxxxxxxxxxxxx] 
Verzonden: vrijdag 23 januari 2004 3:19
Aan: Multiple recipients of list ORACLE-L
Onderwerp: RE: Re: pga_aggregate_target and a memory leak

I am interested in the bug number.  Currently am having memory problems that
may be related to the pga.

Sandra

-----Original Message-----
Sent: Thursday, January 22, 2004 5:09 PM
To: Multiple recipients of list ORACLE-L


Yes I have and still have a problem with pga memory leak
When using pl/sql tables. I'm on 9i performance and tuning course at oracle
Now and discussed this with the teacher. He went looking and found a bug
Stating that on 9i (9.2.0.2 and further) there seems to be a limit on total
pga per process of 1Gb. 

Setting pat=0 and work_area_size manual gave me a workaround for my
production problem but with a test of just a simple
Got a decent explanation today that pat=0 gives me more memory for pl/sql
Tables because there are always in pga and pat is about sort areas so
setting pat=0 gives more memory and less possibility of not having enough.

Pl/sql procedure assigning values to an array of number keeps reproducing
A pl/sql storage error also with pat=0 and wasp=manual.
I left the bug number in my notes, can get that tomorrow if somebody is
interested.

Jeroen
-----Oorspronkelijk bericht-----
Van: Ryan [mailto:ryan.gaffuri@xxxxxxx] 
Verzonden: donderdag 22 januari 2004 11:05
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: Re: pga_aggregate_target and a memory leak

Im not sure I see what the size of the PAT has to do with a memory leak. On
metalink there is a laundry list of PGA things that were supposedly causing
memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe
they didnt fix all the memory leaks with the PGA in general?

has anyone had any production issues with pga memory leaks? There are a
series of notes on metalink about this.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L@xxxxxxxxxxx>
Sent: Wednesday, January 21, 2004 11:04 PM


> --- Kirtikumar Deshpande
> <kirtikumar_deshpande@xxxxxxxxx> wrote:
> > I think it depends on your applications.
> >
> > In DSS type environments we are still stuggling to
> > figure out if P_A_T is helping or not. Initial
> > tests are not in P_A_T's favor.
> >
> > But in another Application, that is 80% OLTP, P_A_T
> > was the only choice to avoid swapping. This
> > 9.2.0.3 database had the S_A_S set to 2MB (S_A_R_S =
> > 1MB)at the instance level. It has over 600
> > persistent users. No MTS in use.
> >
> > - Kirti
>
> Kirti,
>
> I saw in a 9.2.0.4 database just this evening, much to
> my surprise, an ORA-00600 in the alert log with - you
> guessed it - [723], [10332], [10332], [memory leak].
>
> The database was setup in a less than optimal fashion
> as far as memory allocations go. The initial
> pga_aggregate_target was only 64M (server had 3 GB of
> memory and only one instance up) so I'm calling this
> one a non-sensical configuration error for the moment,
> as there is no need to size a PGA so small. If you're
> running with that small a memory footprint, don't use
> pga_aggregate_target.
>
> After resetting the parameter to 256M and cycling the
> instance, no ORA-00600's were recorded at instance
> shutdown. That was not really a good test though, will
> have to see tomorrow evening after the day's load has
> hit it.
>
> Paul
>
> this was on w2k server sp3, 9.2.0.4 std ed
>
>
> > > > From: Kirtikumar Deshpande
> > <kirtikumar_deshpande@xxxxxxxxx>
> > > > Date: 2004/01/21 Wed PM 02:44:31 EST
> > > > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L@xxxxxxxxxxx>
> > > > Subject: Re: pga_aggregate_target and a memory
> > leak
> > > >
> > > > Replies in line...
> > > >
> > > > - Kirti
> > > >
> > > > --- Jared.Still@xxxxxxxxxxx wrote:
> > > > > Kirti, you're back!
> > > >
> > > > Thanks. Found some slack time from routine DBA
> > work!
> > > >
> > > > > Must have finished the book.  :)
> > > >
> > > > Not yet.. Its tough..
> > > > >
> > > > > Re the PGA problems, what was the value for
> > 'over allocation count' in
> > > > > v$pgastat?
> > > >
> > > > Actually, I never bothered to look at v$pgastat.
> > Should have.. and will, when we do some more
> > > > testing next week..
> > > > >
> > > > > Did you try increasing P_A_T to a larger
> > number?
> > > >
> > > > Yes...
> > > >
> > > > > Oracle is supposed to grab the memory it
> > needs, if available, regardless
> > > > > of
> > > > > the P_A_T setting.
> > > > >
> > > > > Also, did your system go in to excessive
> > paging or swapping?
> > > >
> > > > Yes, it did with a large P_A_T.
> > > >
> > > > > I've been curious as to what the effects would
> > be of having P_A_T too low.
> > > >
> > > > I saw more disk sorts..
> > > >
> > > > As time permits, I will play with event 10032,
> > 10033 trace for sorts to see what's going on..
> > > >
> > > > > Oracle is supposed to grab whatever memory it
> > needs.  I'm assuming at this
> > > > > point that doing so involves a different code
> > path as it needs to alloc
> > > > > the memory.
> > > > >
> > > > > Don't know what the cost of that is, haven't
> > tried to test it.
> > > > >
> > > > > It seems likely that the OS was out of memory,
> > regardless of the P_A_T
> > > > > value.
> > > > >
> > > > No. The system has 4 GB of physical memory. Over
> > 2GB was free.
> > > >
> > > > > Jared
> > > > >
> > > > >
> > > > > Kirtikumar Deshpande
> > <kirtikumar_deshpande@xxxxxxxxx>
> > > > > Sent by: ml-errors@xxxxxxxxxxx
> > > > >  01/21/2004 06:09 AM
> > > > >  Please respond to ORACLE-L
> > > > >
> > > > >
> > > > >         To:     Multiple recipients of list
> > ORACLE-L <ORACLE-L@xxxxxxxxxxx>
> > > > >         cc:
> > > > >         Subject:        Re:
> > pga_aggregate_target and a memory leak
> > > > >
> > > > >
> > > > > Setting P_A_T to a 1GB limit with over 2GB of
> > *available memory* on AIX
> > > > > 4.3.3 and 9.2.0.4 caused
> > > > > ORA-4030, till we turned off hash joins. OS
> > level resources (ulimit -a)
> > > > > were all set to
> > > > > 'unlimited'. In a very limited testing,
> > setting P_A_T to less than S_A_S
> > > > > (and S_A_R_S) worked,
> > > > > however, the disk sorts increased. Finally,
> > Developers chose no hash
> > > > > joins, 1GB P_A_T and 'AUTO'
> > > > > workarea_size_policy... seems to run okay...
> > > > >
> > > > > - Kirti
> > > > >
> > > > > --- Stephane Faroult <sfaroult@xxxxxxxxxx>
> > wrote:
> > > > > > ryan.gaffuri@xxxxxxx wrote:
> > > > > > >
> > > > > > > One of our production DBAs does not want
> > to use pga_aggregate_target
> > > > > on a 9.2.0.3 instance due
> > > > > > to a possible memory leak. The only note on
> > memory leaks and
> > > > > pga_aggregate_target I can find on
> > > > > > metalink is: 334427.995
> > > > > > >
> > > > > > > doesnt seem to apply to
> > pga_aggregate_target. We are on sun solaris.
> > > > > Dont know version
> > > > > > offhand.
> > > > > > >
> > > > > > > he is under the impression that if we
> > patch to 9.2.0.4 this goes away.
> > > > > not sure about that
> > > > > > either...
> > > > > > >
> > > > > >
> > > > > > Be careful with pga_aggregate_target. I have
> > very recently seen a case
> > > > > > (Solaris + 9.2 but I cant't tell you exactly
> > which patch level -
> > > > > > probably the most recent) where two (by the
> > way atrocious) queries
> > > > > > generated by a DSS tool were responding very
> > differently - and in a way
> > > > > > that differences in the queries couldn't
> > explain. From an Oracle
> > > > > > standpoint, stats were roughly the same.
> > Tracing proved that we were
> > > > > > waiting for CPU, and truss that a call to
> > mmap() was the culprit. Why,
> > > > > > no idea. We first switched it (pga_thing)
> > off, no more slow call to
> > > > > > mmap(). However, it was still slow because
> > we hadn't checked
> > > > > > sort_area_size which was ridiculously small.
> > We set sort_area_size to
> > > > > > 10M, still with pga_aggregate_target unset,
> > and once again the same very
> > > > > > slow calls to mmap(). Memory misalignment?
> > Anything else? Not much time
> > > > > > to enquire but it looks like a mine field.
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > >
> > > > > > Stephane Faroult
> > > > > > Oriole Software
> > > > > > --
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free web site building tool. Try it!
> http://webhosting.yahoo.com/ps/sb/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Paul Drake
>   INET: discgolfdba@xxxxxxxxx
>
> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
> San Diego, California        -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru@xxxxxxxxxxx (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: ryan.gaffuri@xxxxxxx

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@xxxxxxxxxxx (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jeroen van Sluisdam
  INET: jeroen.van.sluisdam@xxxxxxxxxx

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@xxxxxxxxxxx (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arnold, Sandra
  INET: ArnoldS@xxxxxxxxxxxxxxxx

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@xxxxxxxxxxx (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Other related posts: