Oracle library cache lock tips Oracle Consulting Tips by Burleson

Oracle notes that the library cache lock controls concurrency between clients in the library cache. The library cache lock 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 (e.g., no other client can change the object)

This lock is also obtained to locate an object in the library cache.

Library Cache Latch

Library cache latches function to protect all cached SQL statements as well as the associated object definitions contained within the library cache region in the shared pool of the Oracle SGA. Whenever new statements are added to the library cache area, this type of latch must be acquired by Oracle as part of the operation. Oracle scans the library cache area during the parse phase for matching SQL statements. If one is not found, then Oracle will complete the task, obtain the library cache latchfor the SQL statement and then insert it into the library cache area for future usage by the Oracle database.

Also see thee important notes on the library cache load lock.

There is a hidden Oracle database initialization parameter called kgl_latch_countwhich controls behavior in terms of the number of library cache latches created within the Oracle database. By default, this value should not be changed as it is sufficiently set in most cases. In the event that there should be a library cache contention issue and no other recourse is available to resolve the contention with the library cache latch, this value can be increased.

By the way, the default value for kgl_latch_countis set to the following prime number plus value of the database initialization parameter for cpu_count. The maximum allowable value for kgl_latch_count is no greater than 66 according to Oracle support per Bug # 1381824. Now move on to learning more about shared pool latches within Oracle.

Library cache lock bugs:  Oracle MOSC documents offer some valuable information on the library cache lock metric as well, specifying a related Oracle bug (2666244),  "Hang - Involving shared resource; Memory Corruption" This problem is introduced in 9.2.0.1., Library cache lock corruption is possible. This can result in processes waiting for a library cache lock without a holder, incorrect lock mode held by processes etc..."
 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.