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.
No comments:
Post a Comment