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
  • 45. Re: How to tune effectively to the buffer cache hit ratio?
    APC Oracle ACE
    Currently Being Moderated
    I wouldn't call that a ringing endorsement of the BCHR as even an in-extremis
    useful measurement!
    I think we can all agree (well most of us) that ratios are not a very useful measurement in most situations. Because most tuning problems exist at the SQL level rather than the database.

    However, the sort of person who tries to run a production system using the factory default settings is also the sort of person who is most likely to use ratios as a diagnostic tool. There's no point trying to get such folks to understand statspack or the wait event interface when all they need to do is enlarge their SGA. Once they've got their system configured properly then we can start to educate them in the sweet science of database tuning.

    Cheers, APC

    Blog : http://radiofreetooting.blogspot.com/
  • 46. Re: How to tune effectively to the buffer cache hit ratio?
    153119 Pro
    Currently Being Moderated
    There are more than enough unscalable apps on the market in which 'enlarging the SGA' won't help, or only for a few weeks.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 47. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    No, it isn't a myth.

    Oracle 8i, for certain, can experience rather unpleasant "speed" problems associated with many extents and SMON gallantly trying to coalesce them.

    I have seen SMON so active, owing to the many thousands of extents it was trying to coalesce, that the server "froze" within a few minutes of booting-up.

    init.ora parameter event = "10269 trace name context forever" offers a reprieve. [see: http://www.ixora.com.au/tips/creation/extents.htm]

    Myths and rarities are not the same thing.
  • 48. Re: How to tune effectively to the buffer cache hit ratio?
    94799 Explorer
    Currently Being Moderated
    init.ora parameter event = "10269 trace name context forever"
    Steve Adams makes that suggestion with several restrictions and under a section entitled 'Oracle 7.3'. Can you confirm whether it is appropriate or even supported in 8i (for which you appear to be suggesting it) and under what circumstances it is effective?
  • 49. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    Hi padders:

    Just had a look on MetaLink. The Notes I found say that this parameter applies to 7.3 onwards.

    I used it as I suspected the cause, and got lucky (couldn't use email or phone at the time, so no Google or Oracle Support: long story). However, from MetaLink:

    "What to look for
    ~~~~~~~~~~~~~~~~
    The most common indicator is the SMON process consuming large amounts of CPU for a long period.
    UNIX O/S utilities sar or vmstat will show how busy CPU(s) are; ps will show which process is using the CPU."

    HTH
  • 50. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    Hi Don:

    It certainly looked like a Silver Bullet... but it's easy to look fab when predecessors have been, at best, weak.

    I had just started with a company, and was doing the usual "making oneself look good" thing by checking the various databases they managed for their customers.

    I ran various health check reports on some customer systems (Hey! Some from your Oracle 9i UNIX Admin. Handbook!), as well as running StatsPack, having a peep at the BHCR (Yes! Really! It was tragically low, compared to other, supposedly similar customer sites... but, of course, I was a fool & a communist to assign any worth to that!), and finally (it's always finally, isn't it?) then ran Tom Kyte's Binds script:http://asktom.oracle.com/pls/asktom/f?p=100:11:3106270358563696::::P11_QUESTION_ID:1163635055580 Ah Ha! Tons of non re-entrant SQL polluting the Shared Pool like a blocked drain.

    Turned out that a naughty developer had put a quick fix bit of SQL into a Java App., which didn't use Binds, without it going into their Change Control system! It then found its way into all customer systems* via the usual periodic software upgrades & patches.. and nobody noticed! Customers had simply accepted that the speed of the database was as good as it could be! I then did a quick bit of SQL fiddling and made the Java code use binds (actually, the code ended up as a procedure that the Java App. called-out to... hooray!). A good thing, especially as there were plans to install more RAM to overcome "memory issues" in some customer systems! You couldn't make it up.

    Point is: The end user may not (and it's not their job to know) be aware of a problem; that does not mean that such a problem is trivial.

    * all systems, but not all actually used it.

    Message was edited by:
    user585565: Hyperlink was not quite right.
  • 51. Re: How to tune effectively to the buffer cache hit ratio?
    51034 Newbie
    Currently Being Moderated
    However, the sort of person who tries to run a production system using the factory default settings is also the sort of person who is most likely to use ratios as a diagnostic tool.

    Translation: only complete idiots use ratios as a diagnostic tool.

    It's a bit of an extreme position (but then the poster in that thread was a bit of an extreme case, I suppose), but yes, I'd go along with the spirit, at least, of that!

    There's no point trying to get such folks to understand statspack or the wait event interface when all they need to do is enlarge their SGA.

    Well, again, remember the context here. The suggestion was made by you that it was the BCHR that had provided the evidence that they needed to "enlarge their SGA". My point was that practically any other measure on the database would have done the same, the library cache hit ratio amongst them. You can't really characterise that as me suggesting they need to learn the wait interface before taking action!

    Frankly, I'd suggest a quick perusal of a parameter file in notepad would have achieved the same outcome to someone with an ounce of common sense. You don't, I think, have to be educated in the mysteries of database tuning to spot obvious howlers such as the one you suggested could be held to be one example where the BCHR had been put to good use.

    Yes, the BCHR did tell the man there was a problem. But so would anything else. And the BCHR didn't tell him what to do to fix his problem: a quick squiz at the init.ora did that.

    So, my point: no, that's a lousy example of when the BCHR was put to good use.
  • 52. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    Howard:
    >> Frankly, I'd suggest a quick perusal of a parameter file in notepad would have achieved the same outcome to someone with an ounce of common sense.

    What if the file (I assume you meant pfile, rather than spfile) had been changed since the DB had been started? Better to interrogate the DB directly, surely?

    Have you had any more feedback about your castigation of the Oracle ACE thing?
  • 53. Re: How to tune effectively to the buffer cache hit ratio?
    51034 Newbie
    Currently Being Moderated
    Don't assume things.

    I used the phrase "parameter file" deliberately. It covers ALL possible parameter files, not just pfiles. And not just spfiles. If I'd meant init.oras alone, I'd have said "init.ora" or "pfile"; if I'd meant spfiles alone, I'd have said "binary version of the init.ora" or "spfile". But I didn't do either of those things, and it wasn't an accident that I didn't.

    Regardless, I regularly peruse spfiles using Wordpad or vi, depending on the OS du jour. Just because it's a binary file doesn't mean you can't look at it in a plain old text editor.

    And no, I find it a lot quicker to open an spfile or pfile in a text editor than to issue a syntactically-exact query of V$PARAMETER not quite knowing whether I should actually be looking at V$SPPARAMETER or any of the other parameter-related views!

    And yes, I had a lot of feedback about my criticism of the Oracle ACE program, and I published a piece in which I retracted my suggestion that the ACE program was being dumbed down. I still don't agree with the way it's being managed in many respects, and I will continue to argue that introducing two levels of ACE-dom was silly. But last time I looked, one of the essentials for ACE-dom was still technical competence... and that was something I think their frontpage had dropped at one point and which I was therefore concerned about. Fact is, it wasn't dropped functionally, and I am happy to acknowledge as much.
  • 54. Re: How to tune effectively to the buffer cache hit ratio?
    APC Oracle ACE
    Currently Being Moderated
    Howard
    only complete idiots use ratios as a diagnostic tool.
    I would prefer to say "only people who don't know any better". This may be because they are idiots or it may be because nobody has told them a different way of doing things. Some people can learn and other people are idiots.
    the BCHR didn't tell him what to do to fix his problem: a quick squiz at the
    init.ora did that.
    Actually Laurent Schneider told him. Which I originally posted with a smiley but there is a serious point here. The guy had looked at the init.ora parameters because he posted them to the forum and he still couldn't see what the problem was. But what can we do? People are allowed to have kids without getting a breeding licence and people are allowed to administer databases without understanding how the darn things work.
    So, my point: no, that's a lousy example of when the BCHR was put to good use.
    Whatever.

    Cheers, APC

    blog : http://radiofreetooting.blogspot.com
  • 55. Re: How to tune effectively to the buffer cache hit ratio?
    94799 Explorer
    Currently Being Moderated
    one of the essentials for ACE-dom was still technical competence
    Dammit I thought you just had to get to 2,500 posts and your sew-on Oracle Ace arm badge was automatically posted to you.
  • 56. Re: How to tune effectively to the buffer cache hit ratio?
    108476 Journeyer
    Currently Being Moderated
    Hi APC,
    People are allowed to have kids without getting a breeding licence and people are allowed to administer databases without understanding how the darn things work.
    Ha, good comment!

    As to BCHR, I think that I agree with most everything here, except the assertion that the BCHR is completely useless. When combined with other metrics, a radical change to BCHR alerts me that "something" has changed. Also, when examining a optimized database (not all databases are un-tuned!), a low BCHR may indicate "excessive" disk I/O, caused by an undersized buffer cache.

    Andrew, what do you think of the data buffer cache advisor, which uses a ratio-based model for predictive analytics?

    ***********************************************************
    Fnally, do you disagree with the 10g docs?

    http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/memory.htm

    "The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE"

    Don Burleson
    Oracle Press author
  • 57. Re: How to tune effectively to the buffer cache hit ratio?
    ca110974 Newbie
    Currently Being Moderated
    How do you get points? Just post a lot?
    Hey any idiot can do that, given enough downtime at work. ;-)
    Or perhaps when they should be doing work...
    We rarely have these flames on the PL/SQL Pipeline. Folks are just so much more professional over there.
  • 58. Re: How to tune effectively to the buffer cache hit ratio?
    588568 Newbie
    Currently Being Moderated
    Hi Howard:

    Apologies for the assumption, but your wording was ambiguous.
    Fact is, it wasn't dropped functionally, and I am happy to acknowledge as much.
    From your Blog (my emphasis):
    "It was bad enough seeing some of the people who managed to acquire ACEdom in the not-too-distant past. I'm not going to single particular names out because even I'm not that much of a social klutz; but[b] there's a bunch of complete nobodies who nominated themselves for starters and got approved despite a paucity of any demonstrated technical skill or community contribution at all. And then there are the one or two well-known names... well-known, that is, for getting some of the simplest stuff about Oracle quite profoundly wrong."

    So, then, has OTN booted out these various "nobodies" and purveyors of the "profoundly wrong" since your Blog article hit our screens?
  • 59. Re: How to tune effectively to the buffer cache hit ratio?
    ca110974 Newbie
    Currently Being Moderated
    I'm at 49 posts! One more to go and I'm "on the board"! ha hahahahahahaha!
1 2 3 4 Previous Next