RE: Interesting PL/SQL Puzzle

Hi LIST

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: http://www.orafaq.net
-- 
Author: Carel-Jan Engel
  INET: cjpengel.dbalert_at_xs4all.nl

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).