PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE
--------------------------------
Prior to PL/SQL V8, 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. While this is an important feature of PL/SQL packages (persistent, global data), it limits scalability since such memory grows linearly with the number of users.
To help applications better manage memory usage, PL/SQL V8 provided 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 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 user 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.

Note: Use this feature with care. Many of your existing packages may absolutely rely on the persistent data feature.

SERIALLY_REUSABLE Pragma Format:
PRAGMA SERIALLY_REUSABLE
The keyword pragma signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

Example
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 we will exercise this package. First, we enable output from SQL*Plus:

SQLPLUS> set serveroutput on;
Next, we initialize the package with a value of 4 and then display the 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 we had placed the call to SR_PKG.print_pkg inside the same PL/SQL block, however, that package variable would lose its setting, as is shown 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