Remote DBA Plans
Remote DBA Service

Diving Into the Shared Pool - An In Depth Look at Tuning the Shared Pool (Part 2)

Oracle Tips by Mike Ault

Monitoring Library and Data Dictionary Caches

      namespace,
      gets,
      gethitratio*100 gethitratio,
      pins,
      pinhitratio*100 pinhitratio,
      RELOADS,
      INVALIDATIONS
from
      v$librarycache
/
spool off
pause Press enter to continue
set pages 22 lines 80
ttitle off
undef output

Figure 9: Example Script To Monitor The Library Caches

Look at the example output from the script in Figure 9 in Figure 10. In Figure 10 we see that all Get Hit% (gethitratio in the view) except for indexes are greater than 80-90 percent. This is the desired state, the value for indexes is low because of the few accesses of that type of object. Notice that the Pin Hit% is also greater than 90% (except for indexes) this is also to be desired. The other goals of tuning this area are to reduce reloads to as small a value as possible (this is done by proper sizing and pinning) and to reduce invalidations. Invalidations happen when for one reason or another an object becomes unusable. However, if you must use flushing of the shared pool reloads and invalidations may occur as objects are swapped in and out of the shared pool. Proper pinning can reduce the number of objects reloaded and invalidated.

Guideline 7: In a system where there is no flushing increase the shared pool size in 20% increments to reduce reloads and invalidations and increase hit ratios.