PRAGMA SERIALLY_REUSABLE IN ORACLE

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

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 consumes 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.

USE CASE WITH A PACKAGE

create or replace package without_serially_reusable_pkg as
  v_without_sr int := 0;
  end;
  --------------------------------------------------------
  --With pragma serially reusable
  create or replace package with_serially_reusable_pkg as
  pragma serially_reusable;
  v_with_sr int := 0;
  end;
  ---------------------------------------------------------
  Assign values to the Global variables of both the packages
 
  begin
  without_serially_reusable_pkg.v_without_sr := 100;
  with_serially_reusable_pkg.v_with_sr := 100;
  end;
 
  Now execute the o/p
 
  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;
  ------------------------------------------------------------
 

O/P

without_serially_reusable_pkg.v_without_sr value is -> 100
 with_serially_reusable_pkg.v_with_sr values is ->0
 
 We can see that serially_reusable resets the packaged variable global values to default immediately after its call.
 

  Please also check Pragma Inline-

#Oracle #PLSQL #Pragma_inline #PRAGMA SERIALLY_REUSABLE