Database Reference
In-Depth Information
user calls, it's almost always doing disk I/O operations that read a single block ( db file sequential read ). All other
wait events and the CPU utilization are negligible.
For some wait events, like the one associated with disk I/O operations, you might want to get information about
the average latency. In fact, if you have that information, you can compare the experienced performance with the
expected performance (you should know what to expect from the disk I/O subsystem used to store the database).
Based on a view like v$system_event , you can, for example, run a query to compute the average latency of a specific
wait event:
SQL> SELECT time_waited_micro/total_waits/1E3 AS avg_wait_ms
2 FROM v$system_event
3 WHERE event = 'db file sequential read';
AVG_WAIT_MS
-----------
9.52927176
Given that an average value like the one computed by the preceding query hides a lot of information,
Oracle Database provides a view that presents, at the system level, a histogram for each wait event. The view is
v$event_histogram . It has three key columns:
event is the name of the wait event,
wait_time_milli represents the interval's upper limit (which isn't included) of the
histogram's bucket, and
wait_count is the number of wait events associated to the histogram's bucket.
For example, the following query shows that while a plurality (45.7 percent) of the wait events took between
4 and 8 milliseconds, about 24 percent (3.27 + 2.75 + 18.37) took less than 4 milliseconds, and about 10 percent
(5.96 + 2.66 + 1.34 + 0.17 + 0.01) took 16 milliseconds or more:
SQL> SELECT wait_time_milli, wait_count, 100*ratio_to_report(wait_count) OVER () AS "%"
2 FROM v$event_histogram
3 WHERE event = 'db file sequential read';
WAIT_TIME_MILLI WAIT_COUNT %
--------------- ---------- ------
1 348528 3.27
2 293508 2.75
4 1958584 18.37
8 4871214 45.70
16 2106649 19.76
32 635484 5.96
64 284040 2.66
128 143030 1.34
256 18041 0.17
512 588 0.01
1024 105 0.00
2048 1 0.00
In statistics like the previous ones, it's especially interesting to check what the maximum values are. In this
example, notice that, according to typical expectations, some disk I/O operations were far too long (between
Search WWH ::




Custom Search