Re: On AIX, blocksize an DataWarehouses

Well, above all, know your system usage and test. There is no "perfect" block size. However, some things to keep in mind while choosing. #1, the impact of greater block sizes is the amount of LIO that occurs. If your data fits in 100 bigger blocks instead of 400 smaller blocks then reading the 100 blocks takes less latching resources and scanning lists, etc... However, the more data packed into a block may lead to block contention if there is a lot of DML going on. Several people wanting row locks will end up creating a bunch of ITL slots that will always have to be scanned and buffer busy waits can occur, etc....

Typically I go above 8k for more read intensive databases. If changes only occur in batches within defined windows and the rest of the time you will have lots of FTS and range scanning happening, I lean towards 32k.

As always, test and know how the app will be using the database.

Mike Fullerton

Frank R. wrote:
> Hello everybody,
>
> I'm currently involved in a Siebel Analytics/Datawarehouse deployment.
> The datawarehouse database (9.2.0.8 AIX 5.3) was originally created
> with a 8kb blocksize (standard) which as far as i've read anywhere or
> asked anyone, is quite small, reccommending 32kb instead.
>
> Apart from a stripping strategy between several disks, blocksize seems
> critical in I/O improvement and I need advice on the relationship
> between oracle blocksize and AIX blocksize. ATM, the OS blocksize is
> 4kb but, what would be the perfect size for it ¿32k too? ¿any oracle
> blocksize multiple?
>
> Thanks in advance for your help.
Received on Tue Nov 21 2006 - 11:06:36 CST