Categories
dba

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.

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 ?
regards

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

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.