Lesson 1

Using Oracle Blocks Efficiently and the Database Engine

Oracle data blocks — also called Oracle blocks or pages — are the fundamental unit of storage and I/O in Oracle Database 23ai. Every read and write operation the database performs occurs at the block level, making the data block the most important structural element in Oracle storage architecture. Understanding how data blocks work, how they are sized, and how storage parameters affect them is essential to tuning an Oracle database for performance.
This module discusses the interaction between Oracle data blocks and the database engine. The size of the data blocks, the structure of the rows stored on those blocks, and the settings of the table storage parameters all directly affect the contents of each block and the overall performance of the database. By the time you finish this module, you should be able to:

Determine the appropriate DB_BLOCK_SIZE for your database
Optimize space usage within blocks
Describe the internals of the segment header
Set proper values for PCTUSED and PCTFREE
Describe the effect of the high water mark on full-table scans
Detect and resolve row migration

The Oracle Storage Hierarchy

Oracle organizes data logically — independent of the operating system — in a four-level hierarchy from smallest to largest. A data block is the smallest unit of storage, typically 2 KB, 4 KB, or 8 KB. An extent is a group of logically contiguous data blocks. A segment is a set of extents allocated to a specific database object such as a table or index. A tablespace is a logical container for segments, consisting of one or more physical data files.
A single logical data block maps to a fixed number of bytes on persistent storage. The database always reads and writes in whole data block multiples, even when the operating system uses a different native block size.

Choosing the DB_BLOCK_SIZE

One of the most consequential decisions in Oracle database setup is selecting the default data block size. The DB_BLOCK_SIZE initialization parameter sets this value at database creation time and applies to the SYSTEM and SYSAUX tablespaces as well as all other tablespaces by default. This parameter cannot be changed without recreating the database, so the choice must be made carefully before the database is built.
The default block size is typically 8 KB, with 4 KB and 8 KB being the most common values. Oracle supports multiple block sizes within a single database — non-default tablespaces can use different block sizes — but the default set by DB_BLOCK_SIZE is permanent for the instance. Managing the database buffer cache, which holds data blocks in memory for fast access, is directly tied to this setting. Oracle Databases can be deployed on up to 64,000 datafiles. Because a bigfile tablespace can contain a file that is 1,024 times larger than a smallfile tablespace, and bigfile tablespaces support 32 KB block sizes on 64-bit operating systems, an Oracle Database can grow to up to 8 exabytes in size — an exabyte is equivalent to a million terabytes. The bigfile tablespace is designed for use with Oracle Automatic Storage Management (ASM), logical volume managers that support striping, and RAID.^[1]

Internal Structure of a Data Block

Every Oracle data block has a consistent internal format regardless of whether it stores table rows, index entries, or other segment types. The block header occupies the top of the block and contains general information including the disk address, segment type, and transaction details used for concurrency control. Below the header, the table directory holds metadata about the tables whose rows are stored in the block. The row directory holds pointers to the physical locations of each row within the block.
Row data is stored from the bottom of the block upward, while the header and directories grow downward from the top. Free space sits between these two areas and is managed by the storage parameters PCTFREE and PCTUSED. Rows are variable-length and Oracle tracks each row via a rowid that encodes the datafile number, block number, and row position within the block.

PCTFREE and PCTUSED

The two storage parameters that most directly control space usage within a data block are PCTFREE and PCTUSED. Understanding both is essential to avoiding row chaining and row migration, which are among the most common causes of excessive I/O in Oracle databases.
PCTFREE reserves a percentage of each block for future UPDATE operations that expand column values. For example, with the default PCTFREE of 10, Oracle stops inserting new rows into a block once 90 percent of the block is used, reserving the remaining 10 percent for updates. Setting PCTFREE too low risks row chaining, where an UPDATE causes a row to exceed the available space in its original block. Oracle must then store the overflow in a chained row on a separate block, requiring two block reads to retrieve a single row and significantly increasing I/O overhead. PCTUSED determines the minimum percentage of used space that must be present in a block before Oracle returns it to the freelist for new inserts. Once a block's used space falls below the PCTUSED threshold due to DELETE operations, Oracle re-links it to the freelist and allows new rows to be inserted. Together, PCTFREE and PCTUSED define the insertion window for each block and directly affect both space utilization and performance.

