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

Question

Make two examples: A full table scan will produce this result:

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

This is the result of access using a non-selective index range scan:

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

I would expect scattered readings after you take a lot of rows from a non-selective index. And only one sequential read to read the entire table in a defragmented I / O system.

Can you explain to me how to read this data?

Answer 1

The naming of these two pending events is rather confusing. Both of these mean I / O bottlenecks: db file scattered read usually comes from multi-block reads, which means a full table scan, while db file sequential read usually comes from single-block reads, which means indexed reads.

There are various theories about why Oracle engineers gave these events such explicitly misleading names. I heartily recommend that you read Eric Emric's essay on the subject. Why do vague thoughts about reading Oracle?

---

Regarding the interpretation of data, an indexed read is at least two logical readings: an index search, followed by a search for the row of the table indicated in the table. Things like wrapped lines can increase the number of reads. Thus, reading a large number of rows through an index is an expensive operation. While a full table scan can simply line up, using multi-block reads: it extends multiple rows for a single logical read. This is why a full table scan is usually more efficient for even relatively small percentages of the total row table.