Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 46 Part 1 — Why a Row Needs Extra Blocks

An index can locate a row's original address even after Oracle moves the row's data to another block. Retrieving the row can then require an extra block visit. Understanding the row's layout helps you decide whether a reorganization would improve the affected workload.

Oracle DBA Lesson 46 Part 1 — Why a Row Needs Extra Blocks

This lesson covers ordinary heap tables in Oracle Database 19c. The next part covers deferred segment creation.

Inside a table block

A block contains a header, a table directory, a row directory, free space, and row data. Transaction entries in the header support transaction management. The table directory identifies table information. Row-directory entries locate row pieces within the block. Free space allows stored rows to expand during updates. Row data contains their values.

These blocks belong to segments stored in physical data files on disk. The video's block diagrams explain logical placement. They do not depict the timing of physical writes. Shapes and row markers are schematic and are not proportional to byte sizes. See logical storage structures.

Follow row 42

Row 42 initially fits in block A, alongside other rows. An UPDATE enlarges its payload. Block A has insufficient room for the growth, but the complete updated row fits in another block with sufficient available space.

Oracle moves the complete row to block B and leaves a forwarding address at the original location in block A. The original ROWID remains valid. Existing index entries can continue to point to that original location. A fetch through that location follows the forwarding address to block B.

This is row migration. The additional block access can increase retrieval work. A logical block visit does not necessarily cause a physical disk read. Assess the actual access path and the measured reads, rather than assuming one physical read per arrow.

When the row needs pieces

If the row is too large to fit in one block, including when first inserted, Oracle stores it in linked row pieces across blocks. Retrieving all values follows the pieces. This is row chaining.

A row piece holds at most 255 columns. Rows with more columns require multiple pieces. Those pieces may reside in the same block or span blocks. Multiple pieces therefore do not automatically establish multiple block visits for every fetch. The requested columns and the access path also affect retrieval work.

QuestionMigrationChaining
What happened?An updated row outgrew its available space and moved as a complete rowA row requires linked pieces
What remains at the original location?A forwarding addressA row piece can contain values and a link to another piece
Can the complete row fit in a suitable single block?Yes, in this exampleAn intrinsically oversized row cannot
Will reorganization necessarily remove the issue?It may remove costly migration; assess the operation and measureWidth or row-piece constraints can remain

See tables and table clusters.

Inspect table metadata

Read PCT_FREE, CHAIN_CNT, and LAST_ANALYZED from DBA_TABLES:

SELECT table_name, pct_free, chain_cnt,
       TO_CHAR(last_analyzed,'YYYY-MM-DD') AS last_analyzed
FROM dba_tables
WHERE owner='APP_USER' AND table_name='ROW_DEMO';
TABLE_NAMEPCT_FREECHAIN_CNTLAST_ANALYZED
ROW_DEMO10NULL2026-10-19
  • PCT_FREE describes space reserved for future updates in table blocks. It is not a measurement of current migration.
  • CHAIN_CNT is a combined count of chained and migrated rows from the relevant collection. A missing value means this result supplies no such count. An existing value can be stale.
  • LAST_ANALYZED is the table's statistics analysis date. Its presence alone does not establish when or how CHAIN_CNT was populated, or whether it is current. Routine optimizer statistics collection must not be assumed to supply a fresh physical chaining diagnosis.

USER_TABLES or ALL_TABLES can show the same columns for a narrower scope. Adjust the ownership predicate to match the view. See DBA_TABLES and ALL_TABLES.

Measure continued fetching

In the container database root, V$SYSSTAT reports instance-wide data with CON_ID = 0. That scope is different from one table's metadata in its pluggable database.

SELECT con_id, value
FROM v$sysstat
WHERE name = 'table fetch continued row';
CON_IDVALUE
02000

The statistic records encounters with migrated or chained rows during fetching. It does not count distinct affected rows in ROW_DEMO.

For two samples from the same instance, within the same uninterrupted instance lifetime:

SampleVALUE
Start2000
End2040

The delta is 40 continued fetch events. Other workloads can contribute. Record the interval and the instance identity. A restart or a counter reset invalidates a simple subtraction across that boundary. In RAC, establish each instance's scope before comparing or aggregating values.

To decide whether action is useful, identify the affected SQL and access path, then measure the relevant logical and physical reads and the response time under comparable conditions. A global delta alone does not attribute those events to one table or prove a user-visible problem. See V$SYSSTAT and table fetch continued row.

Choose action from the cause

For rows that expand during updates, PCTFREE can reserve more growth room. A larger reserve generally means fewer rows packed during initial filling. Match it to observed row expansion and the workload, rather than selecting a high value indiscriminately. Changing PCTFREE alone does not relocate rows that have already migrated. See PCTFREE.

If investigation shows costly migration, evaluate a suitable reorganization, including object eligibility, required free space, concurrency, dependencies, index maintenance, and possible ROWID changes. Measure the workload afterward. Reorganization does not remove intrinsic row-width constraints or guarantee that future updates will avoid migration. See managing tables.

A different block size is a storage and workload design decision with configuration and eligibility implications. It is not the automatic response to a CHAIN_CNT value. An intrinsically oversized row may remain chained under the existing block size after rebuilding.

Optional targeted investigation

ANALYZE TABLE ... LIST CHAINED ROWS can identify migrated and chained rows for further investigation. It does not by itself distinguish a costly migration from unavoidable chaining, or establish workload impact.

Prepare an output table first. Oracle supplies utlchain.sql for physical rowids and utlchn1.sql for universal rowids. Use the script that matches the rowid type and the object type. The output table needs space for the diagnostic rows.

-- Optional syntax illustration, after preparing the output table:
ANALYZE TABLE row_demo LIST CHAINED ROWS INTO chained_rows;

The command writes diagnostic rows to the output table. Avoid mixing old and new diagnostic results. Use DBMS_STATS for optimizer statistics. Do not adopt ANALYZE as a general method for collecting optimizer statistics. See managing schema objects, ANALYZE, and CHAINED_ROWS.

Recap

Migration moves a complete updated row to another block and leaves a forwarding address at the original location. The original ROWID stays valid, index entries can still point there, and a fetch through that address visits the extra block. A logical visit is not automatically a physical read. Chaining stores a row as linked pieces when the row does not fit in one block, including on insert, or when a row has more than 255 columns. Pieces can share a block, so multiple pieces are not automatically multiple block visits.

PCT_FREE reserves room for later updates. It does not measure current migration, and changing it does not move rows that have already migrated. A missing CHAIN_CNT supplies no chain count, and LAST_ANALYZED does not prove that the count is current. In the root, a rise in table fetch continued row from 2000 to 2040 is 40 instance-wide continued-fetch events in that interval. Other workloads can contribute. Evaluate reorganization when measured migration cost supports it. An intrinsically oversized row can remain chained at the same block size.

Quiz

1. An UPDATE expands a row, and Oracle moves the whole row while retaining a forwarding address at the original location. What is this?

2. Will rebuilding a table always eliminate row chaining?

3. What can you conclude from CHAIN_CNT = NULL and a recent LAST_ANALYZED value?

4. In the root, the instance statistic rises from 2000 to 2040. What does that establish, assuming the same instance lifetime?

5. Why might you increase PCTFREE for rows with substantial update growth?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...