Spotlight on Oracle – Library cache latches (SOO32)

Library cache latches

The library cache latches protect the cached SQL statements and object definitions held in the library cache within the shared pool.

The library cache latch must be obtained in order to add a new statement to the library cache. During a parse request, Oracle searches the library cache for a matching statement. If one is not found, Oracle parses the SQL statement, acquires the library cache latch, and inserts the new SQL. Contention for the library cache latch can occur when an application generates very high quantities of unique, non-sharable SQL (usually because literals have been used instead of bind variables).

If the library cache latch is a bottleneck, try to improve the use of bind variables within your application. Misses on this latch can also be a sign that your application is parsing SQL at a high rate and may be suffering from excessive parse CPU overhead as well.

The library cache pin latch must be obtained when a statement in the library cache is re-executed. Misses on this latch occur when there are very high rates of SQL execution. There is little you can do to reduce the load on this latch, although using private rather than public synonyms (or even direct object references such as OWNER.TABLE) may help.

The _KGL_LATCH_COUNT parameter controls the number of library cache latches. The default value should be adequate, but you may want to increase it if contention for the library cache latch cannot be resolved.