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.

Example Exercise

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.

create table employees
(
    employee_id constraint pk_employees primary key
  , employee_name
  , employee_salary
)
as
-- Using the "generator" technique that you see on the Johnathan Lewis blog
with generator as
(select level
 from dual
 connect by level <= 1000)
select /*+ NO_GATHER_OPTIMIZER_STATISTICS */ cast(rownum as number(*,0)) employee_id
       -- Below will generate employee first names like "AAAAAAAAAAAAAA..."
       -- "BBBBBBBBBBBBBBBBBBBBBBBB...."
       -- "CCCCCCCCCCCCCCCCCCCCCCCC...."
     , lpad(chr(mod(rownum, 26) + 65) , 25, chr(mod(rownum, 26) + 65)) employee_name
       -- Below expression generates a salary, ranging between 20k and 250k
       -- It's in $500 increments, so it generates numbers randomly like
       -- $20,000
       -- $20,500
       -- $21,000
       -- $21,500
       -- etc...
     , (mod(abs(dbms_random.random()), 461) * 0.5 + 20) * 1000 employee_salary
from generator a
     cross join generator b
     cross join generator c
-- Ten millinon rows
where rownum <= 100000000;

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.

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , case when employee_salary < 35000 then 0.11
            when employee_salary < 44000 then 0.13
            when employee_salary < 59000 then 0.15
            when employee_salary < 72000 then 0.18
            when employee_salary < 96000 then 0.22
            when employee_salary < 117000 then 0.25
            when employee_salary < 130000 then 0.29
            when employee_salary < 180000 then 0.34
            else 0.48 end * employee_salary tax_amount
from employees;

This ran on my system in 49.163 seconds.

Now, lets drop the table we just made…

drop table graduated_taxes_example;

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

create or replace function graduated_taxes( p_salary in number ) 
  return number
as
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

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

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

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:

create or replace function graduated_taxes( p_salary in number ) 
  return number
as
  pragma udf;
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

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

drop table graduated_taxes_example;

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

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

create or replace function graduated_taxes( p_salary in number ) 
  return number deterministic
as
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

Alright, lets see if there’s any improvement.

drop table graduated_taxes_example;

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

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”

create or replace function graduated_taxes( p_salary in number ) 
  return number parallel_enable
as
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

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

drop table graduated_taxes_example;

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

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.

create or replace function graduated_taxes( p_salary in number ) 
  return number parallel_enable deterministic
as
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

Running the exact same test as before:

drop table graduated_taxes_example;

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

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:

create or replace function graduated_taxes( p_salary in number ) 
  return number parallel_enable 
as
  pragma udf;
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

Doing the same test as before:

drop table graduated_taxes_example;

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

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:

create or replace function graduated_taxes( p_salary in number ) 
  return number deterministic 
as
  pragma udf;
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

Same test:

drop table graduated_taxes_example;

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

This ran in 54.888 seconds

PRAGMA UDF, PARALLEL_ENABLE, and DETERMINSTIC

Using all 3 now:

create or replace function graduated_taxes( p_salary in number ) 
  return number parallel_enable deterministic 
as
  pragma udf;
begin
  return case when p_salary < 35000 then 0.11
            when p_salary < 44000 then 0.13
            when p_salary < 59000 then 0.15
            when p_salary < 72000 then 0.18
            when p_salary < 96000 then 0.22
            when p_salary < 117000 then 0.25
            when p_salary < 130000 then 0.29
            when p_salary < 180000 then 0.34
            else 0.48 end * p_salary;
end;
/

Same test as before:

drop table graduated_taxes_example;

create table graduated_taxes_example
  parallel 2
as
select employee_id
     , employee_name
     , employee_salary
     , graduated_taxes(employee_salary) tax_amount
from employees;

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.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets