this is very critical problem in RAC environment and need a lot of experience and scripts to detect and circumvent such problem. Here is only theory part of the problem.
All the wait events related to Library Cache can be put in this category. Actually there are the 3 following wait events:
Library cache lock
Library cache pin
Library load lock
The Library Cache wait events typically occur in the following 4 circumstances:
A. During compilation of PL/SQL, views, packages, functions, procedure, triggers, types, etc.
B. During ALTER, GRANT, REVOKE privileges and roles on packages, functions, etc.
C. During loading object into the memory.
D. During calculating statistics.
Library cache lock
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that:
1.One client can prevent other clients from accessing the same object.
2. The client can maintain a dependency for a long time (for example, no other client can change the object).
During the parsing or compilation of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym) a library cache lock is required for the referenced objects, which is released at the end of parse or compilation. All this operation is synchronous and deadlock sensitive.
This lock is also obtained to locate an object in the library cache.
More information how to troubleshoot with “Library cache locks” you can find here
Library cache pin
This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock.
Both locks and pins are provided to access objects in the library cache. Locks manage concurrency between processes, whereas pins manage cache coherence. In order to access an object, a process must first lock the object handle, and then pin the object heap itself. Requests for both locks and pins will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.
By acquiring a lock on the object handle, a process can prevent other processes from accessing the object, or even finding out what type it is. It can even maintain a dependency on an object without preventing other processes from accessing the object. Acquiring a lock is also the only way to locate an object in cache—a process locates and locks an object in a single operation.
If the process wants to actually examine or modify the object, then it must acquire a pin on the object itself (after acquiring a lock on the handle). Pinning the object causes information about the object and its heaps to be loaded into memory if they were not already there. This information is guaranteed to remain in memory at least until the pin is released.
Locks and pins are externalized in X$KGLLK and X$KGLPN, respectively.
Oracle needs Library Cache Lock and Library Cache Pin for the compilation/Parsing of Package/Procedure/Function/View. This is to ensure that no one is using the object during compilation/parsing as we are making some changes to the definition of the object and need to drop and recreate the object with the new definition.
When a SQL statement is hard parsed by a session , the session has to acquire a library cache lock so that the other sessions can be prevented from accessing or modifying the same object. If this event accumulates a lot of time, then it may indicate that the shared pool is too small or is being flushed regularly. Otherwise it indicates that database object definitions are being changed regularly.
Library load lock
Basically this happens when the session tries to find the load lock for the database object so that it can load the object. The load lock is always obtained in Exclusive mode, so that no other process can load the same object. If the load lock is busy the session will wait on this event until the lock becomes available.
If an object is not in memory, then a library cache lock cannot be acquired on it. So the object has to be loaded into the memory to to acquire the lock. Then the session tries to find the load lock for the database object so that it can load the object. In order to prevent multiple processes to request the load of the same object simultaneously, the other requesting sessions have to wait for the library cache load lock as the lock is busy with loading the object into the memory.
The waits on the library cache load lock is due to the objects not available in the memory. The unavailability of the library cache object in the library cache is due to the undersized shared pool causing reloads often, too many hard parse because of unshared sqls.
Tip: To avoid Library cache wait events to occur you can try two things,
1. First try not to compile source code or grant/revoke/alter privileges while batch programs run at the database
2. Second try not to calculate statistics while batch programs run at the database
Finally what is the conclusion
Library cache pins and load locks may occur during PL/SQL, views, types, etc. compilation or recompilation. The compilation is always explicit (application installation, upgrades, applying patches), but object recompilation may happen transparently due to object invalidations.
Dealing with slow downs related to “mysterious” library cache pins and load locks we should look for the reason of the database object invalidations. They are likely to be triggered by actions causing changes to “LAST_DDL” attribute of database objects that have other dependent ones. Typically they are the object maintenance operations – ALTER, GRANT, REVOKE, replacing views, etc. This behavior is described in Oracle Server Application Developer’s Guide as object dependency maintenance.
After object invalidation, Oracle tries to recompile the object at the time of the first access to it. It may be a problem in case when other sessions have pinned the object to the library cache. It is obvious that it is more likely to occur with more active users and with more complex dependencies (eg. many cross-dependent packages or package bodies). In some cases waiting for object recompilation may even take hours blocking all the sessions trying to access it.
This may be seen in library cache dump, level 10:
Look for ALTER … COMPILE statement and objects/handles with lock=X or pin=X.
In some circumstances these errors may also occur: many sorts of ORA-600, eg. ORA-600, ORA-4061, ORA-4065, ORA-6508. Note that they may be caused by some other reasons as well.
Be very careful with altering, granting or revoking privileges on database objects that frequently used stored PL/SQL is dependent on. In fact, resolving this issue mostly depends on application project and system maintenance practices. Application developers should also consider that some project decisions have negative impact to the application scalability and performance.
-The load lock is obtained in EXCLUSIVE mode
-If the load lock is busy the session will wait on this event until the lock becomes available.