Re: PL/SQL stored procedure performance questions

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 24 Jun 2002 15:24:16 +0400
Message-ID: <af6vha$cip$1_at_babylon.agtel.net>


[Quoted] Limits are on DIANA nodes. Afaik there are no limits on source code size granted you have enough space in SYSTEM to fit it. So it's ok to group logically coherent procedures into single package.

--
Vladimir Zakharychev (bob_at_dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Bill Thorsteinson" <billthor_at_yahoo.com> wrote in message
news:282ahuob6dsavk33nbnag6tljs6u1bdusc_at_4ax.com...

> 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 Mon Jun 24 2002 - 13:24:16 CEST

Original text of this message