This discussion is archived
8 Replies Latest reply: Feb 21, 2007 7:43 AM by 181444 RSS

Buffer hit ratio

user135518 Newbie
Currently Being Moderated
I am using the following:

SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads', value, 0)) /
(SUM(DECODE(NAME, 'db block gets', value, 0))+
(SUM(DECODE(NAME, 'CONSISTENT GETS', value, 0))))))*100), 2) || '%' BCHR
FROM V$SYSSTAT

to calculate the buffer hit ratio. This query is returning: -1753.28%

Can someone explain why I am getting this crazy number?

Thanks,
mdp
  • 1. Re: Buffer hit ratio
    552036 Newbie
    Currently Being Moderated
    The query is OK. In my environment, after running this query, I got the result

    BCHR
    ------
    83,69%

    You can first check the values => physical reads, db block gets and consistent gets.
  • 2. Re: Buffer hit ratio
    181444 Expert
    Currently Being Moderated
    The last I checked metalink note 33883.1 detailed the proper calculation.

    The OP fails to list the Oracle version. From time to time there have been problems with the manner in which Oracle updated various statistics. Any such problem would be version specific.

    Everyone should know that the buffer cache hit ration in an of itself is a meaningless number which bares no direct relation to how well a database is performing. If should usually be used to compare against a history of the value on the same database (trend). Other statistics also need to be checked to be sure a high ratio is not the result of poorly performing SQL that scans the same buffers repeatedly via an index and that lower values are not due to well performing SQL plans that use hash joins. In other words there is no magic value that is good or bad. The ratio is relative to other statistics.

    HTH -- Mark D Powell --
  • 3. Re: Buffer hit ratio
    ScottZheng Newbie
    Currently Being Moderated
    I am using the following:

    SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads',
    value, 0)) /
    (SUM(DECODE(NAME, 'db block gets', value,
    0))+
    (SUM(DECODE(NAME, 'CONSISTENT GETS', value,
    0))))))*100), 2) || '%' BCHR
    FROM V$SYSSTAT
    lculate the buffer hit ratio. This query is
    returning: -1753.28%

    Can someone explain why I am getting this crazy
    number?

    Thanks,
    mdp
    Hi,
    Why did you use upper case for 'consistent gets'????
    SQL> SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads', value, 0)) /
    (SUM(DECODE(NAME, 'db block gets', value, 0))+
    (SUM(DECODE(NAME, 'CONSISTENT GETS', value, 0))))))*100), 2) || '%' BCHR
    FROM V$SYSSTAT
      2    3    4    5
    db9i :SQL> /

    BCHR
    -----------------------------------------
    -79.62%

    SQL> SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads', value, 0)) /
    (SUM(DECODE(NAME, 'db block gets', value, 0))+
    (SUM(DECODE(NAME, 'consistent gets', value, 0))))))*100), 2) || '%' BCHR
    FROM V$SYSSTAT  2    3    4
      5  /

    BCHR
    -----------------------------------------
    97.53%
  • 4. Re: Buffer hit ratio
    108476 Journeyer
    Currently Being Moderated
    Hi,

    The BHR has a "bad rap" because it is only the measure of the propensity for a data block to be in the cache upon re-read. Also, it is only useful for identifying "undersized" data buffers, in cases where the db_cache_size is too small to cache the "working set" of frequently-requested data blocks. Also note that the parameter optimizer_index_caching is set by the DBA to tell the CBO how much of the indexes are cached.

    Oracle has the v$buffer_pool_statistics and the v$db_cache_advise to assist with this, and in 10g you can set alerts based on BBW. For the equation used by Oracle look at $ORACLE_HOME/rdbms/admin/spreport.sql.

    But beware that the BHR is meaningless for databases that have a tiny working set, like warehouses, where data is re-read infrequently. I have my notes here:

    http://www.dba-oracle.com/m_buffer_cache_hit_ratio.htm

    http://www.dba-oracle.com/art_so_orace_tuning_myths.htm

    Hope this helps. . . .

    Don Burleson
    Oracle Press Author
  • 5. Re: Buffer hit ratio
    orafad Oracle ACE
    Currently Being Moderated
    It is often useful to verify scripts like that for yourself by breaking them into smaller pieces. Like:
    select name,value from v$sysstat where name in ('physical reads','db block gets','CONSISTENT GETS')
    (try it and see...)
    lculate the buffer hit ratio. This query is
    returning: -1753.28%

    Can someone explain why I am getting this crazy number?
    Since the "decode" for consistent gets had an error, it always returns 0. Hence your query got reduced to 1-(phyrds/dbbgets) in percents. With a relatively large ratio of physical reads to db block gets (but maybe not compared to the excluded consistent gets), say 18,5:1 you get this "crazy" percentage.

    So... try to always verify scripts before running them!

    As others have noted. The bchr in itself is not of much use. Better avoid it and stick with proven methods to analyze performance related problems.

    Message was edited by:
    orafad
  • 6. Re: Buffer hit ratio
    APC Oracle ACE
    Currently Being Moderated
    I always like to point people to Connor MacDonald's site. He has a very helpful script which generates enough useless database activity to set the buffer hit ratio to[b]as high a value as our heart desires.

    Cheers, APC
  • 7. Re: Buffer hit ratio
    108476 Journeyer
    Currently Being Moderated
    Hi APC,

    Many folks misunderstand that bit about "setting your own BHR", and falsely conclude that it's a useless metric. It's not useless.

    Of course, doing lots pre-buffered I/O (consistent gets) will increase the BHR, that's the whole point, right?

    That does not mean that the BHR is useless, it just means that it's not a panacea.

    The BHR remains very useful for detecting "undersized" data buffers, where the working-set is not cached and Oracle is forced to do extra physical reads. . . .

    If the BHR was totally useless, why does Oracle continue to include it in OEM alert thresholds, and STATSPACK and AWR reports?

    The BHR is just like any other Oracle metric, you must understand its limitations and proper usage. It's just one of many tools. . . . .
  • 8. Re: Buffer hit ratio
    181444 Expert
    Currently Being Moderated
    >>
    Many folks misunderstand that bit about "setting your own BHR", and falsely conclude that it's a useless metric. It's not useless.
    <<

    The buffer cache ratio is useful only when considered in relation to other statistics. The problem is that the majority of users seem to think that that a high ratio value is good and a low ratio value is bad based on absolute values and do not understand that the static is dependent on how SQL plans are being solved. If you measure the ratio when the dominant work on the system is being done via hash joins, full scans that touch the target blocks only once, or make use of PQO during the process you can get a fairly low value, but the system is performing well. On the other had poorly performing SQL can result in a high value for the statistic. The value of the statistics bears no direct relationship to performance of the system and it needs to be emphasized that the ratio must be used in conjunction with other available information. The ratio by itself should be considered useless.

    >>
    If the BHR was totally useless, why does Oracle continue to include it in OEM alert thresholds, and STATSPACK and AWR reports?
    <<

    Over the years Oracle has done lots of things that turned out to be wrong so just because Oracle includes the statistics in certain products does not really provide a lot of support for the validity of the statistic. Known errors in the documentation have made it through two full releases. Again it is the misapplication of the statistic that is really at issue. Unfortunately, many poorly written DBA Administration and Tuning books in the past claimed that ratio could be used to measure database performance, and in point of fact the ratio has only a passing relationship to performance depending on the application.

    HTH -- Mark D Powell --