V$EVENT_HISTOGRAM_METRIC

V$EVENT_HISTOGRAM_METRIC does not exist (at least up to 11.2.0.3) however it could be handy! The idea is combine the detailed information from event histograms and the ease of use of metric views. Here below an example of how this can be implemented with a sample script and a reference to the code of the script.

Example: Measure latency for single block reads for an OLTP database, as exposed by Oracle wait event interface as 'db file sequential read'. This is used to investigate a case of performance degradation where storage behaviour is involved.

Step 1: collect GV$EVENT_HISTOGRAM data in a period of normal activity using the script ehm.sql

Step 2: Plot data and analysis. Roughly 30 % of the reads are accounted as being competed in less than 1 ms. Those reads are being served from the SSD cache of the storage. A peak of latency is around 16 ms. We can interpret that as reads from physical disks (SATA disks at 7.2K rpm, that's why not very fast BTW)

Step 3: collect GV$EVENT_HISTOGRAM data during a period when applications report performance issues and plot data.

Analysis: Data measured in step 3 show that there are almost no blocks read from the SSD cache (that is there is no peak around the 1 ms measurement point) and that reads from physical IO have very high latency and definitely higher latency than normal (compare with graph above).
Note: This is the same case as discussed in a previous blog entry, see there for further details analisys of the investigations on what triggers this type of behaviour in the storage: "Of latency, skew and histograms 2/2"


The point I want to make: event histogram data gives valuable information for troubleshooting. In particular I have found useful the study of db file sequential read event. I is important to collect data samples over an interval of time of interest, as opposed to using raw data from gv$event_histogram that are cumulative since instance(s) startup.

This article describes the use of the script ehm.sql v1.0. A newer version has been released since. Both the old and new version can be found at this link.