Re: How to troubleshoot heavy RAM consumption
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Neil Kodner <nkodner@xxxxxxxxx>
- Date: Tue, 23 Mar 2010 11:33:35 -0700
Normally if there were heavy swapping going on I would
expect to see some page out activity, but that doesn't
seem to be happening:
pi po
16473 55
3588 3
7302 2
7920 2
21752 5
21926 3
25373 0
33929 0
39866 5
32109 2
20420 42
11618 3
The page in activity may be accounted for by new processes starting,
which is a normal part of starting a process (at least on unices, linux
that I have been familiar with - been awhile since I've worked on AIX,
Solaris)
As for reducing PGA usage, could be that some of the SQL statements
could benefit from some optimization.
Perhaps materialized views with query re-write could be used, but I don't
know your apps, so this may not be an option.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
On Tue, Mar 23, 2010 at 8:59 AM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> I'd say we're spending plenty of time swapping
>
> oracle- WSCP10>vmstat 5 12
> kthr memory page disk faults cpu
> r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy
> id
> 0 2 0 23249264 8514968 1870 1499 16473 55 56 94048 21 0 0 0 0 5234 23399
> 15356 10 6 84
> 0 7 0 9463488 654320 580 4993 3588 3 3 55544 0 0 0 0 0 5454 77866 22089 38
> 10 52
> 0 7 0 9447904 638992 303 4079 7302 2 2 32816 0 0 0 0 0 5536 128397 21793
> 41 12 47
> 0 8 0 9451584 642008 1124 5260 7920 2 2 19384 0 0 0 0 0 5824 123475 23344
> 34 15 51
> 0 12 0 9449792 636648 1950 5793 21752 5 3 10312 0 0 0 0 0 6481 95698 24062
> 41 16 43
> 0 7 0 9442400 627152 2319 6994 21926 3 2 6104 0 0 0 0 0 5850 148490 23835
> 33 17 49
> 0 7 0 9400144 577904 1945 6007 25373 0 0 95704 0 0 0 0 0 5065 98153 20889
> 34 13 53
> 0 12 0 9448896 622712 2941 4145 33929 0 0 56520 0 0 0 0 0 4877 70036 21715
> 32 11 57
> 0 17 0 9804352 956424 3381 7072 39866 5 5 33392 0 0 0 0 0 5987 111988
> 24021 31 18 51
> 0 20 0 9730568 884992 3132 9930 32109 2 2 19728 0 0 0 0 0 7168 142250
> 25074 45 18 37
> 0 17 0 9773440 924344 1662 6122 20420 42 42 11664 0 0 0 0 0 6672 125605
> 30617 40 16 44
> 0 12 0 9770808 918480 1036 6440 11618 3 3 6904 0 0 0 0 0 7420 134967 28124
> 49 18 33
>
>
>
> On Wed, Mar 10, 2010 at 8:38 PM, Jared Still <jkstill@xxxxxxxxx> wrote:
>
>> Heavy use of swap can be a good indication of memory shortage.
>>
>> You can't simply rely on the '29G swap' reported however.
>>
>> True, that is a lot of swap, but what you probably want to track
>> is how much time the system is spending on swapping memory.
>>
>> vmstat 5 12 will show a minutes worth at 5 second intervals.
>>
>> It's been quite awhile since I worked on Solaris, but I do know
>> there are some other things to look for there.
>>
>> When looking for Solaris advice, it wouldn't hurt to include
>> Glenn Fawcett in your search terms. :)
>>
>> http://blogs.sun.com/glennf/
>>
>>
>> Re the advice views, I've found them to be pretty good the
>> few times I have used them.
>>
>>
>> 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 4:11 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
>>
>>> Thanks for all of your help. I dont know if this is a case of 'need more
>>> memory' or need more tuning and have never really been in this situation
>>> before. This used to be plenty of server 5-6 years ago but now, as
>>> unemployment has not only increased, but benefit eligibility has increased,
>>> we are doing far more business than ever.
>>>
>>>
>>> On Wed, Mar 10, 2010 at 4:57 PM, Jared Still <jkstill@xxxxxxxxx> wrote:
>>>
>>>>
>>>> On Wed, Mar 10, 2010 at 2:09 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
>>>>
>>>>> 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.
>>>>>
>>>>>
>>>> In general I would think the SGA too large if there were more
>>>> free memory available at peak periods than need be.
>>>>
>>>> select *
>>>> from v$sgastat
>>>> where name = 'free memory'
>>>> order by upper(name)
>>>> /
>>>>
>>>> The 'need be' is the hard part. I personally don't have to go
>>>> through this kind of exercise very often.
>>>>
>>>> If I had allocated 12 gig for an SGA and consistently had 2 gig
>>>> free I would certainly consider distributing some of that elsewhere.
>>>>
>>>> Jared Still
>>>> Certifiable Oracle DBA and Part Time Perl Evangelist
>>>> Oracle Blog: http://jkstill.blogspot.com
>>>> Home Page: http://jaredstill.com
>>>>
>>>>
>>>>
>>>
>>
>
Other related posts:
