Library cache hit ratio

 
-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/03/10
-- # Name: Library cache hit ratio
-- ############################################################
-- #
-- # Compatible: Oracle 7i 8i 9i 10g 11g
-- #
-- ############################################################
-- #
-- # How to calculate Library cache hit ratio
-- # Other ratios see: "Quick tune"
-- # 
-- ############################################################

Calculate the cache hit ratio for the library cache with the following query:

    Select sum(pinhits) / sum(pins) "Hit Ratio",
        sum(reloads) / sum(pins) "Reload percent"
    From v$librarycache
    Where namespace in
    ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

-- The hit ratio should be above 85 percent. 
-- The reload percent should be very low, 2% or less. 
-- If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. 
-- OPEN_CURSORS may also need to increased.