Oracle Database Discussions

I have been noticing an anomaly in the buffer cache hit ratio on a 10.2.0.2 system: during two or three half-hour periods per week, the BCHR goes negative.

(Let us please leave aside the discussion of whether BCHR is useful, relevant, or helpful. There are many good threads out there on that topic already. The fact is, the client I am working for is interested in this metric, and I am contractually bound to provide the data.)

So, moving along:

According to Metalink Note 308040.1, you can get a negative BCHR if you use the wrong formula. The correct formula for 10g is:

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

But even with the correct formula, I am still getting a negative BCHR. Here are the values I obtained by calculating the deltas between snapshots in the AWR repository (dba_hist_sysstat):

physical reads cache  4153259
consistent gets       3325451
db block gets          222202

Popping these into the formula, they give a result of -0.170706098. If I run an AWR report for that period, the identical negative value is reported, thus proving that my formula is the same as the one Oracle is using.

Here's my difficulty: It seems to make no sense to have a negative BCHR. It implies that physical reads exceed buffer gets. In my case, this means that for each block read from the buffer cache, Oracle is going out and fetching 1.17 blocks from the datafiles. Is that crazy, or what?

I've submitted an SR and am having trouble getting Oracle Support to understand the issue. So I ask this forum, Does this situation strike anyone as odd?

Here is another version of the same formula provided by Note 308040.1, which leads to the same negative result:

    'physical reads'-'physical reads direct'-'physical reads direct (lob)'
1 - ---------------------------------------------------------------------------------------
                        'consistent gets' + 'db block gets'

physical reads              4156424
physical reads direct          3165
physical reads direct (lob)       0
consistent gets             3325451
db block gets                222202