Choosing the Proper Database Block Size

Choice  of  an  appropriate  Block size for a database  is  often  one of  the  most  important  decisions  that can  affect  the  performance of  the  system. Choosing  the  proper  block size requires a very  good  understanding of the application environment .

For example , in a particular scenario , When the 16k instance runs an 850,000 row update (no where clause), it finishes in 45 minutes.  When the 4k instance runs an 850,000 row update (no where clause), it finishes in 2.2 minutes. The change in block size caused the job to run TWENTY TIMES FASTER. So choosing a proper data block size is very necessary task

The proper choice of database block size depends to a large extent on the type of data involved and the application. The following guidelines can help in this process:

1.) Operating system block size :   Good performance can be achieved by ensuring that the Oracle block size is equal to or a multiple of the operating system block size. If this is not the case, the OS may end up performing extra reads and writes during the processing of Oracle blocks, resulting in wasted CPU cycles.

2.) Size of buffer cache used :   Larger database block size means that you are using up more memory for the same number of db_bock_size . This also means that more rows are cached. If our rows are small and we use a large block size, a block fetch will result in a lot of rows being fetched (and we may not be interested in all of them). The end result is that the OS is doing more work to fetch things that we don't need. On the other hand, if the row length is large, a large block size may prevent chaining.

3.) Balancing of index branches :  Large Oracle block sizes can result in better index balancing, as there are more rows in each branch. A bigger block size means more space for key storage in the branch nodes of B-tree indexes, which reduces index height and improves the performance of indexed queries.

4.) Datafile size limits :  The  block size leads to the max size of the database and  ultimately result into database size.Below the max size of datafiles based on the database block size.

db_block_size                  Maximum Datafile Size

2kb                                         8G

4kb                                         16G

8kb                                         32G

16kb                                       64G

32 kb                                      128G

Beyond this, i found a very good post by  Charles Hooper  about the optimal size of the data block size.  

Enjoy     :-)