In the databases that I currently manage, PL/SQL functions are used everywhere.  Honestly, one of the things I do when I try to get serious about tuning a SQL query is to (if it can be easily done) eliminate any PL/SQL functions and try to re-write them as “plain SQL” expressions.

I’m using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production for this example.

Let’s start with an EMPLOYEES table.

First, you run a full-scan operation on the table to get rid of delayed block cleanout.

Now, suppose we want to make a table that will calculate annual taxes withholding for each employee, based on a graduated tax scale (I’m making this up btw, these aren’t real numbers).  Something like:

If salary between… Taxes will be
< 35k 11%
<44k 13%
< 59k 15%
< 72k 18%
< 96k 22%
< 117k 25%
< 130k 29%
< 180k 34%
180k+ 48%

Lets run some SQL that does a CTAS, parallel 8.

This ran on my system in 49.163 seconds.

Now, lets drop the table we just made…

…and re-create it, but this time instead of using an SQL expression, let’s use a Pl/SQL function.

Alright, let’s retry the CTAS with the new function:

Suddenly, the query that does the exact same thing that it did before takes 1 minute, 27.002 seconds to complete on my system (177% increase in runtime).  A few seconds may not seem like a big deal here, but this is potentially the difference between a batch job that runs an hour, versus a batch job that runs 8 hours vs a batch job that runs 14 hours, 15 minutes if you scale the timings out.

I intend to cover some of the things that can be done to make this simple function go faster, but I would say that the first thing worth noticing here is that it’s difficult to write a PL/SQL block of code (even a *very* simple one!) that performs as well as a pure SQL expression.  So Tom Kyte’s mantra applies really well here:

I have a pretty simple mantra when it comes to developing database software, and I have written this many times over the years:

  • You should do it in a single SQL statement if at all possible.
  • If you cannot do it in a single SQL statement, do it in PL/SQL.
  • If you cannot do it in PL/SQL, try a Java stored procedure.
  • If you cannot do it in Java, do it in a C external procedure.
  • If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.

If you can do it in a single SQL statement, by all means do it in a single SQL statement. Do not waste time, energy, and CPU cycles writing procedural code that will run slower than regular SQL.

(this mantra is all over the AskTom site and in his Tom Kyte’s books in various forms, but I pulled the text above verbatim from here).

In other words, if you can avoid writing a Pl/SQL function……if what you’re doing can be done in just straight SQL, then if you’re looking for maximum performance, consider using just plan ol’ SQL (granted, Pl/SQL functions do have advantages such as modularity and reusability, and those aren’t to be discounted).

Pragma UDF

So, how can we make our little function more efficient?  The first thing we can try is PRAGMA UDF.
Excellent articles written by Steve Feurerstein here: http://stevenfeuersteinonplsql.blogspot.com/2017/03/speed-up-execution-of-your-functions.html
….and by Martin Widlakes here: https://mwidlake.wordpress.com/2015/11/04/pragma-udf-speeding-up-your-plsql-functions-called-from-sql/

A brief one-line summary of what this does…Plagiarizing directly from aforementioned Steven Feuerstein blog post:

[You’re] 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.

Here’s what the new function looks like:

Alright, armed with our new function, let’s see what the new run time is (running the same SQL as before)

Runs in 59.193 seconds!  This is awesome, but we’re still 10 seconds behind where we were with plain SQL.

 

Deterministic

Is there anything else we can do to make this function faster?  Well, one of the things we can say about this function is that if we plug in the same parameter, the function…regardless of when its run or anything like that…will always return the same answer.  We can signal this by making the function deterministic, telling Oracle “Hey! It’s ok to cache and re-use the answers for this function.”  Let’s see what this buys us (we’ll remove the pragma udf bit so we can test in isolation).

Alright, lets see if there’s any improvement.

The above code ran in 56.030 seconds!  Even better than Pragma UDF (for this particular example using PRAGMA UDF ran in 59.193 seconds …your milage may vary based on data and other circumstances)

PARALLEL_ENABLE

Alright, lets try one more thing…parallel_enable.

This basically tells Oracle “hey, it’s ok if this function is executed in parallel”

Let’s run the same test again and see what we get.

This ran in 1 minute, 22.746 seconds.  So minor improvement over what we saw without PARALLEL_ENABLE (remember that just using the “regular” function ran 1 minute, 27.002 seconds)

 

PARALLEL_ENABLE and DETERMINISTIC

Let’s see what happens when we start combining some of the features.

Running the exact same test as before:

This ran in 55.063 seconds.  Close to *only* using DETERMINISTIC (remember if we just used deterministic, we got 56.030 seconds).

PARALLEL_ENABLE and PRAGMA UDF

Let’s see what this gives us:

Doing the same test as before:

This completed in 54.216 seconds…5 seconds better than using PRAGMA UDF alone! (If we just used PRAGMA UDF, it ran in 59.193 seconds)

DETERMINISTIC and PRAGMA UDF

Modifying the function:

Same test:

This ran in 54.888 seconds

PRAGMA UDF, PARALLEL_ENABLE, and DETERMINSTIC

Using all 3 now:

Same test as before:

This ran in 54.710 seconds.

Summary

Here’s a table mapping the fastest to the slowest

Type of  Pl/SQL

Runtime (seconds)

No Pl/SQL (pure SQL) 49.163
PRAGMA_UDF, PARALLEL_ENABLE 54.216
PRAGMA_UDF, PARALLEL_ENABLE, DETERMINISTIC 54.710
PRAGMA_UDF, DETERMINISTIC 54.888
PARALLEL_ENABLE, DETERMINISTIC 55.063
DETERMINSTIC 56.030
PRAGMA_UDF 59.193
PARALLEL_ENABLE 82.746
Plain Pl/SQL with “nothing fancy” 87.002

So we can get Pl/SQL pretty close to just “pure” SQL run times if we really try hard.  And like I said earlier, Pl/SQL has modularity and reusability advantages, and you may determine that it’s worth whatever performance hit you’re getting.

Thoughts to consider though:  When you write a Pl/SQL piece of code, what are the odds that you’re going to always remember to used “DETERMINISTIC” on deterministic functions…or use PARALLEL_ENABLE if the function can be used in parallel…or that you’ll remember to use PRAGMA UDF?  I have seen very few functions in production that take advantage of these small (but highly useful) performance tweaks.  It’s a lot to know and remember.

In short, when considering the performance aspect of queries, if at all possible, consider *not* using Pl/SQL…and if other advantages of Pl/SQL (like modularity and reusability) warrant the use of Pl/SQL, then consider small tweaks that can actually make a sizable difference.