Re: Another Oracle "Myth"?

"Geomancer" <pharfromhome_at_hotmail.com> wrote in message news:cf90fb89.0311210459.57364286_at_posting.google.com...

> Again, my problem was with this statement:
>
> "A hit ratio in excess of 99% often indicates the existence of
> extremely inefficient SQL"
>
> I was simply hoping that someone could explain why a stallar BHR often
> indicates poorly optimized SQL. Can someone explain the "OFTEN" part
> here?

Ways to get a high hit ratio.

1. Have everything in memory before hand and read it just enough.
2. Repeatedly read the same data over and over and over and.....

I think the comment (and bear in mind that Cary works almost exclusively with systems with performance problems) is really saying that when you have spectacularly high hit ratios it is most likely that point 2 is happening rather than point 1. You will *usually* get this where

you are doing nested loops joins rather than a merge or hash join. you are repeatedly issuing the same sql with different literals, or in a loop rather than using array fetches.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK