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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Working With JSON Arrays in PL/SQL

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks. In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods. Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods. Here are the methods you are most likely to use: A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL: Error Handling Behavior By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not  raised back to your block. If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greate