Lesson 9

The interaction between Oracle data blocks and the database engine is more layered than it first appears. Block-level storage parameters — PCTFREE, PCTUSED, INITRANS, and FREELISTS — are not set-and-forget values. They interact with segment space management, freelist mechanics, the high water mark, and index structure to determine whether the database performs at peak levels or degrades quietly over time as data volume grows.

This module covered the foundational block-level tuning controls that every Oracle DBA and developer working on performance-sensitive systems needs to understand. The eight lessons progressed from the Oracle storage hierarchy and block sizing through space parameter tuning, segment header internals, index maintenance, and high water mark management — each topic building directly on the one before it.

The main points of this module are:

1. Oracle data blocks are the fundamental unit of storage and I/O. Every read and write operation occurs at the block level. The block size set by DB_BLOCK_SIZE at database creation time applies to the SYSTEM, TEMP, and SYSAUX tablespaces and cannot be changed without re-creating the database.
2. Oracle databases generally perform faster with larger block sizes, especially for index range scans and sequential reads. Larger blocks reduce the number of I/O operations required to retrieve a given volume of data, because more rows or index entries fit in a single block read.
3. Larger block sizes require more RAM in the buffer cache (DB_CACHE_SIZE). Size the cache to hold a working set of blocks appropriate for the chosen block size and workload. The historic DB_BLOCK_BUFFERS parameter is superseded by DB_CACHE_SIZE under Automatic Shared Memory Management.
4. Space is reserved within each block for UPDATE-driven row growth using the PCTFREE table parameter. Setting PCTFREE too low causes row migration and row chaining, both of which increase I/O cost per row access. Setting it too high wastes block space and increases full-table scan cost.
5. Only one object type may inhabit any given Oracle data block. A block holds either table rows or index entries — not both — except in clustered tables where multiple tables intentionally share blocks.
6. The first data block of a table or index segment is the segment header. It coordinates all space management activity for the segment, including the freelist chain, the extent map, and the high water mark pointer.
7. The segment header contains five internal freelist structures: the master freelist, super master freelist (legacy OPS), freelist groups, process freelists, and transaction freelists. In ASSM tablespaces, a multi-level bitmap replaces all manual freelist structures.
8. The high water mark (HWM) is the boundary between blocks that have at some point held data and blocks that have never been formatted. It represents the table at its historical greatest size.
9. The high water mark does not decrease automatically after DELETE operations. It can be reset by TRUNCATE TABLE, ALTER TABLE ... MOVE, ALTER TABLE ... SHRINK SPACE, or a full segment reorganization via DBMS_REDEFINITION.
10. Full-table scans always read every block from the segment start up to the high water mark, including empty blocks left by deleted rows. A table with ten rows but a high HWM from a prior 100,000-row load will scan at the cost of the 100,000-row table.
11. The PCTUSED parameter controls when a block rejoins the freelist after DELETE activity. Set PCTUSED to a low value for high INSERT throughput; raise it only when disk space efficiency is the priority. PCTUSED has no effect in ASSM tablespaces (SEGMENT SPACE MANAGEMENT AUTO).
12. The PCTFREE parameter should be set to match the expected row growth from INSERT to final UPDATE state, so that row migration occurs infrequently. Detect migration with ANALYZE TABLE ... LIST CHAINED ROWS and remediate with ALTER TABLE ... MOVE or DBMS_REDEFINITION.
13. An index may warrant rebuilding when any of the following conditions is true: the index has spawned to more than 3 levels (HEIGHT > 3 in INDEX_STATS); the block gets per access (BLKS_GETS_PER_ACCESS) is greater than 5; or there are a significant number of deleted leaf rows (DEL_LF_ROWS). Use ALTER INDEX ... REBUILD ONLINE for production-safe rebuilds.

The following summary ties each lesson's objective to the key takeaway that carries forward into production tuning work.

Lesson 1 — Using Oracle Blocks Efficiently: Oracle organizes storage in a four-level hierarchy — block, extent, segment, tablespace. Every piece of data in an Oracle 23ai database, whether a table row, a vector embedding in a VECTOR column, or a JSON document accessed through a duality view, ultimately resides in data blocks. Block size and storage parameters set at schema creation time have lasting performance consequences.

Lesson 2 — Determining Database Block Size: DB_BLOCK_SIZE is set once at database creation and cannot be changed without a rebuild. Oracle benchmarks consistently show that larger block sizes outperform smaller ones even for single-row access, because index adjacency means surrounding rows are likely to be needed by subsequent queries. The buffer cache exposes three pools — DEFAULT, KEEP, and RECYCLE — allowing DBAs to pin frequently accessed small tables and isolate large scan workloads from polluting the default pool.

Lesson 3 — Optimizing Space Usage Within Blocks: Each Oracle data block has a fixed overhead area (header, table directory, row directory) and a variable area split between free space and row data. PCTFREE governs the freelist un-link threshold; PCTUSED governs the re-link threshold. INITRANS pre-allocates ITL entries for concurrent row locking. In ASSM tablespaces, bitmap tracking replaces the freelist chain and eliminates the need for PCTUSED and FREELIST parameters.

Lesson 4 — Oracle Segment Header Internals: The segment header block is the operational center of space management for every table and index segment. It holds the master freelist, process freelists, transaction freelists, the extent map, and the high water mark pointer. Buffer busy waits on the segment header are a diagnostic indicator of freelist contention; the resolution is ASSM, increased FREELISTS, or FREELIST GROUPS depending on the environment.

