Re: Overhead for Local Modules

From: Lewis C <lewisc_at_excite.com>
Date: Fri, 15 Apr 2005 00:27:42 GMT
Message-ID: <772u51p4tjob62172gv09ak8g7nv7n7547@4ax.com>

On 14 Apr 2005 12:46:41 -0700, a_muthiah_at_hotmail.com (jdi) wrote:

>Many articles have suggested using local procedures and functions when
>writing PL/SQL code. This helps in simplifying and modularizing code.
> My questions is: what, if any, overhead is introduced by local
>procedures and functions? In other words, is there a performance
>decrease if I use local procs and functions as opposed to simply
>writing all my PL/SQL in the main BEGIN..END block?

Not really no. There may be a minute overhead for the call cpu-wise but I found that it's not even measureable. I've used the profiler and my own timing calls and wasn't able to measure any. That was in 9i. The timings probably added more than calling the procedures did.

10g is supposed to be highly optimized so it may even inline the calls when it writes the p-code. And if you use native compilation, you get C optimizations.

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Thu Apr 14 2005 - 19:27:42 CDT