The reads section looks only at read waits: in duration buckets designed for disk and for SSD, and by rereads of the same block, which show how often the session went back to disk for data it had already read.
What the section shows
The Reads section appears when the trace has read waits and has up to three tables:
- reads (disk buckets) and reads (SSD buckets): the same reads, in different buckets. They include read waits such as
db file sequential read,db file scattered read,direct path readandread by other session. - First read and reread of the same block: for the waits that carry the file and block read (
file#andblock#), such asdb file sequential readanddb file scattered read.
How to read it
The two bucket tables have the columns of the latency distribution: Duration bucket, Occurrences, % occ., Duration (s) and % duration. Only the buckets change:
| Table | Buckets |
|---|---|
| reads (disk buckets) | up to 5 ms, in 5 ms steps up to 25 ms, from 25 to 50 ms, from 50 to 100 ms, from 100 ms to 1 s, and 1 s or more |
| reads (SSD buckets) | up to 15 µs, from 15 to 25 µs, from 25 to 50 µs, from 50 to 100 µs, from 100 to 500 µs, from 500 µs to 1 ms, from 1 to 10 ms, and 10 ms or more |
Use the table for the storage the database has: on disk, a 3 ms read is good; on SSD or flash, it is slow.
| Column | What it says |
|---|---|
| Event | The read event. |
| Distinct blocks | How many different blocks (file and block) the event read. |
| 1st read and 1st read (s) | How many reads were of a block read for the first time in the trace, and their time. |
| Rereads and Rereads (s) | How many reads were of a block the trace had already read before, and their time. |
In a very large trace, the analysis can reach the limit of distinct blocks it tracks. From then on, a new block counts as a first read, and a warning at the top of the report says so: rereads end up below what they were.
In the sample report, block 100 of file 1 was read twice: 1 distinct block, 1 first read and 1 reread, which took 0.001 s.
When it is a concern
- In the table for your storage, a good part of the duration falls in the slowest buckets: the storage is slow or contended (slow reads).
- The reads are fast, but there are many of them: the problem is volume, and the cause is usually in the plan, which reads too many blocks (many reads).
- Rereads with a good part of the time: the block left the buffer cache before being used again. The console sums it up: Frequent rereads of the same block point to a small cache or repeated access to the same data.
Where to go next
- Reads in the sample report and the disk buckets.
- Objects with the most wait time, to know which table or index was read.
- Latency distribution, with the buckets of each event separately.