Database Reference
In-Depth Information
Figure 9-2. Structure of a data segment
If the db_file_multiblock_read_count initialization parameter is set to 8, the following buffer cache reads
are performed:
One single-block read of the segment header (block 1).
One multiblock read of two blocks (2 and 3). More blocks can't be read because block 4 is cached.
One multiblock read of four blocks (from 5 to 8). More blocks can't be read because block 9
is cached.
One multiblock read of six blocks (from 11 to 16). More blocks can't be read because block 16
is the last one of the extent.
One multiblock read of two blocks (17 and 18). More blocks can't be read because block 19
is cached.
One single-block read of block 20. More blocks can't be read because block 21 is cached.
One multiblock read of eight blocks (from 22 to 29). More blocks can't be read because the
db_file_multiblock_read_count initialization parameter is set to 8.
One multiblock read of three blocks (from 30 to 32).
In summary, the process performs two single-block reads and six multiblock reads. The average number of
blocks read by a multiblock read is about four. The fact that the average size is smaller than eight explains why Oracle
introduced the mbrc value in system statistics.
The db_file_multiblock_read_count initialization parameter is dynamic and can be changed at the instance
and session levels. In a 12.1 multitenant environment, it can also be set at the PDB level.
At this point, it's also important to discuss how the query optimizer computes the cost of multiblock read
operations (for example, full table scans or index fast full scans).
When workload system statistics are available, the I/O cost isn't dependent on the value of the
db_file_multiblock_read_count initialization parameter. It's computed by Formula 9-1. Note that mreadtim is
divided by sreadtim because the query optimizer normalizes the costs according to single-block reads, as already
discussed in Chapter 7 (Formula 7-2).
Formula 9-1. I/O cost of multiblock read operations with workload statistics
blocks mreadtim
io_cost
mbrc
sreadtim
 
Search WWH ::




Custom Search