Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to disagree with the performance benefit. If loading code into memory is an appreciable wait event, something very bad has happened. And there is a strong possibility that the fact that packages will decrease performance because you've got to read in more code when you load the entire package when you only really needed a single method. In neither case, though, will the performance difference be measurable.
–
Justin CaveNov 11 '11 at 19:52

@Justin - That point is from the 11.2 Concepts Guide. Here is what it says: "Better performance - An entire package is loaded into memory in small chunks when a procedure in the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. When calls to related packaged procedures occur, no disk I/O is needed to run the compiled code in memory."
–
Leigh RiffelNov 11 '11 at 20:13

4

I agree that the documentation claims there is a performance benefit. The documentation is incorrect or, at a minimum, insufficient. Best case, the magnitude of the "benefit" is miniscule. And the sign of the benefit is unknown. Just as a table scan is more efficient if you're reading most of the rows and an index access is more efficient if you're reading a single row, reading an entire package into memory in one go is beneficial if you're going to use every method and disadvantageous if you really only wanted the one method.
–
Justin CaveNov 11 '11 at 20:37

1

@Justin - Your assessment sounds logical. I haven't found anything definitive one way or the other, so I added a caveat to the point in the answer. Thanks for your input.
–
Leigh RiffelNov 11 '11 at 21:01

I think this performance benefit is similar to caching. If you use only that procedure and you use it less frequently you do not gain any performance benefit. But if you use it frequently and any other procedure is used in this package you gain benefits. Developer all around is using caching, since not real performance but perceived performance is improved. Since related procedures put to same package, it is logical that some other procedures will be called also. This is all about probability of calling two procedures in same package. And normally, read with high probability, it occurs.
–
Atilla OzgurNov 14 '11 at 9:53