Let the Sunshine In: Software support engineer in Tokyo

ORACLE 10.2 is one of supported RDBMS for Sun Java System Identity Manager. I was comparing oracle's sql trace with Sun IdM's log and noticed sql trace doesn't show user friendly timestamp. So, I googled.

OTN Discussion Forums : Event 10046 and timestamp ...

When setting event 10046 for tuning purpose, I'd like to get the timestamp to compare with other logs (application, OS, ...)
Is it possible ?
How to convert "tim=8317892534" in readable date/time ?

Oracle 10046 tim, e and ela Values use Nanoseconds/1024 not Microseconds, on some Platforms - oracle-l - FreeLists

In recent attempts to correlate 10046 extended trace data from Oracle 10.2, with DTrace data collected under Sun Solaris, I discovered that the tim, ela and e fields in Oracle trace data are not measured in microseconds.

I got hints from above and created this quick & dirty perl. I tested this only on SPARC Solaris10. I think I can elaborate some more in my next blog entry.

#!/usr/bin/env perl
use Time::HR;
use Time::HiRes;

my $diff = Time::HiRes::time() - gethrtime()/1000000000;

line: while (<>) {
    if (m/^([EFPW].*tim=)([0-9]*)(.*)$/o) {
        my $seconds = $2*1024/1000000000 + $diff;
        my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($seconds);
        my $subsec = substr($seconds, index($seconds, '.'));
        printf ("%s%4d-%02d-%02d %02d:%02d:%02d%s %s\n",$1,$year+1900,$mon+1,$mday,$hour,$min,$sec,$subsec,$3);
        next line;
    }
    else {
      print $_;
      next line;
    }
}