Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Buffer Cache hit ratio components all screwed up
As many may know, the Buffer cache hit ratio is a key statistic
in monitoring db efficiency. The components of the calculation are
retrieved from v$systat as follows :
select sum(decode(Name, 'consistent gets',value,0)) "Consistent",
sum(decode(Name, 'db block gets',value,0)) "Dbblockgets", sum(decode(Name, 'physical reads',value,0)) "Physrds", round(((sum(decode(Name, 'consistent gets', value, 0))+ sum(decode(Name, 'db block gets', value, 0)) - sum(decode(Name, 'physical reads', value, 0)) )/ sum(decode(Name, 'consistent gets',value,0))+ sum(decode(Name, 'db block gets', value, 0)))) *100,2) "Hitratio" from V$SYSSTAT
CONSISTENT DBBLOCKGETS PHYSRDS HITRATIO ---------- ----------- ---------- ---------- 1.8447E+19 68339236 34295505 100
The trouble is that after the database has been up some length of time, one or more of the component values becomes negative, or as in the case above (1.8447E+19), an extremely large number. Of course, this renders the buffer cache ratio usless.
Bouncing the database resets these components, of course, but we are going to 24X7 operation in the near future and shutting down the database is not going to be an option.
My question is threefold :
![]() |
![]() |