Row Migration & Row Chaining — Understand Causes, Detection, and Remedies in One Go (Oracle)

To solve database performance issues, you must understand row migration and row chaining. This article digs into both concepts with explanations you can put into practice right away, plus concrete examples for beginners.

1. What are Row Migration and Row Chaining?

In Oracle Database, a table row normally fits in a single data block. Problems arise under the following conditions:

Row Migration
Occurs when a row is updated, becomes larger, and no longer fits in its original block.

Row Chaining
Occurs when a row never fit in a single block from the beginning.

Both issues can degrade query performance.

2. Problems Caused by Row Migration and Row Chaining

Row Migration — Why it hurts

• Additional I/O: The database must follow a pointer from the original block to the new block.
• Random access delays: Following that pointer slows retrieval.

Row Chaining — Why it hurts

• Multi-block access: The row spans multiple blocks, increasing I/O.
• Query-wide slowdown: The impact is especially visible during full table scans.

3. Row Migration: Concrete Example and Mechanics

Row migration occurs when a row is updated and can no longer fit in its original block.

Example: Flow of Row Migration

1. Initial state:
   The row is stored in Block A, which still has free space.

[Block A]
+---------------------+
| Row Data           | Free Space |
+---------------------+

2. Update operation:
   After the update the row is larger and no longer fits in the free space.

[Block A]
+---------------------+
| Pointer            | <-- Pointer to migrated row
+---------------------+

[Block B]
+---------------------+
| Updated Row Data    |
+---------------------+

Points

• The row is referenced indirectly via a pointer.
• Common on tables with high update frequency.

4. Row Chaining: Concrete Example and Mechanics

Row chaining occurs when a row is too large to fit into one block from the start.

Example: Flow of Row Chaining

Initial state:
The row is too large and gets split across multiple blocks.

[Block A]
+---------------------+
| Part of Row Data    |
+---------------------+

[Block B]
+---------------------+
| Remaining Row Data  |
+---------------------+

Points

• Likely with LOB data and long strings.
• Especially harmful to performance during full scans.

5. How to Diagnose the Problem

Use the following SQL to identify row migration and row chaining.

ANALYZE TABLE employees LIST CHAINED ROWS;

Steps

1. Run the command above.
2. Results are stored in the CHAINED_ROWS table.
3. Review rows with issues.

Example
SELECT * FROM CHAINED_ROWS;

6. Practical Approaches to Prevention

1. Set PCTFREE appropriately
   Reserve free space in each block to prevent migration.

CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_description VARCHAR2(1000)
) PCTFREE 20;

Recommendation: 15–30% for frequently updated tables.

2. Tune block size
   Choose a suitable block size to reduce chaining.

• Small data: use a smaller block size.
• Large data: use a block size of 16 KB or larger.

3. Review table design
   Place long strings and LOBs in separate LOB segments.
4. Monitor and maintain
   Run ANALYZE TABLE regularly to check for migration and chaining.

7. How to Fix It (Example) and a Success Pattern

Example — Eliminating row migration

— Verify the problem
ANALYZE TABLE employees LIST CHAINED ROWS;

— Reorganize the table
CREATE TABLE new_employees AS SELECT * FROM employees;
DROP TABLE employees;
RENAME new_employees TO employees;

— Recreate with PCTFREE set
ALTER TABLE employees PCTFREE 20;

— Verify again
ANALYZE TABLE employees LIST CHAINED ROWS;

8. Summary

Row migration and row chaining are preventable with the right design and settings. Put the following into practice:

Key Points

• Prevent migration: set PCTFREE based on update patterns.
• Prevent chaining: choose data types wisely and optimize block size.
• Monitor regularly: detect early and fix promptly.

Checklist

Item	Status	Notes
Checked PCTFREE settings?	✅	Set based on update frequency
Optimized block size?	✅	Sized for large data
Performing regular monitoring?	✅	Periodically list candidates

Build a database that stays performant by managing row migration and row chaining!

[Reference]
Oracle Database Database Performance Tuning Guide, 19c

A Complete Guide to Creating Tables in Oracle Database [CREATE TABLE Explained]

Creating tables is one of the most fundamental skills when working with Oracle Database.This article provides a thorough...

oracle-master00000.com

2025.07.30