Assuming everything else has been tuned to perfection, what’s the best you can do while calling PL/SQL functions from SQL ? Here’s a little code to create a table with some data, and a function that we can use to start an investigation:
create table t1 nologging pctfree 0 as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 ) select trunc(dbms_random.value(1,1001)) n1 from generator v1, generator v2 where rownum <= 3e5 ; create or replace function func_normal(i_in number) return number as begin return round(exp(ln(i_in))); end; /
That’s 300,000 rows in the table and a silly little function to use up some CPU to get nowhere. There are 1,000 distinct integer values scattered uniformly through the table, and the function returns the value it’s called with – but it does it a hard way.
Here’s some test code – with “set timing on” – followed by the results:
select count(distinct(n1)) from t1; select count(distinct(func_normal(n1))) from t1 ; COUNT(DISTINCT(N1)) ------------------- 1000 1 row selected. Elapsed: 00:00:00.03 COUNT(DISTINCT(FUNC_NORMAL(N1))) -------------------------------- 1000 1 row selected. Elapsed: 00:00:11.39
This is running on 18.104.22.168 on a machine with a CPU speed of aboaut 3 MHz – you may want to adjust the number of rows in the table for your own testing.
The question is, what options can we use to improve the efficiency of the calls to the PL/SQL. One option, of course, is to use the new 12c “with PL/SQL” clause – we could embed the function in the SQL like this:
with function func_with ( i_in number ) return number is begin return round(exp(ln(i_in))); end; select count(distinct(func_with(n1))) from t1 / COUNT(DISTINCT(FUNC_WITH(N1))) ------------------------------ 1000 1 row selected. Elapsed: 00:00:09.77
So, for the cost of copying the function into the SQL we get a 10% improvement in performance – which we could reasonably attribute to an improved efficiency in the call mechanism. There are arguments for and against copying code like this, of course, and my improvement was only 1 second for 300,000 calls, but you may decide that the benefit of the “With-PL/SQL” method is sufficient to justify the effort.
If you don’t want to copy a PL/SQL function into the SQL, though, there is another alternative – the pragma UDF (for user-defined function) – which has the effect of reducing the cost of the so-called “context switch” as you call from SQL to PL/SQL or vice versa.
Technically I think what’s happening is that the stack formats for SQL and PL/SQL are different and the context switch is the work needed to reformat the stack as you pass from one environment to the other – by declaring the function as UDF you probably set it up to deal with the incoming SQL stack itself.
Here’s how to add the UDF pragma to the function, and the results showing the effects:
create or replace function func_udf(i_in number) return number as pragma UDF; begin return round(exp(ln(i_in))); end; / COUNT(DISTINCT(FUNC_UDF(N1))) ----------------------------- 1000 1 row selected. Elapsed: 00:00:09.55
With pragma UDF the standalone function call is slightly faster than the in-line “WITH” function. I did wonder whether the pragma UDF would make the function call slower if I simply called it from a loop in a PL/SQL block, but there didn’t seem to be any significant difference between the normal function and the UDF function.
Initially, then, it looks like UDF is faster than WITH, which is faster than basic; but there are other considerations. My sample data has only 1,000 possible inputs to the function – and Oracle has three different methods for caching that I might be able to benefit from:
- Declaring the function as deterministic
- Putting the function into the PL/SQL result cache
- Modifying the SQL to take advantage of scalar subquery caching
Here’s what the function declaration looks like if I want to use the PL/SQL function cache:
create or replace function func_cached(i_in number) return number result_cache as begin return round(exp(ln(i_in))); end; /
Changing my query to use func_cached() the query completed in 1.65 seconds – a clear winner, but can anything else get close.
To make the functions deterministic, I just have to add the word “deterministic” after the declaration of the return type:
create or replace function func_normal(i_in number) return number deterministic as begin return round(exp(ln(i_in))); end; /
We can also add the deterministic keyword to the function defined in the WITH clause. Before reporting the results of testing the functions with determinism, there’s one more strategy to consider. Remove the deterministic key word from the functions, and introduce a scalar subquery to the test query, e.g.:
select count(distinct(select func_normal(n1) from dual)) from t1 ;
Here’s a table of results:
Before stating any conclusions it’s worth remembering that the sample data is based on a relatively small number of distinct input values. It is the repetition that allows us to benefit from things like caching and determinism. On top of that we need to consider the scale of the time-saving in light of the number of calls made (or not made).
Having said that, the PL/SQL function cache is clearly the thing that gives us the most benefit in return for a simple implementation. We should remember that the cache is “public” – i.e. stored in the SGA – and each input value takes up another bit of the public result cache: on the plus side this means that everyone else calling this function gets the benefit of our prior calculation; on the minus side this means if we use the result cache for the wrong function then we could take up a lot of space in the cache for very little benefit. Since the relevant result cache latch has not child latches it’s also possible to end up with latch contention if the too many sessions are taking advantage of the result cache too frequently.
Although we might expect a deterministic function to give us a big benefit (in the “very repetitive inputs” case), we find that the deterministic keyword has no effect (as at 22.214.171.124) in functions declared in a WITH clause or declared as standalone with pragma UDF. Fortunately scalar subquery caching (which seems to use the same hashing algorithm as the deterministic caching algorithm) still works with WITH functions or UDF functions and (slightly surprisingly) standalone functions declared with pragma UDF seem to have a very small edge over WITH functions.
Both of the latter two approaches use a local cache with a strictly limited size. The benefit is that the locality means they won’t interfere with other sessions or end up hogging an extreme amount of a public memory area; the drawback is that the size limit (which can be adjusted with a hidden parameter) means that you can get unlucky with hash collisions and end up with extremely variable performance from day to day because of a small change in the data being processed, or even a change in the order in which an unchanged data set is processed.
I’ll leave you with one thought. Without declaring the original function as deterministic, I ran the following query to maximise the benefit from the scalar subquery caching algorithm:
select count(distinct(select func_normal(n1) from dual)) from ( select /*+ no_eliminate_oby */ n1 from t1 order by n1 ) ;
The query completed in 0.13 seconds: the subquery was called 1,000 times (once for each distinct value – see this ancient URL), and the benefit of eliminating the function calls outweighed the cost of having to sort the data set.