Re: PL/SQL Package Standards

One thing to remember is that when you call a packaged procedure, the entire package is instntiated. In your case, a 2MB package is going to eat 2MB of the SGA plus the associated overhead.

Becuase of this, it is probably best to maintain packages in a fashion that groups the packages into common sets. Since the entire package will be instantiated, it is logical to group procedures into packages that will result in all of the required procedures being in memory after the first procedure is invoked.

If you feel that you must need to maintain all of the procedures in one large package, you can do this by using a "broker" package. The broker is a complete list of all of the available procedures compiled into a single package spec and body. However, the broker doesn't contain any of the procedural code. Rather it contains a stub that points to different packages that contain the code.

Say my main package is called UTIL (original ain't it!) and contains 100 procedures averaging 200K in size. Now suppose that all I want to do is call one procedure that is 20K... Doesn't matter the entire 20MB (is that math right?) gets instantiated. Now, split that single large package into 20 (1MB) packages containing 50 procedures each. Invoking that 20K procedure only requires a 1MB package to be instantiated. Since I still want a single package with all my procedures, I make the procedures in UTIL point to the procedures in other packages. For example, UTIL.VALIDATE_PRODUCT could point to the packaged procedure PRODUCT.VALIDATE_PRODUCT which would contain all of the PL/SQL code.

Suppose that the UTIL package takes 1MB and the package containing the code takes 1MB... Instead of sucking up 20MB of SGA, I only need 2MB.

Hope that helps.

cakilner_at_my-deja.com wrote in message <810st5$kvk$1_at_nnrp1.deja.com>...
>Does anyone know any standards around developing Packages? It is
>recommended to keep the Packages fairly small? Should everything go into
>packages where possible?
>
>One of the problems I have encountered it we are having a lot of
>problems with space in the shared memory pool. I guess an easy fix
>would be to just bump up the size....but this really isn't an option at
>this point. I do know what a lot of the developers on the system are
>coding using packages and I know one of this is close to 2Meg in
>size...should I recommend we reduce the size of these packages and
>perhaps move some code that it used less frequently into
>procedures/functions that are called from within the package?
>
>Can anyone share any experience in this area?
>
>Thanks,
>Carol Kilner
>ckilner_at_protectair.com
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Nov 22 1999 - 22:29:56 CST