Take the 2-minute tour ×
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.

Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.

share|improve this question
add comment

1 Answer 1

up vote 12 down vote accepted

Benefits of Packages

Logical Grouping – Methods that work together can be put into a cohesive unit rather than just logically coupled but physically separate.

Secure Private Methods - Functions and Procedures can be made private to the package and only be used within it. This makes the public surface simpler and more secure.

Privilege Management – Permissions can be granted once for a group of procedures that work together rather than separately for each procedure/function required.

Secure Wrapping - Wrapped packages are more difficult to unwrap than wrapped functions/procedures.

Simplified Naming – A larger namespace allows names that are simpler and can be re-used in other packages.

Better Performance – Packages can be compiled and are loaded into memory in entirety rather than piecemeal as other methods. This benefit if it exists at all is minimal compared to the other benefits.

Reduced Invalidation – Changing a package body does not invalidate dependencies as changing a function or procedure does.

Unique Features - Package Variables, Package Constants, Initialization, Session State, Package Comments, and Overloaded Methods.

References:
11.2 Concepts Guide
Ask Tom Question
StackOverflow.com Question on Package Performance
Unwrapping PL/SQL Presentation (pdf)

share|improve this answer
2  
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 Cave Nov 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 Riffel Nov 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 Cave Nov 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 Riffel Nov 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 Ozgur Nov 14 '11 at 9:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.