Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

Oracle and multiple block sizes

For the Oracle administrator, multiple blocksizes in Oracle are extremely important and exciting.  For the first time, you will be able to customize your data buffer sizes according to the specific needs of your database.  Prior to Oracle, your entire Oracle database had to have a single block size and this block size was determined at the time that the database was created. 

With Oracle, we can define tablespaces with block sizes of 2K, 4K, 8K, 16K and 32K, and assign tables and indexes to the best block size to minimize I/O and best manage wasted space in our data buffers.  When we combine the new data buffers for these block sizes, we get a total of seven separate and distinct data buffers to segregate our incoming table and index rows.

As we know, disk I/O is the single most expensive operation within an Oracle database, and multiple block sizes give us a powerful new tool to manage disk I/O with more power than ever before.

Until RAM memory becomes cheap enough that we can cache our whole database, we need to manage the RAM that we allocate to our data buffers. The allocation of tables and indexes according to block sizes is a balancing act.

If we allocate the data blocks too large, then we waste valuable data buffer space holding row data that Oracle will never reference.  If we allocate the data block too small, and Oracle will have to do more disk I/O to satisfy a request. Here are some general rules for allocating data block sizes:

  1. Segregate large-table full-table scans - Tables that experience large-table full-table scans will benefit from the largest supported block size and should be placed in a tablespace with your largest block size.

  2. Set db_recycle_cache_size carefully - If you are not setting db_cache_size to the largest supported block size for your server, you should not use the db_recycle_cache_size parameter.  Instead, you will want to create a db_32k_cache_size (or whatever your max is), and assign all tables that experience frequent large-table full-table scans to the largest buffer cache in your database.

  3. The Data Dictionary cache uses the default block size - You should ensure that the data dictionary (e.g. your SYSTEM tablespace) is always fully cached in a data buffer pool. Remember, the block size of the data dictionary is not as important as ensuring that the data buffer associated with the SYSTEM tablespace has enough RAM to fully-cache all data dictionary blocks.

  4. Indexes want large block sizes - Indexes will always favor the largest supported blocksize. You want to be able to retrieve as many index nodes as possible in a single I/O, especially for SQL that performs index range scans.  Hence, all indexes should reside in tablespaces with a 32k block size.

  5.  Average row length determines block size - The block size for a tables’ tablespace should always be greater than the average row length for the table (dba_tables.avg_row_len).  Not it is smaller than the average row length, rows chaining occurs and excessive disk I/O is incurred.

  6.  Use large blocks for data sorting – Your TEMP tablespace will benefit from the largest supported blocksize. This allows disk sorting to happen in large blocks with a minimum of disk I/O.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.