Oracle Library Cache Lock solution

I.Library Cache Lock

 

 

Library cacheHandle stores the lock and pin information. In addition, both the Library cache handle and child cursor have lock and pin. These are called library cache lock and library cache pin.

Library cachelock/pin is used to control concurrent access to librarycache objects. Lock Management concurrency, pin management Consistency,LockForLibrarycache handle,WhilePinForHeap.

To access a library cache object, first obtain the handle lock pointing to this object. After obtaining the lock, we need to pin the heap pointing to this object.

When we compile packages, stored procedures, functions, and viewsOracle will first obtain a library cache lock on the handle of these objects, and then obtain the pin on the heap of these objects, in this way, the definition of these objects will not be changed by other processes during compilation, or the objects will be deleted.

WhenSessionPairSQLWhen the statement is hard parsed,ThisSessionYou must obtainLibrarycache lockIn this way, otherSessionCannot access or change thisSQLReferenced object. If this wait event takes a long time, it usually indicates that the shared pool is too small (because the Shared Pool is too small, you need to search for the free chunk, or you can remove some object pages out, this takes a long time). Of course, another session may be modifying the object (such as the split partition), and the current session needs to reference the table, in this case, we must wait for another session to complete.

Library Cache lockYes3Moderate mode:

(1) Share (S): obtained when reading a library cache object

(2) Exclusive (X): obtained when a library cache object is created/modified.

(3) Null (N ):Used to ensure object dependency

For example, if a process wants to compile a view, a shared lock will be obtained. If we want to create/drop/alter an object, the exclusive lock will be obtained. The Null lock is very special. We have a NULL lock on any executable object (cursor, function). We can break this NULL lock at any time. When this NULL lock is broken, it indicates that this object has been changed and needs to be re-compiled.

NULLThe main purpose of the lock is to mark whether an object is valid.. For example, an SQL statement obtains the NULL lock during parsing. If the SQL object is always in the shared pool, the NULL lock will always exist, when the table referenced by this SQL statement is modified, the NULL lock is broken because the Exclusive lock is obtained when the SQL statement is modified. Because the NULL lock is broken, the next time you execute this SQL statement, you need to re-compile it.

Library Cache pinYes2Mode:

(1) Share (S): Read object heap

(2) Exclusive (X): Modify object heap

When a session wants to read the object heap, it needs to get a pin in the sharing mode. When a session wants to modify the object heap, it needs to get the exclusive pin. Of course, you must get the lock before getting the pin.

In Oracle10gR2, the library cache pin is replaced by library cache mutex.

Library cache latch is used to control concurrent access to library cache objects. As mentioned above, you must obtain the librarycache lock before accessing the library cacheobject. lock is not an atomic operation (an atomic operation is an operation that will not be broken in the operation process, obviously, here the lock can be broken), Oracle to protect this lock,IntroducedLibrary cache latchMechanism.Library cachelockYou need to obtainLibrary cache latchWhen obtainingLibrary cache lockThen releaseLibrarycache latch.

IfLibrarycache objectNot in memory.LockIt cannot be obtained.Library cache load lock latchAnd then obtainLibrarycache load lock,WhenLoad lockRelease after obtainingLibrary cache load lock latch.

Librarycache latch is controlled by the implicit parameter _ KGL_LATCH_COUNT. The default value is the minimum prime number equal to or greater than the number of CPUs in the system. However, Oracle imposes a hard limit on it. This parameter cannot be greater than 67.

Note: Query _ kgl_latch_count is sometimes 0, which is a bug.

Oracle uses the following algorithm to determine which sub-latch is used to protect the library cache object handle:

Latch # = mod (bucket #, # latches)

That is to say, which sub-latch is used to protect a handle Based on the bucket number where the handle is located, and the total number of sub-latch for hash calculation.

MOSDocumentation 【122793.1]CauseLibrarycache lockUsually there are2Cause:

(1) a dml operation that is hangingbecause the table which is accessed is currently undergoing changes (ALTERTABLE ). this may take quite a long time depending on the size of the table andthe type of the modification (e.g. alter table x MODIFY (col1 CHAR (200) on atable with thousands of records)

In this case, V $ LOCK will show that the session doing the 'alter table' with an exclusive DMLenqueue lock on the TABLE object (LMODE = 6, TYPE = TM where ID1 is the OBJECT_IDof the table ). the waiting session however does not show up in V $ LOCK yet so inan environment with a lot of concurrent sessions the V $ LOCK information will beinsufficient to track down the culprit blocking your operation.

(2) The compilation of package willhang on Library Cache Lock and Library Cache Pin if any users are executing aprocedure/function defined in the same package.

1 2 Next Page