ibrary.sql

/* Determine if effectively using shared sql area of sga.  */
/* if ratio of cache missed to executions if more than 1%, */
/* increase init.ora shared_pool_size                      */

select sum(pins) "Executions",
sum(reloads) "Cache Misses while Executing"
from v$librarycache;

select round(((sum(reloads))/(sum(pins)))*100,2)||'%' "Ratio of Reloads to Misses"
from v$librarycache;