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
  • 15. Re: How to tune effectively to the buffer cache hit ratio?
    555329 Newbie
    Currently Being Moderated
    BCHR are only one part of the tuning effort.
    Let say all your SQL statement need to sort rows before fecth them, and suppose that all rows were gotten to the buffer cache.
    If for any reason the memory allocated to sort rows is not enough, the sort goes through the temp tablespace, and now suppose that you sub-system disk where the temp files are stored is not efficient, all these cause the application to wait before getting its rows. So Wait events troubleshooting should supplement your BCHR tuning.

    there are some links given above that should help you tune your database efficiently.

    A.J
  • 16. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    BCHR is a meaningless indicator for the performance of the database.
    Not always, just sometimes.
    Also tuning BCHR is often symptom fighting as the root cause is inefficient SQL.
    Not often, just sometimes.
    Stop fighting symptoms, fix the application.
    I doubt strongly that you speak from a position of meaningful experience; I do not doubt in the least that the only Oracle database you've ever presided over is on a 1980s IBM PC in your beer can-strewn bedroom.
  • 17. Re: How to tune effectively to the buffer cache hit ratio?
    6363 Guru
    Currently Being Moderated
    BCHR is a meaningless indicator for the performance of the database.
    Not always, just sometimes.
    So how do you tell when it is not meaningless? Or that when it increases it is a sign of trouble?
    Also tuning BCHR is often symptom fighting as the root cause is inefficient SQL.
    Not often, just sometimes.
    Well since you can tune the ratio by running inefficient SQL it seems somewhat pointless to argue about this.

    http://forums.oracle.com/forums/profile.jspa?userID=585565

    Looks like Don has a tame parrot as well some tiny horses.
  • 18. Re: How to tune effectively to the buffer cache hit ratio?
    153119 Pro
    Currently Being Moderated
    BCHR is a meaningless indicator for the performance of the database.
    Not always, just sometimes.

    We manage a database with a BCHR of 20 percent.
    There are no complaints from the end-users.
    BCHR is meaningless.


    Stop fighting symptoms, fix the application.
    I doubt strongly that you speak from a position of
    meaningful experience; I do not doubt in the least
    that the only Oracle database you've ever presided
    over is on a 1980s IBM PC in your beer can-strewn
    bedroom.
    This is of course utter rubbish. I bet I fixed more performance dramas than you can ever dream of.
    If you have an unscalable application, you need to fix the application.
    If you state otherwise, your experience probably predates 8.0 and likely you stopped thinking way before that time.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 19. Re: How to tune effectively to the buffer cache hit ratio?
    3520 Explorer
    Currently Being Moderated
    BCHR is a meaningless indicator for the
    performance of the database.
    Not always, just sometimes.
    Also tuning BCHR is often symptom fighting as the
    root cause is inefficient SQL.
    Not often, just sometimes.
    Stop fighting symptoms, fix the application.
    I doubt strongly that you speak from a position of
    meaningful experience; I do not doubt in the least
    that the only Oracle database you've ever presided
    over is on a 1980s IBM PC in your beer can-strewn
    bedroom.
    Hello user585565 with perfectly known experience and background!
    Isn't your other initials D.B.? ;)

    Gints Plivna
    http://www.gplivna.eu
  • 20. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Hi Gints,
    Isn't your other initials D.B.? ;)
    Nope, it's not me.

    I don't think that racist Mr. Sybrand has ever been on a database . . . .
  • 21. Re: How to tune effectively to the buffer cache hit ratio?
    153119 Pro
    Currently Being Moderated
    'Mr' Burleson,

    Why the hell are you always flaming people who point out that many of your 'tips', 'Silver Bullets', or whatever you call them simply don't work?
    Do you recall your 'High Peformance Tuning with Oracle Statspack
    ' book? Do you recall none of the scripts you published in that book, I repeat NONE, work when you have gaps in your snapshot ids?
    Any comment on that?
    'Mr' Burleson, frankly I don't believe you are a DBA. The only thing you did was publishing books like hell full of factual errors, which you didn't thorougly review, boasting you have been a DBA for 20 years.
    Just as Bill Gates never was a developer (he was only a debugger), and didn't invent Windows (he simply stole it), you might have been hacking around for more than 20 years in database, you are not a DBA.

    Please tell me 'Mr' Burleson, what is wrong with you you claim I have never been on a database?
    How come I can point out the many errors in your books within a few minutes?
    Any explanation for that, 'Mr' Burleson?
    How come you continue to harass me with past posts, where even your recent posts are full of factual errors (remember that O/S cleanup script 'Mr'
    Burleson you posted today, which won't work when you don't have a 'bdump' and an 'udump' directory)?
    Shouldn't you better concentrate of correcting the many factual errors in your own books, instead of continue to misuse this forum for self-promotion and flaming people who point out you are incorrect,and have been incorrect for many many years?

    Could it be your knowledge of Oracle is reciprocal to your self-inflated personality?

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 22. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    Hi Gints - your posting reveals two things about you: 1. You are prone to making mistakes, as I am not Don. 2. You are a numpty.
  • 23. Re: How to tune effectively to the buffer cache hit ratio?
    311441 Employee ACE
    Currently Being Moderated
    BCHR is a meaningless indicator for the
    performance of the database.
    Not always, just sometimes.
    If the BCHR is low, average or high, if it goes up, down or stays the same, the database could be OK or not OK.

    In other words, it's totally redundant.

    Always.

    Cheers

    Richard Foote
  • 24. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    In other words, it's totally redundant.
    No! From "Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok" [Millsap}:
    "It is true that a very low database buffer cache hit ratio can indicate a specific type of problem."

    So, it can be useful, after all. There seems to be a tacit, false belief in this Thread that using BHCR means that the DBA uses that metric only, which is surely bunkum.

    Scenario: You generate a StatsPack report. It shows a 15% BHCR. You know that your database ordinarily and consistently has a BHCR of approx. 87%. Do you blithely ignore the metric, or get to work on finding out why it has dropped?

    If I were to see such a drop, or increase, I'd be beavering away to discover if anything has gone wrong with the database, because something may well have done. e.g. some idiot flushed the Shared Pool, etc.

    There's far too much "cloak touching" in the Oracle community, whereby poor quality DBAs unthinkingly take sides with Oracle hot shots, hoping that by regurgitating their mantra, they themselves will become, by pseudo-association, equally as hot. Doesn't work like that.
  • 25. Re: How to tune effectively to the buffer cache hit ratio?
    311441 Employee ACE
    Currently Being Moderated
    "It is true that a very low database buffer cache hit ratio can indicate a specific type of problem."

    It can indicate there's a problem but it can also indicate there's not a problem.

    Like I said, it's totally redundant.

    Yes !!

    OK let me ask you, assuming you have a low database buffer cache hit ratio, what additional checks do you perform to determine whether or not there indeed is a problem or whether it's a result of that large batch process performing all that processing or that user performing that large global search, etc.

    How do you differentiate the low BCHR from it being caused by a "problem" or not ?

    Cheers

    Richard Foote
  • 26. Re: How to tune effectively to the buffer cache hit ratio?
    153119 Pro
    Currently Being Moderated
    Scenario: You generate a StatsPack report. It shows a 15% BHCR. You know that your database ordinarily and consistently has a BHCR of approx. 87%. Do you blithely ignore the metric, or get to work on finding out why it has dropped?

    If I were to see such a drop, or increase, I'd be beavering away to discover if anything has gone wrong with the database, because something may well have done. e.g. some idiot flushed the Shared Pool, etc

    So what?
    In the specific case I mentioned earlier, I investigated what went wrong. I was particularly interested, because in the past I was the DBA of this database.

    I found out there were
    select *
    from ....
    where upper(column_name)=upper(hardcoded_literal)
    in almost every statement.
    This work was implemented and supervised by a former Oracle employee, who shall remain nameless.
    So, what should I do?
    Right now, my position with respect to this database is 'third line support'.
    The developers have long gone, so beating them is not an option.
    I am not allowed to change anything without a formal change.
    Ct doesn't have any complaints.

    BCHR is meaningless.
    Always.
    Making sure BCHR is 99.99 percent only pays Don Burlesons bills. Not mine.



    --
    Sybrand Bakker
    Senior Oracle DBA
  • 27. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Hi Richard,
    if it goes up, down or stays the same the database could be OK or not OK.
    Well stated. Agreed.

    Ah, but would you agree that a statisticaly significant deviation from the norm signals some sort of a "change" that might warrant investigation?

    I once plotted the BCHR per minute, and it's funny how the ratio changes. It could be 20% one minute, 95% the next, and

    Once I've tuned and stabilized my OLTP systems, I notice that the many metrics create repeatable "signatures", regular patterns of usage, any deviance from which warrants further investigation.

    Me, I establish a "exception threshold" for the BCHR, (e.g. +- 20%), and compare that to the historical average, normalized  by the historical average per hour and the day-of-the-week.  When the alert fires, an e-mail notifies the DBA.
    In other words, it's totally redundant.
    Sorry, how do you mean, "redundant"? Are you saying that the metrics are better used with another formula?

    I've summarized by thought here:

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

    Please advise.
  • 28. Re: How to tune effectively to the buffer cache hit ratio?
    311441 Employee ACE
    Currently Being Moderated
    Sorry I meant to add if there's truly a performance issue, then you'll usually find "response times" are impacted. If response times remain acceptable for all business requirements, then I don't really care what the BCHR might be, let it be 20% for all it matters.

    If response times become problematic, then yes, I dig further to determine the issue, regardless of what the BCHR might be, let it be 100% for all it matters.

    The BCHR is simply redundant in determining whether there are problematic issues.

    I'll tell you something else.

    I have never checked to see if someone has flushed the shared pool if the BCHR drops or increases.

    Never.

    And I don't think I ever will ...

    Cheers

    Richard Foote
  • 29. Re: How to tune effectively to the buffer cache hit ratio?
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    Like I said, redundant in that the BCHR can be high, low or average, it can go up, down or be stable, there can be performance issues in any of those scenarios and there might not be performance issues in any of those scenarios.

    The problem with trends and averages is that they're just that,trends and averages. There can be activity in the database that's at odds with the averages that could be totally acceptable.

    Again, how do you tell whether or not the 20% variance was a problem or not ?

    And why couldn't the same problem appear without the variance occurring with it being swollen by the overall average activity of the database ?

    And if could occur regardless, why not perform the checks regardless of the BCHR ?

    Like I said, redundant.

    I honestly don't look at the BCHR of any database I manage.

    Cheers

    Richard Foote