Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: pga_aggregate_target and a memory leak

RE: Re: pga_aggregate_target and a memory leak

From: Arnold, Sandra <ArnoldS_at_OSTINET.osti.gov>
Date: Thu, 22 Jan 2004 08:44:25 -0800
Message-ID: <F001.005DDE5D.20040122084425@fatcity.com>


I have had a problem on my 9i database for three weeks. I am getting a ORA-7445 error which is pointing to some memory problems. It is occurring during the CTX_DOC.FILTER process. We are running this process from a custom PL/SQL package that is being initiated from an Oracle Job. However, we still have the problem when we run it from a crontab job. I currently have a 21 page TAR concerning this problem.

Sandra Arnold
Principal DBA
NCI Information Systems
175 Oak Ridge Turnpike
Oak Ridge, TN 37830

-----Original Message-----
Sent: Thursday, January 22, 2004 5:05 AM To: Multiple recipients of list ORACLE-L

Im not sure I see what the size of the PAT has to do with a memory leak. On metalink there is a laundry list of PGA things that were supposedly causing memory leaks prior to 9.2.0.4. Are you certain its PAT causing it? Maybe they didnt fix all the memory leaks with the PGA in general?

has anyone had any production issues with pga memory leaks? There are a series of notes on metalink about this.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, January 21, 2004 11:04 PM

> --- Kirtikumar Deshpande
> <kirtikumar_deshpande_at_yahoo.com> wrote:
> > 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
>
> 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
> > > > > > --
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free web site building tool. Try it!
> http://webhosting.yahoo.com/ps/sb/
> --
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arnold, Sandra
  INET: ArnoldS_at_OSTINET.osti.gov

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 Thu Jan 22 2004 - 10:44:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US