Improving Buffer Cache Hit Ratio

Many DBAs do their best to get a 99% or better buffer cache hit ratio, but quickly discover that the performance of their database isn't improving as the hit ratio gets better.

Here is a query to get your database's current hit ratio:

SQL> -- Get initial Buffer Hit Ratio reading...
SQL> SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
  2    FROM v$sysstat cur, v$sysstat con, v$sysstat phy
  3   WHERE cur.name = 'db block gets'
  4     AND con.name = 'consistent gets'
  5     AND phy.name = 'physical reads'
  6  /

Cache Hit Ratio
---------------
         90.75

However, to show how meaningless this number is, let's artificially increase it:

SQL>
SQL> -- Let's artificially increase the buffer hit ratio...
SQL> DECLARE
  2    v_dummy dual.dummy%TYPE;
  3  BEGIN
  4    FOR I IN 1..10000000 LOOP
  5      SELECT dummy INTO v_dummy FROM dual;
  6    END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Let's see what happened:

SQL>
SQL> -- Let's measure it again...
SQL> SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
  2    FROM v$sysstat cur, v$sysstat con, v$sysstat phy
  3   WHERE cur.name = 'db block gets'
  4     AND con.name = 'consistent gets'
  5     AND phy.name = 'physical reads'
  6  /

Cache Hit Ratio
---------------
          99.94

Conclusion: Don't even bother trying to tune the Buffer Hit Ratio!

There are better ways to tune now. The Oracle Wait Interface (OWI) provides exact details. No need to rely on fuzzy meaningless counters anymore.