Oracle PCTFREE Tuning: Minimizing Row Migration and Row Chaining

PCTFREE is Oracle's primary block-level control for preventing two of the most common causes of unnecessary I/O: row migration and row chaining. It works by reserving a fixed percentage of each data block exclusively for UPDATE-driven row growth, so that when an UPDATE expands a row's length, the row can grow in-place within the original block rather than being displaced to another location.

The trade-off is deliberate. PCTFREE reduces storage density slightly — fewer rows fit in each block when more free space is reserved — but the payoff is fewer extra block reads during subsequent access. A well-tuned PCTFREE value is not about eliminating migration entirely; it is about reducing it to a level that does not materially impact the primary access paths for the table.

PCTFREE remains relevant in both Automatic Segment Space Management (ASSM) and manual freelist environments. PCTUSED and freelist re-link behavior are no longer required in ASSM tablespaces, but PCTFREE continues to govern how much in-block headroom exists for row growth regardless of the space management mode.

Two distinct behaviors arise when a data block lacks sufficient free space to accommodate a row's full size. They share the symptom of extra I/O but have different root causes and different remediation paths.

Row Migration

Row migration occurs when a row originally fits in a block, but a subsequent UPDATE increases its length beyond the remaining free space in that block. Oracle cannot split the row at UPDATE time, so it moves the entire row to a different block with sufficient free space and leaves a forwarding pointer in the original block's row directory slot.

The performance consequence is an extra block read on every subsequent access to that row. Oracle must first read the original block to find the forwarding pointer, then follow the pointer to read the destination block. For a table with high rates of row migration, this doubles the logical read cost for affected rows and increases physical I/O proportionally. Row migration is caused by insufficient PCTFREE and is fully correctable by increasing PCTFREE and reorganizing the segment to rebuild blocks under the new free-space strategy.

Row Chaining

Row chaining occurs when a row is too large to fit in a single block at INSERT time. This happens with very wide rows — tables with many columns or large VARCHAR2 values — or with legacy datatypes such as LONG and LONG RAW that can hold multi-kilobyte values in a single column. Oracle stores the row in multiple pieces across multiple blocks, linking them with chain pointers in the row directory of each block.

Each access to a chained row requires reading all blocks in the chain, multiplying I/O cost by the number of chain segments. Unlike row migration, chaining caused by row width exceeding block size cannot be resolved by PCTFREE tuning alone. The long-term remedy for LONG and LONG RAW columns is to migrate them to LOB datatypes, which store large values out-of-line and eliminate the wide-row pressure that causes structural chaining. See Oracle Partitioned Objects for additional context on restructuring wide tables. For rows that genuinely exceed the block size due to column count and average value length, increasing DB_BLOCK_SIZE in a new tablespace is an option, though it requires data migration.

PCTFREE specifies what percentage of each block Oracle reserves for future row updates. During INSERT operations, Oracle fills a block until the used space reaches (100 - PCTFREE) percent of the block's total capacity. At that point Oracle performs a freelist un-link: the block is removed from the segment's freelist and no further rows can be inserted into it. The reserved PCTFREE percentage remains available exclusively for UPDATE operations that expand existing rows in that block.

Worked example with PCTFREE = 20 and an 8KB block:

• Total usable data space per block (after block overhead): approximately 8,000 bytes
• INSERT threshold: 80% of 8,000 = 6,400 bytes. New rows can be inserted until the block reaches 6,400 bytes used.
• Reserved for updates: 20% of 8,000 = 1,600 bytes per block
• If an UPDATE on a 200-byte row adds 300 bytes (expanding it to 500 bytes), the row grows by 300 bytes. As long as the block's reserved space covers that growth, the row stays in place. If growth exceeds the reserved space, the row migrates.

The correct PCTFREE value therefore depends on the typical row length at INSERT time compared to the typical row length after the full UPDATE lifecycle. A table where rows are inserted complete and rarely updated needs a low PCTFREE. A table where rows start small and grow substantially through UPDATEs needs a higher PCTFREE to match the expected delta.

In ASSM tablespaces, Oracle tracks free space per block using a bitmap rather than a linked freelist. The freelist un-link mechanism is replaced by bitmap state transitions, but PCTFREE still controls the threshold at which a block's bitmap state changes from "available for INSERT" to "reserved for UPDATE only." The fundamental behavior is the same; only the internal bookkeeping mechanism differs.

Use the following rules to choose an initial PCTFREE value, then validate with measurement after a representative workload period.

1. Read-mostly tables with minimal updates: keep PCTFREE near the default of 10. Rows do not typically expand after INSERT, so a high PCTFREE wastes block space and increases the number of blocks Oracle must read to satisfy full-table scans.
2. Rows inserted small then updated large: increase PCTFREE significantly, often to 20 or 30. Avoid the anti-pattern of inserting rows with many NULL columns and later updating most of them through the application lifecycle. When possible, insert complete rows to prevent migration on the very first UPDATE.
3. Variable-length columns (VARCHAR2) that grow over time: analyze the distribution of column lengths at INSERT time and after typical UPDATE workloads. Raise PCTFREE to cover the expected growth for the median row in a single block. Over-reserving for outlier rows is less efficient than accepting occasional migration on the longest rows while protecting the majority.
4. Large or legacy row formats: for tables using LONG or LONG RAW datatypes, some chaining is structurally unavoidable. Plan a migration to LOB datatypes as the correct long-term resolution rather than attempting to eliminate chaining through PCTFREE alone. For tables where average row width approaches the block size, consider whether the table design can be normalized to reduce row width, or whether a larger non-standard block size is warranted.

