25.2.1 Tune the Size of the Shared Pool of the SGA

Before you can execute a stored package module or reference a stored package object, the compiled code for that package must be loaded into the SGA. Clearly, if the package is already present in shared memory, your code executes more quickly. An important element of tuning an application which is heavily dependent on stored packages (especially large ones) is to optimize package access so that the most often-used packages are always present when needed.

The default method for maintaining packages in the SGA (or "shared pool") is to let the RDBMS manage the code using its least-recently-used algorithm. The first time you reference a package, the compiled code is loaded into the shared pool. It is then available to anyone with EXECUTE authority on that package. It remains in the shared pool until the memory is needed by other memory-based resources and that package has not been used most recently. At that point, the package is flushed from the shared pool. The next time an object in the package is needed, the whole package has to be loaded once again into memory.

The larger your shared pool, the more likely it is that your programs will be resident in memory the next time they are needed. Yet if you make your shared pool too large, you will be wasting memory. You should monitor your shared buffer pool to make sure it is retaining all the parsed SQL cursors and PL/SQL code segments which are commonly referenced in your application. If you find that too much swapping is occurring, increase the size of the shared buffer pool (as physical memory permits) by adjusting the SHARED_POOL_SIZE parameter in your INIT.ORA file.

You can display all the objects currently in the shared pool that are larger than a specified size (in the example, 25KB) with the following statement (make sure you have SET SERVEROUTPUT ON in SQL*Plus before you make this call):

SQL> exec dbms_shared_pool.sizes (25);

25.2.2 Pin Critical Code into the SGA

To increase your ability to tune application performance, Oracle supplies the DBMS_SHARED_POOL package to pin a package in the shared pool. When a package is pinned, the RDBMS does not apply its least-recently-used algorithm to that package. The package remains in memory for as long as the database instance is available (or until you explicitly "unpin" the package, as described below).

At this time, only packages can be pinned in the shared pool; this fact provides added incentive to define your procedures and functions inside packages, rather than as standalone modules.

You will only want to pin code when absolutely necessary, since you can end up setting aside too much memory for code, resulting in a degradation in performance of other aspects of the application. In fact, Oracle Corporation warns that the KEEP and UNKEEP procedures may not be supported in future releases, since it might provide an "automatic mechanism" which replaces these procedures.

The usual candidates for pinning are particularly large programs. Prior to Oracle 7.3, Oracle requires contiguous memory in the SGA to store the code, so if sufficient space is not available at a given point of execution, Oracle will either raise an error or start swapping out other programs to make room. Neither scenario is optimal.

Usually you will pin programs right after the database is started up, so that all the critical elements of your application are in place for all users. Here is an example of the pinning of the order entry package (owned by the appowner schema):

DBMS_SHARED_POOL.KEEP ('appowner.ordentry');

Here is the code you would execute to unpin the same package from shared memory:

DBMS_SHARED_POOL.UNKEEP ('appowner.ordentry');

Keep the following factors in mind when working with the DBMS_SHARED_POOL package:

• When you call the KEEP procedure, the package is "queued" for pinning in the SGA. However, the KEEP procedure does not immediately load the package into the shared pool -- that happens when the package is first referenced, either to execute a module or to use one of the declared objects, such as a global variable or a cursor.

• Oracle recommends that you pin all your packages in the shared pool soon after instance startup, when the SGA is still relatively unfragmented. That way it can set aside contiguous blocks of memory for large packages.

• If you pin a package which is not owned by SYS (DBMS_OUTPUT, for example, is owned by SYS), you must grant EXECUTE on the package to PUBLIC before you execute the KEEP procedure. Once you have pinned the package, you can revoke the PUBLIC access.

• The SQL DDL command, ALTER SYSTEM FLUSH SHARED_POOL, does not affect pinned packages. You must either explicitly UNKEEP a package or restart the database to release the package from the shared pool.

25.2.3 Tune ACCESS$ Table to Reduce First Execution Time of Code

When a database object is first referenced in a PL/SQL program, the PL/SQL engine checks the ACCESS$ table (owned by SYS) to see if the executor of the program has authority on that database object. The structure of this table is shown here:

