Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Another Oracle "Myth"?

Re: Another Oracle "Myth"?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 22 Nov 2003 13:57:33 GMT
Message-ID: <hXJvb.22322$aT.5831@news-server.bigpond.net.au>


"Geomancer" <pharfromhome_at_hotmail.com> wrote in message news:cf90fb89.0311201853.126b1516_at_posting.google.com...
> Cary Millsap makes the assertion that a buffer hit ratio of > 99%
> OFTEN indicates inefficient SQL:
>
> http://www.hotsos.com/dnloads/1.Millsap2001.02.26-CacheRatio.pdf
>
> According to Mr. Millsap:
>
> "A hit ratio in excess of 99% often indicates the existence of
> extremely inefficient SQL that robs your system's LIO capacity."
>
> With 30 gigabyte data buffer becoming more common and RAM caches
> approaching 100% for small systems, I wonder if it is true that a
> 99.9% data buffer hit ratio is due to high caching of frequently
> referenced objects than some mysterous un-tuned SQL.
>
> To me, this does not make any sense, because many well-tuned systems
> benefit from additional RAM. The v$db_cache_advice view was
> introduced in 9i for this very reason.
>
> Is this another Myth, or am I missing something?

Hi Geomancer,

Having just spent 3 days this week with Cary and Gary Goodman, I'm sure he would get a giggle that his thoughts on hit ratios would be consider a myth !!

Currently it's thought by many and promoted by some that a high BHR means the "database" must be well tuned and is a good thing. Point is of course that it doesn't necessary mean any such thing and if what Cary has done is question any such opinions, then that's great.

I understand it's the word *often* that you have difficulty with. Putting 'often' into percentage terms is difficult but I would suggest there are many many databases out there is Oracle Land (enough to make the word 'often' grammatically correct) that have very high BHR because of very poorly tuned SQL and other factors, not because they're optimally tuned. This is Cary's point.

And it only take *one* piece of what I technically define as "Crap" code to both inflate the BHR to incredibly high levels whilst at the same time killing or impacting *database* performance.

I'm probably more sympathetic to BHRs than many. However, it provides only one very small piece in the tuning puzzle, one that needs to be put into perspective. It can be used as an indicator of whether the buffer cache is set way to low/high and nothing more. And what it's actual *value* is of little consequence, there is no such thing as an ideal value x.

Does a 99.9% BHR mean the database/buffer cache/sql is well tuned. Possibly.

Does a 99.9% BHR mean the database/buffer cache/sql is poorly tuned. Possibly.

So what does a 99.9% BHR actually mean and represent ? Without being able to answer this question in it's fullness, the figure is meaningless.

You get the point.

Let me ask you a very simple question. If you could cache your entire database in memory, the whole damn lot (say 50G) and that you could therefore guarantee all objects would always be found in memory and after the database has warmed up, a 100% hit ratio is assured, would this be a good thing, would you have to worry about tuning, is there such thing as inefficient SQL, could we relax and spend more time watching David Bowie DVDs ????

Interesting question which might further explain this issue ;)

Cheers

Richard Received on Sat Nov 22 2003 - 07:57:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US