Oracle Library Cache Hit Ratio Oracle Consulting Tips by Burleson

The Library Cache Hit Ratio Oracle metric monitors the percentage of entries in the library cache that were parsed more than once (reloads) over the lifetime of the instance. 

Since you never know in-advance how many SQL statements need to be cached, the Oracle DBA must set shared_pool_size large enough to prevent excessive re-parsing of SQL.  Also see my library cache misses.

Here is a script for measuring the library cache hit ratio:

SELECT 'Buffer Cache' NAME,
ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE)
* 100
/ (congets.VALUE + dbgets.VALUE),
2
) VALUE
FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads
WHERE congets.NAME = 'consistent gets'
AND dbgets.NAME = 'db block gets'
AND physreads.NAME = 'physical reads'
UNION ALL
SELECT 'Execute/NoParse',
DECODE (SIGN (ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
),
-1, 0,
ROUND ( (ec.VALUE - pc.VALUE)
* 100
/ DECODE (ec.VALUE, 0, 1, ec.VALUE),
2
)
)
FROM v$sysstat ec, v$sysstat pc
WHERE ec.NAME = 'execute count'
AND pc.NAME IN ('parse count', 'parse count (total)')
UNION ALL
SELECT 'Memory Sort',
ROUND ( ms.VALUE
/ DECODE ((ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE))
* 100,
2
)
FROM v$sysstat ds, v$sysstat ms
WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)'
UNION ALL
SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2)
FROM v$librarycache
WHERE namespace = 'SQL AREA'
UNION ALL
SELECT 'Avg Latch Hit (No Miss)',
ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
FROM v$latch
UNION ALL
SELECT 'Avg Latch Hit (No Sleep)',
ROUND ((SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2)
FROM v$latch;

My notes on library cache hit ratio include:

This, and many other Oracle performance metrics are discussed in my book "Oracle Tuning" by Rampant TechPress.  You can buy it directly from the publisher and save 30% at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm