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
> --
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_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 - 08:09:24 CST
