Oracle DBA Lesson 46A — Understanding Extra Block Visits

A heap-table row can require more than one block visit to retrieve its values. Row migration leaves a forwarding location behind when a growing row moves. Row chaining stores a row as linked pieces. Understanding the row's path helps you choose a useful investigation before reorganizing a table.

Oracle DBA Lesson 46A — Understanding Extra Block Visits

What is inside a block?

An uncompressed heap-table block contains management structures and row storage:

Area	Purpose
Block header	Identifies the block and records transaction information.
Interested transaction list (ITL)	Describes transactions affecting rows, including locks and committed/uncommitted changes. It links to transaction information in undo.
Table directory	Describes the table whose rows occupy the block.
Row directory	Uses slots pointing to the row pieces within the block.
Free space	Can accommodate growth of existing rows and additional internal structures.
Row data	Stores row-piece headers and column values.

The anatomy drawing represents these roles. Its proportions are conceptual. Block overhead and free space vary. A row can move within a block while its directory slot is updated and its ROWID remains stable. See block format and interested transaction lists.

Migration: follow the original location

Consider a row that initially fits in Block A. An UPDATE enlarges a variable-length value. Block A has insufficient room for the enlarged row, but the entire row fits in Block B. Oracle can migrate the row to Block B and retain a forwarding address at the original location in Block A.

The migrated row keeps its original ROWID. A lookup using that location can follow the forwarding address to Block B, adding a block visit. The example is a row-location path, with no specific byte sizes or disk-read count implied. Buffer-cache state and the actual access path affect physical I/O. Full scans and indexed row lookups should be assessed through their own measured behavior.

Chaining: inspect the pieces a row requires

A row that exceeds the usable capacity of a single block can be split across blocks, either on insertion or after growth. A row with more than 255 stored columns also requires multiple row pieces, because an individual piece supports at most 255 columns. Such pieces may occupy the same block. Multiple pieces alone do not guarantee multiple block visits. The diagram shows both an across-block example and a possible same-block placement.

A reorganization may remove migration caused by the previous placement. A row that still exceeds a block's usable capacity, or that requires multiple pieces because of its column layout, can retain chaining. Investigate the row's shape before estimating what a move or rebuild could achieve. A different tablespace block size is a specialized design decision with memory, storage, and workload implications. See chained and migrated rows.

PCTFREE: leave room for expected growth

PCTFREE reserves a percentage of block space for updates to existing rows. More reserved space can accommodate growth while reducing the number of rows packed into blocks initially. Choose it from the application's insertion and update pattern, together with the storage cost. The side-by-side drawing demonstrates the tradeoff without prescribing a percentage.

For example, inserting a short row and later filling many previously null or short values can increase its storage substantially. Compare the initial and mature row shapes when planning growth room. The parameter concerns block space. Changing it does not automatically relocate existing migrated rows. In an automatically managed segment, Oracle tracks block availability and ignores PCTUSED for that purpose. See PCTFREE and physical attributes.

Collect table context

As an appropriately delegated administrative account in the table's PDB, use:

SELECT table_name, pct_free, chain_cnt, last_analyzed
FROM dba_tables
WHERE owner = 'COURSE_OWNER'
AND table_name = 'EMPLOYEES';

The owner and table filters select the exact object in the connected container. PCT_FREE records the table's setting. CHAIN_CNT describes collected information about rows chained across blocks or migrated with a link that retains the old ROWID. LAST_ANALYZED supplies the table's statistics timestamp.

Record the collection method and its coverage alongside the timestamp. CHAIN_CNT can be null, unavailable, or outdated, and a zero value needs that context. A recent optimizer-statistics gathering timestamp alone is insufficient to certify that a fresh physical chaining investigation was performed. Use DBMS_STATS for optimizer statistics and ANALYZE ... LIST CHAINED ROWS for the targeted listing when appropriate. See ALL_TABLES for the statistics-column collection footnote, DBA_TABLES for this view's scope, and ANALYZE together with managing schema objects for the distinction between targeted physical investigation and optimizer-statistics gathering.

These fields provide table context. They do not identify the query encountering the rows or measure its current response time. For a partitioned table, inspect the appropriate partition or subpartition evidence rather than assuming a table-level value describes every materialized partition.

Measure an interval and establish its scope

An authorized reporting connection can query the continued-row fetch statistic:

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

This statistic counts fetch encounters with migrated or chained rows. It counts activity, so the same row can contribute repeatedly. It is separate from a count of distinct rows and from physical disk reads. See continued-row fetches.

From the CDB root, Oracle documents V$SYSSTAT as instance-wide with CON_ID = 0. A PDB connection has a different reporting scope. Record the connected container and the returned CON_ID before interpreting the value. Do not combine a LABPDB table report with a whole-instance statistic as though both were object-level evidence. See V$SYSSTAT and statistics definitions.

Suppose two comparable root snapshots for the same running instance show:

Snapshot	VALUE
Before the observation interval	1,000
After the observation interval	1,030
Difference	30

