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.

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
 

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;
 
FILE_NAME                                                                      
-----------------------------------------------------------
C:\ORACLE\ORADATA\DIOGENES\SYSTEM01.DBF                                        
C:\ORACLE\ORADATA\DIOGENES\UNDOTBS01.DBF                                       
C:\ORACLE\ORADATA\DIOGENES\CWMLITE01.DBF                                       
C:\ORACLE\ORADATA\DIOGENES\DRSYS01.DBF                                         
C:\ORACLE\ORADATA\DIOGENES\EXAMPLE01.DBF                                       
C:\ORACLE\ORADATA\DIOGENES\INDX01.DBF                                          
C:\ORACLE\ORADATA\DIOGENES\TOOLS01.DBF                                         
C:\ORACLE\ORADATA\DIOGENES\USERS01.DBF                                         

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
   ts_16k
datafile
   'c:\oracle\oradata\diogenes\16k_ts.dbf'
size
   10m
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

select
   tablespace_name,
   block_size,
   contents,
   extent_management,
   allocation_type,
   segment_space_management
from
   dba_tablespaces;

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.