Skip to main content

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

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

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel