You are using MTS/SS; have you configured the Large Pool to accommodate all of the UGA structures? If you do not have the Large Pool configured from its default of 0, then all of the UGA (i.e. session global areas, shared amongst the dispatchers and shared servers) will be placed into the Shared Pool, which is not an appropriate place for them.
For sizing, you might consider querying V$SYSSTAT where NAME = ‘session uga memory max’, then add a “fudge factor” (i.e. double it, if possible?) depending on how confident you are that you captured peak activity.
If you have already configured the Large Pool and can confirm that the MTS/SS processes are placing their UGAs there (by querying V$SGASTAT), then your shared pool problems lie elsewhere. Please confirm that the ORA-4031 is mentioning the Shared Pool, not the Large Pool in that case, please...
If this doesn’t help, could you query V$SGASTAT, order by POOL and BYTES, and post the output to your reply to this list? Since V$SGASTAT is a “real-time” view (i.e. reflective of the present point-in-time), it would be most useful if the query were performed as soon as possible after an ORA-04031 is received, but we understand that they are occurring intermittently and that may not be possible...
Thanks!
-Tim
on 10/27/03 4:59 AM, VIVEK_SHARMA at [EMAIL PROTECTED] wrote:
Intermittent ORA-4031 errors Out of shared Pool :-
Oracle ver 9203
Solaris 9
Concurrent Users = 6000
Shared Servers / MTS being used
Listeners = 4
Application using Bind Variables
Application = Banking - Hybrid in Nature
Database size = 1 TB
m/c = SF15K
How can this issue be approached ?
Should we consider moving to Oracle 9204 / higher ?
large_pool_size big integer 2147483648
max_shared_servers integer 1000
mts_circuits integer 11000
mts_dispatchers string (address=(protocol=tcp)(host=1
0.16.14.236))(listener=CONSOLD
GLIST)(dispatchers=7), (addres
s=(protocol=tcp)(host=10.16.14
.236))(listener=OEMDGLIST)(dis
patchers=7), (address=(protoco
l=tcp)(host=10.16.14.236))(lis
tener=BBYDGLIST6)(dispatchers=
7), (address=(protocol=tcp)(ho
st=10.16.14.236))(listener=BBY
DGLIST2)(dispatchers=7), (addr
ess=(protocol=tcp)(host=10.16.
14.236))(listener=BBYDGLIST3)(
dispatchers=7), (address=(prot
ocol=tcp)(host=10.16.0.215))(l
istener=BBYDGLIST4)(dispatcher
s=7),
mts_listener_address string
mts_max_dispatchers integer 150
mts_max_servers integer 1000
mts_multiple_listeners boolean FALSE
mts_servers integer 300
mts_service string bby01
mts_sessions integer 10995
shared_pool_reserved_size big integer 367001600
shared_pool_size big integer 1056964608
Will provide any Data needed
Thanks
