Sizing Extents for Performance

www.kevinloney.com/free/extperf.doc

This is an edited excerpt from ORACLE8 Advanced Tuning and Administration, by Eyal Aronoff, Kevin Loney, and Noorali Sonawalla, published under Osborne/McGraw-Hill's Oracle Press imprint. This edited version first appeared on http://www.kevinloney.com.

During a full table scan, Oracle uses its multiblock read capability to scan multiple blocks at a time. The number of blocks read at a time is determined by the database's DB_FILE_MULTIBLOCK_READ_COUNT setting in the init.ora file and by the limitations of the operating system's read buffers. For example, if you are limited to a 64KB buffer for the operating system, and the database block size is 4KB, you can read no more than 16 database blocks during a single read.
Consider the SALES table again, with an 8MB initial extent and a 4MB second extent. For this example, assume that the highwatermark of SALES is located at the end of the second extent. In the first extent, there are 2048 database blocks, 4KB each in size (8 MB/4 KB = 2048). During a full table scan, those blocks will be read 16 at a time, for a total of 128 reads (2048/16 = 128). In the second extent, there are 1024 database blocks. During a full table scan, those blocks will be read 16 at a time, for a total of 64 reads (1024/16=64). Thus, scanning the entire table will require 192 reads (128 for the first extent, plus 64 for the second extent).
What if the two extents were combined, with the table having the same highwatermark? The combined extent would be 12MB in size, consisting of 3072 database blocks. During a full table scan, those blocks will be read 16 at a time, for a total of 192 reads. Despite the fact that the extents have been compressed into a single extent, the exact same number of reads is required because of the way the extents were sized. As you will see in the next section, the location of the extents may influence the efficiency of the reads, but the number of reads is the same in both the single- and two-extent examples.
What if SALES had 192 extents, each one 16 blocks in length? A full table scan would read 16 blocks at a time, for a total of 192 reads. Thus, whether the SALES table had 1, 2, or 192 extents, the exact same number of reads was required to perform the full table scan. The size of the extents is critical—the size of each extent must be a multiple of the number of blocks read during each multiblock read (set via the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter value).
In the 192-extent example, the extent size matched the setting of the DB_FILE_MULTIBLOCK_READ_COUNT value (16). If each extent had been 20 blocks (instead of 16 blocks), how many reads would be required?
The SALES table contains 3072 blocks of data (12MB total). If each extent is 20 blocks (80 KB) each, you'll need 154 extents to store the 3072 blocks of data. When reading the first extent during a full table scan, Oracle will read the first 16 blocks of the extent (as dictated by the DB_FILE_MULTIBLOCK_READ_COUNT). Because there are four blocks left in the extent, Oracle will issue a second read for that extent. Reads cannot span extents, so only four blocks are read by the second read. Therefore, the 20-block extent requires two reads. Since each extent is 20 blocks in length, each extent will require two reads. Because there are 154 extents, a full table scan of SALES will now require 308 reads—a 60 percent increase over the 192 reads previously required!