Wednesday, 28 March 2012

Buffer Cache Hit Ratio goes down to Negative values

Today I faced an interesting issue in my production database. Daily we check the Buffer Cache Hit Ratio of the database. Today I got the negative value for the hit ratio.
I am using the below formula for calculating the buffer cache hit ratio.

               1  -   ------------------------------------------------------
                                DB_BLOCK_GETS + CONSISTENT_GETS

I tried with different formulae that I got from the google. But it is of no use. The I searched it in the metalink and I got the answer in it. It was a bug 2645071 stated in the 'Oracle Database Performance Tuning Guide 10g Release 1 (10.1)'  and found the  correct formula to calculate the Buffer Cache Hit Ratio

                                      'physical reads cache'
             1 - -----------------------------------------------------------------------------
                       'consistent gets from cache' + 'db block gets from cache'

physical reads cache’=’physical reads’-‘physical reads direct’-‘physical reads direct(lob)’
‘consistent gets from cache’= ‘consistent gets’
‘db block gets from cache’= ‘db block gets’

physical reads direct:
  This Oracle metric is the number of reads directly from the disk bypassing the buffer cache. For example if there is any request for large data, data-intensive operations such as parallel query, reads of the disk blocks bypass the buffer cache to maximize the transfer rates.
High disk reads indicates the small db buffer cache size.

No comments:

Post a Comment