Friday, November 22, 2013

Calculate buffer cache hit ratio in the database. Make sure it is more than 80

Code:
column "phys_reads" format 999,999,999 
column "phy_writes" format 999,999,999 
select a.value + b.value "logical_reads", 
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / 
(a.value+b.value)) 
"BUFFER HIT RATIO" 
from v$sysstat a, v$sysstat b, v$sysstat c
where 
a.statistic# = 38 
and 
b.statistic# = 39 
and 
c.statistic# = 40;
Code:
 
logical_reads   phys_reads BUFFER HIT RATIO
------------- ------------ ----------------
   1.3177E+12      839,472              100
SQL>

No comments:

Post a Comment