Hardware

Creating tablespaces with multiple block sizes

Optimizing database I/O is a DBA's most important performance tuning activity. Follow these steps to maximize Oracle9i database performance through the use of multiple block sizes.


Oracle9i supports multiple data buffer sizes, which you can use to segment and partition disk I/O patterns within the database. Oracle9i 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 Oracle9i, 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 Oracle9i 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;
 
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 Oracle9i 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 Oracle9i 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 Oracle9i 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
TABLESPACE_NAME BLOCK_SIZE CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
SYSTEM 4,096 PERMANENT DICTIONARY USER MANUAL
UNDOTBS 4,096 UNDO LOCAL SYSTEM MANUAL
CWMLITE 4,096 PERMANENT LOCAL SYSTEM MANUAL
DRSYS 4,096 PERMANENT LOCAL SYSTEM MANUAL
EXAMPLE 4,096 PERMANENT LOCAL SYSTEM MANUAL
INDX 4,096 PERMANENT LOCAL SYSTEM MANUAL
TEMP 4,096 TEMPORARY LOCAL UNIFORM MANUAL
TOOLS 4,096 PERMANENT LOCAL SYSTEM MANUAL
USERS 4,096 PERMANENT LOCAL SYSTEM MANUAL
TS_16K 16,384 PERMANENT LOCAL SYSTEM AUTO

Now that we have the 16-KB tablespace, we need a mechanism to move our Oracle tables and indexes into the new tablespace. Oracle9i 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 Oracle9i 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 Oracle9i
Oracle9i 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. Oracle9i'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.

 

Editor's Picks