RE: ORA-4031 Error

Hi Peter,

The shared pool is a cache. Oracle only refrains from using all of it to reduce the risk of fragmentation. Otherwise, it will be fully used.

If you have large numbers of users, then you should focus on KEEPing and only increase the shared pool in small increments (say 10%) until the ORA-4031 errors stop.

The use of the SET TRANSACTION statement (and doing things from Pro*C) has no impact on shared pool memory demands.

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/


-----Original Message-----
From:	pschauss_at_parker.com [SMTP:pschauss_at_parker.com]
Sent:	Friday, June 16, 2000 4:29 AM
To:	Multiple recipients of list ORACLE-L
Subject:	ORA-4031 Error

One of our application processes got the following error:

 ORA-04031: unable to allocate 4216 bytes of shared memory ("shared pool","unknown object","sga heap","library cache")

The Error Messages manual says that the cure it either to use dbms_shared_pool package to pin large packages into memory, reduce use of shared memory, or increase the value of the SHARED_POOL_SIZE parameter.

If we opt for the latter, how do we know how much larger to make it?

Is there some system table or view which will tell give us a high water mark on use of shared pool so we can tell how much room we've got once we increase this parameter?

The code which caused the error was a SET TRANSACTION READ ONLY statement in PRO*C. How was this putting us over the limit on our shared pool usage?

thanks,
Peter Schauss
Parker Hannifin Corp
Smithtown, NY

--
Author:
  INET: pschauss_at_parker.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).