Choosing
a Database Block Size (DB_BLOCK_SIZE) Parameter
Bigger
blocks means more data transfer per I/O call. This usually leads to an
increase in performance since the cost of I/O setup (disk seek + rotation,
data transfer) makes up the majority of the cost of an I/O. The performance
increase does not benefit just the data blocks. A bigger block size means
more space for key storage in the branch nodes of B-tree indexes, which
reduces index height and improves the performance of indexed queries.
But,
if you have a high transactional concurrency to a segment, using bigger
blocks will increase the transactional concurrency to the individual data
blocks. You are storing more data in each block and increasing the number
of transactions accessing those blocks concurrently. If you have a very
high number of concurrent users and you increase the database block size
without taking INITRANS
and MAXTRANS
into consideration, you may cause performance problems.
In
addition, Oracle has a tendency to waste memory when it writes undo into
rollback segment blocks. If you have large database block sizes, your undo
per transaction is small and your transaction concurrency is high (two
or more transactions per rollback segment), you may cause transaction slowdowns
in highly volatile OLTP environments.
Recommendations:
Use a
DB_BLOCK_SIZE
of 2048 only in special circumstances. A neat trick is to use a 2K block
size to execute stress tests in which you are trying to drive server workload
artificially high so you can analyze the bottlenecks less expensively (i.e.,
without generating a lot of test data and test transactions)
Use DB_BLOCK_SIZE
of 8192 for most transactional processing systems. This represents a good
balance between advantages and the disadvantage for undo segments
Use DB_BLOCK_SIZE
of larger than 8KB for OLTP systems in which your data structures drive
the block size to a naturally larger size. If your most popular tables
have row sizes that are greater than 8KB, consider using a larger block
size to decrease the costs of accessing a single row
Use DB_BLOCK_SIZE
of larger values than 8KB for systems where undo generation isn't a meaningful
part of your workload (i.e., data warehouses, decision support systems).
In addition, data warehouses, data marts and decision support systems access
greater amounts of data to provide users with the information they require
to make business decisions. You can reduce I/O by storing more data in
each block
