Library Cache Hit Ratio

You can calculate Library Cache hit ratio by executing the following query:

Select sum(pinhits) / sum(pins) "Hit
Ratio", sum(reloads) / sum(pins) "Reload percent" From
v$librarycacheWhere namespace in('SQL AREA', 'TABLE/PROCEDURE', 'BODY',
'TRIGGER');

Cache hit ratio should be at least 85% (i.e. 0.85) and reload percent should be very low, 2% (i.e. 0.02) or less. If this output 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.