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
Received on Thu Aug 12 2004 - 02:26:24 CDT
