Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-4031 and Shared Pool Fragmentation

Re: ORA-4031 and Shared Pool Fragmentation

From: Howard J. Rogers <howardjr_at_www.com>
Date: Wed, 8 Nov 2000 01:32:03 +1100
Message-ID: <3a0812a4@news.iprimus.com.au>

Well, in that case you have the option to set up a reserved pool so that there is (theoretically) plenty of unfragmented space in your SGA to accomdate an extra package or three.

You should also try and used dbms_shared_pool to keep what SQL you can, to minimise the contention issues, in the keep pool -though it sounds as if you'll have an enormous default pool, and a fairly small keep pool. A reserved pool will certainly help with the 4031 errors, though.

A bigger shared pool size in and of itself is not a bad idea.

Flushing will fix the same problem in the same way that taking a sledgehammer to a walnut will result in something approximating in a shelled piece of edible nut.

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------


"Alan Molloy" <alan_molloy_at_hotmail.com> wrote in message
news:01c048c4$03265e90$617da8c0_at_opc097...

> Sorry about that Howard,
> v$version returns
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
>
> Regards,
> Alan.
>
>
> Howard J. Rogers <howardjr_at_www.com> wrote in article
> <3a07e864$1_at_news.iprimus.com.au>...
> > A version number would help, on the grounds that shared pool management
has
> > changed so dynamically between 8.0 and 8i that the answers in one case
are
> > totally inappropriate in another.
> >
> > Regards
> > HJR
> > --
> >
> >
> --------------------------------------------------------------------------
-
> > Opinions expressed are my own, and not those of Oracle Corporation
> > Oracle DBA Resources:
http://www.geocities.com/howardjr2000
> >
> --------------------------------------------------------------------------
-
> >
> >
> > "Alan Molloy" <alan_molloy_at_hotmail.com> wrote in message
> > news:01c048ab$d24f8c50$617da8c0_at_opc097...
> > > I'm sure this has been answered before, and I have had some
interesting
> > > info from IXORA's website, but I'm still a bit confused.
> > > I've an ORACLE database that receives a lot of unbound/unparsed SQL. I
> > > can't really change that behaviour unfortunately, because the SQL is
> > > generated dynamically by a seperate production application.
> > > Am I right in saying that the symptoms of shared pool and library
cache
> > > latch contention are pretty much expected in this situation?
> > > After this production application has been chugging away for a number
of
> > > hours, I get a ORA-4031 error. I assume because the shared pool
becomes
> > > progressively more and more fragmented.
> > > There is no big packages or PL/SQL or anything. Just these SQL
statements
> > > being passed through via an oracle client.
> > > My question really is what steps(assuming I can't bind the SQL) can be
> > > taken to get the most out of this situation?
> > > 1 Can I stop the 4031 error where all the SQL coming in is different?
Do I
> > > have to schedule a shared pool flush every few hours to stop this from
> > > happening?
> > > 2 Is there a way of cutting down the latch contention?
> > >
> > > Regards,
> > > Alan
> >
> >
> >
Received on Tue Nov 07 2000 - 08:32:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US