Causes of library cache latch

Causes of library cache latch
The library cache latches protect the cached SQL statements and objects ' definitions held
In the library cache within the shared pool. The library cache latch must be acquired
In order to add a new statement to the library cache. During a parse, Oracle searches
The library cache for a matching statement. If one is not found and then Oracle would parse
The SQL statement, obtain the library cache latch and insert the new SQL.

The

may cause the library cache latch contention
to be unnecessary versions of a cursor, each time the cursor is executed ,
The parse engine has a to search through the list of versions to see which is the cursor, the want. The
This wastes CPU cycles the could is using on something else.

High version counts can easily cause high contention for library cache latches.
A process parsing a SQL statement with many versions (children cursors) would need to scan
through all these children WH Ile holding on to a library cache latch.
This means, other processes needing the same latch'll has to wait and

Before each SQL is executed, it is necessary to find the parent cursor in the library cache according to Hash_value, which needs to obtain the library cache latch first, and when the parent cursor is found, it will find the corresponding child Cursor, when found unable to find, will release the library cache latch, to obtain share pool latch allocated space to the hard-resolved resulting execution plan;
Then again get the library cache latch to put the execution plan into share pool, into the library cache Pin+lock (null mode) Start execution sql.library cache latch The number of limited (and cpu_ Count parameter related), when a large number of hard parsing in the database, a SQL can not get the library cache latch will start spin, reached spin count has not been obtained,
Will start sleep, reach sleep time, wake up again tried to the library cache latch can not get to spin and sleep ... And so on.
In the process of SQL execution, it can be seen that the library cache latch may appear in the case of high Versions count and hard parse.
About Hard parse See: Shared Pool latch Wait Event
About high Versions count see: About high Versions count summary

Reprint http://www.xifenfei.com/3151.html

Causes of library cache latch