SQL> desc access$
 Name                            Null?    Type
 ------------------------------- -------- ----
 D_OBJ#                          NOT NULL NUMBER
 ORDER#                          NOT NULL NUMBER
 COLUMNS                                  RAW(32)
 TYPES                           NOT NULL NUMBER 

The PL/SQL engine searches through this table on the D_OBJ# column, so if you create a nonunique index on the D_OBJ# column, you may in some cases reduce significantly the amount of time needed to perform this security check.

25.2.4 Creating Packages with Minimal Interdependencies

Design your code (preferably, most of it inside packages) so that you only load into memory the code you need at any given moment in time. To accomplish this objective, you should create more smaller packages, each of which is tightly focused on implementing functionality in a given area. The alternative, which too many of us employ without giving it much thought, is to create a few large packages that group together lots of different elements of functionality.

The problem with this approach is that if I need to execute one function in that 32K package, the entire package still must be loaded up into memory. Suppose that my application then touches another element of the package, such as a constant or perhaps a different function in a different functional area. The least-recently-used algorithm will then ensure that all the memory for that package continues to be set aside, perhaps crowding out other smaller programs. The result can be excessive swapping of code.

As you build your programs and design your package interfaces, be on the lookout for an opportunity to break up a single package into two or even more distinct packages with minimal interdependencies.

25.2.5 Reducing Memory Usage of Package Variables

Prior to PL/SQL8, any data declared in a package simply stayed around until the end of the session, whether or not it was needed any more by the application. This is an important feature of PL/SQL packages (persistent, global data), but it limits scalability since such memory grows linearly with the number of users.

To help applications better manage memory usage, PL/SQL8 provides the pragma SERIALLY_REUSABLE, which lets you mark some packages as "serially reusable." You can so mark a package if its state is needed only for the duration of a call to the server (for example, an OCI call to the server, a PL/SQL client-to-server, or server-to-server RPC).

The global memory for such packages is not kept in the memory area per user, but instead in a small SGA pool. At the end of the call to the server, this memory is returned to the pool for reuse. Before reuse, the package global variables are initialized to NULL or to the default values provided.

The pool is kept in SGA memory so that the work area of a package can be reused across users who have requests for the same package. In this scheme, the maximum number of work areas needed for a package is only as many as there are concurrent users of the package, which is typically much fewer than the total number of logged on users. The use of "serially reusable" packages does increase the shared-pool requirements slightly, but this is more than offset by the decrease in the per-user memory requirements. Further, Oracle ages out work areas not in use when it needs to reclaim shared pool memory.

The following example shows how global variables in a "serially reusable" package behave across call boundaries:

CREATE OR REPLACE PACKAGE sr_pkg
IS
  PRAGMA SERIALLY_REUSABLE;
  num NUMBER := 0;
  PROCEDURE print_pkg;
  PROCEDURE init_pkg (n NUMBER);
END sr_pkg;
/
CREATE OR REPLACE PACKAGE BODY sr_pkg
IS
  -- the body is required to have the pragma since the
  -- specification of this package has the pragma
  PRAGMA SERIALLY_REUSABLE;
  -- Print package state
  PROCEDURE print_pkg is
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('num: ' || sr_pkg.num);
  END;
  -- Initialize package state
  PROCEDURE init_pkg(n NUMBER) IS
  BEGIN
    sr_pkg.num := n;
  END;
END sr_pkg;
/

Now I will exercise this package. First, I enable output from SQL*Plus:

SQLPLUS> set serveroutput on;

Next, I initialize the package with a value of 4 and then display package contents -- all within a single PL/SQL block:

SQLPLUS> begin
           -- initialize and print the package
           SR_PKG.init_pkg(4);

           -- Print it in the same call to the server.
           -- We should see the new values.
           SR_PKG.print_pkg;
         end;
         /
Statement processed.
num: 4

And we see that initial value of 4. If I had not placed the call to sr_pkg.print_pkg inside the same PL/SQL block, however, that package variable would lose its setting, as you can see in the following steps:

SQLPLUS> begin
           -- We should see that the package state is reset to the
           -- initial (default) values.
           SR_PKG.print_pkg;
         end;
         /
Statement processed.
num: 0

Use this feature with care! Many of the packages I have constructed over the years absolutely rely on the persistent data feature.