Re: Which caused the "library cache" latch?
- From: Thomas Day <tomday2@xxxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 24 Sep 2005 09:28:25 -0400
On 9/22/05, Gogala, Mladen <MGogala@xxxxxxxxxxxxxxxxxxxx> 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.
Other related posts:
