Monday, November 14, 2016

PL/SQL Programming Joke #1: Don't Use Packages, Says My DBA

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.

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

7 comments:

  1. I don't know whether the following one is joke or its a good suggestion :), please advise…

    My 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?

    ReplyDelete
  2. 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.

    ReplyDelete
  3. How about the difference between invoke cursor from app, such as Java and package internal?

    ReplyDelete
    Replies
    1. Depends 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).

      Delete
    2. Of course, minimize the SQL out side of database is always a good design.
      Thank you,

      Delete
  4. Advice from my DBA: write all SQL in your packages with dynamic SQL, that way you don't have to worry about invalid packages.

    I really thought he meant it as a joke, but later it becomes clear that he really means it!

    ReplyDelete
    Replies
    1. 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