How to Avoid Row Chaining in a Database

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Dec 21, 2017

How to Avoid Row Chaining in a Database

We encounter row chaining when the size of the row is larger than the size of the database block used for storing it. In this situation, the row is split across more than one database block. When this row needs to be accessed, more than one database blocks are traversed, resulting in greater I/O.

Introduction:

Row chaining can be defined as a bottleneck causing disruption in the database performance. We should check and avoid row chaining as much possible. In this blog I am going to discuss row chaining, how to identify it, and how to remove or avoid it completely.

Normally, we encounter row chaining when the size of the row is larger than the size of the database block used for storing it. In this situation, the row is split across more than one database block. When this row needs to be accessed, more than one database blocks are traversed, resulting in greater I/O.

row_chaining.png

Image Source: Oracle Press books

Basic assumption to test the scenario:

Before we start, we need to alter an initialization parameter. Assuming the default block size is 8KB.

ALTER SYSTEM SET DB_16K_CACHE_SIZE =16M SCOPE=BOTH;

We need to set this parameter to allocate a memory buffer dedicated to store database blocks of different sizes.

We can understand row chaining by following below steps:

  1. Create the table BIG_ROWS:

CREATE TABLE HR.BIG_ROWS (

                   Id number not null,

                   Field1 char(2000) default ‘A’ not null,

Field2 char(2000) default ‘B’ not null,

                   Field3 char(2000) default ‘B’ not null,

                   Field4 char(2000) default ‘D’ not null,

                   Field5 char(2000) default ‘E’ not null,

                   Constraint pk_big_rows primary key (id));

  1. Populate the table:

INSERT INTO HR.BIG_ROWS (ID) SELECT ROWNUM FROM SYS.DBA_OBJECTS WHERE ROWNUM<101;

  1. Analyze the table to refresh the statistics:

ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;

  1. Verify if there are chained rows:

SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER=’HR’ AND TBALE_NAME=’BIG_ROWS’;

  1. Below is the resultant screenshot:
    row_chaining_1.png
  1. Create a tablespace with a different block size:

CREATE TABLESPACE TS_16K BLOCKSIZE 16K DATAFILE ‘TS_16K.DBF’ SIZE 30M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

  1. Move the table BIG_ROWS to the tablespace just created:

ALTER TABLE HR.BIG_ROWS MOVE TABLESPACE TS_16K;

  1. Rebuild the indexes as they are unusable after the move;

ALTER INDEX HR.PK_BIG_ROWS REBUILD;

  1. Analyze the table to refresh the statistics;

ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;

Validate if row chain still exists or not with Screenshot

SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER=’HR’ AND TBALE_NAME=’BIG_ROWS’;

row_chaining_2.png

With steps discussed above we created a new tablespace using larger block size and moved the table into a newly created tablespace to gather the statistics. Once we are done with moving the table, you need to do index rebuild. 

The question that arises is that why do we have to do index rebuild post table move?

The answer is simple, an index contains the ROWIDs of the table rows, and the ROWIDs identify the position of the row, made-up by the objects, the datafile, the block number, and the slot (row) number. When we move a table, the datafile and the block number changes, so the indexes need to be rebuilt. 

Row chaining leads to poor performance because accessing a row in the database will have to read more than once DB block, even when we access the table by the index lookup. When we plan to introduce different block sizes in the database, we need to keep in mind the pros and cons of a larger block size. The larger the block size, the more likely there will be contention issues on the database block. 

There are also advantages in using multiple block size which are as follows:

  • Contention reduction
  • Reduced chaining
  • Faster update
  • Reduced pinging
  • Less disk space waste
  • Less RAM Waste
  • Minimum redo-generation
  • Faster scan 

A few points to keep in mind as chained rows affect index reads and full table scans:

  • Row chaining is typically caused by INSERT operation.
  • SQL statements which are creating/querying these chained rows will degrade the performance due to more I/O work.
  • To diagnose chained/migrated rows use ANALYZE command, query V$SYSSTAT view
  • To remove chained rows, use higher PCTUSED/PCTFREE using ALTER TABLE MOVE.

Conclusion:

I/O is the main culprit for Oracle database performance issues. If a database is having row chaining, Oracle process needs to traverse more database blocks to complete the job work which in turns degrades the database performance. By removing row chaining we can reduce the number of blocks that are needed to traverse by the Oracle processes.

Leverage Our Extensive Database Expertise with DBA Services

The Developer Blog