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
- ryan.gaffuri_at_cox.net wrote:
> kirti-- would you recommend avoiding pga_aggregate_target for now?
> >
> > 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
> > > > --
> > >
> > >
> > >
> > >
> > >
> >
> >
> > __________________________________
> > 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).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <ryan.gaffuri_at_cox.net
> INET: ryan.gaffuri_at_cox.net
>
> 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).
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 - 15:04:34 CST
