Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 






Many benefits to multiple blocksizes

Oracle Tips by Burleson Consulting
September 23, 2002

Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster
Important 2015 Update: For the latest consensus on using multiple blocksizes in Oracle, see The latest consensus on multiple blocksizes.

Oracle blocksize and performance

Tables containing small rows that are accessed randomly should be placed into tablespaces with smaller block sizes. This way, more of the buffer RAM remains available to store rows from other tables that are referenced frequently.

Larger block sizes are suitable for indexes that receive full scans or fast-full scans, row-ordered tables, single-table clusters, and tables with frequent full-table scans. In this way, a single I/O will retrieve many related rows, and future requests for related rows will already be available in the data buffer.
Some objects that may benefit from a larger blocksize (16K or 32K) include:

   - Most indexes (because of the serial nature of index range scans)
   - Large tables that are the target of full table scans
   - Tables with large object (BLOB, CLOB, etc.) data
   - Tables with large row sizes that might blossom into chained/migrated rows
   - Temporary tablespaces used for sorting

The simple goal is to maximize the amount of RAM available to the data buffers by setting the block size according to the amount of I/O the table or index sees. Smaller block sizes are appropriate for randomly accessed small rows, while larger blocks are more suitable for rows sequentially accessed.

To illustrate, suppose a query retrieves 100 random 80 byte rows from Oracle. Since the rows are randomly accessed, we can safely assume that no two rows exist on the same block, implying that it is necessary to read 100 blocks to fulfill the task.

If the blocks are sized 16K, the db_16k_cache_size buffer will need 16 MB (16K * 100) of RAM. If the blocks are instead 2K, we only need 2 MB of RAM in the buffer for the 100 I/Os. Using the smaller block size would save 14 MB of RAM for this query alone, RAM that will be available elsewhere to hold other data.

Oracle supports multiple data buffer sizes, which you can use to segment and partition disk I/O patterns within the database. Oracle also allows segregated RAM memory regions for multiple block sizes, giving you complete control over disk I/O patterns. Let's take a closer look at how you can create data files with different block sizes and move Oracle tables and indexes into partitioned RAM data buffers.

To minimize disk I/O activity, DBAs must be aware of the specific access patterns that are associated with different tables and indexes inside their databases. For example, Oracle indexes and Oracle large object data types (BLOB, CLOB) perform best with large block sizes; random access of small data rows do best with small block sizes.

Once you identify the tables and indexes that will benefit from a specific block size, you should take the following three steps to move the table or index:

  1. Define the data buffers for multiple block sizes in the RAM of the Oracle SGA.
  2. Define tablespaces and data files with block sizes to match the instantiated block sizes in the RAM data buffers.
  3. Use Oracle alter table and alter index commands to move tables and indexes into these new tablespaces.

As a simple example, let's assume that we need to move small OLTP table rows into a 2-KB block size while moving our indexes into tablespaces with 32-KB block sizes.

Allocating many RAM data buffers

In Oracle, you can start using new RAM buffers at any time. However, when you add space to a new data buffer, you must make sure that RAM is available within the Oracle SGA. Otherwise, you'll get this error:

SQL> alter system set db_16k_cache_size=10m;
alter system set db_16k_cache_size=10m
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

To get around this problem, you can reduce the size of an existing RAM region or tell Oracle to increase the SGA size. Increasing the total size of the RAM SGA is accomplished with this simple command:

alter system set sga_max_size=130m scope=spfile;

Now that you have room to add frames to a new pool, add a new data buffer, and issue an alter system command, like so:
alter system set db_16k_cache_size=1028576;
System Altered.

You can verify that this new buffer exists by viewing the current Oracle parameters with this command:
SQL> show parameters cache_size

Table A shows the results.
Table A

Name Type Value
db_16k_cache_size big integer 1048576
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_cache_size big integer 33554432
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0

Now that we have a data buffer, we're ready to allocate tablespaces that fit into our new 16-KB block size.

Allocate a tablespace of a specific block size

