convert ORACLE SQL trace's timestamp with perl

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;
    }
}
Comments:

Thanks for sharing your script. This was a big help. I tried using Cary Millsap's perl script tim.pl from "Optimizing Oracle Performance" and couldn't understand why the timestamps I was seeing were incorrect.

Craig Jackson
Rally Software Development

Posted by Craig Jackson on July 21, 2009 at 08:56 PM JST #

Thanks for trying, Craig. I assume you ran it on SPARC Solaris ?? I just posted new blog and hope my observation is correct.

Posted by Katsumi INOUE on July 23, 2009 at 05:35 PM JST #

Actually, I ran it on CentOS 5.3, but it appears to have given me accurate results. I did convert the trace file immediately after creating it.

Craig

Posted by Craig Jackson on July 23, 2009 at 07:24 PM JST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today