Oracle Scratchpad

October 9, 2015

PL/SQL Functions

Filed under: 12c,Oracle,Performance — Jonathan Lewis @ 6:17 pm GMT Oct 9,2015

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 12.1.0.2 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:

Method Initial Deterministic Scalar Subquery
Basic 11.39 4.30 4.32
With 9.77 9.72 3.60
UDF 9.55 9.57 3.57
Cached 1.65 0.71 0.72

Summary

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 12.1.0.2) 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.

Footnote

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.

Ref: 12c_function_options.sql

16 Comments »

  1. I’d say also that performance of inline-functions depends on the number of “PL/SQL Virtual Machine started”/PL/SQL Virtual Machine stopped”(the number of optimized operations), we can see it with 10938 trace: http://orasql.org/2013/07/13/oracle-12c-behavior-tests-of-the-inline-functions-identities-and-defaults/

    Comment by Sayan Malakshinov — October 9, 2015 @ 6:29 pm GMT Oct 9,2015 | Reply

  2. One more option – DIY session-level cache (it’s session-level so many statements can benefit, can be used in standard edition [unlike result cache], and you can control the cache size).

    A simple example:

    CREATE OR REPLACE PACKAGE pkg AS
        FUNCTION f(i_in IN NUMBER) RETURN NUMBER;
    END pkg;
    /
    
    CREATE OR REPLACE PACKAGE BODY pkg AS
        TYPE my_tt IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
        g_cache my_tt;
    
        FUNCTION f(i_in IN NUMBER) RETURN NUMBER IS
        BEGIN
            IF NOT g_cache.exists(i_in) THEN
                g_cache(i_in) := round(exp(ln(i_in)));
            END IF;
            RETURN g_cache(i_in);
        END f;
    END pkg;
    /
    
    select count(distinct pkg.f(n1)) from t1;
    
    

    Thanks,
    Oren.

    Comment by Oren Nakdimon (@DBoriented) — October 9, 2015 @ 6:54 pm GMT Oct 9,2015 | Reply

  3. Hi Jonathan – thank you for wonderful example. I have a doubt –


    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
    )
    ;”

    Is the function above same as below?

    create or replace function func_normal(i_in number)
    return number
    as
    begin
            return round(exp(ln(i_in)));
    end;
    /
    

    Because it sounds something is not right – You are not using DETERMINISTIC clause – so how is ORACLE calling function only 1000 times? For example if you had done wouldn’t it result in much more calls than 1000 (it will be unknown number of rows to be frank) (following wont qualify for deterministic any way due to SYSDATE call)

    create or replace function func_normal(i_in number)
    return number
    as
    begin
            return to_num(to_char(sysdate,'yyyymmddhh24miss'));
    end;
    /
    

    I can understand following will lead to 1000 iterations –

    select
            count(distinct(select func_normal(n1) from dual))
    from    (
            select /*+ no_eliminate_oby */  distinct(n1) from t1 order by n1
            )
    

    Am i misunderstanding something?

    Thank you
    /S/

    Comment by ora777 — October 9, 2015 @ 6:58 pm GMT Oct 9,2015 | Reply

  4. Hi Jonathan, I am really concerned about the “scalar subquery” thing – At the end of the day if I input 5 rows and if the function is NOT deterministic – I had assumed function will be called 5 times. All optimizer tricks aside. Because I may be doing something complex in the function that ORACLE couldn’t possibly underestimate – for example if I said heartbeat of patient on 1st minute of every hour for last 24 hours. So input is 1 for 24 rows – does not mean heartbeat is same. Here is example – I feel the sub-query is throwing some curve on my understanding. On any other day I would have said answer would be same – but now you have shown that its not the case. We need to agree on one of the ANSWER is right and one is wrong – I am not talking about path taken. If I try to explain to heart patient that they have no problem because subquery says so – wont fly – would it? (I am sorry I could come up with better analogy if more time spent..)

    set serveroutput on
    
    create or replace function f1(p_i number) return number as
    l_n number := p_i;
    begin
    dbms_output.put_line('F1 Param: '||p_i||' return: '||l_n);
    return l_n;
    end;
    /
     
    create or replace function f2(p_i number) return number as
    l_n number := to_number(to_char(systimestamp,'yyyymmddhh24missff'));
    begin
    dbms_output.put_line('F2 Param: '||p_i||' return: '||l_n);
    return l_n;
    end;
    /
     
     
     
    SQL&gt; select count(distinct(select f1(i) from dual))
      2  from (select 1 i from dual union all select 1 i from dual);
     
    COUNT(DISTINCT(SELECTF1(I)FROMDUAL))
    ------------------------------------
                                       1
     
    F1 Param: 1 return: 1
    SQL&gt; select count(distinct(select f2(i) from dual))
      2  from (select 1 i from dual union all select 1 i from dual);
     
    COUNT(DISTINCT(SELECTF2(I)FROMDUAL))
    ------------------------------------
                                       1
     
    F2 Param: 1 return: 20151009154543462815000
    SQL&gt;
    
    SQL&gt; select count(distinct(select f1(i) from dual)), count(distinct f1(i))
      2  from (select 1 i from dual union all select 1 i from dual);
     
    COUNT(DISTINCT(SELECTF1(I)FROMDUAL)) COUNT(DISTINCTF1(I))
    ------------------------------------ --------------------
                                       1                    1
     
    F1 Param: 1 return: 1
    F1 Param: 1 return: 1
    F1 Param: 1 return: 1
    SQL&gt; select count(distinct(select f2(i) from dual)), count(distinct f2(i))
      2  from (select 1 i from dual union all select 1 i from dual);
     
    COUNT(DISTINCT(SELECTF2(I)FROMDUAL)) COUNT(DISTINCTF2(I))
    ------------------------------------ --------------------
                                       1                    2
     
    F2 Param: 1 return: 20151009154623008740000
    F2 Param: 1 return: 20151009154623008812000
    F2 Param: 1 return: 20151009154623008834000
    SQL&gt;
     
    SQL&gt; select count(distinct(select f1(i) from dual)), count(distinct f1(i)),
           count(distinct(select f2(i) from dual)), count(distinct f2(i))
    from (select 1 i from dual union all select 1 i from dual);
     
    COUNT(DISTINCT(SELECTF1(I)FROMDUAL)) COUNT(DISTINCTF1(I))
    ------------------------------------ --------------------
    COUNT(DISTINCT(SELECTF2(I)FROMDUAL)) COUNT(DISTINCTF2(I))
    ------------------------------------ --------------------
                                       1                    1
                                       1                    2
     
     
    F2 Param: 1 return: 20151009154819185473000
    F2 Param: 1 return: 20151009154819185549000
    F1 Param: 1 return: 1
    F1 Param: 1 return: 1
    F2 Param: 1 return: 20151009154819185613000
    F1 Param: 1 return: 1
    

    /S/

    Comment by ora777 — October 9, 2015 @ 9:01 pm GMT Oct 9,2015 | Reply

    • You missed one basic point here. It’s was recently added to the oracle docs by Tom Kyte:

      “Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language.
      If your application requires that a function be executed a certain number of times, do not invoke that function from a SQL statement. Use a cursor instead”
      https://docs.oracle.com/database/121/ADFNS/adfns_packages.htm#ADFNS00908

      So you must NOT rely on your function to be called a specific number of times when used in a sql statement.

      Think about what perhaps might happen in Oracle DB 19z. May be the optimizer then is smart enough to recognize that your function is nothing more than select sysdate from … .
      You wont expect different sysdates from select sysdate from all_objects by now, will you?

      Comment by chris227 — October 14, 2015 @ 10:57 am GMT Oct 14,2015 | Reply

      • Chris227,

        I didn’t respond to your previous post because there didn’t seem to be a question involved. All I could see was that you were worried about the implications of scalar subquery caching. It happens, that’s a fact; if you want to argue about whether or not it should happen then you have to raise an SR with Oracle.

        As far as your follow-up post is concerned, you might like to consider that I’ve written an article that says “the number of times the function will be called can vary”, and Tom Kyte’s addition to the documentation says “you cannot know how many times the function will be called”. I’ve shown a couple of strategies for things that might minimise the number of times a function could be called – Tom Kyte has written something about handling a requirement where the exact number of calls has to take place.

        In fact, Tom has given you a solution to your requirement for calling a function many times with the same input, the right number of times and in the right order, and getting a correct but constantly varying result on each call.

        Your comment about 19z carries some weight, of course – whenever you come up with a “trick” that manages to take advantage of some unannounced optimisation you have to be very careful that the trick still works on the next release; that’s why documentation and reproducible test cases are so very important. One of the side effects of unannounced optimisations that “disappear”, though is that sometimes you don’t even realise that your SQL has taken advantage of them until something slows down in the upgrade, so it’s always good to test important code very carefully, especially when it runs faster than you were expecting.

        Comment by Jonathan Lewis — October 15, 2015 @ 10:05 am GMT Oct 15,2015 | Reply

        • Hi Jonathan,

          chris227’s follow up was a replay to ora77 comment.
          Not more than a naiv attempt of predection of what you might have answered.
          So it was ora77 missing the point mentioned not you ;-)

          Comment by chris227 — October 16, 2015 @ 10:16 am GMT Oct 16,2015

        • Chris227

          Sorry about the mix-up.
          I hope it’s still possible for people to make sense of the various comments.

          Comment by Jonathan Lewis — October 16, 2015 @ 2:09 pm GMT Oct 16,2015

  5. For ora777:

    I have been under the impression, that scalar subquery happens only inside one execution of the statement. When statement ends, then cache is flushed

    Comment by nemo — October 12, 2015 @ 1:52 pm GMT Oct 12,2015 | Reply

  6. […] posted a note a few days ago about read consistency, the Cross Session PL/SQL Function Result Cache, deterministic functions, […]

    Pingback by Read Consistency | Oracle Scratchpad — October 12, 2015 @ 7:44 pm GMT Oct 12,2015 | Reply

  7. […] esto viene a cuento de que en una entrada reciente de su blog, Jonathan Lewis analiza y compara el rendimiento de esas opciones en un caso de […]

    Pingback by Funciones en SQL: opciones para la mejora de rendimiento | Database and More — November 22, 2015 @ 3:44 pm GMT Nov 22,2015 | Reply

  8. Hi,
    One can check the internal behavior of the “PRAGMA UDF;” mechanism with PERF and GDB on Linux: http://blog.ora-600.pl/2015/10/29/oracle-12c-pragma-udf-the-truth/

    Regards!
    Kamil.

    Comment by Kamil Stawiarski — November 24, 2015 @ 8:02 am GMT Nov 24,2015 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.