RE: V$SGASTAT

From: Uzzell, Stephan <SUzzell_at_MICROS.COM>
Date: Tue, 11 Mar 2014 14:14:06 +0000
Message-ID: <ba980ff8a7dd4fd1adb32f01da0932aa_at_USMAIL2K1303.us.micros.int>

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<mailto: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:14:06 CET