Re: How to troubleshoot heavy RAM consumption
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: Paul Drake <bdbafh@xxxxxxxxx>
- Date: Wed, 10 Mar 2010 12:42:13 -0700
Great advice. I'll look at sessions by PGA usage in v$process. I didn't
realize that the pga_aggregate_target was that soft of a limit. Is there a
better source for determining exactly what, at the session level, is eating
up the PGA memory?
What are some factors that can contribute to sessions using, and hanging on
to pga?
As far as parallel goes, we're not doing a whole lot during business hours.
On Wed, Mar 10, 2010 at 10:51 AM, Paul Drake <bdbafh@xxxxxxxxx> wrote:
> Neil,
>
> It looks like you have some memory free in the large and java pools.
> Is this running with automatic memory management?
> Still, that's not in the ballpark of a pga that should be 6 GB going to 14
> GB.
>
> It appears that the max pga_aggregate used was around 14 GB.
> You appear to be on the right track.
> Its not that the pga_aggregate_target setting is too small, its that
> it isn't being respected.
> You're probably going to want to drill down into pga mem usage.
> Perhaps you have a few sessions that would be better off with a manual
> workarea policy.
> Is there a large amount of parallel work going on?
>
> If you hike the value for pga_aggregate_target, it might make the
> problem worse as Oracle may extrapolate into using even larger amounts
> of pga memory (than the peak of 14 GB).
>
> Paul
>
>
> On Wed, Mar 10, 2010 at 12:14 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> > Our 32gb solaris 9 server running two instances of 10gr2
> > One small db to support OID (sga max size 288M, pga aggregate 32M) and
> one
> > of our production dbs(6gb pga aggregate target, 12gb sga_max_size).
> > According to top, before the workday starts, we are sitting at 10-12gb
> > available physical memory. Once our users get working, that number drops
> to
> > 500-600mb available. Top reports 29G swap in use, 11G swap free. OEM
> says
> > that we're performing heavy swapping and I'm not sure how to resolve
> this.
> > We typically have 1500-1700 connections. My gut tells me that pga might
> be
> > too small and that sga is too large, but i'm not really able to tell.
> > My v$pgastat and v$sgastat can be viewed at
> >
> http://spreadsheets.google.com/ccc?key=0ArUTHdqN0J--dFZBNE0wU0JpUW1Na1hVYlBfeFVYOEE&hl=en
> >
> >
>
Other related posts:
