Tuesday, 15 July 2008

sga components used,free

select round(sum(bytes)/1024/1024,2)' MB' total_sga,
round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))' MB' used,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)' MB' free
from v$sgastat


free memory in shared pool:

select * from v$sgastatwhere name='free memory'and pool='shared pool'

Library cache hit ratio:

select sum(pinhits)/sum(pins) from v$librarycache

Dictionary cache hit ratio:

select (sum(gets-getmisses-fixed))/sum(gets)"row cache" from v$rowcache

No comments: