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:

1. Related parameter: DB_BLOCK_BUFFERS

2. Oracle recommends that this parameter be set to a numeric power of 2 when the database is created. eg: 2048, 4096 , 8192 or 16384. The best setting of the parameter DB_BLOCK_SIZE depends on the type of data you are looking at, the application and implementation. This parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. DSS (data warehouse) database environments tend to benefit from larger block size values.

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 O/S blocksize. It is not sensible to have he size SMALLER than the OS blocksize as a single read will actually read in 'OS block size bytes' even if only part of this is passed on to Oracle.

2). Balance with application data: 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. Larger blocks mean more rows cached for the same number of DB_BLOCK_BUFFERS (taking up more memory). If your application has LONG row lengths choosing a larger blocksize may allow the entire row to fit in a single DB block. This reduces the overhead associated with chained rows. Be careful here because INSERTS will always try to fit a row entirely into a block rather than chaining it across blocks so the extra space from a larger block size may be wasted.

3). Index Branches: Larger oracle block sizes typically give fewer index levels and hence improved index access times to data. This is one of the major benefits of a larger block size.

4). Locking/Block Contention: With larger blocks processes are more likely to want access to the same block hence potentially increasing block contention. Use larger values for INITRANS / MAXTRANS to help safeguard against this.

3. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘DB_BLOCK_SIZE’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
db_block_size	16384	FALSE	FALSE	FALSE	FALSE	FALSE	Size of database block in bytes

Oracle initializatoin parameters