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 --- [EMAIL PROTECTED] wrote: > kirti-- would you recommend avoiding pga_aggregate_target for now? > > > > 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! Hotjobs: Enter the "Signing Bonus" Sweepstakes > > http://hotjobs.sweepstakes.yahoo.com/signingbonus > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Kirtikumar Deshpande > > 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: <[EMAIL PROTECTED] > 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). __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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).
