How many function calls and sysdate evaluated

Talking here about function calls and when sysdate is evaluated. Starting with a silly function f2 that takes a second to execute and a session that shows only the seconds out of a date type.

SQL> alter session set nls_date_format='ss';

Session altered.

SQL> create or replace function f2( x in date ) return date
  2    as
  3    begin
  4            dbms_lock.sleep(1);
  5            return x;
  6    end;
  7  /

Function created.

SQL> select a a, a b from (select f2(sysdate) a from dual);

A  B
-- --
37 37

Elapsed: 00:00:02.02

Written a query that uses the function once. And seems like sysdate is evaluated at the main query and it returns the same value for both a and b. But how come the execution time is two seconds? From the optimizer trace can be seen that the query is transformed to have two calls to the function. That explains the execution time.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "RAFU"."F2"() "A","RAFU"."F2"() "B" FROM "SYS"."DUAL" "DUAL"

SQL> select f2(sysdate) a, f2(sysdate) b from dual;

A  B
-- --
39 39

Elapsed: 00:00:02.02

Similar result. Lets change the function to have its own cursor.

SQL> create or replace function f3 return date
  2    as
  3    x date;
  4    begin
  5            dbms_lock.sleep(1);
  6            select sysdate into x from dual;
  7            return x;
  8    end;
  9  /

Function created.

SQL> select a a, a b from (select f3() a from dual);

A  B
-- --
42 43

Elapsed: 00:00:02.02

Execution time the same and the result changed. The sysdate is evaluated at the time cursor in the function is called. Remember the query transformation. The transformation may be avoided and the function is called actually only once.

SQL> select a a, a b from (select /*+no_merge*/ f3() a from dual);

A  B
-- --
54 54

Elapsed: 00:00:03.01

Be aware query transformations may lead to changing query results when function are mixed in a query. How about transaction isolation. Does that have influence on the issue when the function is called twice. So is the sysdate behaving like changing rownum. The changing rownum is talked in Laurent Schneider blog and comments there.

SQL> set transaction read only;

Transaction set.

Elapsed: 00:00:00.00
SQL> SELECT F3() A,F3() B FROM DUAL;

A  B
-- --
03 04

Elapsed: 00:00:02.01
SQL> rollback;

The sysdate is not respecting the read only setting on the transaction but it is getting its value just when asked.