Many single-block reads

Read time is the number of reads times the average of each one. When the average is normal and the time is high, the problem is the quantity: the plan visits too many blocks for the rows it returns.

Symptoms

In the Diagnosis, the finding Many single-block reads (db file sequential read) (MUITAS_LEITURAS) appears, with the event name in parentheses. The text says how many reads add up to what share of the total time, for example 25,000 reads, totaling 32.4% of the total time, suggests checking whether the plan reads too many blocks and ends with View latency distribution, or View in profile when the event has no latency table.

The finding appears when a wait for reading one block at a time, such as db file sequential read or cell single block physical read, takes a large share of the total time, whatever the average per read. The severity is always medium. It can appear together with slow reads: one is about the quantity, the other about the speed.

Cause

Common causes:

• an unselective index, which returns many ROWIDs and leads to one table visit for each (TABLE ACCESS BY INDEX ROWID);
• NESTED LOOPS over many rows, with one lookup in the index of the inner table for each row of the outer one;
• an index with a high clustering factor: rows that are neighbors in the index are spread across many table blocks;
• the same SQL executed once for each row of a loop (row by row in PL/SQL);
• a buffer cache too small for the data volume, with the same block read from disk more than once.

Resolution

1. Find the statement. In Top statements by component, open the group of the read event and see the statements that read the most.
2. In the statement detail, under Where the plan spends time, see the operation that concentrates the time, with the plan columns PR (physical reads) and Rows. Many reads for few rows point to unselective access.
3. Find the object in Objects with the most wait time. In the console, the obj# opens the object page, with ready-made queries such as Table: indexes and their columns and Index: details, table and columns.
4. Fix the access: an index with the filter columns, in the right order; HASH JOIN instead of NESTED LOOPS when the volume is large; up-to-date statistics when the estimate is wrong (wrong row estimate).
5. If the same SQL runs once per row, replace the loop with set-based processing (row by row in PL/SQL).
6. If rereading the same block weighs (in Reads, First read and reread of the same block), evaluate the size of the buffer cache.

How to confirm

Generate a new trace of the same operation after the change. In the event row of the profile, the Occurrences should drop; for the fixed statement, so should the PIO column of SQL statements. The finding disappears when the event no longer takes a large share of the total time.

In the first execution after the change, part of the blocks may come from disk because they are not in the cache yet. Compare measurements taken under the same condition: both with a cold cache, or both with the cache already warm.

In the report

In the sample report, db file sequential read adds up to 18.5% of the total time in 2 reads, without a finding. The case 2 report, from a real trace, has this finding (Diagnosis of case 2). The links below open the sections of the example:

• Diagnosis: the finding and the link to the event latency.
• Profile, db file sequential read row: Occurrences and % of total.
• Top statements by component, event group: the statements that read the most.
• Plan of statement #1: the TABLE ACCESS BY INDEX ROWID operation highlighted.
• Objects with the most wait time and Reads: the object read and the rereads of the same blocks.