Re: Oracle block size - OS block size

db_block size should be determined based on your application type. If you are 100% OLTP you should use 2K. If you are purely data warehouse maybe you should use 32K. If you are in between, locate where you are in this scale and determine db_block_size appropriately.

It is not that the bigger is better. I have databases running on 2K and I also have databases running on 16K.

You will get more contentions if the blocks are too large. Typical ones are buffer busy waits and write complete waits. The theory is simple, more transactions are after the same block because it contains more rows.

With larger blocks, you also waste more memory, more rollback segments.. etc as everything is stored in the unit of blocks in buffer cache and rbs.

You get major advantages in full table scans if you use large blocks. Other advantages are reduced overhead (as each block has header and footer) and reduced B-tree level.

Some of the disadvantages can be offset by other parameters like db_file_multiblock_read_count. As long as you do not exceed your OS max physical read size, you can still make your full table scan with 2K efficient.

I think most people choose 8K feeling it is safe. But this does not mean 2K is unrealistic.

-Bass Chorng Received on Mon Jun 18 2001 - 14:41:43 CDT