DB_BLOCK_SIZE

Property	Description
Parameter type	Integer
Default value	8192
Modifiable	No
Range of values	2048 to 32768, but your operating system may have a narrower range
Basic	Yes
Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

Caution:

Set this parameter at the time of database creation. Do not alter it afterward.

DB_BLOCK_SIZE specifies (in bytes) the size of Oracle database blocks. Typical values are 4096 and 8192. The value of this parameter must be a multiple of the physical block size at the device level.

The value for DB_BLOCK_SIZE in effect at the time you create the database determines the size of the blocks. The value must remain set to its initial value.

For Real Application Clusters, this parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. Oracle uses one database block for each freelist group. Decision support system (DSS) and data warehouse database environments tend to benefit from larger block size values.

Note:

Guidelines for DB_BLOCK_SIZE (Oracle Metalink note 34020.1)

The setting of the parameter DB_BLOCK_SIZE depends on the type of data you are looking at, the application and implementation. The main considerations are as follows:

1. Balance with Operating System Block Size.

- For good performance, Oracle Block size should be made equal to or a multiple of the operating system blocksize.

- If your rows are small and you use a large blocksize, when you fetch a block you may get lots of rows that you are (probably) not interested in. The operating system has to do more work to get the row(s) that you are interested in.

- Larger blocks also mean more rows cached for the same number of DB_BLOCK_BUFFERS (taking up more memory).

2. Larger Oracle blocksizes may give better index balancing with more rows in each branch.

3. Remember that with more processes looking at the same block you are more likely to get block contention.

4. If your rows are comparatively large then you may need a large blocksize to (possibly) prevent chaining.

Oracle initializatoin parameters