Re: Another Oracle "Myth"?

"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.

30GB SGA Common, surely not. For a system written without bind variables and with 200 or so concurrent users against a 50gb database we require under 1gb SGA. to be honest I'd be sceptical of any system that had an SGA of more than 2-4GB but I'm sure those that deal with the Boeings and BTs of this world might be able to educate me if I'm wrong.

> 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?

I thought the most effective use of db_cache_advice is that it tells you to *stop* adding more ram as it becomes pointless.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK