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

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Thu, 12 Aug 2004 08:56:07 +0200
Message-ID: <2o0is0F5funcU1@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 ????

1 Zeile wurde ausgewählt.

Wenn Shared_Pool_Pct ständig über 90%: => shared_pool_size erweitern !

I also have a small Oracle Form for Session observation where I can requery this value 10 times in a second.
I can see the trend going constantly up.

Additional information: 40 users use about 2000 SQL areas in total, the top 6 are:

Select

Username,

   Sum(Sharable_Mem) Sharable,
   Sum(Persistent_Mem) Persistent,
   Sum(Runtime_Mem) Runtime,

Count(*) Areas,
Sum(Sharable_Mem+Persistent_Mem+Runtime_Mem) Sum_Mem From JG_SQL_Summary
Group By Username
Order By 2 Desc;
Übersicht Ausnutzung des Shared Pool:

USERNAME                         SHARABLE PERSISTENT    RUNTIME      AREAS

SUM_MEM

------------------------------ ---------- ---------- ---------- ---------- -
---------
MASTERMIND                       10758622     671096    2594084        692
14023802 --the owner of 80% of the apps
PAYROLL                           7110912    1027704    1811984        655
9950600 --the owner of the rest of the apps
KLOSTERMAUER                  6689916      96312    1370896         51
8157124 --a user
ZIEGELSTEIN                 3739888     176076    1051944        128
4967908 --another user
SERVICEACCOUNTING                 2900458      76076     687496         58
3664030 --and yet another ...
SYSTEM                            2770015     155240    1012380        190

3937635
[real names altered except SYSTEM]
...

where JG_SQL_Summary is the following view:

Select

   Username,
   Sharable_Mem,
   Persistent_Mem,
   Runtime_Mem
From

V$Sqlarea A,
Dba_Users B
Where A.Parsing_User_Id = B.User_Id

Right now I am doing some RTFM again, but maybe somone has a tip, maybe there is something wrong with the query for Shared_Pool_Pct ?

Any comments are greatly appreciated.

TIA, Jan Received on Thu Aug 12 2004 - 01:56:07 CDT