Home

How much overhead do PL/SQL procedure invocations add to your code?

Fri, 2006.08.11 - 14:48 — müzso
In my experience the overhead of PLSQL procedure invocations is so small that you'll most probably never have to think about it. In 99.99% of all cases the "real" overhead comes from the way you do things inside the procedure(s) and not from the procedure invocation itself. So putting as much code into a single procedure as you can won't help. Smile

Let's take a look at a very basic example.
Run this:
DECLARE
  nLoops CONSTANT NUMBER := 10000000;
  nStart NUMBER;
  PROCEDURE calculate IS
  BEGIN
    NULL;
  END calculate;
BEGIN
  dbms_output.enable(buffer_size => 10000);
 
  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    calculate();
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');

  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    NULL;
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');
END;
/

On my server the output was:
time: 4.33 s
time: 1.47 s

I ran it around 10 times and got quite similiar results with a very small deviation. This shows that even in a magnitude of 10 million invocations, the overhead is minimal (~3s which is 66% in this case). Of course this is nothing like a real world situation. Smile

Let's do some work in the procedure. What if we pass in a parameter and return a value (make it a function)?

DECLARE
  nLoops CONSTANT NUMBER := 10000000;
  nStart NUMBER;
  nTemp NUMBER;
  FUNCTION calculate(nParam IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN nParam + 1;
  END calculate;
BEGIN
  dbms_output.enable(buffer_size => 10000);
 
  nTemp := 0;
  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    nTemp := calculate(nTemp);
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');

  nTemp := 0;
  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    nTemp := nTemp + 1;
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');
END;
/

The output is:
time: 12.24 s
time: 3.75 s

The overhead seems to be quite "big" compared to the previous test (~8.5s, which is ~70% in this case), however this is still as much unrealistic as the previous one was. Smile Generally you could say that the more work you do inside the function call (or procedure), the less percentage the overhead will be.

Let's do the test again with a bit more work inside the function.
DECLARE
  nLoops CONSTANT NUMBER := 10000000;
  nStart NUMBER;
  nTemp NUMBER;
  FUNCTION calculate(nParam IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN POWER(nParam + 1, 2) - POWER(nParam, 2);
  END calculate;
BEGIN
  dbms_output.enable(buffer_size => 10000);
 
  nTemp := 0;
  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    nTemp := calculate(nTemp);
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');

  nTemp := 0;
  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    nTemp := POWER(nTemp + 1, 2) - POWER(nTemp, 2);
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');
END;
/

time: 47.28 s
time: 31.1 s

Now the overhead is ~16s which is ~34% of the total running time.

All of the above tests use very fast, builtin arithemtical functions. Let's take an example that is a lot more realistic ... eg. we generate some output to a browser. The procedures in the SYS.htp package do a lot more (and versatile) work than any of the above examples. I've split this test into two parts to avoid misleading results due to the internal workings of the SYS.htp package. I've also decreased the loop count so the server won't run out of memory (since SYS.htp maintains an internal buffer of all the strings the you send to the output). Smile

DECLARE
  nLoops CONSTANT NUMBER := 1000000;
  nStart NUMBER;
  sTemp VARCHAR2(200);
  PROCEDURE calculate(sParam IN VARCHAR2) IS
  BEGIN
    htp.p(sParam);
  END calculate;
BEGIN
  dbms_output.enable(buffer_size => 10000);
  sTemp := LPAD('a', 200, 'a');
 
  htp.init();
  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    calculate(sTemp);
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');

  htp.init();
END;
/

And the procedure-free version ...

DECLARE
  nLoops CONSTANT NUMBER := 1000000;
  nStart NUMBER;
  sTemp VARCHAR2(200);
  PROCEDURE calculate(sParam IN VARCHAR2) IS
  BEGIN
    htp.p(sParam);
  END calculate;
BEGIN
  dbms_output.enable(buffer_size => 10000);
  sTemp := LPAD('a', 200, 'a');
 
  htp.init();
  nStart := dbms_utility.get_time();
  FOR i IN 1..nLoops LOOP
    htp.p(sTemp);
  END LOOP;
  dbms_output.put_line('time: ' || ROUND((dbms_utility.get_time() - nStart)/100, 3) || ' s');

  htp.init();
END;
/

You should run them in two separate sessions, otherwise you can easily end up with false results due to the package state of SYS.htp.

My results were:
time: 23.7 s
time: 23.27 s

You can see that the overhead is less than 0.5s, which makes up less than 2% of the total running time.

After all these tests prove only one thing: the overhead seriously depends on the number of parameters, the type of parameters, etc. However in real-world situations you should rarely worry about overhead of procedure invocations. Smile

PS: I've added this short "article" as a comment to someone's blog too, but I put it here as well ... just to make sure it won't accidentally disappear. Smile