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.
Small blocksizes use less redo log space for the update of a single row when the tablespace is in backup mode.
Large blocksizes require that larger amounts of data be transferred to and from disk, and may be less I/O efficient as a result. However, modern controllers with disk caching can often negate this fact.
Large blocksizes hold more rows of data per block, and as a result, less overhead is required.
Large blocksizes require fewer blocks to be read in order to return all data for a query.
Large blocksizes require more redo log space for the update of a single row when the tablespace is in backup mode.
While every database and application environment is different, we can make the following general recommendations for choosing a database blocksize:
Choose a small blocksize (2048 or 4096) for transaction systems where there are frequent queries, inserts, and updates that involve a single row.
Choose a large blocksize (8192 or larger) for data warehouses and other large database applications where most data is bulk loaded, there are few updates, and most queries return multiple rows or involve full table scans.