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
SQL> alter session set 
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.

By Laurent Schneider

Oracle Certified Master

5 replies on “tracefile tim to readable date format”

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

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

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

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”

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.

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

Leave a Reply

Your email address will not be published.