Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with Shared Pool Problem

RE: Help with Shared Pool Problem

From: Smith, Ron L. <>
Date: Fri, 15 Oct 2004 08:00:49 -0500
Message-ID: <>

The version of Oracle is 8.1.7, but I had a similar problem on 7.3.4. In both cases the application is doing a mass insert of data, probably without bind variables. For recoverability and restart ability a commit is only issued at the end of the process. From what you have all said, this is causing the shared_pool to fill up with thousands of similar but not identical SQL insert statements. This is causing the problem

Thanks for your help!

-----Original Message-----
From: Bobak, Mark []=20 Sent: Thursday, October 14, 2004 1:43 PM To: Paul Drake
Cc:; Smith, Ron L.;; Subject: RE: Help with Shared Pool Problem

Good point, Paul.

See this item over at AskTom for full details: 9
50_P8_DISPLAYID,F4950_P8_CRITERIA:17989406187750, Original poster: What version of Oracle are you using???

-----Original Message-----
From: Paul Drake [] Sent: Thursday, October 14, 2004 2:27 PM To: Bobak, Mark
Cc:;;; Subject: Re: Help with Shared Pool Problem

IIRC, in - the behavior of open_cursors changes. I remember while attending a presentation at NYOUG ( given by Steven Feuerstein, that if your code is keeping cursors open that are not using bind variables, that you are in serious trouble.

Wait - it might have been at Jonathan Lewis' seminar. I'll have to get back to you on that.

Check as far as the number of session_cached_cursors and open_cursors. Perhaps Jonathan might have something to add.


On Thu, 14 Oct 2004 11:55:52 -0400, Bobak, Mark <> wrote:
> Ganesh,
> While ORA-4031 can mean that there is no space in the shared pool, it=20
> can also mean there is not enough contiguous memory in the shared=20
> pool. So, if you need 1k for a particular SQL statement, and the=20
> largest available chunk is 900k, then=3D20 Oracle will signal an=20
> ORA-4031. In this case, sometimes, flushing the shared pool can help,

> but not always. =3D20
> Yes, there is an LRU mechanism for certain components in
> the shared pool. See the scripts I previously mentioned
> on Steve Adams' website, IxOra.
> -Mark

Received on Fri Oct 15 2004 - 07:56:25 CDT

Original text of this message