Wait Events in Oracle Database and RAC

Most common wait events in RAC environment:

Wait Events:

1.  Library Cache Wait Events: LC load lock , Lock and pin
2.  Mutex
3.  Row Cache Locks
4.  Buffer Cache Wait Events
5.  Transaction Wait Events

• Locks manage concurrency
• Pins ensures Cache coherency
• Lock is acquired on handle

Pinning: load object info on memory by reading from disk , if object or data block is not loaded.

• Request for locks an pins will wait until granted 

Library Cache Load Lock:
Load an object which is not present in memory.
Exclusive lock , no parallel lock on same object.


Library Cache Lock:
Once object is loaded into the memory / cache, that object acquires lock for further processing and no other session is able to modify it or make any changes over it.
This lock happens during parsing or compilation of body, packages function etc.

Library Cache Pin:

• This event manages library cache concurrency.
• When an object is used it acquires the lock. It object is pinned to use and other will wait till it is released.
• If it is waiting for long in that case we have to look into it.

Causes of Library Cache wait Events:

• High hard parse ( reading from disk)
• Shared pool is too small , which causes reloading. 

Troubleshooting Library cache wait vents:

• Reduce the reloads by increasing shared pool size.
• change the parameter cursor_sharing to force, before doing this - it has to be tested as it may change the execution plan.
• Increase session cached cursors.
• collect statistics in regular period to reduce invalids.
• Use literals for SQL statement to avoid hard parse.