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: Duret, Kathy <>
Date: Thu, 14 Oct 2004 14:18:47 -0500
Message-ID: <>

Might want to check cursor spaced for time, we had it set to true for some reason and it caused memory leaks (perm chunks increase) for IUD's across db_links,
We also had someone miss using dbms_output in code and that caused leaks as well,.
This was on an database..

As your perm chunks counts goes up your SGA starts getting fragmented. Steve Adams has some good queries for this.

If you are using .NET make sure they are using the dispose parameter. There is a garbage collection problem /

These were my problems when I had 4031 errors acouple of months ago.


-----Original Message-----
From: Bobak, Mark [] Sent: Thursday, October 14, 2004 1:43 PM To: Paul Drake
Subject: RE: Help with Shared Pool Problem

Good point, Paul.

See this item over at AskTom for full details: 950_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 can also mean there is not enough contiguous memory
> in the shared pool. So, if you need 1k for a particular SQL
> statement, and the largest available chunk is 900k, then=3D20
> Oracle will signal an 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


This transmission contains information solely for intended recipient and may
be privileged, confidential and/or otherwise protect from disclosure.  If
you are not the intended recipient, please contact the sender and delete all
copies of this transmission.  This message and/or the materials contained
herein are not an offer to sell, or a solicitation of an offer to buy, any
securities or other instruments.  The information has been obtained or
derived from sources believed by us to be reliable, but we do not represent
that it is accurate or complete.  Any opinions or estimates contained in
this information constitute our judgment as of this date and are subject to
change without notice.  Any information you share with us will be used in
the operation of our business, and we do not request and do not want any
material, nonpublic information. Absent an express prior written agreement,
we are not agreeing to treat any information confidentially and will use any
and all information and reserve the right to publish or disclose any
information you share with us.
Received on Thu Oct 14 2004 - 14:14:27 CDT

Original text of this message