Top 5 Timed Foreground Events - Library Cache Lock
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:
Library cache lock will be obtained on database objects referenced during parsing or compiling of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym). The lock will be released at the end of the parse or compilation.
Cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.
Library cache lock is not deadlock sensitive and the operation is synchronous.
How can Library cache lock be reduced?
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:
- One client can prevent other clients from accessing the same object.
- The client can maintain a dependency for a long time (for example, so that no other client can change the object).
Library cache lock will be obtained on database objects referenced during parsing or compiling of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym). The lock will be released at the end of the parse or compilation.
Cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.
Library cache lock is not deadlock sensitive and the operation is synchronous.
How can Library cache lock be reduced?
In general , high contention on library cache lock is usually a result of an under-sized shared pool or non-sharing of sql. Some ways of reducing the contention are:
- Reduce the reloads by increasing the shared pool size as the locks may take a long time if the pool is undersized.
- Increase sharing by setting the cursor_sharing to similar or force.
Be aware this may change the execution plan; so setting the parameter should be thoroughly tested. - Reduce the invalidations by running the batch jobs to collect statistics or any other maintenance jobs separately from OLTP.
Why are these two type of locks needed( library cache lock & library cache pin)?
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 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.
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 while changes are being made to the definition of the object and the object dropped 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 to prevent other sessions 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.
Apart from hard parsing, if the session wants to change the definition of the object specified in the SQL or do any modifications, then it has to acquire a library cache lock along with the library cache pin. The pin is required because it needs the dictionary information to be loaded into memory to access the same code.
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.
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 while changes are being made to the definition of the object and the object dropped 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 to prevent other sessions 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.
Apart from hard parsing, if the session wants to change the definition of the object specified in the SQL or do any modifications, then it has to acquire a library cache lock along with the library cache pin. The pin is required because it needs the dictionary information to be loaded into memory to access the same code.
No comments:
Post a Comment