Row chaining happens when updates occur on rows in database blocks which increase the row length to a point where it cannot fit in the same block. Another data block is used to contain the overlap, and this block is chained to the original. Why this matters to the I/O activity within Oracle is that effectively two I/Os are required to perform the same amount of work as a single I/O before chaining occurred.

The Oracle script utilchain.sql is the first stop when tackling chained rows. Found in the ($ORACLE_HOME)/rdbms/admin directory, this can be run to create the CHAINED_ROWS table. Once the empty table is created (use desc to see the field names) the command:

analyze table (tablename) list chained rows

can be run to populate the table with information from whatever you specify as (tablename).

Next, select * from chained_rows to get a listing of all the chained rows in the specified table.

Once identified, the chaining can be eliminated by creating a copy table (create table ... as select ...), deleting the rows from the original table, then inserting them from the copy table.

Regular pruning of chained rows will improve the performance of your I/O subsystem.