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