Oracle: What is Single-Block vs. Multiblock IO

Question

So I know this is a really basic question but I'm struggling to find an answer on Google, Wiki or Oracle. (Every tech document I see assumes that these topics are already understood)

I'm reading a great article on the intricacies of the Oracle Optimizer and how Explain Plan determines the cheapest plan for a SQL query. Right now I'm on the section about the different access methods (such as Full Index Scan and Fast Full Index Scan) and I keep seeing references to Single-Block vs. Multiblock I/O reads.

I've seen these concepts before but I only vaguely understand that it has to do with how the Oracle engine accesses the data stored on blocks.

Questions

1. Can someone explain to me the difference between these two read types and why one might be more advantageous over the other in certain situations?
2. What is the significance of the Full Index Scan using a single-block read vs. the Fast Full Index Scan using a multiblock read?

Answer 1

Multiblock I/O means how many database blocks are read with a single operating system READ call. In current releases (11gR2 and 12c), the default value corresponds to the maximum I/O size that can be performed efficiently. The maximum I/O size value is platform-dependent and is 1 MB for most platforms. Multiblock I/Os are controlled by the DB_FILE_MULTIBLOCK_READ_COUNT database parameter.

With other words: Usually these days a database block is 8kb. 8kb can include several rows of an table and even more index keys if it is used for an index leaf entry. Now when you do a Singleblock I/O you read 1 of these 8kb blocks - typically a row id lookup by index key value (determine the block where the index key reside and do a single block I/O to get the database block containing the actual row). However, if you read a lot of data because you do e.g. aggregations on huge tables, normally in data warehousing environments, it would be less efficient to ask for an 8kb block after an 8kb block after an 8kb block when the underlying I/O system can for example read 1MB in one physical read. Therefore Oracle issues a Multiblock I/O and requests 1MB worth of block (128 8kb blocks) in one system READ call rather than 128 individual requests and therefore speeds up performance of the I/O requests.

Have a look at the Database Reference for the DB_FILE_MULTIBLOCK_READ_COUNT parameter and Requested I/O size in the Database Performance Tuning Guide for further information on this. Latter is also a great resource to understand Oracle Database Performance in general.