| 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: pga_aggregate_target and a memory leak
Replies in line...
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: Kirtikumar Deshpande INET: kirtikumar_deshpande_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 - 13:44:31 CST
 |
 |