Tracking milliseconds between processes (Timestamp Differences)

by Granville Bonyata on August 24, 2012

When checking a process for performance it’s useful to track fractions of seconds between steps. In the old days, we had to use dbms_utility.get_time, but with the introduction of TIMESTAMP in Oracle9i, it gets simpler. However, getting the differences between two timestamps is still more complicated than with dates.


create or replace function milliseconds (pStartDate IN TIMESTAMP,
pEndDate IN TIMESTAMP) RETURN NUMBER
IS
   nMilliseconds NUMBER;
BEGIN
   nMilliseconds :=
      EXTRACT(DAY FROM pEndDate - pStartDate) * 24 * 60 * 60 * 1000
     +EXTRACT(HOUR FROM pEndDate - pStartDate) * 60 * 60 * 1000
     +EXTRACT(MINUTE FROM pEndDate - pStartDate) * 60 * 1000
     +EXTRACT(SECOND FROM pEndDate - pStartDate) * 1000;

   RETURN (nMilliseconds);
END;

DECLARE

BEGIN
   dbms_output.put_line('1 Day: '||milliseconds(SYSDATE-1, SYSDATE));
   dbms_output.put_line('1 Hour: '||milliseconds(SYSDATE-(1/24), SYSDATE));
   dbms_output.put_line('1 Minute: '||milliseconds(SYSDATE-(1/(24*60)), SYSDATE));
   dbms_output.put_line('1 Second: '||milliseconds(SYSDATE-(1/(24*60*60)), SYSDATE));
END;

1 Day: 86400000
1 Hour: 3600000
1 Minute: 60000
1 Second: 1000

DECLARE

   tStart TIMESTAMP;
   tEnd TIMESTAMP;
   x NUMBER;
   y VARCHAR2(1);

BEGIN
   tStart := CURRENT_TIMESTAMP;
   FOR i in 1..10000 LOOP
      x := x + 1;
      SELECT dummy
        INTO y
        FROM DUAL;
   END LOOP;
   tEnd := CURRENT_TIMESTAMP;
   dbms_output.put_line('Time to run 10000 loops: '||milliseconds(tStart, tEnd));
END;

Time to run 10000 loops: 511

 

Previous post:

Next post: