Re: Shared Pool: Can it be filled *over* 100% ?

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<2o0jciF5efc1U1_at_uni-berlin.de>...
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> schrieb im Newsbeitrag
> news:2o0is0F5funcU1_at_uni-berlin.de...
> > Hello,
> >
> > Oracle EE 8.1.7.3 on AIX 5,
> > 1 instance on 1 central Bull Escala Server (8GB RAM), Dedicated Server
> > Configuration
> > 200 users using Oracle Forms 5 as inhouse app over C/S.
> >
> > Symptom: For the first time on the instance I saw ORA-04031 on some rare
> > occasions.
> > So, before taking action, I wanted to see how the shared pool is used, and
> I
> > tried the following SQL
> > taken from Mike Aults´ book "Oracle Administration and Management":
> >
> > prompt
> > prompt Übersicht Ausnutzung des Shared Pool:
> >
> > select
> > sum(a.bytes)/(1024*1024) Shared_Pool_Used
> > ,max(b.value)/(1024*1024) Shared_Pool_Size
> > ,(max(b.value)/(1024*1024))-(sum(a.bytes)/(1024*1024))
> Shared_Pool_Avail
> > ,(sum(a.bytes)/max(b.value))*100 Shared_Pool_Pct
> > from
> > v$sgastat a,
> > v$parameter b
> > where a.pool = 'shared pool'
> > and a.name != 'free memory'
> > and b.name = 'shared_pool_size'
> > ;
> >
> > Yesterday I saw Shared_Pool_Pct constantly in a bandwidth between 88 and
> 95
> > %.
> > So I followed Mikes´ advice to increase shared_pool_size and
> > shared_pool_reserved_size
> > and rebounced the instance over night.
> >
> > This morning I was one of the first being in place
> > and I saw Shared_Pool_Pct about 80%; so I thought - OK.
> >
> > Two hours later, most users are already working meanwhile,
> > I see:
> >
> > Übersicht Ausnutzung des Shared Pool:
> >
> > SHARED_POOL_USED SHARED_POOL_SIZE SHARED_POOL_AVAIL SHARED_POOL_PCT
> > ---------------- ---------------- ----------------- ---------------
> > 100,34935 95,3674316 -4,9819183 105,22392 --<-
> > !!! *over* 100 % and climbing ????
> >
>
> ... which appears to be stabilized around 105 %, but still, this is strange
> ...
>
> Any comments are greatly appreciated.
>
> TIA, Jan
>
> >

The query is, indeed, wrong. It should be this:

prompt
prompt Übersicht Ausnutzung des Shared Pool:

select a.spused/(1024*1024) Shared_Pool_Used,

       b.spsize/(1024*1024) Shared_Pool_Size, 
       ((b.spsize/(1024*1024)) - (a.spused/(1024*1024))) Shared_Pool_Avail,
       (a.spused/b.spsize)*100 Shared_Pool_Pct
from
	(select sum(bytes) spused from v$sgastat where pool = 'shared pool'
         and name != 'free memory') a,
	(select max(value) spsize from v$parameter where name = 'shared_pool_size') b; 

This will eliminate the cartesian product present in the previous query.

David Fitzjarrell Received on Thu Aug 12 2004 - 17:05:12 CDT