Re: V$SGASTAT

From: Ls Cheng <exriscer_at_gmail.com>
Date: Tue, 11 Mar 2014 15:16:07 +0100
Message-ID: <CAJ2-Qb9a48Qae1QPCW29a13M8dXMbiRV7wOa93zoRv9rzs640Q_at_mail.gmail.com>

Yea that is the minimum value you have set, the rest is configured by the automatic sga stuff

I would set a minimum value for db_cache_size as well, somehoe the database kernel favours shared pool, most of time if you let it manage all you end up huge shared pool and small db cache

On Tue, Mar 11, 2014 at 3:14 PM, Uzzell, Stephan <SUzzell_at_micros.com> wrote:

> We do have them set... So I presume that means the 256MB is the floor
> value, and Oracle has increased the value aiming for our target or max
> values.
>
>
>
> Thanks!
>
>
>
> *Stephan Uzzell*
>
>
>
> *From:* Ls Cheng [mailto:exriscer_at_gmail.com]
> *Sent:* Tuesday, 11 March, 2014 10:01
> *To:* Uzzell, Stephan
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: V$SGASTAT
>
>
>
> Hi
>
> Do you have sga_target, sga_max_size, memory_target or memory_max_target
> set?
>
>
>
> On Tue, Mar 11, 2014 at 2:45 PM, 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:07 CET