Monday, October 02, 2006

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 :

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

   ldt_temp     date;


   ltimestamp_start := systimestamp;

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

   ltimestamp_stop := systimestamp;

   linterval_diff := ltimestamp_stop - ltimestamp_start;

   dbms_output.put_line(CHR(10)||LPAD('=',22,'=')||CHR(10)||'  Runtime Difference'||CHR(10)||LPAD('=',22,'='));
   '     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) );

02-OCT-06 AM
02-OCT-06 AM

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

1 comment:

Tani said...

It is a very good coding example. This script is simple to understand and good to learn from this . Thanks for sharing this post. This is helpful for me and others too.

oracle ebs