The average of an event hides the shape of the time: a thousand waits of 1 ms and one wait of 1 s add up to the same. The latency distribution splits the occurrences by duration bucket and shows which of the two cases you have.
What the section shows
The Latency distribution section (in the index, Latency) has a table for each of the wait events with the most time and one for each call type, with the type name followed by (call duration e), such as FETCH (call duration e). The call table uses the duration (e=) of all calls of that type, those of the application and those of recursive SQL.
Each table opens with a click. Its row shows the name, the occurrences and the seconds, such as 2 occurrences · 0.001 s.
How to read it
The buckets grow in powers of 10: from 0 to 1 µs, from 1 to 10 µs, from 10 to 100 µs, from 100 µs to 1 ms and so on, up to 1,000 s or more. On screen, each bucket appears as 1 ms ≤ e < 10 ms, where e is the duration of each occurrence.
| Column | What it says |
|---|---|
| Duration bucket | The duration interval of the bucket. |
| Occurrences | How many occurrences fell in the bucket. |
| % occ. | The share of the occurrences of the event or call that fell in the bucket. |
| Duration (s) | The sum of the duration of the occurrences in the bucket. |
| % duration | The share of the time of the event or call that fell in the bucket, with the bar next to it. |
Compare the two percentages. With % occ. and % duration both high in the same bucket, the time comes from volume: to reduce it, cut the number of occurrences. With most of the occurrences in a fast bucket and most of the duration in a slow bucket, a few slow occurrences dominate: look for the calls they fell in (longest calls).
In the sample report, db file sequential read has 2 reads: one in the bucket from 100 µs to 1 ms, with 23.1% of the duration, and one in the bucket from 1 to 10 ms, with 76.9%.
When it is a concern
- Single-block reads with a good part of the duration in the buckets of 10 ms or more: the storage is slow. The slow reads finding leads straight to the event table, with the View latency distribution link.
SQL*Net message from clientwith many waits in the microsecond and low-millisecond buckets: the application talks to the database too much. A few long waits point to slowness in the application itself (session waiting for the application).log file syncwith the duration in the buckets of several milliseconds: redo is written slowly; with many short waits, there are too many commits (commit waits).- A call table with few occurrences in the buckets of seconds: these are long calls, and the level-0 ones appear one by one in longest calls.
Where to go next
- Latency in the sample report and the db file sequential read table.
- Reads, with buckets designed for disk and for SSD.
- Response time profile, where the average, minimum and maximum of each event are.