Re: ORA-4031 and Shared Pool Fragmentation

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