Re: Percent of Shared Pool used

The only reason I would flush the pool is if your application doesn't use bind variables AND the pool has become severely fragmented. One way to know whether it's fragmented is to monitor waits on latches. If you see lots of waits for the shared_pool latch or if you see sessions holding the shared_pool latch for extended periods of time ( like 1 second ), flushing might help. Otherwise, it's just not a good thing to do as it also flushes the SQL used in recursive calls and lots of other stuff that needs to be in there for performance.

Have a look at v$db_object_cache to see what's in there now and the size of the objects. There is also a SYS view called x$ksmlru. Only the user SYS can select from this object.

Here's a link that describes more:
http://www.zoftware.org/tuning/tune_shared_pool.html

Good Luck,
Jack
"Javier Villegas" <mask_at_impsat1.com.ar> wrote in message news:hj75a.359$Km2.207_at_fe04.atl2.webusenet.com...
> Hi
>
> I want to make a query for find the percent of Shared Pool used, and if
the
> percent is grater than 70 % run the alter system flush shared_pool;
>
> Do you know how can I do that ?
>
> Thanks in advance
>
> Javier Villegas
>
>
>
Received on Fri Feb 21 2003 - 11:25:41 CST