Friday, September 30, 2005

PL/SQL Procedure Call Overhead

Is there much overhead in calling PL/SQL procedures?

I assume that if the answer is "yes," you'll want to avoid procedure calls, which would likely mean making your procedures bigger (by combining several into one). That makes me shudder because clean, modular code is easier to read and maintain, not to mention making it easier to develop new code if its based on reliable, tested code.

I assume there is at least some overhead to calling PL/SQL procedures. I mean, if the procedure is not in the cache, you'll obviously have to go the disk to fetch it.

If it's already in memory, there could still be some overhead in the passing of parameters. UNLESS you can use the "NOCOPY" hint, that is.

http://thinkoracle.blogspot.com/2005/05/nocopy-hint.html

But to be honest, I don't know how much overhead any particular procedure call will have. Sorry to those that read the title and hoped that the content would contain the definitive answer. I might have something more conclusive after doing some research. In the meantime, here is what I do every time I have a question: I test it.

Even if I showed you a quote in a book that shows you how to calculate the overhead, I would STILL advise testing it. Documents can be out-of-date, misunderstood and just plain wrong. You need to test it.

Here is how to test it.

1. Write (or identify) a stored procedure that reflects your business requirements.

CREATE OR REPLACE PROCEDURE DoIt
IS
BEGIN
NULL;
-- Do some stuff in here!
END DoIt;

2. Now split that stored procedure into two (or more) parts, and a master proc

CREATE OR REPLACE PROCEDURE DoItPartOne
IS
BEGIN
NULL;
-- Do part of the stuff here...
END DoItPartOne;

...etc!

CREATE OR REPLACE PROCEDURE DoItInParts
IS
BEGIN
DoItPartOne;
-- DoItPartTwo;
-- etc...
END DoItInParts;

3. With stats on, call that first stored procedure that does everything, and then run TKPROF to analyse it.

ALTER SESSION SET SQL_TRACE = TRUE;
EXEC DoIt;
ALTER SESSION SET SQL_TRACE = FALSE;

TKPROF robert_ora_3028.trc robert_ora_3028.prf explain='sys/******** as sysdba'

More on gathering and analysing simple performance statistics:
http://thinkoracle.blogspot.com/2005/09/analyzing-query-performance.html

4. With stats on, call that second stored procedure.

ALTER SESSION SET SQL_TRACE = TRUE;
EXEC DoItInParts;
ALTER SESSION SET SQL_TRACE = FALSE;

You may find, as I did, that it is very hard to set up a test that reveals any kind of noticeable performance overhead. But if the procedures were spread out over the disk and not in the cache, or if there were lots and lots of parameters, I bet we could see some overhead. But if your procedure is called often enough for it to be important to you, the procedures would probably be in the cache at any given time.

But don't spend too much time in conjecture, and even when I do produce some facts, set up your tests anyway.

Comments:
you'll want to avoid procedure calls

What a drastic idea! But really Rob, you now made me think. Whenever I call a procedure, I will ask myself: does this stand alone code "unit" really need to be stand alone or can it be combined with another logically similar unit? and vice versa. I think it all boils down to good PL/SQL programming practice.
 
Hi.

You can meansure the overhead:

SQL> CREATE OR REPLACE PROCEDURE overhead_procedure AS
2 BEGIN
3 NULL;
4 END;
5 /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_loops NUMBER := 1000000;
3 l_start NUMBER;
4 BEGIN
5 -- Time block.
6 l_start := DBMS_UTILITY.get_time;
7
8 FOR i IN 1 .. l_loops LOOP
9 overhead_procedure;
10 END LOOP;
11
12 DBMS_OUTPUT.put_line('Procedure : ' ||
13 (DBMS_UTILITY.get_time - l_start));
14
15 -- Time no block.
16 l_start := DBMS_UTILITY.get_time;
17
18 FOR i IN 1 .. l_loops LOOP
19 NULL;
20 END LOOP;
21
22 DBMS_OUTPUT.put_line('No Procedure: ' ||
23 (DBMS_UTILITY.get_time - l_start));
24 END;
25 /
Procedure : 129
No Procedure: 3

PL/SQL procedure successfully completed.

SQL> DROP PROCEDURE overhead_procedure;

Procedure dropped.

SQL>

Does this mean you shouldn't use procedures? Hell no. Without them you won't have maintainable code. The overhead for each call is so small I think it's safe to say we can live with it :)

Cheers

Tim...
PS. This example is taken from my latest book "Oracle PL/SQL Tuning". Plu, plug :)
 
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. :-)

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

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

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 proove 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. :-)
 
How do I call a procedure inside a procedure?? with call or exec or just with :=
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?