Up to this point, all wait time discussed has been the average wait time. Just as with weather temperatures, you should expect values both above and below the average. While the average conveniently simplifies potentially thousands of values into a single value, we do lose some information through this statistical simplification. Starting with Oracle Database 10g, the view v$event_histogram provides a more detailed look into the actual wait times, providing more than just the simple statistic of average.

Figure 5-11 is a good example of how a histogram perspective adds important additional information to your analysis. While not shown, a standard wait event report showed the top wait event to be db file scattered read, with an average wait time of 14.9 ms. While the average multiblock read request is greater than my rule-of-thumb of 10 ms, some IO administrators will argue this is not significant enough to motivate change. But what most people miss is that over 25% of the multiblock read requests took more than 16 ms, and 18% of the requests took an excess of 32 ms! That is shocking and makes the argument to reduce IO response time stronger and more urgent.

Figure 5-11. Based on the v$event_histogram view and during the report interval, while the average wait time is 14.9 ms (not shown), it can seen that about 25% of the scattered read waits took more than 16 ms. The far-right column is a running total.