PRAGMA SERIALLY_REUSABLE

 

PRAGMA SERIALLY_REUSABLE:

About It:

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.

 

The SERIALLY_REUSABLE Pragma can appear in the declare_section of the specification of a package without body, 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 instantiate 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.

When to use Serially reusable:

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

Why Serially Reusable Packages?

Because the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In some applications, such as Oracle Office, a log-on session typically exists for days. Applications often need certain packages only for short periods of the session. Ideally, such applications could de-instantiate the package state in after they finish using the package (the middle of the session).

 

SERIALLY_REUSABLEpackages enable you to design applications that manage memory better for scalability. 

For serially reusable packages, the package global memory is not kept in the UGA for each user; rather, it is kept in a small pool and reused for different users. Therefore, the global memory for such a package is only used in a unit of work. After that unit of work, the memory can be released to the pool to be reused by another user.

Drawbacks of Non-reusable Package:

PL/SQL packages usually consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability, because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE.

Examine the package with & without SERIALLY_REUSABLE

without SERIALLY_REUSABLE

create or replace package without_serially_reusable_pkg as

 v_without_sr int := 0;

 end;

 /

Package created.

With serially reusable:

create or replace package with_serially_reusable_pkg as

 pragma serially_reusable;

 v_with_sr int := 0;

 end;

  /

 Now, let us assign values to packaged variables

  begin

 without_serially_reusable_pkg.v_without_sr := 100;

 with_serially_reusable_pkg.v_with_sr := 100;

 end;

  /

 Print the assigned value:

begin

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

 dbms_output.put_line (‘with_serially_reusable_pkg.v_with_sr values is ->’ || with_serially_reusable_pkg.v_with_sr );

end;

/

 Output:  without_serially_reusable_pkg.v_without_sr value is -> 100

with_serially_reusable_pkg.v_with_sr values is ->0

Summary: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 PL/SQL block.

  begin

 without_serially_reusable_pkg.v_without_sr := 100;

 with_serially_reusable_pkg.v_with_sr := 100;

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

dbms_output.put_line (‘with_serially_reusable_pkg.v_with_sr values is ->’ || with_serially_reusable_pkg.v_with_sr );

end;

/

Output: 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.

 example 2: (Without serially reusable)

CREATE OR REPLACE PACKAGE pkg1 IS
  — PRAGMA SERIALLY_REUSABLE;
   num NUMBER := 0;
   PROCEDURE init_pkg_state(n NUMBER);
   PROCEDURE print_pkg_state;
END pkg1;
/

CREATE OR REPLACE PACKAGE BODY pkg1 IS
   –PRAGMA SERIALLY_REUSABLE;
   PROCEDURE init_pkg_state (n NUMBER) IS
   BEGIN
      pkg1.num := n;
   END;
   PROCEDURE print_pkg_state IS
   BEGIN
      dbms_output.put_line(‘Num: ‘ || pkg1.num);
   END;
END pkg1;
/

execute  init_pkg_state(10);

execute  print_pkg_state;

Output: 6

Output : 0  (un-comment the pragma line in the above query)  with serially reusable.