RE: Help with Shared Pool Problem

  • From: "Smith, Ron L." <rlsmith@xxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, "Ganesh Raja" <ganesh.raja@xxxxxxxxx>
  • Date: Thu, 14 Oct 2004 09:29:07 -0500

Thanks Mark.

-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]=20
Sent: Thursday, October 14, 2004 9:11 AM
To: Smith, Ron L.; Ganesh Raja
Cc: shaharul.anuar@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Help with Shared Pool Problem


Ron,

I have to disagree w/ Ganesh.  A SQL, once execution completes, should
be=20
eligible for flushing, unless it was kept.  You don't have something
running that would keep existing cursors into the library cache, do you?
I would tend to doubt that you do, but, if you did, that would certainly
cause problems.

I think you want to go here: http://www.ixora.com.au/scripts/pool.htm
and review some of the scripts, expecially shared_pool_lru_stats.sql,
shared_pool_free_lists.sql, and shared_pool_summary.sql

Steve covers what these scripts do, but, briefly, the summary shows you
what kinds of objects are taking space in the shared pool. The
free_lists script shows you how fragmented the free lists are, (which
can lead to ORA-4031) and finally, the lru_stats script can give you
some idea of whether your shared pool is undersized.

Check the website and Steve's book for more details as to how it all
works and how to interpret the outputs of these scripts,=20
especially the lru_stats script.

Hope that helps,

-Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Smith, Ron L.
Sent: Thursday, October 14, 2004 9:48 AM
To: Ganesh Raja
Cc: shaharul.anuar@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Help with Shared Pool Problem


So you are saying there is no LRU processing taking place?  Nothing is
ever aged out or overwritten?

Ron

-----Original Message-----
From: Ganesh Raja [mailto:ganesh.raja@xxxxxxxxx]=3D20
Sent: Thursday, October 14, 2004 8:33 AM
To: Smith, Ron L.
Cc: shaharul.anuar@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Help with Shared Pool Problem


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

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

HTH

~
Ganesh



On Thu, 14 Oct 2004 08:08:35 -0500, Smith, Ron L. <rlsmith@xxxxxxx>
wrote:
> I know the reason for the Shared pool filling up is because the=3D20 =20
>programs do not use bind variables. =3D20
> My question was, why do the old SQL statements not get aged out =
and=3D20
> free up space for new statements?
>=3D20
> Ron
>=3D20
> -----Original Message-----
> From: shaharul.anuar@xxxxxxxxxxxxxxxx=3D20
> [mailto:shaharul.anuar@xxxxxxxxxxxxxxxx]=3D3D20
> Sent: Thursday, October 14, 2004 8:06 AM
> To: Smith, Ron L.; oracle-l-bounce@xxxxxxxxxxxxx;=3D20
> oracle-l@xxxxxxxxxxxxx
> Subject: RE: Help with Shared Pool Problem
>=3D20
>=3D20
> Hi Ron,
>=3D20
> 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=3D20
> values, same SQL will be treated as new piece of code and has to =
be=3D20
> hard parsed each time.
>=3D20
> Shaharul
>=3D20
>=3D20
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
> Sent: 10/14/2004 1:55 PM
> Subject: Help with Shared Pool Problem
>=3D20
> Can someone tell me why I keep running out of shared pool space?
If=3D20
> 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=3D20
> data loads are running.
>=3D20
> Ron
>=3D20
> Important Notice!!
> If you are not the intended recipient of this e-mail message, any use,

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

> =3D
let

> me know immediately by return e-mail if you have received =3D3D3D=20
>this=3D20  message by mistake, then delete the e-mail message. Thank =
you.
> --
> //www.freelists.org/webpage/oracle-l
>=3D20
> This e-mail and any attachments are confidential and may also be=3D20  =

>legally privileged and/or copyright material of Intec Telecom=20
>Systems=3D20  PLC (or its affiliated companies). If you are not an=20
>intended or=3D20  authorised recipient of this e-mail or have received =
it

>in error,=3D20  please delete it immediately and notify the sender by=20
>e-mail. In such=3D20  a case, reading, reproducing, printing or further =

>dissemination of=3D20  this e-mail or its contents is strictly =
prohibited

>and may be=3D20  unlawful.=3D3D20 =3D20
> Intec Telecom Systems PLC does not represent or warrant that an=3D20
> attachment hereto is free from computer viruses or other defects.
The=3D20
> opinions expressed in this e-mail and any attachments may be those
of=3D20
> the author and are not necessarily those of Intec Telecom Systems PLC.
>=3D20
>=3D20
>=3D20
>=3D20
> Important Notice!!
> If you are not the intended recipient of this e-mail message, any use,

> =3D3D distribution or copying of the message is prohibited. Please let =
=3D
me=3D20
> know immediately by return e-mail if you have received =3D3D this =3D
message=3D20
> by mistake, then delete the e-mail message. Thank you.
> --
> //www.freelists.org/webpage/oracle-l
>


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.
--
//www.freelists.org/webpage/oracle-l


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 know immediately by return e-mail if you have received =
this message by mistake, then delete the e-mail message.
Thank you.
--
//www.freelists.org/webpage/oracle-l

Other related posts: