Monday, November 27, 2006

PL/SQL : Example using SysTimeStamp (milliseconds for SysDate)

Oracle's SYSDATE only goes to the second. If you want more precision, then you need to use the new SYSTIMESTAMP (available starting in Oracle 9). Source :

declare

ltimestamp_start       timestamp;
ltimestamp_stop        timestamp;
linterval_diff         interval day to second;

ldt_temp               date;

begin

ltimestamp_start := systimestamp;
dbms_output.put_line(ltimestamp_start);

  -- Routine to performance test... 
  for i in 1 .. 10000 loop
    select sysdate into ldt_temp from dual;
  end loop;

ltimestamp_stop := systimestamp;
dbms_output.put_line(ltimestamp_stop);

linterval_diff := ltimestamp_stop - ltimestamp_start;

dbms_output.put_line(CHR(10)||LPAD('=',22,'=')||CHR(10));
dbms_output.put_line('  Runtime Difference'||CHR(10)||LPAD('=',22,'='));
dbms_output.put_line(
     '     Days : '||EXTRACT(DAY FROM linterval_diff)||CHR(10)||
     '    Hours : '||EXTRACT(HOUR FROM linterval_diff)||CHR(10)||
     '  Minutes : '||EXTRACT(MINUTE FROM linterval_diff)||CHR(10)||
     '  Seconds : '||EXTRACT(SECOND FROM linterval_diff) );
end;
Results :
27-NOV-06 01.26.50.118424 PM
27-NOV-06 01.26.51.125141 PM

======================
Runtime Difference
======================
Days : 0
Hours : 0
Minutes : 0
Seconds : 1.006717

6 comments:

S├ębastien Lesaint said...

Thanks for this post, helped me benchmark pl/sql methods of mine that always took less than one seconds (but are used in reality quite intensively).

Seb'

Anonymous said...

Thank you very much, this helped me a lot.

God bless and keep up the good work

Anonymous said...

Thank you! That's exactly what I was looking for!

Anonymous said...

Great, thanks a lot!

Anonymous said...

Thank you!! u da man!! Just awesome!! It helped me so much!

Vova said...

Thank you very much!
Indeed what I was looking for.