Re: Need help understanding a performance problem

From: Anna C. Dent <anacedent_at_hotmail.com>
Date: Mon, 21 Jul 2003 16:41:43 -0700
Message-ID: <US_Sa.16514$Ne.13770@fed1read03>

Harry Boswell wrote:

> On Fri, 18 Jul 2003 23:49:42 +0200, Sybrand Bakker
> <gooiditweg_at_sybrandb.demon.nl> wrote:
> 
> 

>>On Fri, 18 Jul 2003 21:24:19 GMT, hboswel1_at_bellsouth.net (Harry
>>Boswell) wrote:
>>
>>
>>>On Thu, 17 Jul 2003 21:56:53 +0200, "Anton Buijs"
>>><remove_aammbuijs_at_xs4all.nl> wrote:
>>>
>>>
>>>
>>>>Check the same indexes are created in test as in the production instance.
>>>>Check important init.ora parameters like db_block_buffers (can be lower on
>>>>test because there are less users and less memory but not too small),
>>>
>>>Since db_block_buffers was the easiest thing to try, I increased that
>>>parameter, from 500 to 2500. The effect was significant - the ratio
>>>of sequential reads to scattered reads droppped from 15-1 to about
>>>4-1. And response time improved similarly. I think I intended for
>>>that parameter to be 5000 anyway - my production db value is 12500.
>>>So this whole thing was most likely brought about by a mis-type.
>>>
>>>Thanks,
>>>Harry Boswell
>>>
>>
>>
>>I see you are observing the Niemic tuning faith. Likely this will
>>bring you nowhere
>>

> 
> 
> Ah, but I could be persuaded to become a Bakkerite, were I only given words
> of wisdom!!
> 
> IOW, in all seriousness, what's the problem with the increase in
> db_block_buffers?
> 

No problem, but at some point the law of diminishing returns occurs. Since only on rare occasions can you cache the WHOLE database in your db_block_buffers, in the bigger scheme of things this is at best a short term "quick fix".

Which metric & what value of the metric will tell you when you have increased the number of db_block_buffers "enough"? If you can't honestly answer this question, then won't you have to increase it to infinity? You won't know when to stop increasing it.

As an alternative, I suggest reading a copy "Performance Tuning 101". Received on Mon Jul 21 2003 - 18:41:43 CDT