Optimizing Oracle Block Space Usage: How Data Is Stored in a Data Block

High performance often depends on two foundational factors: data block size and row storage organization. Every Oracle tablespace is built from data files, and every data file is subdivided into fixed-size data blocks. When Oracle adds a data file to a tablespace, it formats each block immediately, writing a small header and footer into the block overhead area. The remaining space is available for row data and transaction metadata.

Understanding how Oracle uses that remaining space, and how the storage parameters that control it interact, is essential for tuning insert-heavy tables, avoiding row migration, and making informed decisions about segment space management.

Each Oracle data block has a fixed overhead area divided into three components:

• Block header: identifies the type of content in the block, either table rows or index entries.
• Table directory: lists the table or tables whose rows occupy the block. In most cases a block holds rows from a single table. Clustered tables are the exception: the table directory in a cluster block identifies all tables sharing that block.
• Row directory: maps the physical location of each row within the block, allowing Oracle to locate a specific row without scanning the entire block.

Together these three components consume a small percentage of each block. The remaining space divides into two areas: free space, governed by PCTFREE, and row data, which holds the actual column values. Only one object type may inhabit any given Oracle data block. When Oracle allocates an extent for a table or index, every physical block in that extent is dedicated to that object. A tablespace can therefore contain blocks with different PCTFREE values, but each individual block holds only one type of object.

Oracle fills each data block with new rows up to the limit set by the PCTFREE storage parameter. PCTFREE specifies what percentage of each block to hold in reserve for future updates to rows already stored in that block.

When a block is filling due to SQL INSERT operations and the percentage of used space reaches the PCTFREE threshold, Oracle removes the block from the table's freelist. This is called a freelist un-link. No additional rows can be inserted into that block until used space drops below the PCTUSED threshold and Oracle re-links the block to the freelist.

PCTFREE example: With PCTFREE set to 20, Oracle reserves 20 percent of each block for row updates. New rows can be inserted until the block reaches 80 percent full. At that point the block leaves the freelist. The reserved 20 percent absorbs UPDATE operations that increase row length, such as replacing a NULL with a value or extending a VARCHAR2 column.

Performance implication: Setting PCTFREE too low leaves insufficient room for row growth. When an UPDATE cannot fit the expanded row into the existing block, Oracle must either chain the row across multiple blocks (row chaining) or migrate the entire row to a different block (row migration). Both conditions force Oracle to perform additional I/O reads to retrieve a single logical row, degrading query and DML performance. Setting PCTFREE too high wastes block space and increases the number of blocks Oracle must read to satisfy full-table scans.

PCTUSED specifies the minimum percentage of used space a block must drop to before Oracle returns it to the freelist for new INSERT operations. The default value is PCTUSED = 40, meaning Oracle will not accept new rows into a block until used space falls below 40 percent.

PCTFREE and PCTUSED work as a pair. PCTFREE controls when a block leaves the freelist during INSERT activity; PCTUSED controls when a block rejoins the freelist after DELETE or UPDATE operations reduce its used space. The gap between them (for example, PCTFREE = 20 and PCTUSED = 40 leaves a 40-percent dead zone) prevents excessive freelist toggling, which itself consumes CPU and latch resources.

PCTUSED is ignored in two situations:

1. Automatic Segment Space Management (ASSM) is enabled: When a locally managed tablespace is created with SEGMENT SPACE MANAGEMENT AUTO, Oracle tracks free space in each block using a bitmap structure rather than a linked freelist. PCTUSED has no effect and does not need to be specified.
2. Index segments: Oracle manages freelist re-linking for index tree structures independently, because index node boundaries align with the index block size. PCTUSED is not used for index segments regardless of the tablespace type.

The use of manual freelists is no longer encouraged. Creating locally managed tablespaces with SEGMENT SPACE MANAGEMENT AUTO is the current best practice for all new schemas.

When Oracle creates a segment, it allocates an initial extent sized by the storage clause or the tablespace default. If subsequent DML causes data volume to exceed that allocation, Oracle extends the segment automatically. Table and index creation is fast relative to data loading, but three storage decisions made at CREATE time have lasting performance consequences:

