RE: Measuring shared pool fragmentation

I wouldn't worry about fragmentation, you want to monitor for shared pool misses and request failures, I would set

EVENT="4030 trace name errorstack level 12" EVENT="4031 trace name errorstack level 12"

in my init file to ensure my alert log monitor always catches these situations. If you start having problems make sure the other pool related parameters (i.e shared_pool_reserved_size and I think large_pool) are set correctly before you mess with the size of the shared pool.

other than that I find monitoring the average age of objects in the shared pool is a good thing, if the average age of statements in the pool goes from 4 hours to 1 hour, you likely just had a process run that should be using bind variables, it gives you a way to set up some alerting so you can determine when to go in a look for stuff like this, of course you could just monitor the hard parse rate.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Schauss, Peter Sent: Wednesday, September 22, 2004 3:14 PM To: Oracle-L (E-mail)
Subject: Measuring shared pool fragmentation

Is there a way do measure the amount of shared pool fragmentation? I would be looking for metrics such as the largest available chunk of memory, number of fragments, average size of memory fragments ...

thanks,
Peter Schauss
--

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

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 22 2004 - 15:26:04 CDT