RE: ORA-04031

As one who has been there, I can sympathize. I had a TAR open for months going back and forth with Oracle Support. The entire problem boiled down to the fact that Oracle Corporation does not have a supported method ("supported" is the key word) to query the free chunks of the shared pool to determine the amount of fragmentation. Fragmentation of the shared pool was the most likely cause of our ORA-4031 errors, but because Oracle Support said it couldn't be measured, I wasn't able to measure the effect of my changes to the shared pool size and the pinning. Their answer was "bump up the shared pool, bounce the instance, repeat" until it stopped.

So, completely frustrated, I ended up on Steve Adams' site at http://www.ixora.com.au There are some great Q&A sections there about dealing with ORA-4031s. I also purchased Steve's book, Oracle8i Internal Services (http://www.oreilly.com/catalog/orinternals/) from http://www.bookpool.com The book complements the website by going in-depth into some of the X$ views to get info on the shared pool and other structures.

I am by no means an expert on the shared pool, but we were able to solve our ORA-4031s with help from these resources.

GL!

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Seema Singh [mailto:oracledbam_at_hotmail.com]
> Sent: Thursday, September 12, 2002 2:18 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: ORA-04031
>
>
> its 8.1.7.4
>
>
> >From: "Mark J. Bobak" <mark_at_bobak.net>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Re: ORA-04031
> >Date: Wed, 11 Sep 2002 21:08:18 -0800
> >
> >Um, version? (To at least 4 places, please!)
> >
> >On the off change you are on 8.1.7.x, where x < 3, please upgrade to
> >8.1.7.4. There were several bugs related to ORA-4031 from 8.1.7.0
> >through at least 8.1.7.2.
> >
> >-Mark
> >On Wed, 2002-09-11 at 18:14, Seema Singh wrote:
> > > Hi
> > > One of instance are showing ORA-04031 error more
> frequntly(2 times in a
> > > week).I increased SGA ,pinned some of DBMS packages,flush
> SGA etc but
> >all
> > > these are not helping at all.Let me know what could be look next.
> > > Thx
> > > -Seema

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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).