Lesson 5	Setting PCTFREE for optimal performance
Objective	Set PCTFREE to minimize row chaining and migration in Oracle

Oracle PCTFREE Tuning for Row Migration and Row Chaining

PCTFREE is one of Oracle’s most practical “block-level” tuning controls. It deliberately trades a small amount of storage density for fewer extra I/Os during reads. The goal is simple: reserve enough free space in each data block so rows can grow in-place during UPDATE operations, rather than being forced into additional block reads caused by row migration or row chaining. :contentReference[oaicite:0]{index=0}

Row Migration vs Row Chaining

Oracle can encounter two different “continued row” behaviors:

Row migration happens when a row originally fit in a block, but an UPDATE increases its length and it no longer fits in the original block. Oracle moves the entire row to another block and leaves a forwarding pointer behind. A lookup may require an extra block visit to follow that pointer. :contentReference[oaicite:1]{index=1}
Row chaining happens when a row is too large to fit in a single block (for example, very wide rows or certain legacy data types). Oracle stores the row in multiple pieces across multiple blocks. This is sometimes unavoidable. :contentReference[oaicite:2]{index=2}

How PCTFREE Works

PCTFREE specifies how much space Oracle should keep free in each block for future row expansion. If a block crosses the “usable space” threshold (roughly 100 - PCTFREE percent full), Oracle treats the block as no longer eligible for new inserts, preserving the reserved space for updates. This reserved space is what helps reduce row migration caused by row growth. :contentReference[oaicite:3]{index=3}

Note that PCTUSED and related freelist behavior are associated with manual segment space management; modern deployments typically rely on automatic mechanisms, but PCTFREE remains relevant because it directly influences how much headroom exists for update-driven row growth. :contentReference[oaicite:4]{index=4}

Visual Examples: PCTFREE, Migration, Chaining

Low PCTFREE leaves insufficient growth space; row no longer fits in original block A and must move to block B. — Low PCTFREE leaves insufficient growth space; the updated row no longer fits in original block A, so Oracle must find space in another block B.

Oracle migrates the entire row to block B and leaves a forwarding pointer in block A. — Oracle migrates the entire row to block B and leaves a forwarding pointer in block A, which can add an extra block visit during reads.

Row grows beyond available space in candidate blocks; the row cannot fit as a single piece. — If a row grows beyond available space in candidate blocks, Oracle may be unable to store it as a single piece.

Oracle chains the row by splitting it into multiple pieces stored in multiple blocks. Chaining is common for rows that cannot fit in one block.

Practical Rules for Setting PCTFREE

Use these rules of thumb to choose an initial PCTFREE value:

Read-mostly tables, minimal updates: keep PCTFREE lower (often near the default) because rows do not typically expand after insert.
Rows inserted “small” then updated “large”: increase PCTFREE significantly to reserve growth headroom. Avoid the anti-pattern of inserting many NULLs and later updating most columns; insert complete rows when possible. :contentReference[oaicite:5]{index=5}
Variable-length columns (VARCHAR2) that grow: raise PCTFREE enough to cover expected growth for typical rows in that block.
Large or legacy row formats: some chaining is unavoidable for rows that exceed the block size or for certain legacy datatypes (for example, LONG/LONG RAW). Modernize by migrating LONG/LONG RAW to LOB datatypes to improve maintainability and reduce “wide row” pressure. :contentReference[oaicite:6]{index=6}

A practical way to estimate PCTFREE is to measure typical row size at insert time versus row size after updates, then reserve space for the expected delta. You are not trying to eliminate migration entirely; you are trying to reduce it to a level that does not materially impact your primary access paths.

Setting PCTFREE on Tables

You can set PCTFREE when creating a table or alter it later:

-- 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;

-- Change PCTFREE for future block usage
ALTER TABLE orders PCTFREE 20;

Operational note: altering PCTFREE affects how Oracle manages space going forward. If you are correcting an existing migration/chaining problem, you typically combine the new PCTFREE setting with a table reorganization approach (for example, moving/redefining the table) so that blocks are rebuilt with the new free-space strategy, then rebuild dependent indexes and refresh optimizer statistics.

Detecting Migrated or Chained Rows

Oracle supports identifying migrated and chained rows using ANALYZE TABLE ... LIST CHAINED ROWS. This operation records affected ROWIDs in a chained-rows table (commonly named CHAINED_ROWS), which you create by running utlchain.sql or utlchn1.sql. :contentReference[oaicite:7]{index=7}

-- One-time setup (run from $ORACLE_HOME/rdbms/admin)
-- @utlchain.sql
-- or
-- @utlchn1.sql

-- Identify migrated/chained rows for a table
ANALYZE TABLE orders LIST CHAINED ROWS;

Each row has an addressable identifier exposed through the ROWID pseudocolumn. You can display ROWIDs directly, and you can decode components of a ROWID (file number, block number, row number) with DBMS_ROWID when you need low-level diagnostics. :contentReference[oaicite:8]{index=8}

-- Display ROWID values
SELECT rowid, emp_id
FROM emp;

-- Decode ROWID components (example)
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;

Remediation Playbook

When you confirm row migration is materially affecting performance, use a controlled remediation sequence:

Fix the data-change pattern: avoid insert-small/update-large workflows when feasible; insert complete rows.
Set an appropriate PCTFREE: reserve realistic growth headroom for the table’s update profile. :contentReference[oaicite:9]{index=9}
Rebuild the segment layout: reorganize the table so the new PCTFREE strategy applies to rebuilt blocks; rebuild impacted indexes afterward.
Validate with measurements: rerun detection and compare counts to confirm the issue is reduced, then monitor query plans and I/O behavior.
Modernize wide-row risks: migrate legacy LONG/LONG RAW usage to LOB types and reconsider “too-wide” table designs where chaining is structural. :contentReference[oaicite:10]{index=10}

Next Lesson

In the next lesson, you will look at PCTUSED and how block reuse thresholds interact with your segment space management approach. :contentReference[oaicite:11]{index=11}