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

"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

>
Received on Thu Aug 12 2004 - 02:04:59 CDT