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.
Timings in SQL Trace files
Optimizing Oracle Performance Cary Millsap,Jeff Holt Chapter 5 Interpreting Extended SQL Trace Data tim If a tim value is 0, then TIMED_STATISTICS for the session was false when the database call time would have been calculated. You can thus confirm whether TIMED_STATISTICS was true by observing tim values. In our field work, my colleagues and I have found that specific non-zero tim values associated with PARSING IN CURSOR sections are largely irrelevant. In Oracle 9i [and higher] , tim is a value expressed in microseconds (1 us = 0.000 001 seconds). On some systems (such as our Linux research servers), tim field values are unadulterated gettimeofday values. On other systems (like our Microsoft Windows research machines), the origin of tim field values can be much more mysterious.In releases prior to Oracle9i, tim is a V$TIMER.HSECS value expressed in centiseconds (1 cs = 0.01 seconds).
Thanks to Gints Plivna for the reference to Cary’s book info