Re: ORA-4031 Error

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Thu, 15 Jun 2000 15:38:36 -0700 (PDT)
Message-Id: <10529.109481@fatcity.com>

Peter,

The ORA-4031 error is one of the symptoms of shared pool fragmentation occurring in your database. Usually this is caused by significant amount of aging of objects from your shared pool. ORA-4031s could result from a badly-sized shared pool, improper management of the objects within the shared pool or because the shared pool is not properly configured.

The probability of an ORA-4031 occurring has significantly reduced since 7.3, due to some changes made to the shared pool space allocation algorithm. Prior to 7.3, when an object was 'x' bytes in size, then "x contiguous bytes" were required in the shared pool, else an ORA-4031 was generated. In 7.3 and up, when an object was 'x' bytes in size, then "x bytes" were required in the shared pool, regardless of whether it was contiguous or not. So in 7.3 and above an ORA-4031 was not generated unless the shared pool was "out of free space".

Prior to Oracle 8.0, apart from the normal usage of the shared pool for SQL statements, it was also the home for the stack space and the cursor state components of the PGA, when a user connection was made using the multi-threaded server (MTS) mode. This in itself caused fragmentation of the shared pool. In some late versions of 7.3, Parallel Query provided more competition for your shared pool, and RMAN joined the fray in 8.0.

To alleviate this problem, in Oracle 8.0 the initialization parameter LARGE_POOL_SIZE was introduced. When this parameter is configured, MTS/Parallel Query/RMAN will utilize the space allocated for the large pool for its operations, rather than the default shared pool area. This has had significant positive impact towards the reduction of the probability of ORA-4031 occurring. The norm for this parameter is 15-20% of SHARED_POOL_SIZE, but it may need some further tweaking based on your environment.

The other aspect to this problem, is the constant aging of stored SQL from your shared pool, due to increased presence of ad-hoc SQL. This in itself can cause havoc to the space allocation problems and eventually in space fragmentation. To take care of this problem, the SHARED_POOL_RESERVED_SIZE parameter was introduced in 7.3 and this area was intended for storing large stored SQL objects (procedures, functions, packages). While in 8i, the only supported parameter is the above, in prior releases between 7.3 and 8i, you may encounter parameters like SHARED_POOL_MIN_ALLOC etc. Such parameters have been de-supported in 8i (at least in my database).

The sizing of SHARED_POOL_RESERVED_SIZE parameter is dependent on the number of stored SQL objects that needs be "pinned" in memory. You can view V$DB_OBJECT_CACHE to determine size of objects, V$SHARED_POOL_RESERVED for reserved-area statistics, and V$SGASTAT for the usage of the various components of the SGA (shared pool included). By issuing the following query against V$SGASTAT you will find our your answer with regards to shared pool memory usage:

select pool,sum(bytes) from v$sgastat group by pool;

Hope that helps,

Gaja.

• pschauss_at_parker.com wrote:
  > 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).

  ---

Gaja Krishna Vaidyanatha | gajav_at_yahoo.com Brio Technology | (972)-304-1170

"Opinions and views expressed are my own and not of Brio Technology"

---

Received on Thu Jun 15 2000 - 17:38:36 CDT