-- 0.6. QUICK INFO ON HIT RATIO, SHARED POOL etc.. -- ---------------------------------------------------------------- Hit ratio: SELECT (1-(pr.value/(dbg.value+cg.value)))*100 FROM v$sysstat pr, v$sysstat dbg, v$sysstat cg WHERE pr.name = 'physical reads' AND dbg.name = 'db block gets' AND cg.name = 'consistent gets'; SELECT * FROM V$SGA; -- free memory shared pool: SELECT * FROM v$sgastat WHERE name = 'free memory'; -- hit ratio shared pool: SELECT gethits,gets,gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA'; SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; SELECT sum(sharable_mem) FROM v$db_object_cache; -- finding literals in SP: SELECT substr(sql_text,1,50) "SQL", count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,50) HAVING count(*) > 30 ORDER BY 2;