Spotlight on Oracle 10.9 - Getting Started Guide

Library Cache Miss Rate Alarm

The Library Cache Miss Rate 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. Reduce Parse Overhead

Alternatively, perhaps the Shared Pool is too small.