This content has been marked as final. Show 59 replies
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.
I wouldn't call that a ringing endorsement of the BCHR as even an in-extremis
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.
Blog : http://radiofreetooting.blogspot.com/
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.
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?
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."
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.
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.
>> 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?
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.
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.
only complete idiots use ratios as a diagnostic tool.
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.
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.
blog : http://radiofreetooting.blogspot.com
Ha, good comment!
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.
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?
"The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE"
Oracle Press author
Apologies for the assumption, but your wording was ambiguous.
From your Blog (my emphasis):
Fact is, it wasn't dropped functionally, and I am happy to acknowledge as much.
"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?