Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004

    Unanswered: problem with tunning buffer ratio

    Hello, i need to help in this question .
    I have two select for tunning buffer ratio

    select 'BUFFER_HIT_RATIO ' "What", '#',
    (1-(sum(decode(name,'physical reads',value,0))) /
    (sum(decode(name,'db block gets',value,0))+
    sum(decode(name,'consistent gets', value,0)) )) * 100 "Result"
    from v$sysstat;

    select 'READ_CONSISTENCY_MISSES' "What", '#',
    (sum(decode(name,'db block gets',value,0)) /
    (sum(decode(name,'db block gets',value,0))+
    sum(decode(name,'consistent gets',value,0))))*100 "Hit Ratio"
    from v$sysstat;

    The result in the first select is 97% (it is very good) , but the result in the second select is 79%. I think the result in the second select is short, but Im not sure. Why is it shortest?

    I dont know if I would to change the parameter db_cache_size because the second select is short.
    Any idea?
    thanks a lot

  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL


    Try this:
    select a.value / (b.value + c.value) MISS_RATIO
      from v$sysstat a,
           v$sysstat b,
           v$sysstat c
      where = 'physical reads' and
   = 'consistent gets' and
   = 'db block gets'
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    The Buffer Cache Hit Ratio (BCHR) is a meaningless indicator of performance.
    SQL is available to achive a value you deem desirable or appropriate.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2004
    Your two queries are looking at two different metrics. The buffer cache hit ratio is how many times a buffer was needed and found to be in memory. the second query look at how many blocks were read were current. A select process is assigned a SCN number at the start of the select, all block it reads from must be consistent for this SCN number. So if it was assigned a SCN number of 100 and the block it is looking at has an SCN > 100 then it has been modified after the query started. It has to 'unmodify' the block and create the image of the block at at SCN = 100. Low Percentages here usually mean you may have issues with your block size or long running transactions.

    These percentages are not a metric of performance but an indicator of problems that may need to be investiagted when you see perfromance issues.

    You can right poor SQL that has a great buffer hit ratio and good sql that has a poor BCHR. So know how to use these metrics will help you tune your system they are not 'old school' they are another tool you can use in addition to wait states, and other metrics.

    When all you have is nails everything starts to look like a hammer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts