Oracle How To ...
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"
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
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.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.