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
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
> -----Original Message-----
> From: Seema Singh [mailto:[EMAIL PROTECTED]]
> 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" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
