Choosing Data Block Size

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/iodesign.htm#i19636

A block size of 8K is optimal for most systems. However, OLTP systems occasionally use smaller block sizes and DSS systems occasionally use larger block sizes. This section discusses considerations when choosing database block size for optimal performance.

Note:

The use of multiple block sizes in a single database instance is not encouraged because of manageability issues.

1 Reads

Regardless of the size of the data, the goal is to minimize the number of reads required to retrieve the desired data.

·         If the rows are small and access is predominantly random, then choose a smaller block size.

·         If the rows are small and access is predominantly sequential, then choose a larger block size.

·         If the rows are small and access is both random and sequential, then it might be effective to choose a larger block size.

·         If the rows are large, such as rows containing large object (LOB) data, then choose a larger block size.

2 Writes

For high-concurrency OLTP systems, consider appropriate values for INITRANS, MAXTRANS, and FREELISTS when using a larger block size. These parameters affect the degree of update concurrency allowed within a block. However, you do not need to specify the value for FREELISTS when using automatic segment-space management.

If you are uncertain about which block size to choose, then try a database block size of 8 KB for most systems that process a large number of transactions. This represents a good compromise and is usually effective. Only systems processing LOB data need more than 8 KB.