Library cache latch contention

Library cache latch contention

The shared pool is a data structure. people (processes, things, like dbwr, lgwr, etc) allocate memory from it to create data structures they need and use.they then allocate memory from it,
use a latch -- the shared pool latch. In order to allocate memory from this shared data structure called the shared pool we need to latch it, serialize access to it -- to prevent two things from trying to modify this overall structure at the same time.

Now, later, after the memory is allocated, other things need to do stuff to it. some of the memory that is allocated is used for a library cache. In order to parse -- we need to access the library cache, a memory structure that happens to reside in the shared pool. We use a library latch for that.

so, we use the library latch later when inspecting the library cache. not the shared pool. when you have parsing issues (too much parsing, no binds, whatever) you see library latch contention.

Library cache latch contention is typically caused by NOT using bind variables. It is due to excessive parsing of statements.

If you discover your applications do not use bind variables -- you must have this corrected. You'll never have a good hit ratio if everyone submits "unique" sql. Your shared pool will never be used right and you'll be using excessive CPU (90% of the time it takes to process "insert into t values ( 1 )" is parsing. If you use "insert into t values ( :x )", and bind the value of 1 -- then the next person that runs that insert will benefit from your work and run that much faster.

OLTP = mandatory binds ( transactions)

Mixed Used = mostly 99% binds

DW = binds if you want to (i cannot believe i said that!)

 

 Wait Events

Library cache lock
=>This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).

library cache: mutex X
=> related to concurrency. Processes compete for shared resources in the library cache, and to control who has access at any given point in time, we use mutexes, which are like lightweight latches, ie, structures to ensure that two people (processes) are not changing the same memory structure at the same time.