As my 25th winter in Chicago approaches, I attempt
to cheer myself up with jokes.
Programmer jokes.
Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.
Programmer jokes.
Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.
Here's my first joke for your enjoyment.
No Packages for You!
Packages should be the foundation, the building blocks of
any PL/SQL-based applications. I believe that you should not create
schema-level procedures and functions, but instead define them as subprograms
in one or more packages.
So you can imagine it came as quite a shock to me when an
attendee at one my of trainings came up over the break and said to me:
My DBAs won't let me put my code in packages. Everything has to be defined as a procedure or function at the schema level. They tell me that packages take up too much memory and so they cannot be used.
Wow! Packages take up too much memory....
Who would've thought it?
Now, as with most lies, exaggerations and extremely bad
advice, there is a small kernel of truthiness that led this person's DBA to
take this outlandish position.
That small kernel is the fact that when you invoke any
subprogram of a package, the entire
package is loaded into SGA memory.
Given this fact, it is certainly possible to imagine a
worst-case scenario about which a DBA should
be concerned.
Worst case scenario: All of the backend, PL/SQL code for the
application is implemented in a single, enormous package, containing hundreds -
no, thousands - of subprograms. So to run anything
in that backend requires that Oracle load all the partially-compiled code
into the SGA, consuming memory unnecessarily and perhaps causing other data in
the SGA to be pushed out of the cache, through application of the least
recently used algorithm.
I hope you would all agree quite readily that just because
you can come up with a worst-case scenario, you should not necessarily reject
the feature involved in that scenario.
No, you just need to make sure you utilize the feature properly.
So let's revisit the DBA's concern: "Packages use too
much memory."
What he was really saying was: "If you only need to run
procedure X, why use up memory to also load procedures Y and Z, as well?"
This is a valid concern - if your package is filled up with
subprograms that support a wide range of disconnected requirements. Suppose, on
the other hand, that your package is very focused and contains only those
subprograms related to a specific area of functionality.
It is, therefore, quite likely that if you are running
procedure X, you will also soon need to run procedures Y and Z.
If these three procedures are not in a package, then they have to be loaded and managed
separately in the SGA. They will require almost exactly the same amount of
memory as the three procedures in the package, but Oracle will have to consume
more CPU cycles.
In other words, Oracle's memory management for packages -
loading the entire package into memory - should help your application run more
efficiently, and certainly not consume memory unnecessarily when packages are
constructed properly.
So by all means use
packages but follow these guidelines when doing so:
- Create lots of small,
tightly-focused packages. Group all related functionality in the same
package.
- Minimize dependencies
between packages (especially in the package specification).
- When a package gets big, check to see if its scope (the functionality implemented in the package) has broadened. If so, consider breaking up that single package into several, smaller packages.
Know Any Good "Jokes"?
I bet you've run into all sorts of hilariously truthy
stories of developers or DBAs doing the most absurd things for the only
slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com.
In the meantime, don't forget to put all your code in schema
level procedures and functions. That way
you will use less memory and the Schema Browser in SQL Developer will be about 100 miles long.
That was a joke, OK? All code in packages!
Originally published in ODTUG's Confessions of a Quick and Dirty Programmer series
I don't know whether the following one is joke or its a good suggestion :), please advise…
ReplyDeleteMy colleague told me dont write Select into statements in the block instead write Cursor to get values....i felt its a joke....since to get a single value do we really require a cursor?
It's a bit of a joke. To fetch a single row, use SELECT-INTO and usually it is a good idea to put it inside its own function, not cursor. That way you can reuse it across your code base.
ReplyDeleteHow about the difference between invoke cursor from app, such as Java and package internal?
ReplyDeleteDepends on what you need to do. Get a single row? No. Get multiple rows? A cursor variable works nicely with Java. But could also use BULK COLLECT into PL/SQL function and return as table function in query, or collection in function. Java should be able to work with all that. Main thing is to minimize need for SQL to be written outside of database (views and packages).
DeleteOf course, minimize the SQL out side of database is always a good design.
DeleteThank you,
Advice from my DBA: write all SQL in your packages with dynamic SQL, that way you don't have to worry about invalid packages.
ReplyDeleteI really thought he meant it as a joke, but later it becomes clear that he really means it!
Ugh. Ugly. So to make the point really clear: Only use dynamic SQL when you *need* it. And "need" is not defined as: "Ooh, ooh, wouldn't it be so cool if you could provide on package with a generic API, pass in table names, column names, and it constructs all the SQL for you?"
Delete