Typically, an OLTP workload reads or updates a single record at a time, and therefore, Oracle reads or modifies single database blocks (typically 8 KB in size). This is also the I/O size of read and write operations to the data files that comprise an OLTP workload. However, when a query requires a set of data to be fetched, such as for a report, a list, merge of data from multiple sources, or predicate evaluation, Oracle will perform a multi-block read in a single operation.
When Oracle performs a multi-block read it issues large I/Os of sizes up to 1 MB. The size of the large reads can be controlled by a database parameter: db_file_multiblock_read_count (MBRC). This database parameter determines the Oracle maximum size of a multi-block read I/O operation. The maximum I/O size is calculated as a multiplication of the MBRC and the Oracle block size. With an 8 KB database block size, when MBRC is set to 16, the result is a maximum of 128 KB database read I/O size (16 x 8KB = 128KB). When MBRC is set to 128, the result is a maximum of 1 MB database read I/O size (128 x 8 KB = 1,024 KB).
Our tests showed very small differences in bandwidth between the two options. However, the I/O size has a big effect on IOPS and latencies. A 128KB I/O size is eight times smaller than a 1MB I/O size, and will therefore generate more IOPS to achieve the same bandwidth. However, because each I/O is smaller, the response time is much lower than a 1MB I/O.
Our recommendation is to use the 128KB I/O size, as most environments run a mix of OLTP and DSS workloads (single-block, and multi-block operations). In a mixed environment, allowing lower latency for the multi-block operations means that single-block I/Os are not waiting long to be serviced. Even if the system is a dedicated data warehouse (mainly multi-block reads), since the bandwidth is very similar, the lower latency is still an advantage, as long as total IOPS are not an issue to the overall system utilization.