Lesson 5 — Setting PCTFREE for Optimal Performance: Row migration occurs when an UPDATE expands a row beyond the PCTFREE reserve in its original block, forcing Oracle to move the row and leave a forwarding pointer. Row chaining occurs when a row is too large to fit in a single block at INSERT time. Migration is correctable by increasing PCTFREE and rebuilding the segment; chaining from row width requires reducing row size, migrating LONG/LONG RAW to LOB datatypes, or increasing block size. Detect both conditions with ANALYZE TABLE ... LIST CHAINED ROWS.

Lesson 6 — Setting PCTUSED for Optimal Performance: PCTUSED and PCTFREE work as a pair. The gap between them determines how much space must be reclaimed by DELETE before a block cycles back onto the freelist. A wide gap means fewer freelist operations per INSERT session (better throughput); a narrow gap means blocks cycle back quickly (better space utilization, more freelist overhead). Buffer busy waits on the segment header are diagnosed via V$WAITSTAT and resolved by enabling ASSM or increasing FREELISTS and FREELIST GROUPS.

Lesson 7 — Monitoring and Tuning Oracle Indexes: B-tree indexes degrade through two mechanisms: height increase from leaf node splitting and spawning, and space waste from logically deleted leaf nodes. Monitor index health at regular intervals using ANALYZE INDEX ... VALIDATE STRUCTURE and query INDEX_STATS for HEIGHT, DEL_LF_ROWS, and BLKS_GETS_PER_ACCESS. Rebuild with ALTER INDEX ... REBUILD ONLINE when thresholds are exceeded. Oracle 23ai's Automatic Indexing reduces the frequency of manual intervention but does not replace structural monitoring.

Lesson 8 — Table High Water Marks and Full-Table Scans: The HWM advances in five-block increments as INSERT activity demands formatted space and never retreats automatically after DELETE. A full-table scan reads every block from the segment start to the HWM, including empty blocks. After large DELETE operations, reset the HWM via TRUNCATE, ALTER TABLE MOVE, ALTER TABLE SHRINK SPACE, or CTAS to eliminate empty-block I/O. The APPEND hint writes data directly above the HWM for maximum bulk-insert throughput, but serializes concurrent INSERT access on non-partitioned tables.

Objects within the database behave differently after they have been in production use for some time. A table's PCTFREE and PCTUSED settings may interact with the actual INSERT and UPDATE workload to produce row migration, block cycling, or freelist contention that never appears in development or early production. These problems only surface after data volume and DML frequency reach levels that expose the parameter choices made at CREATE TABLE time.

The same applies to indexes. As a B-tree index grows, leaf nodes fill and split. When a full level fills, the tree spawns a new level above it, increasing index height. Each additional level adds at least one block read to every index-driven row access. Applications that perform acceptably during the first weeks of production may falter suddenly when data volume reaches the point where the index crosses the three-level threshold or accumulates enough deleted leaf nodes to degrade range scans.

There is no substitute for testing with production-representative data volumes, loaded at production rates, into tables that already contain a substantial amount of existing data. Block-level parameters must be validated under realistic load conditions before they can be considered correctly tuned.

Having completed this module, you should now be able to:

1. Determine an appropriate data block size for a given workload and set DB_BLOCK_SIZE accordingly at database creation time.
2. Describe the functions of the segment header parameters: freelist structures, extent map, and the high water mark pointer.
3. Set PCTFREE and PCTUSED parameters for optimal performance based on the INSERT, UPDATE, and DELETE profile of each table.
4. Size table and index extents using locally managed tablespace defaults or explicit storage clauses, and choose between ASSM and manual freelist management.
5. Monitor and detect poorly performing indexes using ANALYZE INDEX ... VALIDATE STRUCTURE and the INDEX_STATS view, and rebuild when HEIGHT, DEL_LF_ROWS, or BLKS_GETS_PER_ACCESS exceed the thresholds established in this module.

The most commonly used block size should be chosen as the standard block size. The Oracle-recommended default is 8K (8,192 bytes), which balances buffer cache efficiency with I/O granularity for mixed OLTP and reporting workloads. The block size cannot be changed after database creation without re-creating the database. If the database block size differs from the operating system block size, the database block size must be a whole multiple of the OS block size. For example, with a 2K OS block size:

DB_BLOCK_SIZE=4096

A larger block size improves disk and memory I/O efficiency when:

1. The system has a large amount of physical memory available for the buffer cache and fast storage (NVMe or SAN). Large database systems with substantial hardware resources typically use 8K or larger block sizes.
2. The operating system uses a small native block size. If the OS block size is 1K and the database block size matches it, the database may perform excessive disk I/O during normal operation. Setting the database block size to a multiple of the OS block size — for example, 8K on a 1K OS — consolidates multiple OS I/Os into a single database I/O.

Tablespaces with non-standard block sizes are created using the CREATE TABLESPACE statement with the BLOCKSIZE clause. Supported non-standard values are 2K, 4K, 8K, 16K, and 32K. To use a non-standard block size, the corresponding DB_nK_CACHE_SIZE parameter must be configured in the SGA before the tablespace is created. Platform-specific maximum block size restrictions apply; some sizes may not be available on all platforms.

To complete this module, click the Quiz link below to test your knowledge of data blocks and performance.
Oracle Data Blocks - Quiz