Fast SYSDATE

(See bottom for update)

A very common application requirement is to stamp rows as they are created and/or modified with the date/time that the modification occurred. Typically this is done with a simple trigger:

create or replace 
trigger STAMP_RBI
before INSERT on STAMP
for each row
begin
  :new.date_col := sysdate;
end;
/

This works fine except that calls to SYSDATE implicitly invoke a recursive SQL, namely 'select sysdate from dual'. This means a context switch for every row that is created.

In my simple example above, creating 50,000 rows using

insert into STAMP (x)
select rownum
from   sys.source$
where  rownum < 50000;

took 26 seconds.

However, PL/SQL offers a routine called DBMS_UTILITY.GET_TIME which is gives 100th's of a second from some arbitrary epoch. Most texts will state that this is not useful for timestamps, but it CAN be used with a little arithmetic. We can then build our own sysdate function, called "SYSDA.TE" in the source below.

create or replace
package sysda is
function te return date;
end;
/

create or replace
package body sysda is
v_dte date;
v_csec number;
  function te return date is
  begin
    return v_dte+(dbms_utility.get_time-v_csec)/8640000;
  end;
begin
  select sysdate,dbms_utility.get_time into v_dte, v_csec from dual;
end;
/

This simply stores the SYSDATE and the DBMS_UTILITY.GET_TIME at the same instance on the first instantiation of the package, and then calculates deltas to return the current date. The trigger now needs a slight change

create or replace 
trigger STAMP_RBI
before INSERT on STAMP
for each row
begin
  :new.date_col := sysda.te;
end;
/

Using the same test as above of 50,000 rows, the new code completed the test in only 11 seconds.

Important Note: I haven't confirmed with 8i, but for older versions, DBMS_UTILITY.GET_TIME does wrap around, so being a 32-bit integer, this solution is only appropriate for databases that come down before it wraps (that is, they get bounced at least once every 240 days or so)

Update:

From about 8.1.7, Oracle have resolved this problem. A quick look at the contents in standard.sql (and its subordindate files) shows the following snippet:

  function pessdt return DATE;
    pragma interface (c,pessdt);

  -- Bug 1287775: back to calling ICD.
  -- Special: if the ICD returns NULL, that means we should do the old 
  -- 'SELECT SYSDATE FROM DUAL;' thing.  This allows us to do the SELECT from
  -- PL/SQL rather than having to do it from C (within the ICD.)
  function sysdate return date is 
    d date; 
  begin
    d := pessdt;
    if (d IS NULL) then
      select sysdate into d from sys.dual;
    end if;
    return d;
  end;

So it looks like that Oracle will attempt to get the date from a system call, and if that does not work for some reason (???), then it will fallback to 'select sysdate from dual'.