If you monitor a full table scan operation closely by repeatedly querying the V$SESSION_WAIT view in quick successions or by tracing the session with trace event 10046, you may see some db file scattered read events that request fewer blocks than the MBRC. This irregularity is due to any of the following reasons:
The last set of blocks in an extent is less than the MBRC. If the MBRC is set to 8 and every extent has 10 blocks, Oracle will issue two multiblock read calls for each extent—one read call for 8 blocks and the other read call for 2 blocks—because the MBRC factor cannot span across extents.
One or more blocks in the multiblock read set is already in the buffer cache, so Oracle breaks the fetch into two or more reads, which may be comprised of a single or multiblock I/Os. For example, if the MBRC is 8 and blocks 3 and 7 are in the cache, Oracle will issue three read calls—the first for blocks 1 and 2, the second for blocks 4 through 6, and the third for block 8. Since the third fetch is for a single database block, the wait event is db file sequential read. However, for the first two read calls, the wait event is db file scattered read because the number of blocks is greater than 1. Therefore, cached blocks can cause full table scans operations to perform more reads than required.
阅读(755) | 评论(0) | 转发(0) |
