Sequence solution performance depends on database. RAC will not perform as well as no-RAC sequence-wise since we will be forced to create sequence with ORDER and it will put a strain on interconnect. And without ORDER sequence will not ensure "I need to be able to order those inserts by when they happened" if RAC.

I assume Lisa is talking single row inserts, otherwise SYSTIMESTAMP simply will not work since it is calculated once per statement so all rows that insert statement inserts will have same timestamp:

SQL> create or replace

  2    function sleep(

  3                   p_sec number

  4                  )

  5      return timestamp with time zone

  6      is

  7      begin

  8          dbms_session.sleep(p_sec);

  9          return systimestamp;

 10  end;

 11  /


Function created.


SQL> create table tbl(id number,insert_ts timestamp with time zone,actual_insert_ts timestamp with time zone);


Table created.


SQL> insert into tbl select empno,systimestamp,sleep(2) from emp where deptno = 10;


3 rows created.


SQL> column id format 99999

SQL> column insert_ts format a35

SQL> column actual_insert_ts format a35

SQL> select * from tbl;


    ID INSERT_TS                           ACTUAL_INSERT_TS

------ ----------------------------------- -----------------------------------

  7782 25-JUL-25 07.57.10.885000 AM -04:00 25-JUL-25 07.57.12.894000 AM -04:00

  7839 25-JUL-25 07.57.10.885000 AM -04:00 25-JUL-25 07.57.14.909000 AM -04:00

  7934 25-JUL-25 07.57.10.885000 AM -04:00 25-JUL-25 07.57.16.918000 AM -04:00


SQL>

Next question is how insert statements are produced? Is code that produces inserts database server side code (procedure, package, function…) or is it client side code (sqlplus, java, shell script, etc.). If latter, nanosecond accuracy timestamp can be produced using client side OS command (we need to take into account time zone difference between client and database server time zones).

And, as a sanity check, insert time != commit time, if it matters.

SY.