This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Dec 7, 2010 5:47 AM by 666127 Go to original post RSS
  • 15. Re: Buffer Hit % discussion
    user88831 Newbie
    Currently Being Moderated
    apology aman, from the discussion above, i'm sure that a high hit ratio might not be good.. it might be bad..

    however i don't see any reason where a low hit ratio is good?

    thanks!
  • 16. Re: Buffer Hit % discussion
    Aman.... Oracle ACE
    Currently Being Moderated
    723485 wrote:
    apology aman, from the discussion above, i'm sure that a high hit ratio might not be good.. it might be bad..

    however i don't see any reason where a low hit ratio is good?
    No one said a low hit ratio is good or a high is bad or vice-versa. The whole point is that you should not really give importance to hit ratio, at least until the time when you have troubleshooted all the other areas like design, query statements etc.

    Aman....
  • 17. Re: Buffer Hit % discussion
    user88831 Newbie
    Currently Being Moderated
    noted. thanks.. any good book on Oracle Wait Interface to recommend?

    thanks
  • 18. Re: Buffer Hit % discussion
    CharlesHooper Expert
    Currently Being Moderated
    dbaing wrote:
    noted. thanks.. any good book on Oracle Wait Interface to recommend?

    thanks
    Take a look at the book "A Practical Guide to Performance Diagnostics & Tuning". The book is roughly 6 years old now, but very well written (and very few errors), and for the most part still applies to recent release versions of Oracle Database. You can read my review of the book on Amazon, as well as several other reviews.
    http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/007222729X

    You might also find this article to be interesting:
    http://www.quest.com/whitepapers/TuningOracleWithoutCacheHit_new.pdf

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 19. Re: Buffer Hit % discussion
    jgarry Guru
    Currently Being Moderated
    dbaing wrote:
    apology aman, from the discussion above, i'm sure that a high hit ratio might not be good.. it might be bad..

    however i don't see any reason where a low hit ratio is good?

    thanks!
    It could be "good" on any system where it is normal to be looking at lots of data and not too much of the same data over and over again close in time. The docs say it:
    Oracle Blocks accessed during a long full table scan are put on the tail end of the least recently used (LRU) list and not on the head of the list. Therefore, the blocks are aged out faster than blocks read when performing indexed lookups or small table scans. When interpreting the buffer cache data, poor hit ratios when valid large full table scans are occurring should also be considered.
    Wiggly worded, but clear that valid large full table scans mean ignore the low hit ratio even more than you would usually ignore it.
  • 20. Re: Buffer Hit % discussion
    819264 Newbie
    Currently Being Moderated
    There was a time when hit ratio was used as a gauge of database performance - but times have changed, and it's simply not sensible to use it these days, as far superior measures of performance now exist, or always did exist but are now better understood.
  • 21. Re: Buffer Hit % discussion
    666127 Newbie
    Currently Being Moderated
    "There was a time when hit ratio was used as a gauge of database performance - but times have changed, and it's simply not sensible to use it these days, as far superior measures of performance now exist, or always did exist but are now better understood."

    i agree, but not with this part: "and it's simply not sensible to use it these days".

    the BCHR is not a sensible quantity for evaluating database performance by itself, sure enough. but it is still a very meaningful quantity, as long as you know how what the number means, and know the context of this number.

    a very high BCHR can be a good sign, and it can be a bad sign. If it is very close to 100%, it could be that the db is doing a massive amount of logical reads versus only a few physical reads because, for example, you run queries missing joins. This may lead to the false conclusion that because the BCHR is high, there is no problem. It can also be that your database is just heavily used but small enough to fit in the buffer cache. If you let that run long enough, the BCHR will be high, and a healthy sign. Or maybe a sign that you are wasting memory :)

    A low BCHR does not necessarily mean that there is a problem. If your workload is mostly full table scans, then you can expect lower values for BCHR. But i would certainly investigate -why- the BCHR is low.

    Suppose you use a superior measure of performance, and find that db file sequential read is consuming a significant amount of time. Knowing that these are -not- full table scans (assuming you have no tables with row chaining or many small extents), i would

    - check if it is possible to reduce the IO volume by doing SQL and index analysis
    - check if it is possible to reduce IO by increasing buffer cache size

    the latter is best done with v$db_cache_advice instead of evaluating the BCHR alone, but in any AWR or statspack report, i still look at the BHCR. Because it is still information and adds to the overal picture.

    as to the original question

    "However can i Conclude that a low % of hit ratio = bad performance where we have to either look into the sql <reduce logical i/o which in term reduce physical i/o> and adding memory to the buffer cahe if we confirm all the sqls are good but still low hit %."

    well, have a look at v$db_cache_advice, that sort of sums it up.

    to know wheter the 'SQL is good', you will have to evaluate the execution plans.
  • 22. Re: Buffer Hit % discussion
    sybrand_b Guru
    Currently Being Moderated
    As far as I am concerned performance is good when there are no end-user/customer complaints.
    Once upon a day I managed a database with a hit ratio of 20 percent, which I couldn't influence as this was caused by consistently using
    to_char(<number column>) = 'string literal', but there were no customer complaints, so I was directed not to address it. It would of course have caused a massive amount of Change Requests.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 23. Re: Buffer Hit % discussion
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user7963742 wrote:

    Because it is still information and adds to the overal picture.
    Could you give us an example of how it adds to the overall picture.

    The only value I've seen in the BCHR is that if you graph its value from (say) 10:00 am to 11:00 am over the last few months (or couple of years) you might notice that, apart from a discontinuities, it is trending up or down. (Repeat for a few other periods in the day.)

    This is a trend you might not spot if you graphed the physical I/O and logicall I/O separately .. both could be trending in the same direction but at slightly different rates - making it hard to see if there is any trend to cache exhaustion.

    Realistically, there tends to be so much noise in such graphs that it's rarely worth doing - but it only takes a few minutes every couple of months, so it's not a huge wasted effort unless you have to do it for 300 databases. (You could write a simple analytical script to do it, but you still end up with a lot of false positives that you have to review by eye).


    Regards
    Jonathan Lewis
  • 24. Re: Buffer Hit % discussion
    666127 Newbie
    Currently Being Moderated
    1 say you have a database with db file sequential reads as significant time consumer, and the BHCR is 10% (over a relevant snapshot interval).

    2 say you have a database with db file sequential reads as significant time consumer, and the BHCR is 94% (over a relevant snapshot interval).

    in the first case, i would look at the size of the buffer cache, and what any benefit of increasing it's size would be. as prevention is better than a cure, i would also look if it is possible to reduce IO's, but that's another matter.

    again, i'm not claiming that the BHCR is meaningfull in itself. i say (humbly in your presence :D) that it is not a useless quantity, as suggested above.
  • 25. Re: Buffer Hit % discussion
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Jan-Marten Spit wrote:
    1 say you have a database with db file sequential reads as significant time consumer, and the BHCR is 10% (over a relevant snapshot interval).
    Is this a database you look at frequently, or a database that you've never seen before ?
    What do you mean by significant ? Absolute time or relative time ?
    If you're going to check what's going on with db file sequential reads before you check the BCHR, why bother checking the BCHR - it doesn't add value.
    2 say you have a database with db file sequential reads as significant time consumer, and the BHCR is 94% (over a relevant snapshot interval).
    You haven't said what view you'd take on that value. Now that you've seen it's 94% what does that tell you and what are you going to do next ?
    in the first case, i would look at the size of the buffer cache, and what any benefit of increasing it's size would be. as prevention is better than a cure, i would also look if it is possible to reduce IO's, but that's another matter.

    again, i'm not claiming that the BHCR is meaningfull in itself. i say (humbly in your presence :D) that it is not a useless quantity, as suggested above.
    But when you look at it you haven't changed your level of knowledge about how the system is behaving. The ratio is inherently devoid of informative content - at best it tells you that something has changed, at worst it doesn't tell you that something has changed when something has changed.

    Regards
    Jonathan Lewis
  • 26. Re: Buffer Hit % discussion
    819264 Newbie
    Currently Being Moderated
    "the BCHR is not a sensible quantity for evaluating database performance by itself, sure enough. but it is still a very meaningful quantity, as long as you know how what the number means, and know the context of this number."

    OK - explain.
  • 27. Re: Buffer Hit % discussion
    666127 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    Jan-Marten Spit wrote:
    1 say you have a database with db file sequential reads as significant time consumer, and the BHCR is 10% (over a relevant snapshot interval).
    Is this a database you look at frequently, or a database that you've never seen before ?
    neither, but in this example, it would probably be the latter :)
    What do you mean by significant ? Absolute time or relative time ?
    well, i used the word significant with intention. so significant would mean significant in relation to total DB time.
    If you're going to check what's going on with db file sequential reads before you check the BCHR, why bother checking the BCHR - it doesn't add value.
    agreed, but i am not claiming that you -should- check the BCHR. I'm not convinced that it is a completely useless quantity.

    >
    2 say you have a database with db file sequential reads as significant time consumer, and the BHCR is 94% (over a relevant snapshot interval).
    You haven't said what view you'd take on that value. Now that you've seen it's 94% what does that tell you and what are you going to do next ?
    it tells me it is not an unususal value, so it has no influence on what i would do next.

    >
    in the first case, i would look at the size of the buffer cache, and what any benefit of increasing it's size would be. as prevention is better than a cure, i would also look if it is possible to reduce IO's, but that's another matter.

    again, i'm not claiming that the BHCR is meaningfull in itself. i say (humbly in your presence :D) that it is not a useless quantity, as suggested above.
    But when you look at it you haven't changed your level of knowledge about how the system is behaving.
    true, but that's not what i meant. what i meant is that in the 10% case, the 10% is information.
    The ratio is inherently devoid of informative content - at best it tells you that something has changed, at worst it doesn't tell you that something has changed when something has changed.
    Yeah, i understand it's pointless to -monitor- the BCHR like that.

    But if you see a statspack report on a database that is new to you, and you happen (let's say by accident) to glance over a value of 21% reported for the BCHR in a statspack report over a business day, would you think that it's devoid of informative content? What i do when i see such a value is to check why it has that value.

    i see that i used the phrase 'very meaningfull' above. that was careless, and i take that back. but i'm not convinced it's meaningless either, or devoid of information.

    regards, Jan-Marten
  • 28. Re: Buffer Hit % discussion
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Jan-Marten Spit wrote:

    but i'm not convinced it's meaningless either, or devoid of information.
    As a stand alone metric it is. You need something else to provide perspective or a baseline in order for that number to be meaningful.

    Even you, in your example of a statspack BCHR of 21% used additional data "+over a business day+" to provide perspective to the metric in order to derive some meaning from it.

    On its own however, BCHR is meaningless as a score of 6. As you do not now out of what the score is, how it was scored and what was scored.
  • 29. Re: Buffer Hit % discussion
    666127 Newbie
    Currently Being Moderated
    As a stand alone metric it is. You need something else to provide perspective or a baseline in order for that number to be meaningful.

    Even you, in your example of a statspack BCHR of 21% used additional data "+over a business day+" to provide perspective to the metric in order to derive some meaning from it.
    certainly, but i'snt that true for a whole lot of metrics?

    >
    On its own however, BCHR is meaningless as a score of 6. As you do not now out of what the score is, how it was scored and what was scored.
    sure. but in building a picture of database behavior, would a value of 21% not trigger to evaluate how it is that it has this value?

    i'm not at all advocating BCHR as a good 'performance' metric, certainly not when monitoring a system you know. It always boils down to understand what you are looking at, instead of applying 'truisms'.

    as to the 21% case, that database was doing a lot of full table scans on a table that had severe row chaining (table with 303 columns), the db cache advisor predicted no significant IO reduction by increasing it's size, and top wait event was db file sequential read. At first i could not explain the 21% until i realized what was going on. If i just ignored the 21% altogether, maybe it would have taken me a bit longer to notice the continued row statistic. Could confirm that later with tracing (a forced full table scan was doing excessive amounts of sequential instead of scattered reads, and a few block dumps revealed that oracle was forced adding row pieces for rows that had a not-null value for it's last column, that was recently appended and populated by a application release). Unfortunatly that was done during the migration to our environment, so we only saw the effect and were not aware of it history.

    In this particular case, there was an alternative for the (principal) full table scan: an index with a high clustering factor that the CBO discarded, rightfully so based on the information is had. I've done some testing, and the CBO does add cost for colums near the end of the table (as it has to calculate it's way the the column start by adding previous column sizes, it is cheaper on CPU to get column1 compared to column 303), but the CBO does not see the extra row piece it has to fetch in case of chaining - when it needed column 303. Even with good statistics, the CBO still opted for the FTS, but the index based query was actually faster (almost 3 times faster) because in reality, it had to get less blocks.

    risking going off-topic even more, does anyone know what the exact mechanism is when oracle encounters incomplete rows (nrid pointing outside the MBR blocks) - does it need to resolve all the rows that start in a MBR operation before continuing? It sure looks like it.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points