In the example below, we'll create a 16-KB tablespace to accommodate our indexes. If you're not using Oracle Managed Files (OMF), start by checking the location of the existing data files on the database. We'll make sure that our new tablespace data file goes into this tablespace.
SQL> select file_name from dba_data_files;

Now that we know the file directory, we'll create a tablespace with 16-KB data blocks, using a locally managed tablespace with bitmap freelists:
create tablespace
blocksize 16k
extent management local
segment space management auto;

Even though Oracle supports 23-KB blocks, some OS platforms, including Windows, have a maximum block size of 16 KB. However, virtually all UNIX and mainframe platforms support 32-KB block sizes.

Also note the use of local tablespace management, as opposed to dictionary-managed tablespaces, and the new Oracle automatic bitmap freelists. Locally managed tablespaces (LMTs) greatly reduce fragmentation within the tablespaces, and bitmap freelists remove segment header wait conditions (buffer busy waits) by providing bitmap freelists to replace Oracle8i's outmoded link-list freelists. These new Oracle features, which are becoming the industry standard, reduce the frequency of tablespace and object reorganizations and remove the onerous problem of defining multiple freelists to reduce buffer busy waits for segments within the tablespace.

Now that we've created our tablespace, we can verify our results by running this simple query:
col tablespace_name format a15
col block_size format 99,999


The results in Table B show that the new tablespace exists with a 16-KB block size.
Table B


Now that we have the 16-KB tablespace, we need a mechanism to move our Oracle tables and indexes into the new tablespace. Oracle has online commands that quickly move and rebuild database objects as they dynamically transfer them from one tablespace to another.

Table relocation commands

Oracle provides many methods for relocating Oracle tables:

  • Export/import utilities
  • Create table as select (CTAS)
  • Alter table move

The process of copying a table offers benefits other than just relocating the table to a different block size. These additional benefits are:

  • Freelists are coalesced.
  • Row chaining is eliminated.
  • Rows can be resequenced into primary index order.
  • Table extents can be coalesced.
  • The table is relocated to a tablespace with an optimal block size.

In the following example, we're moving an Oracle table into a 16-KB tablespace.
Alter table customer move tablespace ts_16k;

This command copies the customer table from its existing tablespace into the new tablespace and maintains all index definitions, triggers, and referential integrity constraints that are defined against the table.

Of course, it's not quite that simple. During a table move, Oracle cannot allow any data manipulation language (DML) to occur because the table must be locked against updates. In other words, if it takes an hour to move the target table from the 8-KB tablespace into the 32-KB tablespace, Oracle will prevent anyone from updating the table for the entire time that it takes to copy it.

Oracle gets around this problem with the Oracle automatic table reorganization utility, which can move large Oracle tables from one tablespace to another while updates continue unimpeded. Internally, Oracle keeps the updates in a special snapshot structure and then reapplies the updates after the table has been copied into the new tablespace.

Index relocation in Oracle

Oracle also provides several mechanisms for relocating and rebuilding Oracle indexes. Oracle indexes can be dropped and recreated in the new tablespace, or you can use the alter index rebuild command to relocate the index into the 32-KB tablespace.
alter index customer_pk_idx rebuild tablespace ts_16k;

Internally, the alter index rebuild command rebalances the index b-tree, removes deleted leaf nodes, and optimizes the internal index nodes. Externally, an index rebuild performs the following steps:

  1. It reads the existing index.
  2. It rebuilds a new b-tree in the target tablespace using temporary segments.
  3. Upon success of the copy, it moves the temporary segments into the index segment and the old index nodes into temporary segments. The temporary segments will be removed by an Oracle background process.

Conclusion and recommendations

Since IBM's IMS database was introduced in the 1960s, DBAs have struggled to manage performance-constraining disk I/O. A database management system must allow DBAs to partition data according to known data access patterns and signatures. Oracle's multiple block size feature is one of the most exciting Oracle features in many years—Oracle DBAs now have the tools to manage their disk I/O subsystem.




Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.