Pragma Serially_Reusable Practice:
According to Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1),
the pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
/******************************************/
/* With Pragma Serially_Reusable Package */
/******************************************/
set serveroutput on
create or replace package pragma_reusable is
pragma serially_reusable;
value number(1):=1;
end;
/
begin
pragma_reusable.value:=2;
dbms_output.put_line('Pragma within one call ==> '||pragma_reusable.value);
dbms_output.put_line('Pragma within one call ==> '||pragma_reusable.value);
end;
/
Pragma within one call ==> 2
Pragma within one call ==> 2
exec dbms_output.put_line('Pragma another call ==> '||pragma_reusable.value);
exec dbms_output.put_line('Pragma another call ==> '||pragma_reusable.value);
Pragma another call ==> 1
Pragma another call ==> 1
Reason:
SGA was freed up after execution
--------------------------------------------------------------------------------------------------------------
/****************************************/
/* Without Pragma Serially_Reusable Package */
/***************************************/
create or replace package without_pragma_reusable is
value number(1):=1;
end;
/
begin
without_pragma_reusable.value:=2;
dbms_output.put_line('Without Pragma within one call ==> '||without_pragma_reusable.value);
dbms_output.put_line('Without Pragma within one call ==> '||without_pragma_reusable.value);
end;
/
Without Pragma within one call ==> 2
Without Pragma within one call ==> 2
exec dbms_output.put_line('Without Pragma another call ==> '||without_pragma_reusable.value);
exec dbms_output.put_line('Without Pragma another call ==> '||without_pragma_reusable.value);
Without Pragma another call ==> 2
Without Pragma another call ==> 2
Reason:
SGA was NOT freed up after execution
[Reference]
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/seriallyreusable_pragma.htm
