Useful DBA Monitoring Scripts

Buffer Cache Hit Ratio

select round((1-(pr.value/(bg.value+cg.value)))*100,2)
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name='physical reads'
and bg.name='db block gets'
and cg.name='consistent gets'

Dictionary Cache Hit Ratio
select sum(gets-getmisses)*100/sum(gets)
from v$rowcache

Sorts in Memory
select round((mem.value/(mem.value+dsk.value))*100,2)
from v$sysstat mem, v$sysstat dsk
where mem.name='sorts (memory)'
and dsk.name='sorts (disk)'

Shared Pool Free
select round((sum(decode(name,'free memory',bytes,0))/sum(bytes))*100,2)
from v$sgastat


Shared Pool Reloads
select round(sum(reloads)/sum(pins)*100,2)
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')

Library Cache Get Hit Ratio
The proportion of requests for a lock on an object which were satisfied by finding that object's handle already in memory.
select round(sum(gethits)/sum(gets)*100,2)
from v$librarycache