The arithmetic gives 30 continued fetches during that interval. These values are an authored calculation, not recorded lab results. The interval may include other tables, sessions, and containers. Record the active workload, instance identity and startup, timestamps, and query activity. A restart or a scope change invalidates a simple subtraction across the boundary.

Next, identify the affected SQL and its actual access path, inspect the relevant row length and column layout, and compare logical reads and response time for a representative workload. An appropriately scoped session investigation can narrow attribution. It still needs to establish which work the session performed. A quiet or isolated interval helps interpretation. No threshold or automatic reorganization is prescribed by the sample.

Practice and example conditions

Use the instructor's disposable Oracle Database 19c single-instance environment. The main exercise is read-only and requires no cleanup. COURSE_DBA is a lab identity with narrowly delegated access to the needed catalog and dynamic performance views. Its name does not imply the DBA role. COURSE_OWNER.EMPLOYEES is the established starter heap table in LABPDB. Do not force migration by changing starter rows or storage settings.

Before querying, confirm identity and container:

SHOW USER
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;

SHOW commands belong to SQL*Plus. Record the exact release update, object owner, reporting privileges, and actual outputs. The optional whole-instance counter uses the instructor's separately authorized root reporting connection. It does not require granting root access to the course's local account. If that connection is unavailable, classify the supplied snapshots and record the unavailable live verification.

Complete these tasks:

1. Draw a row moved from one block with a forwarding location left behind. Label the retained ROWID and the extra lookup hop.
2. Draw one row split across blocks. Explain how a more-than-255-column row can require pieces even when the pieces fit within one block.
3. Record the table settings and available collected information, including method and timestamp. Mark unknown collection coverage explicitly.
4. Interpret the 30-fetch interval above and list the workload attribution still required.
5. Describe the affected query, row-growth pattern, and performance evidence you would gather before proposing a change.

Pass when you correctly classify migration and chaining, explain the PCTFREE packing tradeoff, and request query, access-path, and row-shape evidence instead of assigning the entire cumulative counter to EMPLOYEES.

Optional targeted listing

Perform this only in a separately designated disposable copy, under instructor supervision. The instructor must establish an unused COURSE_ROWPATH_DEMO name and a dedicated empty COURSE_CHAIN46A_REPORT output table with the exact Oracle chained-row format, following UTLCHAIN.SQL or UTLCHN1.SQL as appropriate. Do not overwrite an existing table with a packaged script. Record exactly which objects were created, the capacity and finite quota, the privileges, and the reset plan. Creating and dropping tables are DDL operations and have transaction consequences.

For an ordinary heap copy owned by the connected user, with the prepared local output table:

ANALYZE TABLE course_rowpath_demo
  LIST CHAINED ROWS INTO course_chain46a_report;

SELECT owner_name, table_name, head_rowid, analyze_timestamp
FROM course_chain46a_report
WHERE owner_name = 'COURSE_OWNER'
AND table_name = 'COURSE_ROWPATH_DEMO'
ORDER BY head_rowid;

The analysis must target your own local object, or use the specifically required analysis authority. The output table must be local and owned by you, or have the required insert access. The listing identifies migrated and chained candidates together. Further row-layout investigation is needed to distinguish the cause. A simple starter-table copy may contain no such rows. This exercise does not require manufacturing a migration workload or refreshing optimizer statistics. See ANALYZE and targeted chained-row investigation.

After evidence capture, remove only the exact disposable objects created for this exercise. If the instructor confirms both were created exclusively by this exercise, the cleanup is:

DROP TABLE course_rowpath_demo PURGE;
DROP TABLE course_chain46a_report PURGE;

PURGE removes the dropped objects without a recycle-bin recovery route. Confirm the names and ownership before cleanup, and verify that the course's original EMPLOYEES table remains unchanged. For instructor-owned reusable reporting tables, follow their cleanup instructions instead of dropping them. No reorganization, block-size change, or starter-data update is included in this practice.

Quiz

1. A row grows, fits in another block, and leaves a forwarding address in its original block. What happened?

Row migration A segment was deferred The table became an index Every column became a separate segment

2. What can require multiple row pieces even when they can fit in one block?

A higher SELECT privilege More than 255 stored columns in a row An empty dictionary result A single short NUMBER value

3. What is a practical tradeoff when increasing PCTFREE for expected row growth?

Every migrated row immediately returns to its original block Physical files shrink automatically More growth room with fewer rows packed into blocks initially Column limits disappear

4. Two comparable root snapshots show a continued-fetch counter increase of 30. What can you conclude directly?

EMPLOYEES contains exactly 30 migrated rows Exactly 30 physical disk reads occurred Rebuilding EMPLOYEES will eliminate all extra reads There were 30 additional continued-fetch encounters in that instance interval

5. Will rebuilding a table necessarily eliminate row chaining?

Yes, every row always fits in one piece afterward No, row size or column layout may still require pieces Yes, CHAIN_CNT is a rebuild command Yes, ROWID values are all removed

The diagrams, row paths, and numeric snapshots are teaching assumptions checked against documentation. No database connection or SQL execution was performed while preparing this lesson. Actual behavior, statistics availability, and results must be captured in the designated lab.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro