Packaging to improve memory use

When retrieving the bytecode (or, for that matter, the DIANA) of a stored PL/SQL program, Oracle reads the entire program. This rule applies not only to procedures and functions, but also to packages. In other words, you can't get Oracle to retrieve only a part of a package; the first time any session uses some element of a package, even just a single package variable, Oracle loads the compiled code for the entire package into the library cache. So a logical grouping of package elements is not just a good design idea, but it will also help your system's performance.

Because Oracle reads an entire package into memory at once, design each package with functionally related components that are likely to be invoked together.

20.3.3.4 Large collections in PL/SQL

Sharing is a wonderful thing, but of course not everything can be shared at runtime. Even when two or more users are executing the same program owned by the same Oracle schema, each session has its own private memory area, which holds run-specific data such as the value of local or package variables, constants, and cursors. It wouldn't make much sense to try to share values that are specific to a given session.

In my experience, the most common PL/SQL memory problems occur when manipulating large collections, which are a great example of runtime data that is not shareable. (Chapter 11 describes collections in detail.) Imagine that I declare a PL/SQL associative array—formerly known as an index-by table—as follows:

DECLARE TYPE number_tab_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; number_tab number_tab_t; empty_tab number_tab_t;

Now I create a bunch of elements in this table:

FOR i IN 1..100000 LOOP number_tab(i) := i; END LOOP;

Oracle has to put all of those elements somewhere. Following the rules discussed earlier, memory for this array will come from UGA in the case of package-level data, or CGA in the case of data in anonymous blocks or top-level procedures or functions.

You may wonder how you can get that memory back once a program is through with it. This is a case where the natural and easy thing to do will help quite a bit. That is, you can use one of these two forms:

number_tab.DELETE;

or:

number_tab := empty_tab;

Using either of these statements will cause Oracle to free the memory into its originating free list. That is, package-level memory frees into the UGA, and call-level memory frees into the PGA. The same thing happens when the collection passes out of scope; that is, if you declare and use the collection only in a standalone procedure, Oracle realizes after the procedure finishes executing that you don't need it any more. Either way, though, this memory is not available to other sessions, nor is it available to the current session for CGA memory requirements. So, if a subsequent DML operation requires a large sort, you could wind up with some huge memory requirements. When the session ends, Oracle will release this memory to its parent memory heap.

I should point out that it is no great hardship for a virtual memory operating system with plenty of paging/swap space if processes retain large amounts of inactive virtual memory in their address space. This inactive memory consumes only paging space, not real memory. There may be times, however, when you don't want to fill up paging space, and you would prefer that Oracle release the memory. For those times, Oracle supplies an on-demand "garbage collection" procedure. The syntax is simply:

DBMS_SESSION.FREE_UNUSED_USER_MEMORY;

This built-in procedure will find most of the memory that is no longer in use by any program variables, and release it back to the parent memory heap—the PGA in the case of dedicated server, or the SGA in the case of shared server.

There are two ways you can tell Oracle that you no longer need a particular collection in PL/SQL. One way is for the collection to go "out of scope." However, that won't work for package-level data, in which case you would use the built-in DELETE method just shown.

I have run quite a few test cases to determine the effect of running garbage collection in different scenarios: for example, associative arrays versus nested tables, shared versus dedicated server, anonymous blocks versus package data. The following conclusions and tips apply to using large collections.

· Merely assigning a NULL to a nested table or VARRAY will fail to mark its memory as unused. Instead, you can do one of three things: use the collection.DELETE method; assign a null but initialized collection to it; or wait for it to go out of scope.

· If you need to release memory to the parent heap, use DBMS_SESSION.FREE_UNUSED_USER_MEMORY when your program has populated one or more large PL/SQL tables, marked them as unused, and is unlikely to need further large memory allocations for similar operations.

· Shared server mode can be more prone than dedicated server mode to memory-shortage errors. This is because the UGA is drawn from the SGA, which is fixed in size. As discussed in the later section Section 20.3.4, you may get an ORA-04031 error.

· If you must use shared server connections, you cannot release the memory occupied by PL/SQL tables unless the table is declared at the package level.

As a practical matter, for a collection of NUMBER elements, there seems to be no difference in storage required to store NULL elements versus, say, 38-digit number elements. However, Oracle does seem to allocate memory for VARCHAR2 elements dynamically if the elements are declared larger than VARCHAR(30).

When populating an associative array in dedicated server mode, a million-element associative array of NUMBERs occupies about 38 MB; even if the million elements are just Booleans, Oracle9i will require almost 15 MB of memory. Multiply that by 100 users and you're talking some big numbers, especially if you don't want the operating system to start paging this memory out to disk.

If you'd like to discover for yourself how much UGA and PGA your current session uses, you can run a query like the following:

SELECT n.name, ROUND(m.value/1024) kbytes FROM V$STATNAME n, V$MYSTAT m WHERE n.statistic# = m.statistic# AND n.name LIKE 'session%memory%'

(You'll need nondefault privileges to read the two V$ views in this query.)

Incidentally, if you don't want to terminate the session, the only supported way I have found to release freed PGA memory to its parent heap is to do a hard reset of package state as follows:

DBMS_SESSION.RESET_PACKAGE

Frees all memory allocated to package state. This has the effect of resetting all package variables to their default values. For packages, this built-in goes beyond what FREE_UNUSED_USER_MEMORY does, because RESET_PACKAGE doesn't care whether the memory is in use or not.

DBMS_SESSION.MODIFY_PACKAGE_STATE (action_flags IN PLS_INTEGER)

You can supply one of two constants as the action flag: DBMS_SESSION.FREE_ALL_RESOURCES or DBMS_SESSION.REINITIALIZE. The first has the same effect as using the RESET_PACKAGE procedure. Supplying the latter constant resets state variables to their defaults, but doesn't actually free and re-create the package from scratch; also, it only soft-closes open cursors, and does not flush the cursor cache. If these behaviors are acceptable in your application, use the second constant, as it will perform better than a complete reset.