Simple Performance Tuning for Oracle PL/SQL using SYSTIMESTAMP

Starting in Oracle 9.x, there is a more precise version of SYSDATE that goes to the millisecond. Previously, the only way to get to milliseconds was to use dbms_utility.get_time (which didn't really return a real time but a counter position within a time loop that recycled itself).

---

Code :

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)||'  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:

02-OCT-06 11.17.00.863610 AM
02-OCT-06 11.17.01.878845 AM

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