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.
PHYSICAL_READS
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' |
.
Where,
‘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.
