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:
- Define the data buffers for multiple block sizes in the RAM of the Oracle SGA.
- Define tablespaces and data files with block sizes to match the instantiated block sizes in the RAM data buffers.
- 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;
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.
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:
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
The results in Table B show that the new tablespace exists with a 16-KB block size.
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:
- It reads the existing index.
- It rebuilds a new b-tree in the target tablespace using temporary segments.
- 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.