Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Buffer Cache hit ratio components all screwed up

Buffer Cache hit ratio components all screwed up

From: me zawadzki <markz-nospam-_at_starnet.lenfest.com>
Date: Wed, 30 Jun 1999 08:16:42 -0400
Message-ID: <377A0AAA.E2D9D090@starnet.lenfest.com>


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 :

  1. What causes this ?
  2. Can the root cause be fixed ?
  3. If the root cause cannot be fixed, can I reset the component values to zero ? I am running Oracle 7.3.4.3 on a Sun Sparc Solaris 2.5
Received on Wed Jun 30 1999 - 07:16:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US