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

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts