Database Reference
In-Depth Information
As you can see, it isn't that difficult to do. In any case, be careful not to cache the test table at the operating system
and disk I/O subsystem levels, because that would render the test useless. The easiest way to avoid that is to use a
table larger than the largest cache available in your system. For systems expected to use parallel processing, it's worth
extending such a test to execute parallel queries as well (Chapter 15 describes parallel processing).
Figure
9-3
shows the characteristics of my test system measured with the previous PL/SQL block executed against
an 11.2 database with all the initialization parameters set to the default. Here are the characteristics to note:
The throughput increases from about 200MB/s for small values of the
db_file_multiblock_read_count
initialization parameter up to more than 600MB/s
for very large values.
The CPU utilization decreases from about 1.5 seconds for small values of the
db_file_multiblock_read_count
initialization parameter down to less than 0.5 seconds
for very large values.
Figure 9-3.
Impact of disk I/O size on the performance of a full table scan on four different systems
It's also possible to instruct the database engine to automatically configure the value of the
db_file_multiblock_
read_count
initialization parameter. To use this feature, simply don't set it. As shown in Formula 9-2, the database
engine will then try to set it to a value that allows 1MB physical reads. At the same time, however, a kind of sanity
check is applied to reduce the value if the size of the buffer cache is quite small compared to the number of sessions
supported by the database.
Formula 9-2.
Default value of the
db_file_multiblock_read_count
initialization parameter
1048576
db cache size
_
_
db file multiblock read count
_
_
_
_
≈
least
,
db block size sessions db block size
_
_
⋅
_
_
Because, as described earlier, physical reads of 1MB aren't always the ones that perform better, I'd advise against
using this feature. It's better to find out the optimal value case by case.
Be aware that if noworkload statistics are used with this automatic configuration,
mbrc
isn't replaced by the
automatically configured value in Formula 9-1. Instead, the value 8 is used.
optimizer_dynamic_sampling
