Re: db block size, too big wasting buffer?

"sg" <s4v4g3_at_europe.com> wrote in message news:3D18ACAA.7050308_at_europe.com...
> hi
>
> when we read a row from a segment do we read the whole block which that
> row is located into buffer cache??
>

Yes we do. But that's not a 'waste'. It *would* be a waste if, having read vast amounts of data in which we were not interested into memory, it stayed in memory for ever more, untouched and unloved.

But that's not how caches work. There's the small matter of the LRU list, which means that if some of the data *you* didn't want to read is found useful by someone else, it will stay in the cache, but if no-one is interested in it, it ages out, and the memory freed for different data.

With a big block, the probability that someone else will find the block you caused to be read into memory goes up ('cos it's got more data in it). So, whilst *you* may have done extra work with the initial read of the block, in doing so, you've helped many more people coming after you.

That all ignores the other physical issue. If you read less than the optimum amount of data into the cache (ie, not matching the file system buffer), you aren't "saving" memory, but increasing I/O work on the system.

Regards
HJR
> Nuno Souto wrote:
>
> > In article <3D175E5F.6090202_at_europe.com>, you said (and I quote):
> >
> >>Hi
> >>
> >>I am about to create a OLTP database of 16KB in 8.1.7, I have always
> >>read that dont make the db block size too big or we would be wasting
> >>data buffer. Is this true?
> >>
> >
> > No. It is most definitely not true. You cannot "waste" data buffer by
> > changing block sizes. The two things are not related.
> >
> > What you can have is a situation where you may be doing I/O in bigger
> > "chunks" (buffers) than you really need. That's all.
> >
> > But even then, you are not "wasting data buffers". No way.
> >
> >
> >
> >>Some say that db block size is another Oracle Myth, it does not really
> >>matter, is this a correct statement?
> >>
> >>
> >>
> >
> > It does matter and a lot. But not for the "traditional" reasons. In
9i,
> > you have the option of multiple buffer sizes in the same database. In
> > 8i, you don't. So consider looking at 9i if at all possible.
> >
> > As for 8i, 16K is a little above what I feel comfortable with from past
> > experience. I'd stick to 8K across the board (that's Unix and NT).
Even
> > though others here have had good experiences with 16K in NT, I've always
> > had problems.
> >
> > 8K seems to be the sweet spot for database block size at 8i with the
> > largest sample of "types of database", "types of OS" and "types of
> > application".
> >
> > Having said that, there is always the exception. It may well be that
the
> > db you're after and its load characteristics would warrant a smaller
> > block size. Very hard to say without more data.
> >
> >
>
Received on Tue Jun 25 2002 - 14:22:35 CDT