PCTFREE is specified at CREATE TABLE time or modified with ALTER TABLE. Modifying PCTFREE with ALTER TABLE applies to blocks allocated after the change; existing blocks are not reorganized retroactively. A segment rebuild is required to apply the new free-space strategy to all blocks.

-- Create table with explicit PCTFREE
CREATE TABLE orders (
  order_id     NUMBER PRIMARY KEY,
  created_ts   TIMESTAMP NOT NULL,
  status_code  VARCHAR2(30),
  notes        VARCHAR2(4000)
)
PCTFREE 20;

-- Modify PCTFREE for future block allocations
ALTER TABLE orders PCTFREE 20;

-- Rebuild the table to apply new PCTFREE to all existing blocks
-- (requires exclusive lock; use DBMS_REDEFINITION for online tables)
ALTER TABLE orders MOVE PCTFREE 20;

-- Rebuild indexes after MOVE (ROWIDs change)
ALTER INDEX orders_pk REBUILD;

Operational note: combining ALTER TABLE MOVE with the new PCTFREE value rebuilds all blocks in a single pass, compacting the segment and applying the free-space strategy uniformly. For tables that cannot tolerate a lock, use Oracle Online Redefinition via DBMS_REDEFINITION.START_REDEF_TABLE to reorganize the table while it remains available for DML. Rebuild all dependent indexes and refresh optimizer statistics after either approach.

Oracle identifies migrated and chained rows through ANALYZE TABLE ... LIST CHAINED ROWS. This operation scans the table and writes the ROWID of each affected row into a chained-rows table, commonly named CHAINED_ROWS. Create the table by running the provided script from $ORACLE_HOME/rdbms/admin before running ANALYZE.

-- One-time setup: create CHAINED_ROWS table
-- Run from $ORACLE_HOME/rdbms/admin
-- @utlchain.sql
-- or
-- @utlchn1.sql

-- Identify migrated and chained rows
ANALYZE TABLE orders LIST CHAINED ROWS;

-- Review results
SELECT owner_name, table_name, head_rowid, analyze_timestamp
FROM chained_rows
WHERE table_name = 'ORDERS'
ORDER BY analyze_timestamp DESC;

Each row in an Oracle table has a unique physical address exposed through the ROWID pseudocolumn. The ROWID encodes the data object number, relative file number, block number, and row slot number within the block. You can display ROWIDs directly in queries and decode their components using DBMS_ROWID for low-level diagnostics when investigating specific migrated rows identified in the CHAINED_ROWS output.

-- Display ROWID values alongside a key column
SELECT rowid, emp_id
FROM emp;

-- Decode ROWID components for diagnostic purposes
SELECT emp_id,
       DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(rowid,
         schema_name => 'HR', object_name => 'EMPLOYEES') AS file_num,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)               AS block_num,
       DBMS_ROWID.ROWID_ROW_NUMBER(rowid)                 AS row_num
FROM employees;

When LIST CHAINED ROWS confirms row migration is contributing to measurable I/O overhead, apply the following sequence in order.

1. Fix the data-change pattern: identify whether the root cause is insert-small/update-large application behavior. Where feasible, revise the application to insert complete rows. This is the highest-leverage change because it prevents migration from recurring even if PCTFREE is not increased.
2. Set an appropriate PCTFREE: calculate the expected row growth delta from INSERT to final UPDATE state. Set PCTFREE to cover that delta as a percentage of average row size relative to block size. Err on the side of slightly higher PCTFREE while monitoring block density.
3. Rebuild the segment: use ALTER TABLE ... MOVE PCTFREE n for offline reorganization or DBMS_REDEFINITION for online reorganization on production tables. Rebuilding is required to reclaim the forwarding-pointer slots left by previously migrated rows and to apply the new PCTFREE to all existing blocks.
4. Rebuild dependent indexes: a table MOVE changes all ROWIDs, rendering every index on the table unusable. Run ALTER INDEX ... REBUILD for each dependent index immediately after the table MOVE, or the index will be unusable for queries.
5. Validate with measurements: rerun ANALYZE TABLE ... LIST CHAINED ROWS after a representative workload period and compare counts to the pre-remediation baseline. Also compare logical read counts from AWR or V$SESSTAT to confirm a reduction in block reads per query.
6. Modernize wide-row risks: for tables where chaining is structural due to LONG/LONG RAW columns or inherently wide row designs, plan a migration to LOB datatypes or a table redesign that reduces average row width. Chaining from row width cannot be solved by PCTFREE tuning and will recur after any rebuild if the underlying data model is not addressed.

The next lesson examines PCTUSED and how block reuse thresholds interact with the segment space management approach chosen for the tablespace. Understanding how PCTFREE and PCTUSED work together completes the picture of Oracle's block-level space management controls.