tracefile tim to readable date format

In trace file, the time is recorded, this could be used to measure time between two timestamps

But how do you convert 31796862227375 to a human format?

This is how I proceeded :

SQL> oradebug SETMYPID
Statement processed.
SQL> oradebug tracefile_name
/u01/log/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_32440740.trc
SQL> alter session set 
  nls_date_format='YYYY-MM-DD_HH24:MI:SS' 
  sql_trace=true;
Session altered.
SQL> select '&_DATE','Start' from dual;
2015-09-23_13:17:50 Start
SQL> select '&_DATE','End' from dual;
2015-09-23_13:18:38 End
SQL> alter session set sql_trace=false;
Session altered.

I use the sqlplus variable _date and waited about a minute between the select’s.

Now let’s look at the trace

PARSING IN CURSOR #4859519800 tim=31796814530524
select '2015-09-23_13:17:50','Start' from dual

PARSING IN CURSOR #4859511384 tim=31796862227375 
select '2015-09-23_13:18:38','End' from dual

Note the tim=

Between 13:17:50 and 13:18:38 I have 48 seconds; the difference between 31796862227375 and 31796814530524 is 47696851, roughly 48 millions of microseconds.

So the time on this specific version of Oracle is in microseconds.

Now if I substracted 31796862227375 from 2015-09-23_13:17:50 and I get 2014-09-20_12:50:08 on this specific instance.

So to convert tim= to human readable date, I simply add tim microseconds to Sep 20, 2014, 12:50:08.

5 thoughts on “tracefile tim to readable date format”

  1. anonym

    Hi !
    Didnt get it ?
    how do you subtract a number 31796862227375 from string 2015-09-23_13:17:50 ?
    regards

  2. Laurent Schneider Post author

    convert microseconds in days (divide by 1000000 us/sec divide by 60 s/min from the date

    select
    to_date('2015-09-23_13:17:50','YYYY-MM-DD HH24:MI:SS')-
    31796862227375/24/60/60/1000000
    from dual;
    2014-09-20 12:50:08

  3. Anonymous

    The tim value can be converted into wall-clock time by using the following:

    Take the first 10 digits of tim value , and then execute in console:

    $date -d @ten_first_digits +”%m-%d-%Y %T”

  4. Laurent Schneider Post author

    This is not working in my example above

    date -d @3179686222 ‘+%m-%d-%Y %T’
    10-04-2070 23:10:22

    If tim were since 1-JAN-1970, then it would work. But it is on none of the database I tested.

  5. Hans Henrik Krohn

    A more low-level approach is a script to make the “tim=” value more readable in the trace-file; I usually insert a decimal point to separate the seconds from the microseconds with a small ‘sed’ script:

    cat $tracefile | sed ‘s:\(tim=[0-9]\{8,\}\)\([0-9]\{6,\}\):\1.\2:’ | less

Comments are closed.