RE: ORA-04031

Also check out Metalink note 146599.1 which is on diagnosing ora-4031 errors.

This has helped me.

Also you can consider adding an event in your init.ora file like "4031 trace name errorstack level 3" This will dump an errorstack when the 4031 error occurs - this can be interpreted by Oracle support. However, realise those files will take up room and that when the error occurs the user will experience a delay whilst the dump file is written.

HTH,
Bruce Reardon

-----Original Message-----

X$KSMLRU is the free list chunks.
On Thu, 2002-09-12 at 17:03, Jesse, Rich wrote:
> 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
> >
> > its 8.1.7.4
> >
> >
> > >From: "Mark J. Bobak" <mark_at_bobak.net>
> > >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

-- 
--
Mark J. Bobak
Oracle DBA
mark_at_bobak.net
"It is not enough to have a good mind.  The main thing is to use it well."
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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).