Oracle time units in V$ views
Oracle has a crazy mix of units of time in various v$ views
- seconds
- centi-seconds
- milliseconds
- microseconds
Some are straight forward such as time_waited_micro, but what unit is “TIME_WAITED” or “WAIT_TIME” in? For example
v$session
WAIT_TIME – centi
SECONDS_IN_WAIT – seconds
v$session_wait
WAIT_TIME – centi
SECONDS_IN_WAIT – seconds
v$system_event
TIME_WAITED – centi
AVERAGE_WAIT – centi
TIME_WAITED_MICRO – micro
v$system_wait_class
TIME_WAITED – centi
v$eventmetric
TIME_WAITED – centi
v$waitclassmetric
DBTIME_IN_WAIT – “percentage of the measured wait time that was actually in foregrounds and therefore part of DB time” *
TIME_WAITED – centi
v$waitclassmetric_history
DBTIME_IN_WAIT – “percentage of the measured wait time that was actually in foregrounds and therefore part of DB time” *
TIME_WAITED – centi
dba_hist_system_event
TIME_WAITED_MICRO – micro
v$active_session_history
WAIT_TIME – micro, not for general use
TIME_WAITED – micro, only the last sample is fixed up, the others will have TIME_WAITED=0*
dba_hist_active_sess_history
WAIT_TIME – micro , not for general use
TIME_WAITED = micro
v$session_wait_history
WAIT_TIME – centi
WAIT_TIME_MICRO – micro, 11g only
TIME_SINCE_LAST_WAIT_MICRO – micro, 11g only
in 10g, v$session_wait_history is pretty worthless IMO as one of the best uses of it would be to find average wait times for events, and even histograms of wait times and better yet, correlating I/O sizes with I/O times, but alas as most interesting operations are in the micro to millisecond times and wait_time is in centi, most of the interesting data is lost, luckily this is fixed in 11g
With the list in one place it looks like everything is centi unless otherwise stated except for ASH which is micro.
Please correct and/or add other examples to this list – thanks
* thanks to John Beresniewicz for this info.
Oracle L
Trackbacks
Comments
Thanks. This stuff always drives me nuts.
yeah, it’s depressing how much time these little inconsistencies blow for me, and especially because the documentation is wrong. The wrong documentation is what prompted me to do this post. Notice below the 11g doc says V$WAITCLASSMETRIC.TIME_WAITED is in microseconds. It’s not !
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3115.htm#sthref3349
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC displays metric values of wait classes for the most recent 60-second interval. A history of the last one hour will be kept in the system.
Column Datatype Description
BEGIN_TIME DATE Begin time of the interval
END_TIME DATE End time of the interval
INTSIZE_CSEC NUMBER Interval size (in hundredths of a second)
WAIT_CLASS# NUMBER Number of the class of the wait event
WAIT_CLASS_ID NUMBER Identifier of the class of the wait event
AVERAGE_WAITER_COUNT NUMBER Average waiter count
DBTIME_IN_WAIT NUMBER Percent of database time spent in the wait
TIME_WAITED NUMBER Time waited during the interval (in microseconds)
WAIT_COUNT NUMBER Number of times waited
Thank you for identifying the UNITs for the waits.
This info is really hard to find…
Hii Kyle
is there any relation between “Average Active Sessions” in v$sysmetric_history and “average_waiter_count” in v$waitclassmetric_history.
I think Average Active Sessionsx100=average_waiter_count sometimes that calculations gives same result but sometimes there is discrepancy.What do you think about that Which one I should use for AAS ?
Best Regards