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

Labels:

Comments

  1. SriNovember 14, 2016 at 9:56 AM

    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. Steven FeuersteinNovember 14, 2016 at 10:06 AM

    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. trantuananh24hgNovember 14, 2016 at 9:42 PM

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

    ReplyDelete
    Replies
    1. Steven FeuersteinNovember 15, 2016 at 3:25 AM

      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. trantuananh24hgNovember 20, 2016 at 8:21 PM

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

      Delete
  4. AnonymousApril 14, 2017 at 5:06 AM

    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. Steven FeuersteinApril 14, 2017 at 5:08 AM

      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

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more