RE: Help with Shared Pool Problem
- From: "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 14 Oct 2004 14:18:47 -0500
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 8.1.7.4 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.
Kathy
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Thursday, October 14, 2004 1:43 PM
To: Paul Drake
Cc: ganesh.raja@xxxxxxxxx; rlsmith@xxxxxxx; shaharul.anuar@xxxxxxxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: RE: Help with Shared Pool Problem
Good point, Paul.
See this item over at AskTom for full details:
http://asktom.oracle.com/pls/ask/f?p=3D4950:8:5150260736162217859::NO::F4=
950_P8_DISPLAYID,F4950_P8_CRITERIA:17989406187750,
Original poster: What version of Oracle are you using???
-----Original Message-----
From: Paul Drake [mailto:bdbafh@xxxxxxxxx]
Sent: Thursday, October 14, 2004 2:27 PM
To: Bobak, Mark
Cc: ganesh.raja@xxxxxxxxx; rlsmith@xxxxxxx;
shaharul.anuar@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Help with Shared Pool Problem
IIRC,
in 9.2.0.5 - the behavior of open_cursors changes.
I remember while attending a presentation at NYOUG
(http://www.nyoug.org) 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.
Paul
On Thu, 14 Oct 2004 11:55:52 -0400, Bobak, Mark
<mark.bobak@xxxxxxxxxxxxxxx> wrote:
> Ganesh,
>=20
> 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
>=20
> Yes, there is an LRU mechanism for certain components in
> the shared pool. See the scripts I previously mentioned
> on Steve Adams' website, IxOra.
>=20
> -Mark
>
--
//www.freelists.org/webpage/oracle-l
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
