Oracle time units in V$ views

July 20th, 2011

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.


Uncategorized

  1. Trackbacks

  2. October 23rd, 2011: Tuning Blog Entries « Ukrainian Oracle User Group
  1. Comments

  2. Henry Poras
    July 21st, 2011 at 21:16 | #1

    Thanks. This stuff always drives me nuts.

  3. July 21st, 2011 at 22:07 | #2

    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

  4. Bob
    October 31st, 2012 at 15:40 | #3

    Thank you for identifying the UNITs for the waits.
    This info is really hard to find…

  5. Emre
    March 25th, 2013 at 20:34 | #4

    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

You must be logged in to post a comment.