Keeping the Library Cache Reload Ratio at 0 and the Hit Ratio Above 95 Percent

For optimal performance, you’ll want to keep the library cache reload ratio [sum(reloads) /sum(pins)] at zero and the library cache hit ratio above 95 percent. If the reload ratio is not zero, then there are statements that are being “aged out” that are later needed and brought back into memory. If the reload ratio is zero (0), that means items in the library cache were never aged or invalidated. If the reload ratio is above 1 percent, the SHARED_POOL_SIZE parameter should probably be increased. Likewise, if the library cache hit ratio comes in below 95 percent, then the SHARED_POOL_SIZE parameter may need to be increased.

The following query uses the V$LIBRARYCACHE view to examine the reload ratio in the library cache:

select
 Sum(Pins) "Hits", Sum(Reloads) "Misses",((Sum(Reloads) / Sum(Pins)) * 100)"Reload %"
from V$LibraryCache;


The next query uses the V$LIBRARYCACHE view to examine the library cache’s hit ratio in detail:

select
 Sum(Pins) "Hits", Sum(Reloads) "Misses", Sum(Pins) / (Sum(Pins) + Sum(Reloads)) "Hit Ratio"
from V$LibraryCache;

Using a modified query on the same table, we can see how each individual parameter makes up the library cache. This may help diagnose a problem or show overuse of the shared pool.

set numwidth 3
set space 2
set newpage 0
set pagesize 58
set linesize 80
set tab off
set echo off
ttitle 'Shared Pool Library Cache Usage'
column namespace format a20 heading 'Entity'
column pins format 999,999,999 heading 'Executions'
column pinhits format 999,999,999 heading 'Hits'
column pinhitratio format 9.99 heading 'Hit|Ratio'
column reloads format 999,999 heading 'Reloads'
column reloadratio format .9999 heading 'Reload|Ratio'


select namespace, pins, pinhits, pinhitratio, reloads, reloads/decode(pins,0,1,pins) reloadratio
from v$librarycache;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-598233/，如需轉載，請註明出處，否則將追究法律責任。