Re: Oracle slowed down

"hans" <hansF_at_telus.net> wrote in message news:pan.2006.04.21.03.36.49.265000_at_telus.net...
> On Fri, 21 Apr 2006 03:21:28 +0000, Bob Jones wrote:
>
>>
>> "HansF" <News.Hans_at_telus.net> wrote in message
>> news:pan.2006.04.21.02.14.20.634587_at_telus.net...
>>> On Fri, 21 Apr 2006 02:33:53 +0000, Bob Jones wrote:
>>>
>>>
>>>>
>>>> May I aslo suggest reading Oracle's official performance tuning books?
>>>
>>> In which they discuss the ratios as an indicator of buffer cache size
>>> issues - to be used when statement, application, I/O and system tuning
>>> have been completed. Unfortunately many people skip over the 'yes but'
>>> part.
>>>
>>
>> On the other hand, if the buffer cache is too small for the amount of
>> data
>> your are dealing with, everything else can be tune to perfection, the
>> performance will still be poor.
>
> You are absolutely right. And I will agree to the following:
>
> "Tune buffer cache, yes. Tune buffer cache hit ratio, no."
>

What indicator do you look at when tuning buffer cache?

> Ratios are mathematical normalizations that may obsure information.
>

That is a big "may". In most real world cases, it does not.

>>
>>> Bottom line - BCHR calcs can be caused to show just about any value one
>>> wishes. There are a number of sites and books (as indicated) that show
>>> how you can generate virtually any BCHR without changing anything in the
>>> system.
>>>
>>
>> Sure, you can artificially obscure the hit ratios, but that does not make
>> hit ratio irrelevant in a real production enviornment. If hit ratio is
>> meaningless, there would be no need to tune buffers.
>>
>>> A good BCHR may be the result of very, very poorly written tablescanner.
>>
>> I am not sure what you mean here.
>
> I simply mean that you can get a great BCHR from SQL that does
> tablescanning. Seems right, does the wrong job.
>

Again, that's an application issue.

>>
>>> So the novice DBA and developer feel great that they have achieved the
>>> best possible performance for a piece of crappy code. Similar to putting
>>> in wait loops into C and tuning the heck out of the call stack.
>>>
>>
>> That is really a different issue. Application tuning and buffer tuning
>> are
>> two separate things. They are both relevant to the performance.
>
> Yup. Both relevant to performance.
>
> Again you state Buffer Tuning. Again I agree.
>
> Unfortunately, the Buffer Cache *Hit Ratio* is not relevant to either
> application tuning or buffer cache tuning. It's a nice, easy to generate
> number that can be posted on slides. That may, or may not, give an
> indication that something is tuned.

Hit ratio is not meant to tell you whether application is tuned. It simply shows hits verses misses. With everything else being equal, hit ratio is very relevant to buffer cache. Received on Thu Apr 20 2006 - 23:27:31 CDT