Unlock Oracle Performance: How Logical Structures Impact SQL Optimization

Author: Liang Jingbin, senior database expert and trainer.

Logical Structure Overview

Oracle's logical structure is hierarchical, consisting of tablespaces, segments, extents, and blocks. It is user‑oriented, and understanding the relationship between these layers and SQL execution is essential for performance tuning.

Block Capacity Experiment

An 8KB block has 8096 bytes of usable space. By inserting one‑byte rows, the maximum number of rows per block was tested using the dbms_rowid package. Overheads limit each row to roughly 11 bytes, yielding a theoretical maximum of 736 rows per block, which the experiments confirmed.

Row Migration Optimization

Before optimization, logical reads were 219; after applying row migration fixes, reads dropped to 116, significantly improving performance.

Segment and Extent Experiments

Creating a table generates a tablespace, segment, and extents. Experiments with a regular table and a partitioned table showed that partitioning reduces logical reads dramatically (from 5923 to 3) by limiting scans to relevant segments.

High‑Water Mark (HWM) Cases

After massive deletions, the high‑water mark often remains unchanged, causing excessive logical reads. Reorganizing the segment releases HWM, reducing reads from 33350 to 4742 and boosting performance.

Tablespace Size and Auto‑Extend Impact

Tests comparing fixed‑size and auto‑extend tablespaces revealed that frequent auto‑extension leads to many extents (3610 vs 100) and significantly slower inserts (52 s vs 15 s).

Rowid Scanning

Comparisons of full table scan, index scan, and rowid scan illustrate the varying logical read costs, emphasizing the importance of appropriate access paths.

Key Takeaways

Larger block sizes reduce logical reads but may cause hotspot contention in OLTP workloads.

Proper segment management, including partitioning and HWM release, dramatically improves query performance.

Auto‑extend tablespaces can degrade insert performance due to excessive extent allocation.

Choosing the right access method (full scan, index, rowid) is crucial for minimizing logical reads.