RE: V$SGASTAT
- From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- To: 'John Hurley' <hurleyjohnb@xxxxxxxxx>
- Date: Tue, 11 Mar 2014 14:19:58 +0000
In retrospect, this is all because I picked my test bed poorly. We only have
shared_pool_size set because some upgrade note somewhere said it needed to be
at least 256MB. Otherwise this environment is generally all set to auto
management.
Stephan Uzzell
From: John Hurley [mailto:hurleyjohnb@xxxxxxxxx]
Sent: Tuesday, 11 March, 2014 10:16
To: hurleyjohnb@xxxxxxxxx; Uzzell, Stephan
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: V$SGASTAT
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@xxxxxxxxx<mailto:hurleyjohnb@xxxxxxxxx>> 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@xxxxxxxxxx<mailto:SUzzell@xxxxxxxxxx>> 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
Other related posts:
