Oracle Database Administration: The Essential Refe

Choosing a blocksize

While Oracle has defined a default blocksize for every operating system environment (often 2048), this blocksize is often not correct for the database being created. Few characteristics of the database are as important to overall performance as the database blocksize. The blocksize is specified in the INIT.ORA file with the DB_BLOCK_SIZE parameter, and once a database is created with a particular blocksize, it cannot be changed. It is, therefore, important to consider the options for database blocksize and define one that will provide good performance for your site. A few factors to consider are the following:

• The blocksize must be at least as big as a single block or disk sector on the host hardware.

• The blocksize should be a multiple of the host operating system and/or hardware blocksize. For example, many systems write 512-byte blocks to disk, so the blocksize should be a multiple of 512.

• The blocksize should not be bigger than the largest amount of data that the host operating system and/or hardware can read or write in one operation. For example, an operating system may be able to transfer 8192 bytes in a single operation to disk.

• Small blocksizes require less data to be transferred to and from disk, and may result in better I/O operation.

• Small blocksizes hold fewer rows of data and require more overhead (see Chapter 11, for detailed information on block structures).

• Small blocksizes may require more blocks to be read in order to return all data for a query. ...