Re: Schema Placement for Purchased Apps

"Gabriel Gonzalez" <no-spam_at_no-spam.com> wrote in message news:nAqdnXD1pf0LzgijXTWcow_at_giganews.com...
> > Block size is a function of filesystem, not whim, application type, or
> > anything else.
>
> I'll agree and disagree with you there...
>
> I agree because block size should not be set on a whim, nor should it be
an
> unfounded guess.
>
> I disagree because, for example, on a file system that has a 64k physical
> block size, you could set Oracle's block size to (again, for example):
>
> 1) 8K with block multi-read of 8,
>
> 2) 2k with block multi-read of 32,
>
> 3) 64k with block multi-read of 1.
>

No you couldn't. Well, I mean of course that you could, but you shouldn't. Please see Richard's comments and please also visit Steve's website (www.ixora.com.au). You must match the file system buffer size *exactly* (assuming you have one, which you won't on raw or on NT). Anything else will incurr performance penalties of one sort or another: Steve does a very nice article on what happens if you are bigger than the file system buffer, and on what happens if you are smaller than it.

Also read his article on 'Why a Large Database Block Size', and you'll see that the principle is: the bigger the block, the better index performance will be. So whilst a big blocksize will help Warehouse full table scans, it also means it will help OLTP index accesses. So, subject to matching the file system buffer exactly, bigger blocks are better, regardless of the nature of the app. Of course, big blocks in an OLTP environment can lead to contention issues... but contention issues will ordinarily be diagnosable and fixable by means other than fiddling with the block size (such as increasing PCTFREE or INITRANS, implementing hash clustering or hash partitioning and so on).

Net result, if you have a file system buffer, match it exactly. Otherwise, bigger is usually better, whatever the nature of your application.

Regards
HJR Received on Thu Apr 10 2003 - 09:31:25 CDT