RE: Which caused the "library cache" latch?

The third school teaches: Fix the application so that it doesn't issue unnecessary parse calls. :-)    

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com <http://www.hotsos.com/> Nullius in verba

Visit www.hotsos.com <http://www.hotsos.com/> for curriculum and schedule details...  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Day
Sent: Saturday, September 24, 2005 8:28 AM To: Oracle-L
Subject: Re: Which caused the "library cache" latch?    

On 9/22/05, Gogala, Mladen <MGogala_at_allegientsystems.com> wrote:  

Lou, library cache latch is caused by solar flares which, in turn, cause significant magnetic activity.

Mladen isn't quite right about the library cache latch, though he's close. You need to do more research to find out what is causing the library cache latch waits. Some possibilities are that your shared pool isn't big enough, you are not pinning frequently called objects in your shared pool and so fragmenting it, you are not using bind variables and so are filling your shared pool with multiple copies or the same query that differ only in the literals used, or some other reason (most likely cause is "some other reason"). As in all things Oracle the correct answer starts with "it depends".

Spin waits -
There are two schools of thought on spin waits. The first school says to decrease the time of the spin wait so that your object can try to acquire a latch sooner (and execute sooner). The pro is obvious on this. The con is that if there are a lot of objects "spinning" then your CPU will be consumed by objects trying to acquire a latch (and failing). So much CPU may be consumed that objects which have acquired a latch will not get their CPU cycles and so will take longer to complete. This is CPU "thrashing" and can produce a cascading effect to the point that all of your CPU cycles are spent trying to acquire latches and processing slows to a crawl.

The second school says to increase the spin wait time so that objects which already have a latch can process. When the object that is trying to acquire a latch comes out of its "spin" there is a greater likelihood of a latch being available. The down side of this is that if there are only a few objects waiting for latches there is some chance that they will still be spinning while all the prior objects have finished execution and the CPU will be idle. However, that will merely slow the execution of some of your queries; it will not bring the whole database to a grinding halt.

I, for one, am of the second school. Better to occasionally waste a few CPU cycles than to crash a production database.    

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