-- Queries to analyze Library Cache activity (SQL and PL/SQL performance)

SELECT namespace,
  SUM(pins) execution_cnt, SUM(pinhits) pin_hit_cnt,
  SUM(reloads) "Execution Cache Misses",
  SUM(invalidations)  invalidation_cnt    --invalidation/SQL reparsing
FROM v$librarycache
GROUP BY namespace
;


SELECT 
 SUM(pins - reloads)/SUM(pins) Library_cache,
 SUM(pinhits)/SUM(pins) hit_ratio
FROM v$librarycache
;


SELECT lc_namespace, 
  lc_inuse_memory_objects, lc_inuse_memory_size,
  lc_freeable_memory_objects, lc_freeable_memory_size
FROM v$library_cache_memory
;