Problem solve Get help with specific problems with your technologies, process and projects.

Determining optimal block sizes

How can I determine the optimal database block size if I know my average rowsize is 16KB and OS block size is 32KB? Does it depend upon an application like DSS or OLTP?

Your database block size should be an integer multiple of the OS block size. If your db_block_size is set to 16K and the OS block size is 32K, then when Oracle makes two calls to consecutive 16K blocks, the OS will have to perform two 32K reads. The OS will be working twice as hard in this example. So if your OS block size is truly 32K, then make your DB_BLOCK_SIZE=32K as well. Since your average row size is 16K, then on average, you will have two rows of data in one database block.

Your block size of the database is also dependent on the application. But your average row size tells me that your data is large, therefore a larger block size is in order. If you used 4K blocks for the database, then the average row would be spread across 4 blocks.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.