Statistic Histograms

Although the metrics can give you an idea of the trend for particular statistics, they do not tell you if a particular bottleneck is affecting the whole system or if it is just localized. As an example, you can observe a high metric rate but this sudden increase could be localized to only one or two sessions in your system. In this case, it might not be worth investigating the issue. However, if the sudden increase is generalized to the whole system, you need to investigate further. This information is available via histogram performance views.

As shown in the diagram, you observe a sudden increase in your I/O rate. You can correlate this information to the corresponding I/O histogram found in V$FILE_HISTOGRAM. This view displays a histogram of all single block reads on a per-file basis. The histogram has buckets of time intervals, measured in milliseconds, from 1 ms up to 222 ms (69.9 minutes). The value in each bucket is the number of times the system waited for that amount of time. For example, you can see from the slide that the system waited 5,500 times for more than 32 ms and less than 64 ms to read blocks from disks. This is certainly a cause of concern for your system if the access times are normally less than 10 ms, and you should investigate this further. Had you seen large numbers in shorter wait time periods, you would not have worried much.

The metrics are able to alert you to a potential problem. By drilling down using histograms, you can clearly determine whether there really is a problem.

Histogram Views

V$EVENT_HISTOGRAM displays a histogram of the number of waits on an event basis.

V$FILE_HISTOGRAM displays a histogram of all single block reads on a per file basis.

V$TEMP_HISTOGRAM displays a histogram of all single block reads on a per temp-file basis.

The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.

You can also visualize the histogram statistics from Enterprise Manager. From the Performance page, click one of the legend area items for the Active Sessions graph such as User I/O to drill down to the Active Sessions Waiting: User I/O page. Again, click the wait event name to the right of Active Sessions graph to reach the corresponding “Histogram for Wait Event” page. The “Histogram for Wait Event: db file sequential read” graph is shown in the slide.

Note: The histogram will not be filled unless the TIMED_STATISTICS initialization parameter is set to TRUE, which is the default value. TIMED_STATISTICS is set automatically when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.