Investigate the Oracle Library CacheHit Ratio

Investigate the Oracle Library CacheHit Ratio

By RichardNiemiec

Alow library cache hit ratio is a symptom of one of several problems. The sharedand/or Java pools may be too small; the SHARED_POOL_RESERVED_SIZE may be too small;CURSOR_SHARING may need to be set to FORCE; there may be inefficient sharing ofSQL, PL/SQL, or Java code; or there may be insufficient use of bind variables.Investigate which application code is being used over time and how efficientlyit is used (code sharing). Monitor the shared and Java pool freespace overtime. If the amount of free memory in the shared and Java pools is relativelyhigh, no ORA-04031 errors are occurring, and the library cache hit ratio islow, then poor code sharing is probably occurring.

Thequeries in the following listings help you investigate this area. I includesome V$ view queries here because of the applicable nature to this subject.

Librarycache hit ratio

Librarycache reload ratio

 

Librarycache high-use objects (make this a top 10 list by adding “WHERE ROWNUM<11”)

Librarycache object sizes

Sharedpool object sharing efficiency (you may want to limit these)

 

 

Ahigh percentage of reloads indicates that the shared and/or Java pools are toosmall, code sharing is insufficient, and possibly also large code objects arerepeatedly being used. Monitor the application code used over time. Ifparticular large code objects are identified as frequently used, considerpinning them and/or increasing the size of the SHARED_POOL_RESERVED_SIZE. Iffeatures such as Shared Servers, Recovery Manager, or Parallel Query are used,consider a larger SHARED_POOL_SIZE and/or larger LARGE_POOL_SIZE (if settingparameters like MEMORY_TARGET and/or SGA_TARGET, ensure you always set aminimum for the SHARED_POOL_SIZE even if you use these other parameters).