Re: How to troubleshoot heavy RAM consumption

From: Neil Kodner <nkodner_at_gmail.com>
Date: Wed, 10 Mar 2010 17:23:38 -0700
Message-ID: <ae2c46ba1003101623v4299c922ya9896c9fdd1a7454_at_mail.gmail.com>



I've never really known how much I can 'trust' the views *advice views so I've never really made decisions based on them.

Our system was restarted about a week ago so things might still be in the process of balancing out-I don't know how much data the advice views needs.

I've updated my spreadsheet to show the results from v$pga_target_Advice and v$sga_target_advice

http://spreadsheets.google.com/ccc?key=0ArUTHdqN0J--dFZBNE0wU0JpUW1Na1hVYlBfeFVYOEE&hl=en

On Wed, Mar 10, 2010 at 5:15 PM, Allen, Brandon <Brandon.Allen_at_oneneck.com>wrote:

> Have you checked v$sga_target_advice (or the GUI equivalent) and
> v$db_cache_advice?
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Neil Kodner
> *Sent:* Wednesday, March 10, 2010 3:10 PM
> *To:* Jared Still
> *Cc:* Paul Drake; oracle-l-freelists
> *Subject:* Re: How to troubleshoot heavy RAM consumption
>
>
>
> Thanks. As an aside, while we're having memory/paging issues, is there a
> good way to tell if our SGA is in fact too large? One of the challenges
> that we face is that one of the heavier-used applications does not use
> prepared statements and that has the potential to pollute the shared pool.
> We enable cursor-sharing at the session level for these users.
>
> On Wed, Mar 10, 2010 at 2:51 PM, Jared Still <jkstill_at_gmail.com> wrote:
>
> Neil,
>
>
>
> You may want to try running the attached script pga_advice
>
> to see how much PGA Oracle thinks you need to meet current usage.
>
>
>
> The script pga_workarea_active.sql will show how the memory
>
> is being used. It doesn't show which sessions, I don't think I
>
> have a query for that, but someone else posted the tables needed
>
> if you want to figure that out.
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>
>
>
>
> On Wed, Mar 10, 2010 at 11:42 AM, Neil Kodner <nkodner_at_gmail.com> wrote:
>
> 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_at_gmail.com> 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_at_gmail.com> 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
> >
> >
>
>
>
>
>
>
>
> ------------------------------
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 10 2010 - 18:23:38 CST

Original text of this message