Re: How to troubleshoot heavy RAM consumption
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Wed, 10 Mar 2010 15:09:49 -0700
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@xxxxxxxxx> 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@xxxxxxxxx> 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@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:
