Re: PL/SQL stored procedure performance questions

Thanks for the help, everyone! :)

cheers,
bob

"Vladimir M. Zakharychev" wrote:
> 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 Tue Jul 09 2002 - 02:56:31 CEST