Hello,
I've been struggling with this topic for a few days now, and I think I found an answer.
First: what is the tim value? Cary Millsap states in his book "Optimizing Oracle Performance" that it represents
"the V$TIMER.HSECS value for releases prior to Oracle9i".
This still seems to apply for current releases, as Metalink Article 39817.1 notes:
"tim Timestamp. Pre-Oracle9i, the times recorded by Oracle only have a resolution of 1/100th of a second (10mS). As of Oracle9i some times are available to microsecond accuracy (1/1,000,000th of a second).The timestamp can be used to determine times between points in the trace file. The value is the value in V$TIMER when the line was written. If there are TIMESTAMPS in the file you can use the difference between 'tim' values to determine an absolute time."
Example (taken from a UNIX System running 10g, so we have microsecond resolution here):
first timestamp in tracefile=2008-09-13 03:20:25, first tim=447543130411. The actual seconds are 447543 (rounded).
alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
declare
t0 date;
tim0 number := round(floor(447543134861/10E5)); /*** seconds after removing microseconds: 447543 ***/
begin
t0 := to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'); /*** epoch ***/
t0 := t0 + round(tim0/86400); /*** epoch plus days ***/
dbms_output.put_line('tim date: '||t0); /*** show the new date ***/
end;
/
tim date: 06.01.1970 00:00:00
Well this cannot be, since the trace was taken in September 2008. So what's wrong?
If tim is a value from V$TIMER, looking at the Oracle Documentation (10.2) helps understanding this:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2168.htm#REFRN30289
"
This view lists the elapsed time in hundredths of seconds. Time is measured since the beginning of the epoch, which is operating system specific, and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days)."
(maybe the documentation needs an update about the microseconds as of Oracle 10g).
This explains why you sometimes get strange results if you just take the seconds of the tim value, divide it by 86400 to get the days, and add the result to the epoch date: You cannot be sure if tim/10E5 (Oracle10g) or tim/100 (Oracle9i) is always seconds from epoch, since the value in V$TIMER is reset to 0 after appr. 497 days. It apparently depends on the uptime of your instance how small or big the difference to epoch really is.
So a direct conversion without knowing the difference between tim, epoch and the wallclock is not possible.
But, if you have a timestamp in your tracefiles it can serve as a starting point for futher use while reading a tracefile via an external_table or similar. Example (Oracle 10.2): store the first timestamp from the tracefile in a table as a timestamp and add NUMTODSINTERVAL((current_tim_value - first_tim_value)/10E5, 'SECOND') to it:
SQL> create table t1 (ts timestamp);
Table created.
SQL> insert into t1 values(timestamp '2008-09-12 04:57:10.338000'); /* first timestamp in trace*/
1 row created.
SQL> commit;
Commit complete.
/* (latest tim value - first tim value)/1000000 */
SQL> select ts + numtodsinterval(((377986953662-368834245626)/10E5), 'SECOND') new_timestamp from t1;
NEW_TIMESTAMP
12.09.2008 07:29:43.046036
1 row selected.
best regards,
Stefan Obermeyer
NCDC Midrange Operationsmanagement DBA
EDS, an HP company
