Re: Need help understanding a performance problem

On Mon, 21 Jul 2003 15:36:49 -0500, Harry Boswell <hboswel1_at_bellsouth.net> 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?

The problem is that there are almost certainly better things to spend your RAM on!

If you throw memory at the buffer cache, you can achieve very good hit ratios... potentially, practically 100% as you manage to achieve the "ultimate" of caching your entire database in memory. Sounds good... except that you will likely have massive wait events elsewhere in the system. Such as for the library cache latch, because your SQL is written without bind variables, and thus hard parsing is going through the roof; or because you've put so much RAM into the buffer cache and your library cache is therefore so small, execution plans age out as fast as they are created and consequently have to be re-parsed like crazy. Or, such as for buffer busy waits, because you've set PCTFREE and PCTUSED so close to each other that every insert takes a block off the free list, and every delete puts it back on. Or such as for free buffer waits, because you've put all your datafiles on the same hard disk, which happens also to be the C: drive of your primary domain controller and also used to house three other databases, and DBWR can't flush at anything like the required rate.

Point is, a ratio of any sort on the buffer cache is an extremely poor guide to whether or not all these other major faux pas of bad tuning have been dealt with properly.

It's also a poor guide because such ratios can be affected, up or down, for no really good reason. Rollback segment headers, for example, are very frequently accessed, and thus are extremely likely to reside practically permanently in the buffer cache. Therefore, all access to them will register as 'hits' in the buffer cache, and your 'hit ratio' will go up accordingly. But that tells you nothing about how much actual data is being found in the buffer cache. Likewise, if you have freelist contention, segment header blocks will inflate the hit ratio... but that's a *bad* thing to have, even though it 'helps' your hit ratios.

No... ratio tuning is a poor way to go.

Note to Sybrand: So is abusing an entire nation of over 1 billion people, however.

~QM Received on Tue Jul 22 2003 - 15:07:45 CDT