Oracle 10g Performance Tuning Scripts (Sure Shot) ----------------------------------------------------------- -- SHARED POOL TUNING -- Library Cache Hit Ratio should be less than 90%. if not, increase the size of SHARED POOL select gethitratio * 100 "Library Cache Hit Ratio" from v$librarycache where namespace = 'SQL AREA'; -- "RELOADS TO PINS RATIO" ratio should be less than 1 percent. Other wise SHARED_POOL_SIZE need to be increased. SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES", (SUM(RELOADS) / SUM(PINS)) * 100 "RELOADS TO PINS RATIO" FROM V$LIBRARYCACHE; -- "CACHE HIT RATIO" should be greaer than 90 percent SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100 "CACHE HIT RATIO" FROM V$LIBRARYCACHE; -- check invalidations SELECT NAMESPACE,PINS,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE; --take advice for shared pool size SELECT SHARED_PO