🔄 Understanding PRAGMA SERIALLY_REUSABLE in Oracle PL/SQL: A Complete Guide
When working with Oracle PL/SQL packages, memory management and session state become crucial, especially in high-concurrency environments like web-based applications, batch processing systems, or shared server configurations. One powerful yet often overlooked feature for memory optimization is the PRAGMA SERIALLY_REUSABLE directive. This blog aims to break down this concept from scratch, providing both simple explanations and practical use cases for developers ranging from 5 to 15+ years of experience.
📘 What is PRAGMA SERIALLY_REUSABLE?
PRAGMA SERIALLY_REUSABLE is a PL/SQL compiler directive that tells Oracle not to retain global package variables between calls within the same session. In short:
🔹 Without pragma: Global variables persist for the entire session
🔹 With pragma: Global variables reset after each call, saving PGA memory
It is declared in both the package specification and package body, like so:
PRAGMA SERIALLY_REUSABLE;
🧠 Why Was This Introduced?
Oracle introduced this pragma to solve the problem of excessive PGA memory usage caused by persistent global states in packages, especially when the packages are used briefly and don’t need to retain data across session calls.
In shared server environments (like Oracle’s MTS or web apps), session memory is stored in the User Global Area (UGA), and memory cleanup becomes crucial. Without the pragma, unused variables linger in memory, wasting resources.
📦 A Simple Package WITHOUT PRAGMA
CREATE OR REPLACE PACKAGE DemoPkg IS
PROCEDURE set_val(p_val NUMBER);
PROCEDURE get_val;
END DemoPkg;
/
CREATE OR REPLACE PACKAGE BODY DemoPkg IS
g_val NUMBER;
PROCEDURE set_val(p_val NUMBER) IS
BEGIN
g_val := p_val;
DBMS_OUTPUT.PUT_LINE('setting Value: ' || g_val);
END;
PROCEDURE get_val IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Value: ' || g_val);
END;
END DemoPkg;
/
🔎 Test Execution:
BEGIN
DemoPkg.set_val(123);
END;
/
BEGIN
DemoPkg.get_val;
END;
/
- Here, the variable g_val retains its value (99) between calls because it is globally persistent.
📦 Same Package WITH PRAGMA SERIALLY_REUSABLE
CREATE OR REPLACE PACKAGE DemoPkg IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE set_val(p_val NUMBER);
PROCEDURE get_val;
END DemoPkg;
/
CREATE OR REPLACE PACKAGE BODY DemoPkg IS
PRAGMA SERIALLY_REUSABLE;
g_val NUMBER;
PROCEDURE set_val(p_val NUMBER) IS
BEGIN
g_val := p_val;
DBMS_OUTPUT.PUT_LINE('setting Value: ' || g_val);
END;
PROCEDURE get_val IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Value: ' || g_val);
END;
END DemoPkg;
/
🔎 Test Execution:
BEGIN
DemoPkg.set_val(123);
END;
/
BEGIN
DemoPkg.get_val;
END;
/
- Because of the pragma, g_val is reset after set_val ends. So get_val sees no value assigned.
🔍 What’s Happening Behind the Scenes?
- Oracle instantiates the package in UGA (User Global Area) rather than PGA
- When the call finishes, the package memory is discarded
- On the next call, the package is reloaded and reinitialized
- Global variables don’t retain their values across calls
- Improves memory efficiency at the cost of state persistence
✅ Where Should You Use It?
Use PRAGMA SERIALLY_REUSABLE when:
- Your package has global variables used temporarily
- You're building stateless modules like APIs, batch processes
- You're operating in shared server or web app environments
- You want to reduce PGA memory usage
❌ Where Should You Avoid It?
Avoid it when:
- You need global state persistence across procedure calls
- Your logic relies on maintaining session data
- You're in dedicated server environments with abundant memory
🧑💼 Real-Time Example
Let’s say you have a package that performs temporary logging or counters in batch jobs. You don’t need to retain those counters after the procedure ends. Applying PRAGMA SERIALLY_REUSABLE here ensures those temporary values don’t bloat session memory—especially if many sessions are calling it simultaneously.
🔑 Keywords Summary
Keyword Meaning
- PRAGMA SERIALLY_REUSABLE Oracle directive to discard global state after each call
- Global Variable Declared in package body, persists unless pragma is applied
- PGA Private memory for session-level data
- UGA Shared memory when using shared server
- Stateless Session A session where variables do not retain state
🏁 Final Thoughts
Using PRAGMA SERIALLY_REUSABLE is a smart choice when stateless, memory-efficient design is more important than maintaining global variables. Understanding how memory works behind the scenes (PGA vs UGA) and being deliberate about memory optimization is what separates senior developers from others.
Whether you’re building a large-scale enterprise app, a RESTful API, or a batch processor, this pragma can help you write scalable and efficient PL/SQL.
Summary important
- In Oracle PL/SQL, PRAGMA SERIALLY_REUSABLE tells Oracle to discard package variables after each PL/SQL block execution.
- This means values like g_val won’t persist between separate BEGIN...END; blocks, even within the same session.
- Regular packages (without the pragma) retain state throughout the session unless explicitly reset.
- This pragma is useful in systems with many users to save memory by not keeping package state.
- To observe this behavior, run set_val and get_val in separate blocks — you’ll see the value is lost.
When a PL/SQL package is marked with PRAGMA SERIALLY_REUSABLE, it does not retain the values of global variables between different execution contexts. This pragma is used to conserve memory, especially PGA memory (Program Global Area), by releasing the package state after each call.
✅ Case 1 – Same Anonymous Block:
BEGIN
DemoPackage.setVal(99);
DemoPackage.getVal; -- Output: 99
END;
Since both procedures are invoked within the same execution block, the package state remains active, and the global variable retains its value.
❌ Case 2 – Different Anonymous Blocks:
BEGIN
DemoPackage.setVal(99);
END;
BEGIN
DemoPackage.getVal; -- Output: NULL
END;
In this scenario, setVal sets the value, but after the block ends, Oracle discards the package state.
So when getVal is called in the next block, it's a fresh instantiation, and the global variable is NULL.
Summary Important
When we use PRAGMA SERIALLY_REUSABLE in a PL/SQL package, it ensures that package-level variables do not retain state across different execution calls. If you call setVal and getVal inside the same anonymous block, the session memory (PGA) is still active, so you’ll get the expected value. However, if these are called in two different anonymous blocks, the value is not retained, and getVal returns NULL, because Oracle resets the package state after each block ends. This behavior helps optimize memory in environments with many concurrent sessions. It is useful where session-persistent state is not necessary.
Top comments (0)