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.