Tuning the library cache

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 at least 85% (i.e. 0.85). The reload percent should be very low, 2% (i.e. 0.02) or less. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. Although less likely, the init.ora parameter OPEN_CURSORS may also need to increased.

These notes are believed to be correct for Oracle 7.3.4, Oracle 8.0.5, Oracle 8.1.5 and Oracle 9.0.1 and may apply to other versions as well.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.