Result cache side effects on number of calls

By Franck Pachot

.
During the execution of a SQL statement, you cannot guess how many times an operation, a predicate, or a function will be executed. This depends on the execution plan, on some caching at execution, and some other execution time decisions. Here is an example where result cache may bring some overhead by calling a function multiple times.

Here is my function:

SQL> create or replace function F return number is
  2  begin
  3   dbms_lock.sleep(5);
  4   dbms_output.put_line('Hello World');
  5   return 255;
  6  end;
  7  /
Function created.

The function displays ‘Hello World’ so that I can check how many times it is executed (I’ve set serveroutput on).

Obviously, on a one row table, it is called only once:

SQL> select f from dual;
 
         F
----------
       255
 
Hello World

Query result cache miss

I’ll run now the same query but with the result cache hint. The first execution will have to execute the query because the cache is empty at that point:

SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
 
SQL> select /*+ result_cache */ f from dual;
 
         F
----------
       255
 
Hello World
Hello World

Here is what I wanted to show: ‘Hello World’ is displayed two times instead of one. If your function is an expensive one, then the first execution, or every cache miss, will have a performance overhead.

Query result cache hit

Now that the result is in the cache:

SQL> select id, type, status, name from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME
---------- ---------- --------- ------------------------------------------------------------
        33 Dependency Published DEMO.F
        34 Result     Published select /*+ result_cache */ f from dual

and the table has not changed (it’s DUAL here :; ) further executions do not call the function anymore, which is the expected result.

SQL> select /*+ result_cache */ f from dual ;
 
         F
----------
       255

Bug or not?

Bug 21484570 has been opened for that and closed as ‘Not a bug’. There is no guarantee that the function is evaluated once, twice, more or never.
Ok, why not. That’s an implementation decision. Just think about it if you want to workaround an expensive function called for each row, then query result cache may not be the right solution (except if all tables are static and you always have cache hits).

Note that if the function is declared as deterministic, it is executed only once.

You can workaround the issue by using result cache at function level (in place, or in addition to query result cache if you need it).

SQL> create or replace function F return number RESULT_CACHE is
  2  begin
  3   dbms_lock.sleep(5);
  4   dbms_output.put_line('Hello World');
  5   return 255;
  6  end;
  7  /
Function created.
 
SQL> select /*+ result_cache */ f from dual;
 
         F
----------
       255
 
Hello World
 
SQL> select id, type, status, name from v$result_cache_objects;
 
        ID TYPE       STATUS    NAME
---------- ---------- --------- ------------------------------------------------------------
        64 Dependency Published DEMO.F
        66 Result     Published "DEMO"."F"::8."F"#e17d780a3c3eae3d #1
        65 Result     Published select /*+ result_cache */ f from dual

So, not a big problem. Just something to know. And anyway, the right design is NOT to call a function for each row because it’s not scalable. Pipeline functions should be used for that.