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

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<opsclyyaae3d8uqx_at_shostakovich.dizwell.com>...
> On Thu, 12 Aug 2004 09:04:59 +0200, Jan Gelbrich
> <j_gelbrich_at_westfalen-blatt.de> wrote:
>
> > "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
>
>
> Since the query comes from Mike Ault... well, do a Google on his
> contributions here and you'll get the idea.
>
> That the percentage ever goes over 100% means the formula is not reliable,
> or you haven't typed it in right.
>
> I know which of those is more probable... and your typing is fine.
>
> Regards
> HJR
Part of the problem is that the Oracle statistics are not that reliable to begin with. Some statistics partially overlap and the different statistics are updated at various points in the code and depending on what you are looking at you may catch one statistics which reflects certain activities not yet accounted for in another related statistic.

Oracle had/has a script on metalink related to estimating the shared pool size that contains some notes about double counting of shared pool memory by various queries used to make the estimate. It might be  Oracle7: How to calculate your shared pool size, document 1012046.6. You may find of interest.

Also in 8.1 I am pretty sure there were a couple of bugs related to shared pool statistics and the fact v$sga, v$sgastat, and some other views into the same information reported different values. When dealing with the shared pool which is subject to constant change you are really just getting an estimate.

IMHO -- Mark D Powell -- Received on Thu Aug 12 2004 - 10:32:25 CDT