All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at email@example.com .
Thursday, April 7, 2011
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_sizeMaximum Datafile Size
32 kb 128G
Beyond this, i found a very good post by Charles Hooper about the optimal size of the data block size.