1. Automatic Segment Space Management: Create locally managed tablespaces with the SEGMENT SPACE MANAGEMENT AUTO clause. Oracle then manages block-level free space using a bitmap in the segment header, eliminating the need to set or tune PCTUSED and reducing freelist contention in high-concurrency environments.
2. PCTFREE: Set PCTFREE based on the expected update pattern for each table. A read-heavy table with few UPDATEs can use a low PCTFREE value such as 5 or 10, maximizing row density per block. A table where UPDATEs frequently extend row length needs a higher PCTFREE, typically 20 to 30, to prevent row migration. The correct value depends on average row length growth per update, not on a single default across all objects.
3. INITRANS: Each data block maintains a set of transaction entries used for row-level locking. INITRANS specifies the initial number of those entries at block creation time. The default (1 for tables, 2 for indexes) is sufficient for most workloads. For tables or indexes with many rows per block and a high likelihood of concurrent DML from multiple sessions, set a higher INITRANS value at CREATE TABLE or CREATE INDEX time. Oracle can allocate additional transaction entries dynamically up to the MAXTRANS limit, but only within the block's free space. Pre-allocating sufficient entries via INITRANS avoids contention on busy blocks.

Oracle is a row-oriented database. Understanding the distinction between row storage and column storage clarifies why Oracle's block-level tuning parameters are designed the way they are, and when Oracle's optional In-Memory feature provides an alternative access path.

Row-Oriented Storage

In a row-oriented store, all column values for a single row are written contiguously within the same block or set of adjacent blocks. Oracle's standard heap table uses this model. The advantages are straightforward for transactional workloads:

• Inserting, updating, or deleting a single record touches one location in the block, making DML operations efficient.
• Retrieving a complete record requires reading a single block area; no assembly across multiple column segments is needed.
• PCTFREE, PCTUSED, and INITRANS are meaningful precisely because they govern how Oracle manages intra-block space for row-level operations.

The weakness of row storage emerges in analytical queries. A query that aggregates a single numeric column across ten million rows must read every block containing those rows, pulling all other column data into the buffer cache even though only one column is needed. This inflates logical reads and reduces cache efficiency.

Column-Oriented Storage

In a column-oriented store, all values for a single column are written contiguously across all rows. Analytical queries that aggregate or filter on a small number of columns can read only the relevant column segments, dramatically reducing I/O. Column stores also compress more effectively because a single column contains values of the same data type with often limited cardinality, making run-length encoding and dictionary compression highly efficient.

The tradeoff is write performance. Updating one row in a column store requires touching as many column segments as the row has non-null columns. High-frequency OLTP workloads are therefore better served by row-oriented storage.

Oracle In-Memory: Both Models Simultaneously

Oracle Database 12c introduced the In-Memory Column Store (IM column store), a separate memory area in the SGA that holds a column-format copy of selected objects. When In-Memory is enabled, Oracle maintains both representations: the standard row-format copy on disk and in the buffer cache for DML operations, and the column-format copy in the IM column store for analytical queries. The optimizer selects the appropriate access path per query automatically. This eliminates the need to choose between row and column storage at the schema design level for mixed-workload databases.

For the purposes of this course, Oracle's row-oriented block structure is the primary model. PCTFREE, PCTUSED, and INITRANS apply to row-format heap segments; they have no direct equivalent in the In-Memory column store.

Effective space management requires understanding the three-level hierarchy Oracle uses to organize storage within a tablespace.

• Segment: a named database object that consumes storage, such as a table, index, undo segment, or LOB segment. Each segment is associated with exactly one tablespace.
• Extent: a contiguous set of Oracle data blocks allocated to a segment in a single operation. When a segment grows beyond its current allocation, Oracle adds one or more extents. The size of each new extent is determined by the tablespace's uniform extent size (for locally managed tablespaces with uniform sizing) or by the system-managed algorithm (for locally managed tablespaces with autoallocate).
• Data block: the smallest unit of Oracle I/O, and the level at which PCTFREE, PCTUSED, and INITRANS operate. Ideally an Oracle block is a multiple of the operating system block size to ensure efficient I/O. The default block size for the database is set by the DB_BLOCK_SIZE initialization parameter at database creation time. This block size governs the SYSTEM, TEMP, and SYSAUX tablespaces and cannot be changed without re-creating the database.

Additional tablespaces may use non-standard block sizes (2K, 4K, 8K, 16K, or 32K) if the corresponding DB_nK_CACHE_SIZE parameter is configured in the SGA. Multiple block sizes in the same database are uncommon but useful when migrating tablespaces from another platform or supporting a specialized workload with a known optimal block size.

The tablespace overview from the previous module introduced these structures at a high level. This lesson has focused on the block level, where the space management parameters discussed above actually operate. The next lesson examines how Oracle manages data rows using the Oracle segment header.