Top 5 Timed Foreground Events - 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. Pin can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered as a special form of lock. A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.
Library cache pin will be obtained on a database objects that is currently cached in the library cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym); in the library cache, a database object is cached in 2 parts: "handle" and "object"; only when the "object" part is cached is this lock held;
It is not deadlock sensitive and the operation is synchronous.
How can Library cache pin be reduced?
If "library cache pin" waits form a significant amount of the wait time then it is important to determine if this is one or two sessions waiting long periods of time or a more serious general contention issue among lots of processes:
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. Pin can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered as a special form of lock. A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.
Library cache pin will be obtained on a database objects that is currently cached in the library cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym); in the library cache, a database object is cached in 2 parts: "handle" and "object"; only when the "object" part is cached is this lock held;
It is not deadlock sensitive and the operation is synchronous.
How can Library cache pin be reduced?
If "library cache pin" waits form a significant amount of the wait time then it is important to determine if this is one or two sessions waiting long periods of time or a more serious general contention issue among lots of processes:
If there is a blocking scenario where one or two processes are being blocked by one process, the reason for the process not releasing the pin needs to be determined. We could able to trace down the blocking sessions via ASH report when this event happened or one can find with the blocking locks script.
If there is general widespread waiting then the shared pool may need tuning
No comments:
Post a Comment