Skip to main content

Speed up execution of your functions inside SQL statements with UDF pragma

Oracle Database makes it easy to not only write and execute SQL from within PL/SQL, but also to execute your own user-defined functions inside SQL. Suppose, for example, I have built the following function to return a sub-string between start and end locations:
FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;
I can then call it in a SQL statement:
SELECT bewtnstr (last_name, 3, 6)
  FROM employees

Nice, right?

But there's a catch (well, of course, right? No free lunches.). When the SQL engine encounters the PL/SQL function, it has to switch context to the PL/SQL engine to execute the function. Before it can do the switch or hand-off, it must also prepare the values to pass as actual arguments to the formal parameters of the function.

All of that takes time. And we'd much rather it didn't. Since, however, we live in the real world and not a fantasy world, the best we can hope for is that the PL/SQL dev team would do their darnedest to reduce the overhead of that context switch.

Introducing (in Oracle Database 12c Release 1) the UDF pragma. Add this statement to your function as follows:
FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2
IS
   PRAGMA UDF;
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;
And you will, in effect, be telling the PL/SQL compiler:
I plan to call this function mostly (or maybe even always) from a SQL statement. So please do some of the work you'd usually do at run-time right now, at compile-time.
And - wonder of wonders! - the PL/SQL compiler listens to your request and does indeed take some steps at compile-time, thereby reducing the runtime overhead of the context switch.

For an excellent, in depth exploration of the performance impact of UDF, check out this blog post from Martin Widlake. Here's the summary in terms of his performance example:
Version                      Run Time average (secs)
Traditional PL/SQL           0.33
PRAGMA UDF PL/SQL            0.08
Nice. Very nice. And with such a small change to your code!

One thing to keep in mind: the performance of the UDF-ied function could actually degrade a bit when run natively in PL/SQL (outside of a SQL statement). So the use of this pragma is best reserved for those cases when you are quite certain the function will almost always be executed from within SQL.

Comments

  1. Can you tell us more about the downside of UDF functions within PL/SQL?

    On the face of it, a pragma that improves runtime performance at the cost of extra compile time is something I should be applying everywhere. Functions are only compiled when code is changed - i.e. rarely compared to how often they are executed - so why not always use it?

    Also, can a UDF function call a non-UDF function and still get the benefit of UDF-ness? Could I package the two together like this:

    FUNCTION add_one(p IN NUMBER) RETURN NUMBER IS
    BEGIN
    RETURN p + 1;
    END;

    FUNCTION add_one_udf(p IN NUMBER) RETURN NUMBER IS
    PRAGMA UDF
    BEGIN
    RETURN add_one(p);
    END;

    ReplyDelete
    Replies
    1. Chris, the "cost" of using the pragma is that when you execute the function in PL/SQL (not in SQL), you could see the performance degrade a bit. I have not seen much if anything different, in my own tests, but that is the stated potential downside in the documentation. So it's not a run-time vs compile-time tradeoff. Use the pragma with functions that are run almost exclusively via SQL.

      "Also, can a UDF function call a non-UDF function and still get the benefit of UDF-ness?" Yes, certainly. There is no context switch from add_one_udf to add_one. You are "inside" PL/SQL by that time. So you get the benefit of a reduced context switch by marking add_one_udf with UDF, and you have no context switch in the call to add_one.

      Delete
  2. Hello Steven, All,

    The pragma UDF feature seems however to have a few downsides, pointed out by Martin Widlake here:

    https://mwidlake.wordpress.com/2015/11/11/pragma-udf-some-current-limitations/

    As stated in that blog, it does not "quite like" functions with VARCHAR2 parameters having DEFAULT values or with DATE parameters or return values.

    Also, it will probably not help too much for solving the main problem related to calling PL/SQL functions from SQL,
    which is that of having a PL/SQL function being called "too many times" during the SQL statement execution.
    Here there is still a high amount of "clever SQL coding" required, especially if the PL/SQL function itself
    contains many SQL statements in its logic.

    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete
    Replies
    1. Thanks for pointing readers to that blog, Iudith. I was lazy. I figured I'd give readers the link to his starting point and let them explore from there.

      In terms of "too many times" execution, it is worth checking out the function result cache in that regard.

      And for sure please do be careful about putting SQL statements inside functions called from SQL - those "inside PL/SQL" SQL statements are not part of the same read consistent "image" maintained by Oracle Database for the "outer" SQL.

      Delete
    2. Well ... wishful thinking :)

      In our applications, though, I have seen many PL/SQL functions used for encapsulating very complicated business logic, of course requiring much SQL sometimes, indeed, used not in the most "economic" way.

      If not for that complicated logic, for simple "non-sql only" cases no one would have probably cared
      to put that logic into a pl/sql function at all.

      Per se, the principle of encapsulating business logic in one single place is correct, and the technical possibility to use PL/SQL from SQL looks always very appealing and elegant ...
      but it is usually not the most performant.

      Result cache could have been an option, but not in too many cases, because most of the time the functions were called with different parameters.

      I have helped many times to "refactor" the overall SQL statements logic, to keep such PL/SQL function calls
      at a minimum.

      Once for example, yet before the result cache was available at all, we used very nicely packaged
      collections for storing parameters and PL/SQL function results, when the calls were "repeatable enough"
      for having a reasonable temporary store ... and this one also ensured read consistency at the statement level :)

      Well ... those are part of my fine work memories ... working together with one of our most clever developers
      on such things was the "lightest part of the world" ... unfortunately too often "obscured" by the managers ...

      Cheers & Best Regards,
      Iudith

      Delete
  3. There's also this thread by Kamil Stawiarski titled
    "ORACLE 12C: PRAGMA UDF – THE TRUTH" http://blog.ora-600.pl/2015/10/29/oracle-12c-pragma-udf-the-truth/

    ReplyDelete
  4. I've also been wondering about this pragma with supplied PL/SQL packages. For example, this function would be used frequently in SQL in APEX applications -> APEX_PAGE.GET_URL
    What's the best approach here?

    ReplyDelete
    Replies
    1. Scott, I am feeling thick this AM. How would you use the pragma with built-ins? Or are you asking whether the packages are using the pragma?

      Delete
  5. Shall i use compute keyword in sql statment..and pls help me with example

    ReplyDelete
    Replies
    1. Sorry I don't think you've given me enough information for a useful answer. And it sounds like a good case for a browser search.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel