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.

date or timestamp literals

Yesterday I commented on a post on the French developer forums http://www.developpez.net about using DATE and TIMESTAMP literals.

A date literal is DATE '2000-01-01'. It is not possible to have a date literal at 6am, like DATE '2000-01-01 06:00:00', the syntax simply does not exist. You can either use TO_DATE function, or if you are really hooked on litterals, maybe DATE '2000-01-01'+INTERVAL '+0 06:00:00' DAY TO SECOND.

One suggestion was to use TIMESTAMP, because TIMESTAMP literals do have time in it.

However, comparing a date with a timestamp will result in an implicit conversion to timestamp, and if the date colum is indexed, the index will not be used.

SQL> create table lsc_t(x number, d date)
Table created.
SQL> create index lsc_i on lsc_t(d)
Index created.
SQL> insert into lsc_t(d) values (to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS'))
1 row created.
SQL> select * from lsc_t where d>=timestamp '2000-01-01 06:00:00'
         X D
---------- -------------------
           2000-01-01 06:00:00
Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS FULL TGPOWNER.LSC_T
SQL> select * from lsc_t where d>=to_date('2000-01-01 06:00:00',
  'YYYY-MM-DD HH24:MI:SS')
         X D
---------- -------------------
           2000-01-01 06:00:00

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=CHOOSE
   1    0    TABLE ACCESS BY INDEX ROWID TGPOWNER.LSC_T
   2    1      INDEX RANGE SCAN TGPOWNER.LSC_I

As shown above, the index is not used when comparing to timestamp.

Why does Oracle convert the date to timestamp ? Because timestamp may contain nanoseconds.

SQL> select * from lsc_t where 
  d>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  cast(d as timestamp)>=timestamp '2000-01-01 06:00:00.000000001';

no rows selected

SQL> select * from lsc_t where 
  d>=cast(timestamp '2000-01-01 06:00:00.000000001' as date);

         X D
---------- -------------------
           2000-01-01 06:00:00

Converting to date would deliver wrong result.