Oracle performance: a query that performs 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 using the function's result cache?

The application I'm working with is heavily dependent on Oracle features. Many queries end up performing the 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 noticed that Oracle always performs 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 are cached, which results in a slightly faster return. This does not apply to my question, since I want to avoid the entire second or third performance.

Assume that the functions are resource-intensive enough and that these functions can call more functions based on their results on tables that are quite large and frequently found (a million records, updates with 1000 updates per hour). For this reason, it is not possible to use Oracle Result Cache.

Despite the fact that data changes frequently, I expect that the result of these functions will 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.

The following is an example (just a random non-sensory 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; 

Functions Used:

 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 inline lookup and ROWNUM to prevent the Oracle query from being rewritten into one query block and executing functions multiple times.

---

Function example and problem demonstration

 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; 

Oracle job to execute in a specific order

It is difficult to tell Oracle "to execute this code on its own, not to make any predicate clicks, merges, or other transformations on it." There are tips for each of these optimizations, but they are difficult to use. There are several ways to disable these conversions by adding additional ROWNUM , usually the simplest ones.

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

It is hard to see exactly where the functions are calculated. But these explanations of the plans show how ROWNUM causes the built-in 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 clean. Regardless of whether it really improves performance, this is another question.

Update:

I don’t know how realistic your example above is, but theoretically you can always try to restructure your SQL so that it knows about function callbacks (actually duplicate values). Kind of like

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

Answer 3

Use the inline 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 exclude a call for element 3, pass the result of func_val to the third function instead.