Re: Another Oracle "Myth"?

Checking in late on this one but my reaction to what Mr. Millsap is saying is basically "Well duh!"
It is like putting a program into a loop and watching the CPU race and thinking that that means you have a very efficient program. It seems rather obvious that hit ratio, as an isolated metric, is rather meaningless.

Also, there is that old adage, "If it is too good to be true, it probably is." Well, 99% is too good to be true so it is very suspicious and I think Mr. Millsap is saying that the number one suspect would be a badly tuned SQL statement.

So, if the hit ratio is fairly low, and you know your SQL is well tuned, then throwing more RAM at the problem is probably a good idea. Right?

But this raises some questions in my mind that some of the gurus in here may like to comment on.

"When is a PHYSICAL IO NOT A Physical IO? and does it matter?" I am thinking about RAM disk cache here, and RAID and any other type of physical device that may look like a physical IO to Oracle but may not be requiring an actual disk read. And then there is the problem of other applications running on the same system that may be determining if Oracle's request for a physical IO can be serviced by a Logical IO at the OS/device level.

Goodbye science, hello art.

Mike

"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?
Received on Sat Nov 22 2003 - 12:02:27 CST