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.
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