Slow single-block reads

A single-block read is the typical index access, and the average per read measures the storage. A high average points to the disk or to the path to it, but the distribution tells whether the problem affects all the reads or only a few.

Symptoms

In the Diagnosis, one of these findings (LEITURA_LENTA) appears, with the event name at the end of the title:

• Slow single-block reads in db file sequential read, when the average per read is well above what healthy storage delivers. The text recalls that a fast disk or flash responds in under 1 ms and asks you to check the storage and the I/O queue.
• Single-block reads slower than expected in db file sequential read, when the average is above what is expected without getting that far.

The text gives the average in milliseconds, the number of reads and the percentage of the total time, for example Average of 12.5 ms per read over 4 reads (4.9% of the total time). The link is View latency distribution, or View in profile when the event has no latency table.

The severity is high for the first title and medium for the second. When the event weighs little in the total time, the finding comes out as low, just for the record: the average is bad, but speeding up the storage barely changes the time of this operation.

It applies to the waits for reading one block at a time, such as db file sequential read and cell single block physical read. Each slow event raises its own finding.

Cause

The plan decides how many reads the session does; the storage decides how long each one takes. A high average usually comes from:

• storage overloaded during the period, with an I/O queue: other sessions, a backup, a nightly batch or another system on the same storage;
• reads that go to disk instead of hitting the storage cache or flash (on Exadata, the block was not in the flash cache);
• a problem in the I/O path: multipath, controller, storage network;
• a few very slow reads pulling the average up, with most of them fast.

Resolution

1. Look at the distribution before the average. In the latency distribution of the event and in Reads, with buckets for disk (boundaries from 5 ms to 1 s) and for SSD (boundaries from 15 µs to 10 ms), check whether all the reads are slow or whether a few slow ones weigh more.
2. Check the storage during the Period of the trace: service time and queue of the disks, for example with iostat, and whatever else was running on the same storage.

   iostat -x 5

3. In the database, compare with the latency the whole instance sees for the same event. The numbers are cumulative since the instance started.

   select wait_time_milli, wait_count
     from v$event_histogram
    where event = 'db file sequential read'
    order by wait_time_milli;

4. Reduce the number of reads, which usually pays off more than speeding up the disk: see many single-block reads.
5. If the problem is in the storage, take the average, the buckets and the period from the report to whoever manages it.

How to confirm

After the change in the storage or in the plan, generate a new trace of the same operation. In the event row of the profile, Avg (s) should drop, close to what the database storage delivers (reads gives the reference for disk and SSD), and the finding disappears. Also compare the buckets in Reads: the slow part should shrink.

In the report

In the sample report, the 2 reads of db file sequential read average 0.000650 s (0.65 ms), without a finding. The links open the sections of the example:

• Diagnosis: the finding and the View latency distribution link.
• Profile, db file sequential read row: Occurrences, Avg (s) and Max (s).
• Latency distribution of the event: how many reads fall into each bucket.
• Reads, disk buckets and SSD buckets: finer buckets for all the read waits.
• Objects with the most wait time: in which table or index the time was spent.