Re: Package Trade-offs?

In article <XqNu7.16435$5h5.8159322_at_news3.rdc2.on.home.com>, "Mike says...
>
>Recently, our DBA recommended that we use packages as little as
>possible. Her primary concern was excessive memory usage
>because the entire package is loaded into memory the first time
>a call is made to one of its members.
>

you might need a new dba.

plsql is loaded in chunks. even if a package is loaded in its entirety -- SO WHAT? you need the code.

packages PROTECT your database. If you use standalone procedures/functions I can pretty much assure you you will need addition CPU power due to the recompiling caused by the cascading invalidations that will happen.

packages PROTECT your code. they give you encapsulation, they give you a better name space (instead of 1,000 little tiny procedures fighting for named, you have 3 or 4 dozen nice neat packages with names).

packages are all you should use in a production environment.

See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:286816015990

for the clear cut example of why packages are the way to go and procedures/functions are good for examples and demos.

>From what I have read, subsequent memory executions will
>actually increase performance.
>
>Does the memory overhead of packages prohibt their use in a
>multi-user environment? Is the solution as simple as ensuring
>that any packages created are kept as small as possible? What
>are the general guidelines, from Oracle and other users, for
>using packages.
>

use them.

>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp