Row chaining most often occurs with large rows. Examples include rows that contain a column of data type LONG or LONG RAW, or a row with a huge number of columns. Row chaining in these cases is unavoidable.
Row chaining is affected by the following factors:
- Data block size may be too small to accommodate most of the row length.
- PCTFREE may be too small to allow current rows to grow.
- Average row length is larger than it was used to be.
Table ReorganizationTables with many chained rows may degrade the performance due to data fragmentation, you can perform a table reorganization to reduce it.
Some tables cannot significantly reduce chained rows because of their nature of containing LONG, LONG RAW or with thounsands of columns, etc.
Increase PCTFREEFor a long term, you should raise PCTFREE on specific table, which may improve update performance, because Oracle does not need to chain row pieces as frequently, if ever. (Managing Space for Schema Objects) But be sure that more space is available for later growing before increasing the parameter.
You can use the following statement to move table and change PCTFREE in the same command:
SQL> ALTER TABLE <NAME> MOVE PCTFREE 40;
(See Oracle 12c documentation also : Logical Storage Structures)
For more information about the difference between PCTFREE and PCTUSED, you may refer to my post below:
What is Difference Between PCT_FREE and PCT_USED