Oracle wait events: what do you interpret db file scattered/sequential read?

Question

Let's do two examples: A full table scan leads to this result:

                               TOTAL   TIME
EVENT                          WAITS WAITED
------------------------------ ----- ------
db file scattered read           460     13

This is the result of the access through an unselective Index Range Scan:

                               TOTAL   TIME
EVENT                          WAITS WAITED
------------------------------ ----- ------
db file scattered read            15      3
db file sequential read         6209    140

I would like to expect scattered reads after taking many rowid from the unselective index. And just one sequential read to read all the table on a defragmented I/O System.

Can you explain me how to read this data?

Answer 1

The naming of these two wait events is rather confusing. Both of them signify I/O bottlenecks: db file scattered read usually derives from multi-block reads, which means full table scans, whereas db file sequential read generally come from single-block reads, which signifies indexed reads.

There are various different theories about the reason why Oracle engineers gave these events such apparently misleading names. I heartily recommend you to read Eric Emrick's essay on the subject, Why Have Scattered Thoughts About Oracle Reads?

---

As for interpreting the data, an indexed read is at least two logical reads: a look-up of the index, followed by a look-up of the table row indicated by the table. Things such as migrated rows might increase the number of reads. So reading a large number of rows through an index is an expensive operation. Whereas, a full table scan can just hoover up rows using multi-block reads: that is pulling in many rows for a single logical read. This is why a full table scan is usually more efficient for retrieving even relatively small percentages of a table's total rows.