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:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> Sent: Wednesday, January 21, 2004 11:04 PM > --- Kirtikumar Deshpande > <[EMAIL PROTECTED]> 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 > > <[EMAIL PROTECTED]> > > > > Date: 2004/01/21 Wed PM 02:44:31 EST > > > > To: Multiple recipients of list ORACLE-L > > <[EMAIL PROTECTED]> > > > > Subject: Re: pga_aggregate_target and a memory > > leak > > > > > > > > Replies in line... > > > > > > > > - Kirti > > > > > > > > --- [EMAIL PROTECTED] 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 > > <[EMAIL PROTECTED]> > > > > > Sent by: [EMAIL PROTECTED] > > > > > 01/21/2004 06:09 AM > > > > > Please respond to ORACLE-L > > > > > > > > > > > > > > > To: Multiple recipients of list > > ORACLE-L <[EMAIL PROTECTED]> > > > > > 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 <[EMAIL PROTECTED]> > > wrote: > > > > > > [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
