Oracle-L: RE: tim column in trace output

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 24 Jan 2003 07:29:32 -0800
Message-ID: <F001.00539ABA.20030124072932@fatcity.com>

Djordje,

You're right; in Oracle8, tim is measured in centiseconds (0.01s).
The value is the same as that found in v$timer.hsecs.
We're not sure whether the Epoch for this clock is even meaningful or not (I don't think we've been able to prove that the value resets when you do an instance restart. Perhaps the value is a modulus of a gettimeofday() value.)
Item #3 has never really bothered us, because all we generally care about is *relative* tim values, not absolute ones. You can establish a mapping of tim values to wall clock values by comparing the timestamp in a '***' line to the nearest tim value to it.
Oracle9i makes life a little more fun, because tim values become microsecond (0.000001s) values, and they appear to be unadulterated gettimeofday() values, with an Epoch of 00:00:00 UTC, 1 January 1970 (we *think* this is true on all platforms). Hence, it becomes easy to convert from tim values to wall clock values and back with a simple Perl script that uses str2time and time2str function calls.
Finally, a minor detail correction: the tim of a given line in the trace file tells you the time at which the given cursor action *completed*. You certainly aren't incorrect by saying it's the time that something "ran," but saying "completed" is a little more precise.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- 2003 Hotsos Symposium, Feb 9-12 Dallas
- RMOUG Training Days 2003, Mar 5-6 Denver
- Hotsos Clinic 101, Mar 26-28 London

-----Original Message-----
Jankovic
Sent: Friday, January 24, 2003 6:55 AM
To: Multiple recipients of list ORACLE-L

Anybody knows what is the reference point for the timing used in the "tim"
column in the trace output, like in:

PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=832261739 EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=832261739

Apparently the scale is 100 per second for 8i and 976,562.5 (1,000,000,000/1024) for 9, but I am not quite clear what could be the reference time (the time when counting of tim starts). In different databases I tried it, it is usually few months to a year back.

BTW, this column can be used if one needs to find out the exact time when a
query from the trace was run.

Thanks.

Djordje

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Djordje Jankovic
INET: djordjej_at_rogers.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Cary Millsap
INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------