V$EVENT_HISTOGRAM

Oracle 11gR1

V$EVENT_HISTOGRAM displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis. The histogram has buckets of time intervals from <>21 ms, <>22 ms, and >= 2²² ms.

The histogram will not be filled unless the TIMED_STATISTICS initialization parameter is set to true.

Column	Datatype	Description
EVENT#	NUMBER	Event number
EVENT	VARCHAR2(64)	Name of the Event
WAIT_TIME_MILLI	NUMBER	Amount of time the bucket represents (in milliseconds). If the duration = num, then this column represents waits of duration < num that are not included in any smaller bucket.
WAIT_COUNT	NUMBER	Number of waits of the duration belonging to the bucket of the histogram

Note:

1. Many DBAs are familiar with the concept of a histogram from either statistics classes or earlier versions of Oracle where histogram data that allowed the CBO to get a better understanding of the data distribution could be collected. For example, if a small company with 10 employees that had been employed for 10 years recently hired a new person, the hire_date field would be highly skewed. Any query that used hire_date as a selective criterion would be more effective if this field had a histogram.

The v$event_histogram view is similar to index histograms in that it conveys information about the distribution of wait events. It allows the comparison of the frequency and “weight” of a wait event. This allows the DBA to see if the normal wait time is high, or if there are a few outlier events where the wait time was significant. The following query can be used to look at events relating to database file activity (i.e. db file sequential/scattered/parallel read/write). This example can be modified to retrieve any information of interest by simply changing the where clause.

select event#, event, wait_time_milli, wait_count

from v$event_histogram

where event like 'db%';

EVENT#	EVENT	WAIT_TIME_MILLI	WAIT_COUNT
115	db file sequential read	1	198702344
115	db file sequential read	2	8841988
115	db file sequential read	4	26526882
115	db file sequential read	8	111580458
115	db file sequential read	16	69830134
115	db file sequential read	32	16256754
115	db file sequential read	64	4515376
115	db file sequential read	128	1108502
115	db file sequential read	256	272993
115	db file sequential read	512	60321
115	db file sequential read	1024	16160
115	db file sequential read	2048	21
115	db file sequential read	4096	9
115	db file sequential read	8192	23
115	db file sequential read	16384	16

Oracle dynamic performance views