Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Interesting PL/SQL Puzzle

RE: Interesting PL/SQL Puzzle

From: Carel-Jan Engel <>
Date: Mon, 10 Nov 2003 15:14:25 -0800
Message-ID: <>


Thanks Waleed fot the code. I did some extra tests, following Connor's suggestion:

I created 4 extra versions of the testplsql5 procedure.

So I ended up with 5 versions, each with 4 parameters, the first having 4 in out parameters, the second 1 in parameter and 3 in out parameters and so on.

I ran the tests 5 times each (my processor is somewhat slower):

4 in out parameters: 10.9 sec. avg.
3 in out parameters: 9.7 sec avg
2 in out parameters: 8.8 sec. avg
1 in out parameters: 8.0 sec. avg
0 in out parameters: 6.0 sec. avg  (!)

It appears that the overhead caused by a 'call by reference' (in out) will cost you some extra time. The 'call by value' appears to be cheaper. That wonders me. At least in 'C', a call by reference can be made by pushing the pointer to the memory location on the stack, whereas for a call by value the whole contents of that memory must be pushed on the stack. On the other hand, we're testing with strings right here. Strings are always passed by their pointer in 'C', and not copied.

Whatsoever, it appears that the call by reference is more expensive than a call by value. So, when this is really important, declaring your parameters 'in' i.s.o. 'in out' when no value has to be passed back is cheaper.

More extensive testing should be performed to see whether this applies to other datatypes as well.

Regards, Carel-Jan

At 08:09 10-11-03 -0800, you wrote:
>Below are two dummy procs that are good enough to explain the issue (Jared
>forgive me for posting this big code).
>All the code in proc test_plsql1 is inside an IF clause that will not run.

<clipped out some code> ;-)

Please see the official ORACLE-L FAQ:
Author: Carel-Jan Engel

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Nov 10 2003 - 17:14:25 CST

Original text of this message