8 Replies Latest reply: Feb 21, 2007 9:43 AM by 181444 RSS

    Buffer hit ratio

    user135518
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        >>
                        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 --