Re: ORA-4031 errors no a high Load Database

Vivek,

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 VIVEK_SHARMA_at_infosys.com 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
>
>
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).