REM
REM Author: Mohit Dubey
REM Visit http://www.geocities.com/md_seraphin for more goodies!
REM
REM This program is distributed under the GNU Public License Version 2
REM with the additional privisio that the original author's name and
REM contact details must be retained as-is in any modified or copied
REM versions of this program.
REM


spool dbstat2.log
PROMPT *******************************************************************************
PROMPT * Tuning: Database Buffer Cache
select (1-(pr.value/(dbg.value+cg.value)))*100 "Cache Hit Ratio"
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';
PROMPT
PROMPT *******************************************************************************
PROMPT * Tuning: Shared Pool
PROMPT * Library Cache hit ratio:
select gethits,gets,gethitratio*100 gethitratio from v$librarycache
where namespace = 'SQL AREA';
PROMPT
PROMPT * Reloads per pin ratio:
select reloads,pins,(reloads/pins)*100 "rel/pin ratio" from v$librarycache
where namespace = 'SQL AREA';
PROMPT
PROMPT * Getmisses per get ratio:
select sum(getmisses),sum(gets),(sum(getmisses)/sum(gets))*100 "getmiss/get ratio"
from v$rowcache;
PROMPT
PROMPT *******************************************************************************
spool off