Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Choosing a Database Block Size

Configuring the Oracle database to use bigger blocks often leads to an increase in performance since bigger blocks allow more data to be transferred per I/O call  (the database block is the unit of I/O for the database engine).   Larger blocks sizes also allow more key values to be stored in B-tree index blocks, which reduces the index’s height and improves the performance of SQL statements that use the index structures.

Since you are storing more data per block, bigger blocks may increase the number of transactions that access data and index blocks concurrently.   If you have a very high number of concurrent users, you may need to adjust the initrans and maxtrans parameters for data objects that have a higher than normal transactional concurrency. 

A few quick thoughts on database block sizes:

• A block size of 2048 used to be the preferred database block size.  This was before the era of high-speed disk drives and controllers.  Now that more data can be transferred more quickly, 2048 has been replaced with 8192.
   

• A database block size of 8192 is currently the recommended block size for most database configurations.
   

• Use database block sizes of 16K and 32K for applications that have row sizes greater than 8K.  If the application data has row sizes that are greater than 8K, using 16K or 32K block sizes allows more rows to be stored per block and decreases the I/O costs of accessing a single or multiple row(s).
   

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.