Kirti, you're back!
Must have finished the book. :)
Re the PGA problems, what was the value for 'over allocation count' in v$pgastat?
Did you try increasing P_A_T to a larger number?
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?
I've been curious as to what the effects would be of having P_A_T too low.
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.
Jared
|
| Kirtikumar Deshpande <kirtikumar_deshpande@yahoo.com>
Sent by: ml-errors@fatcity.com
01/21/2004 06:09 AM
Please respond to ORACLE-L
|
To: Multiple recipients of list ORACLE-L <ORACLE-L@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@oriole.com> wrote:
> ryan.gaffuri@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
> --
__________________________________
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: kirtikumar_deshpande@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@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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: Jared.Still_at_radisys.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:19:25 CST
