RE: Help with Shared Pool Problem

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Thu, 14 Oct 2004 11:56:08 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF275A9B0C@AABO-EXCHANGE02.bos.il.pqe>

Ron,

For the duration of the execution of a statement, Oracle holds a library cache pin, guaranteeing that it will not be flushed out.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Smith, Ron L. Sent: Thursday, October 14, 2004 10:17 AM To: Ganesh Raja
Cc: shaharul.anuar_at_intecbilling.com; oracle-l_at_freelists.org Subject: RE: Help with Shared Pool Problem

So until the current transaction or cursor is complete, the statements remain in the Shared Pool?

-----Original Message-----
From: Ganesh Raja [mailto:ganesh.raja_at_gmail.com]=3D20 Sent: Thursday, October 14, 2004 9:05 AM To: Smith, Ron L.
Cc: shaharul.anuar_at_intecbilling.com; oracle-l_at_freelists.org Subject: Re: Help with Shared Pool Problem

Yes there is But .. But the In your Case they are not Ageing out .. They are on the Hot List and they need to be there .. They are Pinned by Some other Session....

On Thu, 14 Oct 2004 08:48:07 -0500, Smith, Ron L. <rlsmith_at_kmg.com> wrote:
> So you are saying there is no LRU processing taking place? Nothing is

> ever aged out or overwritten?

>=3D20

> Ron

>=3D20
>=3D20
>=3D20

> -----Original Message-----
> From: Ganesh Raja [mailto:ganesh.raja_at_gmail.com]
> Sent: Thursday, October 14, 2004 8:33 AM
> To: Smith, Ron L.
> Cc: shaharul.anuar_at_intecbilling.com; oracle-l_at_freelists.org
> Subject: Re: Help with Shared Pool Problem

>=3D20

> There is No Aging happening .. Let us assume that u are Calling a=3D20
> Select Stmt Again and Again with different Parameters to it and =
also=3D20
> same parameters .. As Far as Oracle is concerened your stmts have =
not=3D20
> aged . They are new since it has now only been created or it has =
been=3D20
> used in the past few mins.

>=3D20
> So oracle tries to create new space for the stmt that u are trying =
to=3D20
> parse now and it fails there ..

>=3D20

> HTH

>=3D20

> ~
> Ganesh

>=3D20

> On Thu, 14 Oct 2004 08:08:35 -0500, Smith, Ron L. <rlsmith_at_kmg.com>
> wrote:
> > I know the reason for the Shared pool filling up is because the=3D20
> > programs do not use bind variables.
> >
> > My question was, why do the old SQL statements not get aged out =
and=3D20
> > free up space for new statements?
> >
> > Ron
> >
> > -----Original Message-----
> > From: shaharul.anuar_at_intecbilling.com=3D20
> > [mailto:shaharul.anuar_at_intecbilling.com]=3D3D20
> > Sent: Thursday, October 14, 2004 8:06 AM
> > To: Smith, Ron L.; oracle-l-bounce_at_freelists.org;=3D20
> > oracle-l_at_freelists.org
> > Subject: RE: Help with Shared Pool Problem
> >
> >
> > Hi Ron,
> >
> > Have u ran SQL trace on particular session doing large data =
load?=3D20
> > Looks like that piece of SQL is not using bind variables. If no bind

> > values, same SQL will be treated as new piece of code and has to =
be=3D20
> > hard parsed each time.
> >
> > Shaharul
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > To: oracle-l_at_freelists.org
> > Sent: 10/14/2004 1:55 PM
> > Subject: Help with Shared Pool Problem
> >
> > Can someone tell me why I keep running out of shared pool space? If

> > old SQL statements are supposed to be aged out or written over=3D20
> > automatically, how can I run out of space? This is happening =
from=3D20
> > time to time in several databases. It appears it happens when large

> > data loads are running.
> >
> > Ron
> >
> > Important Notice!!
> > If you are not the intended recipient of this e-mail message, =
any=3D20
> > use,

>=3D20
> > =3D3D =3D3D3D distribution or copying of the message is prohibited. =
=3D
Please=3D20
> > let

>=3D20
> > me know immediately by return e-mail if you have received =3D3D3D =
this =3D

> > message by mistake, then delete the e-mail message. Thank you.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> > This e-mail and any attachments are confidential and may also =
be=3D20
> > legally privileged and/or copyright material of Intec Telecom=3D20
> > Systems PLC (or its affiliated companies). If you are not an=3D20
> > intended or authorised recipient of this e-mail or have received =
it=3D20
> > in error, please delete it immediately and notify the sender by=3D20
> > e-mail. In such a case, reading, reproducing, printing or =
further=3D20
> > dissemination of this e-mail or its contents is strictly =
prohibited=3D20
> > and may be unlawful.=3D3D20
> >
> > Intec Telecom Systems PLC does not represent or warrant that an=3D20
> > attachment hereto is free from computer viruses or other =
defects.=3D20
> > The opinions expressed in this e-mail and any attachments may =
be=3D20
> > those of the author and are not necessarily those of Intec =
Telecom=3D20
> > Systems PLC.
> >
> >
> >
> >
> > Important Notice!!
> > If you are not the intended recipient of this e-mail message, =
any=3D20
> > use,

>=3D20
> > =3D3D distribution or copying of the message is prohibited. Please =
let =3D

> > me know immediately by return e-mail if you have received =3D3D =
this=3D20
> > message by mistake, then delete the e-mail message. Thank you.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >

>=3D20
>=3D20
>=3D20
>=3D20

> Important Notice!!
> If you are not the intended recipient of this e-mail message, any use,

> distribution or copying of the message is prohibited. Please let =
me=3D20
> know immediately by return e-mail if you have received this message by

> mistake, then delete the e-mail message. Thank you.
>

Important Notice!!
If you are not the intended recipient of this e-mail message, any use, = =3D
distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received =3D this message by mistake, then delete the e-mail message. Thank you.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 14 2004 - 10:56:47 CDT