PRAGMA SERIALLY_REUSABLE in ORACLE

PRAGMA SERIALLY_REUSABLE:

The SERIALLY_REUSABLE Pragma specifies that the package state is needed for only one call to the server (for example, an OCI call to the database or a stored procedure invocation through a database link). After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.

This Pragma is appropriate for packages that declare large temporary work areas that are used once in the same session.

The SERIALLY_REUSABLE Pragma can appear in the declare_section of the specification of a bodiless package, or in both the specification and body of a package, but not in only the body of a package.

The state of a serially reusable package persists only for the lifetime of a CALL to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, Oracle creates a new instantiation (described below) of the serially reusable package and initializes all the global variables to NULL or to the default values provided. Any changes made to the serially reusable package state in the previous CALLs to the server are not visible.

Note: Creating a new instantiation of a serially reusable package on a CALL to the server does not necessarily imply that Oracle allocates memory or configures the instantiation object. Oracle simply looks for an available instantiation work area (which is allocated and configured) for this package in a least-recently used (LRU) pool in SGA. At the end of the CALL to the server this work area is returned back to the LRU pool. The reason for keeping the pool in the SGA is that the work area can be reused across users who have requests for the same package.

Why Serially Reusable Packages?
Since the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In applications such as Oracle Office a log-on session can typically exist for days together. Applications often need to use certain packages only for certain localized periods in the session and would ideally like to de-instantiate the package state in the middle of the session once they are done using the package.

With SERIALLY_REUSABLE packages the application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a CALL to the server should be captured in SERIALLY_REUSABLE packages.

Example-1: Examine the package with & without SERIALLY_REUSABLE

Create two packages with & without SERIALLY_REUSABLE

sql_11g> create or replace package without_serially_reusable_pkg as

  2  v_without_sr int := 0;

  3  end;

  4  /

Package created.

sql_11g> create or replace package with_serially_reusable_pkg as

  2  pragma serially_reusable;

  3  v_with_sr int := 0;

  4  end;

  5  /

Package created.

Now, let us assign values to packaged variables

sql_11g> begin

  2   without_serially_reusable_pkg.v_without_sr := 100;

  3  with_serially_reusable_pkg.v_with_sr := 100;

  4  end;

  5  /

PL/SQL procedure successfully completed.

Now, let us display the values of packaged variables

sql_11g> begin

  2  dbms_output.put_line ('without_serially_reusable_pkg.v_without_sr value is -> ' || without_serially_reusable_pkg.v_without_sr );

  3   dbms_output.put_line ('with_serially_reusable_pkg.v_with_sr values is ->' || with_serially_reusable_pkg.v_with_sr );

  4  end;

  5  /

without_serially_reusable_pkg.v_without_sr value is -> 100

with_serially_reusable_pkg.v_with_sr values is ->0

PL/SQL procedure successfully completed.

with_serially_reusable_pkg.v_with_sr is showing 0 because the package is marked as serially_reusable which resets the packaged variable global values to default immediately after its call.

Now, clubbing assignment of variables & displaying the values of variables in single plsql block.

sql_11g> begin

  2   without_serially_reusable_pkg.v_without_sr := 100;

  3  with_serially_reusable_pkg.v_with_sr := 100;

  4   dbms_output.put_line ('without_serially_reusable_pkg.v_without_sr value is -> ' || without_serially_reusable_pkg.v_without_sr );

  5    dbms_output.put_line ('with_serially_reusable_pkg.v_with_sr values is ->' || with_serially_reusable_pkg.v_with_sr );

  6  end;

  7  /

without_serially_reusable_pkg.v_without_sr value is -> 100

with_serially_reusable_pkg.v_with_sr values is ->100

PL/SQL procedure successfully completed.