-- ############################################################
-- # 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.
Articles similaires