Re: Oracle slowed down

From: hans <hansF_at_telus.net>
Date: Fri, 21 Apr 2006 03:35:42 GMT
Message-Id: <pan.2006.04.21.03.36.49.265000@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."

Ratios are mathematical normalizations that may obsure information.

>

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

>

>> 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. Received on Thu Apr 20 2006 - 22:35:42 CDT