November 9, 2012 at 3:23 pm | Posted in Oracle Developement | Leave a comment
I came accross 100% library cache pin waits, while trying to compile a procedure which was currently executing.
After a little research, I came across this fantastic article by Riyaj Shamsudeen.
Originally posted on Oracle database internals by Riyaj:
I encountered few customer issues centered around library cache lock and library cache pin waits. Library cache lock and pin waits can hang instance, and in few cases, whole clusters of RAC instances can be hung due to library cache lock and pin waits.
Why Library cache locks are needed?
Library cache locks aka parse locks are needed to maintain dependency mechanism between objects and their dependent objects like SQL etc. For example, if an object definition need to be modified or if parse locks are to be broken, then dependent objects objects must be invalidated. This dependency is maintained using library cache locks. For example, if a column is dropped from a table then all SQLs dependent upon the table must be invalidated and reparsed during next access to that object. Library cache locks are designed to implement this tracking mechanism.
In a regular enqueue locking scenarios there is…
View original 1,754 more words