Myths About Myths

In case you "mythed" it (it's a pun!) here is YAFMA (Yet Another Feckin Myth Article) by Mike Ault, going down the well-worn path of Oracle Myths. Thanks to Bill S for pointing it out.

So this posting is on the subject of myth generation, because (pause for effect) this article itself is in danger of generating a new one! That's right, before our very eyes a new myth is born. See if you can spot it in this quoted excerpt ...

Ancient Myth: A data buffer hit ratio (DBHR) should be kept greater than 90%

This myth was also propagated by Oracle Corporation in the early 1990’s when almost all Oracle databases were I/O-bound and SGA size was constrained by the 32-bit server technology. Oracle-based products such as SAP also note in their manuals that the DBHR should be over 90%. Oracle author Robert Freeman notes:

It has been demonstrated on many occasions that it is easy in a basic proof to prove just about anything. Given a basic proof I can prove that the buffer cache hit ratio means nothing, or I can prove that it is the most important thing in the world.

I know of several scripts which can be run against your database to give you any DBHR you desire. Does this make a myth? Oracle does not seem to think so, and ratio-based advisories form the foundation of the Oracle10g Automatic Memory Management utility and the v$db_cache_advice advisory.

See it? You might miss it at first glance, but it's nestled in there all the same. Here it is: "ratio-based advisories form the foundation of the Oracle10g Automatic Memory Management utility and the v$db_cache_advice advisory".

I can think of two ways of interpreting this:

1. "The Buffer Cache Hit Ratio (BCHR) cannot be completely invalid because the Buffer Cache Advisory (BCA) functionality uses a form of BCHR as the basis for it's 'advice'".
2. "The Buffer Cache Hit Ratio (BCHR) cannot be completely invalid because the Buffer Cache Advisory (BCA) functionality presents its results in the form of a ratio"

If the former is the correct interpretation then I'm going to call "bullshit" on it right now. Anyone who has seen a BCA result graphed will see that there are generally all sorts of flat spots and dog legs involved, and there's no way that those came from the interpretation of a single BCHR number. I had a discussion on this with two luminaries of the Oracle world, and they were unanimous in decrying the "BCA is based on BCHR" theory - it is much more complex than that and hopefully a more complete explanation will be published when their time permits.

On the other hand, maybe the second interpretation is the correct one? Maybe, but in that case the argument is just nonsense. The BCA ratio is of "physical reads (current buffer size)" to "projected physical reads (modified buffer size)", and that's not a BCHR -- it's nothing to do with BCHR. To infer that the presentation of a trend of PIO:PIO ratio by the BCA is somehow an endorsement of the BCHR is bizarre and threatens to mislead the "neophyte". Hey, I could present you with a series of ratios depicting frequency of a two-year old's evening bowel movements in relation to the number of sugary fruit beverages drunk at day care, but while it would be both a valid and a useful ratio it would not mean that all ratios are useful. Would it?

So the article is ambivalent on the subject of BCHR's. Is their usefulness as a tuning aid a myth or not? And because of it's imprecise wording it threatens to spawn off a myth of it's own: that Oracle's buffer cache advisory is based on buffer cache hit ratios.

Sloppy work. We await a clarification of the intent of this section.