Packages and memory
sujata, December 09, 2002 - 6:18 pm UTC
Hi Tom,
I have seen some existing code at my job..where for example
all the functions for an application were in a package (may be 100 functions).
Then there are some packages that have a logical theme going, for example all the employee related procedures and functions are in one package, all the projects related procedures and functions in another package.
I have had this discussion with other developers about should packages be logically divided (employee package, project package etc.) or in some random order..for example all the selects in one package, all the updates in another etc.
Is it true that when a user access a packaged procedure..the whole package is loaded into the memory?
and it stays in the memory for other users to access.
Then it will make more sense to have all the employee stuff in one package, all the projects procedures and functions in another, then what about the functions that are accessed by both employee module and project module..
I am at a point where I need to start a fresh application design (backend procedures, functions) and I am confused about which design to go with..and if I go with and why?
I know only one thing for sure that I do want to go with packages (from what I have read)..instead of stand alone procedures and functions.
Could you please shed some light on it. discuss the pros and cons of each design.
Thanks.
December 09, 2002 - 7:28 pm UTC
You shouldn't be worried about physical things like "memory" here (its all in the shared pool -- you are going to be using it all pretty much, it is stored in little 4k chunks).
Packages are primarily a programming device, use them as such. They are a programming device in support of modularization and encapsulation.
That they have great side effects -- like breaking the dependency chain, is an excellent reason to use them but the primary reason is as a programming device.
You should group functions together because they LOGICALLY belong together. Randomly -- that would be silly. All selects -- doesn't seem to make sense. Ahh - but to put all EMPLOYEE functionality together, and all PROJECT functionality -- that is making great programming sense.
Use it as a programming tool. If you do something with the employee package, the first guy will load it up (and it'll happen so fast they won't even know they did it) and every sussequent user will benefit from the fact they loaded it up. If you use it -it'll stay loaded. If not, it'll get unloaded if and when we need the memory. If you group functions together LOGICALLY by related functionality -- the odds are they will all get used, so the argument of "but it'll load 5 bytes more then I personally need" just doesn't cut it.
Our database version is 8.1.7.4.0
sujata, December 09, 2002 - 6:21 pm UTC
Thanks
December 09, 2002 - 7:29 pm UTC
and this applies universally....
Explain benefit of Pkg to a C guy
robert, January 28, 2003 - 8:00 pm UTC
Tom,
I am trying to "convert" a C-guy whose pl/sql development
experience has been limited to a routine of copy-paste-modify non-modular, long standalone procedures.
I want to show him the beauty and logic of pkg.
As best as I can remember from my C class, there is no such notion as "Object".
Is there an analogy I can draw that underlines some sort of similarities between pkg benefits (modularization, re-use, encapsulation) to the same approach in C coding ?
Thanks
January 28, 2003 - 8:09 pm UTC
Hey -- I'm a C guy, been doing C much longer then PLSQL...
C++ has an object construct.
A C struct is very much like an object - heck, you can have pointers to functions and everything (the first C++ compilers were really just precompilers that spit out -- C!)
The main benefits of PLSQL over C (from a C coder)
o tighter integration with the database itself. Tell me how many lines of C code would it take to do:
for x in (select * from all_objects )
loop
....
end loop;
??
o nice -- very very very nice -- dependency tracking. I ask the question "hey, who is using this table". C guy goes "I don't know", plsql guy goes
select * from all_dependencies where referenced_name = 'T' and referenced_type = 'TABLE' and referenced_owner = 'FOO';
o easier maintenance of code -- add a column to a table and:
for x in ( select * from t ) .....
declare
l_rec t%rowtype;
begin
......
still work. Just have a table and:
declare
x t.y%type;
begin
does the work for you -- someone alters T and makes Y bigger -- no big deal, it just fixes itself.
o faster -- less network round trips
o even more PORTABLE then C itself. C is pretty portable but plsql -- well, it is exactly the same on all Oracle ports and that is alot...
Just like pro*c is 10 times faster then OCI to code with, PLSQL is 10 times faster then Pro*c to code with. Will there be times you need C -- sometimes, but it is less and less frequent that I drop down to an old fashioned compiler and write C code. SQL first, PLSQL second, Java third, C last -- for the reasons above...
C and PL/SQL Package
robert, January 28, 2003 - 8:38 pm UTC
>> Hey -- I'm a C guy, been doing C much longer then PLSQL...
Hey I know...that's precisely why I'm asking your input.
I am not converting the C-guy from doing C to doing PL/SQL
He is doing PL/SQL...just the wrong, dumb way -- stand-alone procedures only -- there is a bit of FUD factor with him on packages.
The thing is that I want to point to something, some technique in good C coding that is similar - in concept - to writing plsql packages that result in modularization, encapsulation, the goodies...and say "hey you'd do this in C, right ?...well this is just like creating plsql packages..." -- Hope that will help him see the "light" right away.
You did mention above about pointers to functions...ah...yes that brings back some (painful) memories...I will try to draw the similarity that concept.
January 28, 2003 - 9:00 pm UTC
Ask him if he ever uses static functions. If he says "no", then it is useless -- he is "global man".... nothing will convince him then.
If he says yes -- then tell him "packages give you static functions, you can hide them just like you can in C, no name clashes"...
Pointers to functions are so cool... PL/1 -- my first language, you always remember your first -- had them on the mainframe. Entry Variables they were called. Coolest thing since sliced bread -- almost wish plsql had them ;)
maximum package size
A reader, December 22, 2003 - 10:33 am UTC
Hi
What is the maximum size of a pl/sql package?
We have some big packages (the procedures and functions are however related) and our senior dba wants to split them up because he says this is faster and wont fragment the memory.
I do not agree.
Another question, if a package has 10 procedures, when we call one of them is the whole package loaded? Or just the package specification and the corresponding procedure?
Thank you
December 22, 2003 - 10:50 am UTC
there isn't one really -- they can get quite enormous. there are limits, but they are based on the complexity (nesting) of the code. You can have a small package bomb on the compile (in older releases, not so much in 8i and up) whereas a much longer package goes no problem.
PLSQL is already paged in in 4k chunks. It matters not if you have 1000k of code or 10 100k pieces of code, you'll still have 1000/4 chunks of memory being used.
Tell the DBA to scientifically prove their point and give you metrics that are convincing enough to break your code up (they cannot, they do not exist)
The code is "paged" as needed. Basically, it doesn't really matter here as SOMEONE is going to load the code.
Loading the packages
A reader, December 22, 2003 - 11:00 am UTC
Hi
Are you saying that procedures and functions of a package is loaded to the shared pool as they are called and not all loaded at the same time :-?
December 22, 2003 - 11:15 am UTC
large packages
Dan Clamage, December 24, 2003 - 9:35 am UTC
For one app, I needed to generate a package body, based on the desired characteristics determined by a Modeler person (actually a marketing person with some math understanding). These characteristics only changed infrequiently, so generating the package body was a suitable solution. There was a data structure hierarchy, so that adding a higher dimension generated more code. I successfully generated a package with about 75,000 lines of code. I think the compiled size was around 300K. No problems.
I've been programming in C on or off for 18 years. Packages provide significant additional features that stand-alone procedures don't have (encapsulation, information hiding, overloading, to name a few). Tell "Mr. Global" that packages are a standard in your shop, so he better get used to them. There are the rare cases where you do want stand-alone, but it's not that often I use them anymore.
Packages are really just containers, like a basket.
One big problem with trying to break up a large package is the namespace. All those references to packaged subroutines that will have to be edited. I wish PL/SQL had a namespace localization feature like Java and C# has.
Large packages are also harder to maintain. It's easy to get "lost in the sauce". They're less likely to reuse common code (I've seen the same procedure copy/pasted into 10 different packages).
Once you start thinking in terms of building lots of small, focused packages, you'll have an easier time working and managing them. On my latest project, I have one package for just SQL on three very closely related tables; one for just DML on said tables; several for just "business rules" for various conceptual levels. My packages have names like company_qry, company_dml, company_bus, extract_bus, rt_bus. I know exactly what kinds of routines are stored in each.
One more recommendation -- don't build more procedures than you actually have a need for. Don't go trying to encapsulate every query you think someone *might* need. If they need it down the road, they can always add it to the appropriate package without breaking other stuff.
December 24, 2003 - 10:20 am UTC
I don't think large packages lead to "less likely to reuse common code".
That is like saying a runny nose causes a cold. You have the symptom and cause backwards if you ask me. It is because the coders cut and paste INSTEAD of reusing that you get large packages. It is because they haven't had to maintain a system for the long haul. It is because they haven't learned yet (it'll come back to bite them in five years time). It is a learning thing -- something that must be taught to them (whatever happened to modular design -- the code I see getting pumped out somedays just frankly scares me to death. no one seems to be taught HOW to code anymore -- they are taught languages and features -- but not common sense, modularity, thinking ahead, designing for change, rant rant rant)....
But I agree with you pretty much 100% there....
Large packages
Dan Clamage, December 26, 2003 - 2:54 pm UTC
>They're less likely to reuse common code
Yeah I didn't say that clearly. What I meant was, if you're shoving everything into your very application-specific package, and you've written something that could actually be general enough to be used in other applications, nobody is going to bother referencing your package for that one tidbit. I was thinking back to a really large package I wrote that really should've been broken up into several (at least). I did have business logic that I'd correctly encapsulated; but improperly struck in the application package itself, reducing considerably the likelihood that another programmer would reuse (call) it. I missed an opportunity to produce something that would be reusable. I wasn't thinking "modular" at the package level.
Alexander the ok, December 16, 2005 - 10:39 am UTC
How's it going Tom?
Some asked a question above that I too am interested in; Are large packages a legitimate performance hit? (We'll say large in this case is 2000-3000 lines.) I was wondering also if there is some place I can see the effect of loading a big package into memory when accessing it. A query or something I could run that I could use to prove that it's a negligible amount. Thanks as always.
December 16, 2005 - 12:59 pm UTC
I have never observed it being a "performance issue". There would be the initial one time load (hopefully one time) but that would be it.
and it would be a function of the size of the generated code, not the source code file - they are only somewhat related to eachother.
Clarification of memory usage
RP, May 31, 2006 - 5:22 am UTC
Hi Tom,
if i develop an application with mutliple packages (logical groups of functionality of course!) and some of these packages have state and some don't - will EACH logged on user connnection have a copy of ALL the packages? Or is there some sharing going on?
Thanks
May 31, 2006 - 10:10 am UTC
the CODE is always shared.
each SESSION has its own "DATA SEGMENT"
It is like shared libraries on Unix - the code is shared (one copy for all) but each process has its own data segment (totally unlike windows where the code is shared AND the data is shared across processes using DLL's)