SYSDATE@!

If you use SYSDATE or SYSTIMESTAMP multiple times in a query that takes some time, will you see different times?

No, because queries are read consistent: they see the data as of the timestamp the query began. So SYSDATE and SYSTIMESTAMP will produce the date and timestamp at the time the query begins, right? You can see this in this little test:

rwijk@ORA11G> select sysdate
  2       , systimestamp
  3    from dual
  4  /

SYSDATE             SYSTIMESTAMP
------------------- -----------------------------------------
24-07-2008 22:35:05 24-07-08 22:35:05,046000 +02:00

1 rij is geselecteerd.

rwijk@ORA11G> select min(n)
  2       , max(n)
  3       , min(d)
  4       , max(d)
  5       , min(t)
  6       , max(t)
  7    from ( select level n
  8                , sysdate d
  9                , systimestamp t
 10             from dual
 11          connect by level <= 2000000
 12         )
 13  /

    MIN(N)     MAX(N) MIN(D)              MAX(D)
---------- ---------- ------------------- -------------------
MIN(T)
-------------------------------------------------------------
MAX(T)
-------------------------------------------------------------
         1    2000000 24-07-2008 22:35:05 24-07-2008 22:35:05
24-07-08 22:35:05,062000 +02:00
24-07-08 22:35:05,062000 +02:00


1 rij is geselecteerd.

rwijk@ORA11G> select sysdate
  2       , systimestamp
  3    from dual
  4  /

SYSDATE             SYSTIMESTAMP
------------------- -----------------------------------------
24-07-2008 22:35:10 24-07-08 22:35:10,031000 +02:00

1 rij is geselecteerd.

The query takes 5 seconds, but the reported minimum and maximum sysdates and systimestamps are the time the query began, as you can see.

But a comment by James Padfield in this OTN-thread convinced me that it is not really read consistency that takes care of this behaviour of sysdate and systimestamp. He says:

I don't believe SYSDATE is truly read-consistent, if it was then references to SYSDATE within a serializable (or read-only) transaction would return the same value, it is trivial to show that they do not.

However It appears that SYSDATE has been made to behave as if it is read-consistent at the statement level and this implementation appears to involve re-write as previously shown. Possibly (and this is pure speculation) it is persisted somewhere at the start of the statement and this value is referenced by SYSDATE@!. This might give benefits in terms of both performance and statement level read-consistent behaviour (you might see the latter as a side-effect). I notice a similar rewrite is used with the USER function.

Of course the implementation is largely irrelevant as long as we clearly understand the behaviour.

Let's check the first remark. In a serializable transaction they produce different values:

rwijk@ORA11G> set transaction isolation level serializable
  2  /

Transactie is ingesteld.

rwijk@ORA11G> select sysdate
  2       , systimestamp
  3    from dual
  4  /

SYSDATE             SYSTIMESTAMP
------------------- --------------------------------------
24-07-2008 22:35:10 24-07-08 22:35:10,031000 +02:00

1 rij is geselecteerd.

rwijk@ORA11G> exec dbms_lock.sleep(2)

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> select sysdate
  2       , systimestamp
  3    from dual
  4  /

SYSDATE             SYSTIMESTAMP
------------------- --------------------------------------
24-07-2008 22:35:12 24-07-08 22:35:12,031000 +02:00

1 rij is geselecteerd.

So, this blows up the read consistency theory. But the first test showed it behaved as statement level read consistent. And the SYSDATE@! internal variable/function (so no, I was not swearing in the title) can be looked up by querying the filter_predicates of the plan_table. I also included the USER and SYSTIMESTAMP pseudo columns:

rwijk@ORA11G> explain plan
  2  for
  3  select dummy
  4    from dual
  5   where sysdate > date '2008-01-01'
  6     and systimestamp > timestamp '2008-01-01 00:00:00'
  7     and user != 'A'
  8  /

Uitleg is gegeven.

rwijk@ORA11G> select filter_predicates
  2    from plan_table
  3   where filter_predicates is not null
  4  /

FILTER_PREDICATES
--------------------------------------------------------------------------------
USER@!<>'A' AND SYS_EXTRACT_UTC(SYSTIMESTAMP(6))>SYS_EXTRACT_UTC(TIMESTAMP' 2008
-01-01 00:00:00,000000000') AND SYSDATE@!>TO_DATE(' 2008-01-01 00:00:00', 'syyyy
-mm-dd hh24:mi:ss')


1 rij is geselecteerd.

James' story about SYSDATE sounds very plausible to me. But now I wonder why SYSTIMESTAMP is not converted to SYSTIMESTAMP@! but to SYS_EXTRACT_UTC(SYSTIMESTAMP(6)), especially since SYSTIMESTAMP also behaves as statement level read consistent...

rwijk@ORA11G> select sysdate@!
  2       , user@!
  3    from dual
  4  /

SYSDATE@!           USER@!
------------------- ------------------------------
24-07-2008 23:11:41 RWIJK

1 rij is geselecteerd.

rwijk@ORA11G> select systimestamp@!
  2    from dual
  3  /
select systimestamp@!
                   *
FOUT in regel 1:
.ORA-00923: FROM-sleutelwoord is niet gevonden waar verwacht.