Re: db file sequential read wait

Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<3FB25CEF.8C7CE29F_at_remove_spam.peasland.com>...
> > A buffer
> > cache of 1G is just ridiculous and clearly demonstrates you are just
> > trying to throw more iron at your problems.
>
> So what would be a good size for the buffer cache?
>

:D

Probably a size that didn't make dbwr work overtime to do a checkpoint... I'd look at how many dbwriters this thing has. Async write or no async write, a 1G buffer cache ain't gonna be flushed easy. Regardless of it being excessive or not, which is a different problem.

The numbers are not so bad for reads, there are just heaps of them. The writes are by comparison very slow.
The other thing is to look at those DELETE statements with an IN (select...). Is that returning a heap of rows or just a few, and is the SELECT itself using indexes as it should?

Freshness of stats is of course important, but given the detailed stats analysis I'd dare say the OP probably knows that already.

As for the INSERTS, does TABLE_A have LOTS of indexes that could be causing the problem? I mean, buffer gets on an INSERT means there is a heap of buffer reads happening. I can only relate that to lots of index blocks that need to be changed as a result of the INSERT. Abstracting of course from other obvious things like triggers, of which we know nothing about.

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed Nov 12 2003 - 20:10:43 CST