Re: V$SGASTAT

From: John Hurley <hurleyjohnb_at_yahoo.com>
Date: Tue, 11 Mar 2014 10:16:12 -0400
Message-Id: <43D5C90C-78E6-4C83-84FB-2EDA174791B3_at_yahoo.com>

Sorry 4 or 5 gigabytes not uncommon 11g 256 meg min siZe seems way way off

Sent from my iPhone

> On Mar 11, 2014, at 10:01 AM, John Hurley <hurleyjohnb_at_yahoo.com> wrote:
> 
> I just presented at Hotsos three approaches to shared pool monitoring can send to you.  Looks like you probably want 4 fb shared pool on each node or more at minimum not uncommon 11g me not a big fan of all the auto dynamic stuff
> 
> Sent from my iPhone
> 

>> On Mar 11, 2014, at 9:45 AM, "Uzzell, Stephan" <SUzzell_at_MICROS.COM> wrote:
>>
>> Hi all,
>>
>> We recently had an issue with an undersized shared pool. We’ve increase the size of the shared pool and think this should be resolved. Our customer, however, is freaking out and wants to monitor this.
>>
>> I’ve been looking at v$sgastat hoping this would provide the information, but I don’t quite understand how the view works. Testing against 11.2.0.3.7 on OEL6x64.
>>
>> Shared_pool_size is set at 256MB for each instance in this RAC. Yet when I try to sum up either the free memory value or the non-free values, I get results far larger than that:
>>
>> SQL> select
>> 2 type
>> 3 , round( sum( bytes /1024 /1024 ) ) MB
>> 4 from
>> 5 (
>> 6 select
>> 7 case when name = 'free memory' then 'FREE' else 'USED' end type
>> 8 , bytes
>> 9 from
>> 10 v$sgastat
>> 11 where
>> 12 pool = 'shared pool'
>> 13 )
>> 14 group by
>> 15 type
>> 16 /
>>
>> TYPE MB
>> ---- ----------
>> USED 3760
>> FREE 1744
>>
>> Am I completely misunderstanding how this is supposed to work? Is there some way to monitor shared pool usage?
>>
>> Stephan Uzzell
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 11 2014 - 15:16:12 CET