Oracle SQL | A function gets called twice if the result

Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
But later I was asked to explain this, so this post is just a short description with test case.

Look at the simple function that just returns random values:

create or replace function f_nondeterministic(p int:=100) return int
as
  res number;
begin
   res:=round(dbms_random.value(0,p));
   return res;
end;

SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       481

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       689

with result_cache_statistics

[sourcecode language=”sql”]
SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

NONDETER
———-
481

SQL> select name,value from v$result_cache_statistics where name in ( ‘Create Count Success’, ‘Find Count’);

NAME VALUE
————————————————– ———-
Create Count Success 1
Find Count 0

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

NONDETER
———-
689

SQL> select name,value from v$result_cache_statistics where name in ( ‘Create Count Success’, ‘Find Count’);

NAME VALUE
————————————————– ———-
Create Count Success 1
Find Count 1

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

NONDETER
———-
689

SQL> select name,value from v$result_cache_statistics where name in ( ‘Create Count Success’, ‘Find Count’);

NAME VALUE
————————————————– ———-
Create Count Success 1
Find Count 2

SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o;

NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALIDATIONS
————————————————– ———- ———- ———- ——— ———- ———- ————-
XTENDER.F_NONDETERMINISTIC 552 0 Dependency Published 0 0 0
select/*+ result_cache */ f_nondeterministic(1000) 2102 1 Result Published 0 2 0
nondeter from dual
[/sourcecode]

[collapse]

As you can see, second execution returns different result than first one.
If we change this function:

create or replace function f_nondeterministic(p int:=100) return int
as
  res number;
begin
   res:=round(dbms_random.value(0,p));
   dbms_output.put_line('fired! ('||res||')');
   return res;
end;

and repeat this test-case:

SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;

  NONDETER
----------
       943    -- << (2)

1 row selected.

fired! (607)    -- << (1)
fired! (943)    -- << (2)
SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

SQL> /

  NONDETER
----------
       607    -- << (1)

1 row selected.

we will see that there were 2 function executions: first result was cached, and the second was fetched!