This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Nov 12, 2007 11:02 AM by ca110974 Go to original post RSS
  • 30. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Hi Richard,
    Again, how do you tell whether or not the 20% variance was a problem or not ?
    Good question. For me, it's an iterative process. If I get a false positive, I simply increase the threshold, and sometimes, turn it off completely (yes, you may quote me).

    I have responsibility for lots of databases, and no single one has the same thresholds. For example, while OLTP databases have predictable patterns of usage, a data warehouse is likely to have less signatures, often making the BCHR a totally useless metric.
    And why couldn't the same problem appear without the variance occurring with it being swollen by the overall average activity of the database ?
    I'm not exactly sure what you mean here, but if you are saying that other metrics give the same alert, the answer is "yes"! When we get a real-world alert, several triggers usually fire together.

    Like most everything in Oracle tuning, there are no fixed rules, no mathematical formulae, and hence the cop-out answer: "it depends".

    All we have are general guidelines and rules of thumb (sorry, I couldn't resist), and everyone knows that they are imperfect and based on probabilities, not proofs.
  • 31. Re: How to tune effectively to the buffer cache hit ratio?
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    What I mean is that the problem that resulted in a change of the BCHR in one database or in one specific occurrence of a problem may just as easily occur at another time or in another database without their being any change of the BCHR, as the specific load of the database flattens out the problem from it being visible in the database wide metric that is the BCHR.

    Conversely, a change in the BCHR that on one occasion was caused by a specific problem may occur again without their being any problem at all.

    It goes back to the simple fact the BCHR can go up or down or remain the same and there might or might not be a problem. The BCHR can be low, average or high and there might or might not be a problem.

    You always need to check with some other metric or metrics regardless, hence the BCHR itself is redundant.

    If you get a false positive on one occasion, it might be a true positive the next time.

    I work primarily with OLTP databases and they most certainly do not have a predicable pattern of usage. Sometimes there are busy days, sometime not. Sometimes there are certain types of usage patterns depending work loads, sometimes there are different patterns. Sometimes large management reports get run, sometimes they don't.

    The BCHR simply can't differentiate between a false or a true positive or negative.

    Hence it's redundant.

    Cheers

    Richard
  • 32. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    Isn't letting the Users decide if response times are good, or not, a bad thing? A bit like having driven one's car around for years, only for a mechanic to one day have a proper look, fix a long-standing fault, and suddenly you get much better mileage. That the driver would have been under the false impression that she was getting the best from her car would only have meant that she had got used to a substandard system; not that it was, all along, a good system.

    I remember inheriting a database that had never been optimized properly. After (admittedly quite simple) tuning, key queries suddenly ran a lot faster. The result was increased efficiency, as Month End reports, in particular, were able to be inspected and business decisions made more quickly, which, in this case, meant getting a jump on the competition. This was a good thing.
  • 33. Re: How to tune effectively to the buffer cache hit ratio?
    311441 Employee ACE
    Currently Being Moderated
    Isn't the volume of the car radio a bad way to determine if the car's running OK ?

    Sometimes the radio can be loud and the car runs bad, sometimes the radio can be loud and it runs OK. Sometimes it can be soft and it runs OK, sometimes it can be soft and it runs bad.

    Sometimes the radio can be off and it runs OK or bad.

    The BCHR is the car radio of the database.

    Only it doesn't play David Bowie songs ...

    Cheers

    Richard
  • 34. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    You always need to check with some other metric or metrics regardless, hence the BCHR itself is redundant.
    I'm not sure that "redundant" is the best word to use here.

    HJR is an English major, maybe he can help. . . .
    If you get a false positive on one occasion, it might be a true positive the next time.
    Yep, there are no absolutes in Oracle, just probabilities . . .
    Sometimes large management reports get run, sometimes they don't.
    Exactly! And as Peter Sellers sez: "I like to watch" . . . . .
  • 35. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Isn't letting the Users decide if response times are good, or not, a bad thing?
    I don't think that DBA's have much of a choice there!

    The DBA serves the end-user community, and when you have SLA's you have NO CHOICE but to improve the response time to keep it within requirements.

    For a mission-critical database, I automated a system to measure each and every transaction for response time, end-to-end, and keeping their response time fast is absolutely required:

    http://www.dba-oracle.com/monitoring_formspack.htm
  • 36. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Sometimes the radio can be off and it runs OK or bad.
    Whoa! Didn't you aleady concede that a change in BCHR indicates that something changed? Granted, it's not very useful, but taken alone, few metrics are. The BCHR is just one one tool in the toolbox . . . .
    Only it doesn't play David Bowie songs ...
    My, what a strange thing to say . . . .
  • 37. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    That the driver would have been under the false impression that she was getting the best from her car would only have meant that she had got used to a substandard system; not that it was, all along, a good system.
    Good analogy. That's sorta like the difference between first_rows and all_rows.

    - The first rows optimizer goal targets returning rows fast (i.e. response time minimization)

    - The all rows optimizer goal targets efficient SQL execution (i.e. better mileage)
    After (admittedly quite simple) tuning, key queries suddenly ran a lot faster.
    My, sounds like a Silver Bullet. . . . Do you remember what you changed?
  • 38. Re: How to tune effectively to the buffer cache hit ratio?
    APC Oracle ACE
    Currently Being Moderated
    Like I said, redundant.

    I honestly don't look at the BCHR of any database I manage.
    This topic does come up here from time to time. You can find one instance where the BCHR did point to a genuine problem Re: performance issue - buffer cache hit ratio below 50%. Basically the guy was running an actual system on the factory default settings for memory, values which would only have been considered right in the mid-nineties. A school boy error.

    Whether BCHR is a sensible metric for monitoring health in a properly-configured system that's been running for a while is a different matter. And one which I shall leave to you gents to resolve.

    Cheers, APC

    Blog : http://radiofreetooting.blogspot.com/
  • 39. Re: How to tune effectively to the buffer cache hit ratio?
    ca110974 Newbie
    Currently Being Moderated
    Whew! What a thread! All that pushing and poking each other in the eye.
    Bill Gates never was a developer
    Actually, Bill co-wrote an early BASIC interpreter. The original storage media for it was paper punch tape (Computer Bowl, ca. 1990).

    Sybrand, please cool down just a little, for the sake of the innocents that come here looking for help and no more. Creating a hostile forum environment only serves to push people away.

    About 80% of the time, it's the application code causing problems. The CBO does a pretty decent job most of the time, but it's not infallible. Often enough, it needs a nudge. Which is why it's crucial for developers to understand the database they're interacting with. Except for mickey mouse applications or extremely low transaction volumes, "database neutral" development leads to mediocre-performing applications.
  • 40. Re: How to tune effectively to the buffer cache hit ratio?
    51034 Newbie
    Currently Being Moderated
    HJR is an English major

    Actually, I'm a French Colonel...

    Well, ok, really and truly, I graduated with a degree in History. I'm not sure if that's the same thing as majoring or not, but anyway, English certainly wasn't something I studied that deeply...

    To the substantive point. Is "redundant" the right word? Well, in the context of that sentence you quoted, absolutely it is. Redundant means 'unnecessary' or 'excessive' (in the sense of something being surplus to requirements and hence exceeding what's needed to get the job done).

    The BCHR is unnecessary because there are a million and other more meaningful statistics that provide in a more accurate manner the information that it is claimed the BCHR provides. It's excessive -that is, surplus to requirements- because those other statistics would be a much better bet to look at long before worrying about the BCHR. By the time you've worked out what those other statistics are telling you, the BCHR won't be able to tell you anything more or new.

    And that's exactly what the sentence you quote is saying, so 'redundant' is certainly correct.

    But I think you might be on to something, too.

    Because left like that, it would imply the BCHR could tell you something, but lots of other metrics could tell you them sooner or more plainly. That would in turn imply that, although redundant, the BCHR has meaning.

    But the real problem with the BCHR is that it is meaningless.

    As I put it on cdos, it's like a red flashing light in a nuclear power station that flashes to indicate imminent melt-down and also flashes when it's time for a tea break. An indicator that flashes when things are both good and bad isn't an indicator with derivable, practical meaning. Thus it is -and always was- meaningless, not just redundant.

    Of course, one can always make the case that if an indicator that is forever flashing suddenly stops flashing, that tells you something. You can likewise claim that if your hit ratio is "normally" 80% and suddenly drops to 20%, you now know something you didn't know before. But since you can't tell me what it is that you now know, the delta in the BCHR is itself devoid of useful meaning.

    All things have meaning, of course. The phrase "fjkhskfjhsismcsdbnkfj sh" means nothing and by meaning nothing, it means something: Rogers has gone mad; his keyboard has malfunctioned; he's writing in secret code.... could be any, all or none of the above. So whlst all things can be said to have some sort of meaning in the broadest sense, I think you have to qualify it as 'betokening something practical'. You have to be able to say something unambiguous about what it means, otherwise you are no better off than before.

    And in the case of the BCHR, or in the case of a change in the BCHR, there is no practical, functional meaning that it can betoken.

    Take any given BCHR and you can't say whether it's good or bad. Spot that BCHR rise or fall by X or Y% and you still can't say whether it's good or bad. Therefore, it's practically (that is, functionally) meaningless.
  • 41. Re: How to tune effectively to the buffer cache hit ratio?
    51034 Newbie
    Currently Being Moderated
    Sorry, but that thread tells us nothing except that the guy measured the hit ratio and it turned out that his shared pool was too small!

    (Well, OK, his shared pool and everything else... but the shared pool was woeful, too... and miss on the shared pool is far more expensive than a miss on the buffer cache!)

    If he'd measured his library cache hit ratio, I suspect he'd have stumbled over that bit of memory misconfiguration! I wouldn't call that a ringing endorsement of the BCHR as even an in-extremis useful measurement!
  • 42. Re: How to tune effectively to the buffer cache hit ratio?
    user588120 Newbie
    Currently Being Moderated
    I here by congratulate Mr.Burleson and Mr.Sybrand for making this particular
    thread so live
    regards
    Josh
  • 43. Re: How to tune effectively to the buffer cache hit ratio?
    602378 Newbie
    Currently Being Moderated
    Ok, so what other metrics tells you the buffer cache is too small or too big and could benefit from adjusting?
  • 44. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    Richard: If you car radio is not playing Bowie, then give thanks!