| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: pga_aggregate_target and a memory leak
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_at_yahoo.com>
> > > Date: 2004/01/21 Wed PM 02:44:31 EST
> > > To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > > Subject: Re: pga_aggregate_target and a memory
> leak
> > >
> > > Replies in line...
> > >
> > > - Kirti
> > >
> > > --- Jared.Still_at_radisys.com 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_at_yahoo.com>
> > > > Sent by: ml-errors_at_fatcity.com
> > > > 01/21/2004 06:09 AM
> > > > Please respond to ORACLE-L
> > > >
> > > >
> > > > To: Multiple recipients of list
> ORACLE-L <ORACLE-L_at_fatcity.com>
> > > > 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_at_oriole.com>
> wrote:
> > > > > ryan.gaffuri_at_cox.net 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
> > > > > --
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Drake INET: discgolfdba_at_yahoo.com 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_at_fatcity.com (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).Received on Wed Jan 21 2004 - 22:04:25 CST
 |
 |