8

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;
/
Improve this question
4
  • You can still use result caching and the "relies_on" keyword to handle table changes - oracle.com/technetwork/issue-archive/2010/10-sep/… Why not call the function once using a WITH clause, then reference that result in your query? Commented May 15, 2015 at 17:33
  • In 12c you can declare function INSIDE the SQL statement which is something VERY different from PL/SQL (from transactional perspective) Commented May 15, 2015 at 18:00
  • Using a WITH clause doesn't help as it still executes the function twice unfortunately. I don't see how RELIES_ON keyword changes anything, in 11gR2 dependencies are automatically identified and also the table gets data changes a lot of times, invalidating the cache. I am looking specifically for caching within the same query. The 12c WITH FUNCTION clause is interesting and I might be able to apply that here and there, it does not solve the bigger problem though when the functions with nested functions are really complicated and leveraging the application framework I'm working in. Commented May 15, 2015 at 19:28
  • is the problem really from calling the function a few times for 1 particular id, or when you try to select many rows (select my_function(id) from my_table where id between 1 and 100000) ? The situation you describe will result in a handful of calls to the function, whereas the 2nd case I describe is much worse. But there is a trick that might work for 2nd case Commented May 15, 2015 at 20:15

3 Answers 3

Reset to default
5

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     |      |
---------------------------------
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Wow, this is very unexpected. And for the example, it works! It doesn't help yet with my "bigger picture" problem but I can definitely use this in some cases. One thing though, I do not understand -at all- why adding ROWNUM changes the explain plan / makes Oracle execute the function only once. Is there a deeper explanation for this? The help is very much appreciated, thanks!
ROWNUM returns the order in which the row was returned so it has to be the last step of the execution. Any type of transformation would change the order so Oracle can't modify a query block with a ROWNUM. (Theoretically the optimizer could recognize when a ROWNUM wasn't used, but that's a rare problem, and would only break a lot of code that uses this trick.)
Brilliant, I would never have thought about this. I am going to flag this as the best answer as I don't think there is a solution for "caching" in the same transaction. Function result cache is probably the closest thing for this. Thanks!
they should give you a medal for this answer, I had the same exact problem, still I can't believe the optimizer doesn't recognize rownum is not used. Do you know if this behavior is documented somewhere?
1

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
)
Improve this answer

3 Comments

No, this is not a deterministic function. it is not returning the same result for the same inputs each time.
@OldProgrammer is the fuction is not deretministic, then you're in trouble. Because Oracle does not guarantee in which order statement will be evaluated. So you never know what you get. Calling SQL from FUNCTIONs, which are called from SQL is anti-pattern. This usally leads to nasty data currptions. The inner SQL lives is different transaction context, and might see phantom data.
The function cannot be deterministic as the definition of deterministic is getting the same result for the same parameters. In this case however, the parameters can be the same but the underlying data can change, which results in a different return value. Also, even when adding deterministic, it doesn't make a difference. It still executes it twice.
0

Use an in-line view.

with get_functions as(
SELECT my_package.my_function(my_id) as func_val,
   my_package.function_also_calling_my_function(my_id) func_val_2
  FROM my_table
 WHERE my_table.id = my_id
)
select func_val,
   func_val / 24 as func_val_adj,
   func_val_2
from get_functions;

If you want to eliminate the call for item 3, instead pass the result of func_val to the third function.

Improve this answer

1 Comment

I don't get this, it doesn't change a thing... It still ends up executing all of the functions independently. Thanks for the help though! EDIT: I understand what you mean now, this is not what I'm after though. My fault as my example is a very simplified explanation for the problem. I'll see if I can add a more realistic example / explanation for my problem later.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.