Re: PL/SQL stored procedure performance questions

[Quoted] On Fri, 21 Jun 2002 23:45:26 -0700, Daniel Morgan <damorgan_at_exesolutions.com> wrote:

>bob sullivan wrote:
>
>> Hi, everyone,
>>
>> A couple of questions...
>>
>> 1. I've inherited a bunch of PL/SQL code that's written in the
>> form of anonymous block script files that are called from
>> SQL*Plus. Would I get any performance benefit by rewriting
>> them as stored procedures?
>>
>> 2. Is there a byte limit for stored packages that I should not
>> exceed for performance reasons? I have a couple of packages
>> that could be logically rewritten as one package, but I don't
>> want to do that if their size together exceeds an optimal
>> size threshold that I don't know about...
>>
>> Thanks!
>> bob
>> bsullivn_at_comcast.net
>
>1. If they were run by multiple users all of the time yes. If they are
>run only from time-to-time probably not. I would still be inclined to
>turn them into stored procedures, performance improvement or not just
>for security. Scripts have a habit of disappearing or being modified
>without accountability.
>
There will be some improvement as the byte code will be pre compiled. How much depends on the time difference between compile time and execution time.

>2. No. And if there is you are in no danger of approaching it until
>you've written something far larger than the size of Oracle Financials.
>
Last I knew the limits where 64k for both the source and byte code per procedure. This should be far more than is needed for any maintainable code. 64k would likely run well over 100 pages.

>Daniel Morgan
Received on Sun Jun 23 2002 - 01:37:44 CEST