Automatic Segment Space Management

In Oracle 23ai, tablespaces are typically locally managed by default, using bitmaps in datafile headers to track free and used extents efficiently. Within locally managed tablespaces, Automatic Segment Space Management (ASSM) uses bitmaps inside the segment itself to track free space at the block level. ASSM eliminates the need to manually manage freelists and freelist groups, simplifies administration, and improves concurrency for high-insert workloads compared to older dictionary-managed or manual freelist methods.

The Buffer Cache and Block I/O

When a query or DML statement accesses data, Oracle loads the required data blocks from disk into the SGA database buffer cache. Subsequent accesses to the same blocks are served from memory, avoiding disk I/O entirely. The efficiency of the buffer cache depends on block size, workload access patterns, and the size of the cache itself. Frequently accessed blocks remain pinned in the cache, while less frequently accessed blocks are aged out using a least recently used algorithm. In Oracle 23ai, the In-Memory Column Store provides a separate in-memory format optimized for analytical queries, but the underlying data still resides in standard data blocks on disk. The two formats coexist transparently, with the database optimizer choosing the access path that delivers the best performance for each query.

The High Water Mark and Full-Table Scans

The high water mark (HWM) is the boundary between blocks that have been used at some point and blocks that have never been formatted. A full-table scan reads every block up to the HWM, regardless of how many rows those blocks actually contain. This becomes a significant performance problem after a large number of rows have been deleted: the HWM does not automatically decrease, so Oracle continues to read all the blocks that previously held data, including those that are now empty or sparsely populated.
Reducing the HWM eliminates this wasted I/O and can dramatically improve full-table scan performance on tables that have undergone significant DELETE activity. In Oracle 23ai this is accomplished with the segment shrink operation:

ALTER TABLE table_name SHRINK SPACE;

Index range scans navigate the B-tree index structure directly and are not affected by the HWM. Only full-table scans are subject to the HWM boundary.

Row Migration and Row Chaining

Row migration occurs when an UPDATE operation expands a row beyond the free space available in its current block. Oracle moves the entire row to a new block but leaves a forwarding address in the original block. A query that accesses a migrated row must read two blocks — the original block for the rowid and the new block for the actual data — doubling the I/O cost for that row. Row chaining is a related but distinct condition that occurs when a row is too large to fit in a single block even when the block is empty. Chaining is unavoidable for very wide rows and is not a tuning problem in itself. Migration, by contrast, is caused by insufficient PCTFREE settings and can be eliminated by setting PCTFREE to an appropriate value for the table's UPDATE patterns. Migrated rows can be detected by querying V$SYSSTAT for the table fetch continued row statistic, or by running the ANALYZE TABLE ... LIST CHAINED ROWS command. Once identified, migrated rows are resolved by exporting and reimporting the table, or by using online table redefinition with DBMS_REDEFINITION.

Oracle 23ai and the Block-Based Foundation

Data blocks provide the consistent, efficient foundation that allows Oracle 23ai's converged capabilities — relational, vector, JSON, graph, and spatial — to coexist in a single engine. Every piece of data in an Oracle 23ai database, whether a traditional table row, a vector embedding stored in a VECTOR column, or a JSON document accessed through a JSON Relational Duality View, ultimately resides in data blocks. Optimizations such as automatic compression, SecureFiles improvements, and Exadata AI Smart Scan all build directly on this block-based architecture without altering its core role. The next lesson reviews the DB_BLOCK_SIZE parameter setting in detail.

[1] RAID: RAID stands for redundant array of inexpensive disks.