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