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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-84743/，如需转载，请注明出处，否则将追究法律责任。