Re: Help with Shared Pool Problem

Sounds like cursors are not being closed by the application program. They are being opened and used once, iteratively within a loop. Each opened cursor is still "active" in the Shared Pool and the space it consumed will not be recycled until it has been closed (inactivating it).

Just curious, have you set OPEN_CURSORS to a really high value? It's not always a good thing to do. This is proof.

This is a "leak", one of the oldest programming mistakes in the world. Allocate something, neglect to free it. Then, repeat in a loop a bunch of times.

Please ask the developer to consider matching each "open cursor" call with a corresponding "close cursor" call.

Hope this helps...

on 10/14/04 8:10 AM, Bobak, Mark at Mark.Bobak_at_il.proquest.com wrote:

> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Smith, Ron L.
> Sent: Thursday, October 14, 2004 9:48 AM
> To: Ganesh Raja
> Cc: shaharul.anuar_at_intecbilling.com; oracle-l_at_freelists.org
> 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_at_gmail.com]=3D20
> 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
>
>
> 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 ...

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