Oracle performance: query executing multiple identical function calls

Question

Is it possible for Oracle to reuse the result of a function when it is called in the same query (transaction?) without the use of the function result cache?

The application I am working with is heavily reliant on Oracle functions. Many queries end up executing the exact same functions multiple times.

A typical example would be:

SELECT my_package.my_function(my_id),
       my_package.my_function(my_id) / 24,
       my_package.function_also_calling_my_function(my_id)
  FROM my_table
 WHERE my_table.id = my_id;

I have noticed that Oracle always executes each of these functions, not realizing that the same function was called just a second ago in the same query. It is possible that some elements in the function get cached, resulting in a slightly faster return. This is not relevant to my question as I want to avoid the entire second or third execution.

Assume that the functions are fairly resource-consuming and that these functions may call more functions, basing their result on tables that are reasonably large and with frequent updates (a million records, updates with say 1000 updates per hour). For this reason it is not possible to use Oracle's Function Result Cache.

Even though the data is changing frequently, I expect the result of these functions to be the same when they are called from the same query.

Is it possible for Oracle to reuse the result of these functions and how? I am using Oracle11g and Oracle12c.

Below is an example (just a random non-sense function to illustrate the problem):

-- Takes 200 ms
SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT')
  FROM dual;

-- Takes 400ms
SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT')
     , test_package.testSpeed('STANDARD', 'REGEXP_COUNT')
  FROM dual;

Used functions:

CREATE OR REPLACE PACKAGE test_package IS

FUNCTION testSpeed (p_package_name VARCHAR2, p_object_name VARCHAR2)
RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY test_package IS

FUNCTION testSpeed (p_package_name VARCHAR2, p_object_name VARCHAR2)
RETURN NUMBER
IS

    ln_total NUMBER;

BEGIN

    SELECT SUM(position) INTO ln_total 
      FROM all_arguments 
     WHERE package_name = 'STANDARD' 
       AND object_name = 'REGEXP_COUNT';

    RETURN ln_total;

END testSpeed;

END;
/

Answer 1

Add an inline view and a ROWNUM to prevent the Oracle from re-writing the query into a single query block and executing the functions multiple times.

---

Sample function and demonstration of the problem

create or replace function wait_1_second return number is
begin
    execute immediate 'begin dbms_lock.sleep(1); end;';
    -- ...
    -- Do something here to make caching impossible.
    -- ...
    return 1;
end;
/

--1 second
select wait_1_second() from dual;

--2 seconds
select wait_1_second(), wait_1_second() from dual;

--3 seconds
select wait_1_second(), wait_1_second() , wait_1_second() from dual;

Simple query changes that do NOT work

Both of these methods still take 2 seconds, not 1.

select x, x
from
(
    select wait_1_second() x from dual
);

with execute_function as (select wait_1_second() x from dual)
select x, x from execute_function;

Forcing Oracle to execute in a specific order

It's difficult to tell Oracle "execute this code by itself, don't do any predicate pushing, merging, or other transformations on it". There are hints for each of those optimizations, but they are difficult to use. There are a few ways to disable those transformations, adding an extra ROWNUM is usually the easiest.

--Only takes 1 second
select x, x
from
(
    select wait_1_second() x, rownum
    from dual
);

It's hard to see exactly where the functions get evaluated. But these explain plans show how the ROWNUM causes the inline view to run separately.

explain plan for select x, x from (select wait_1_second() x from dual);
select * from table(dbms_xplan.display(format=>'basic'));

Plan hash value: 1388734953

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------

explain plan for select x, x from (select wait_1_second() x, rownum from dual);
select * from table(dbms_xplan.display(format=>'basic'));

Plan hash value: 1143117158

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  VIEW            |      |
|   2 |   COUNT          |      |
|   3 |    FAST DUAL     |      |
---------------------------------

Answer 2

You can try the deterministic keyword to mark functions as pure. Whether or not this actually improves performance is another question though.

Update:

I don't know how realistic your example above is, but in theory you can always try to re-structure your SQL so it knows about repeated functions calls (actually repeated values). Kind of like

select x,x from (
    SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT') x
      FROM dual
)