The Library Cache Miss Ratio alarm occurs when the library cache hit ratio falls

below a given threshold.

The library cache hit ratio describes the frequency with which a matching SQL

statement is found in the Shared pool when a SQL parse request is issued by a

session. If a matching SQL statement is not found in the library cache, the SQL

statement must be parsed and loaded into the library cache. Low hit rates therefore

result in high CPU consumption (from parsing), and possible contention for library

cache latches (when the new SQL is loaded into the library cache). An acceptable

rate for the library cache get hit rate is 90-95% or higher.

The most frequent cause of high miss rates in the library cache is the use of

literals rather than bind variables in SQL statements. Bind variables reduce parse

overhead by allowing otherwise identical SQL statements with different query

parameters to be matched in the shared pool. Bind variables, however, preclude the

use of column histograms and are therefore not suitable in all circumstances.