Boosting PL/SQL Performance: Bulk Processing and Memory Optimization Best Practices

PL/SQL is a powerful language tightly integrated with Oracle Database. It is Oracle’s procedural extension for SQL, allowing developers to build complex database applications. Developing efficient Oracle applications requires more than just correct logic; it demands an understanding of how the PL/SQL engine interacts with the database to deliver optimal performance.  However, inefficient code can quickly transform performance into a bottleneck, slow down applications, and consuming excessive system resources. 

The key to scalable, high-performance PL/SQL remains to minimize overhead, optimize data flow, and leverage built-in features for speed.

This blog explores common bottlenecks and provides practical examples to help you optimize your code.

Definition/Explanation of SQL or PLSQL Commands used in the document.

Before reading this article, it will be useful to understand these common commands we will reference:

Common Bottlenecks and Solutions

The following are a few common problems that can impact the performance of the code if not accounted for. I’ll also outline a few solutions to help solve for them: 

1. Excessive Context Switching
   Every PL/SQL block consists of two execution components: the PL/SQL part and SQL part. Both run on different engines; PL/SQL Engine (which handles procedural logic like IF statements and loops) and SQL Engine (which handles data retrieval and DML). Every time a PL/SQL block contains a SQL statement, the PL/SQL engine pauses and passes the query to the SQL engine. The SQL engine executes it and passes the results back to the PL/SQL engine. This transition is known as a Context Switch.
   
   Think of it like a chef (PL/SQL) and a delivery driver (SQL). If the chef asks for one ingredient at a time, the driver has to drive to the store and back for every single item. This constant traveling (context switching) creates massive CPU overhead and latency, especially inside loops.
   
   The single most significant bottleneck in PL/SQL is the “context switch”. Each switch incurs overhead. The goal is to perform as much work as possible in a single engine before switching back.
   
   The below example explains Row by Row processing and how to avoid it using Bulk Operations.
   Example: Row-by-Row Processing
   When you use a cursor loop to fetch and process one row at a time, the system incurs significant overhead for each FETCH operation.

   DECLARE    v_employee_id employees.employee_id%TYPE;
       CURSOR emp_cursor IS SELECT employee_id FROM employees WHERE status = ‘ACTIVE’;
   BEGIN
       OPEN emp_cursor;
       LOOP
           FETCH emp_cursor INTO v_employee_id;
           EXIT WHEN emp_cursor%NOTFOUND;
           — This UPDATE runs once for *each* row, causing a context switch every time
           UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_employee_id;
       END LOOP;
       CLOSE emp_cursor;
   END;

   
   Example: Bulk Operations
   The primary solution is to process data in bulk using native SQL whenever possible. If you must use PL/SQL, utilize BULK COLLECT to fetch many rows at once into a collection, and FORALL to perform DML operations on those collections. This drastically reduces the number of context switches. 

   DECLARE   TYPE t_employee_id_tab IS TABLE OF employees.employee_id%TYPE;
       v_employee_ids t_employee_id_tab;
   BEGIN
       — Use BULK COLLECT to fetch all IDs at once (one context switch)
       SELECT employee_id
       BULK COLLECT INTO v_employee_ids
       FROM employees
       WHERE status = ‘ACTIVE’;
       — Use FORALL to perform the UPDATE for all collected IDs efficiently (one context switch)
       FORALL i IN v_employee_ids.FIRST..v_employee_ids.LAST
           UPDATE employees  SET salary = salary * 1.1  WHERE employee_id = v_employee_ids(i);
       COMMIT;
   END;

2. Optimizing Data Retrieval & Cursor Management
   Efficient data handling starts with intelligent SQL queries and mindful cursor usage. Many times, when we write the SELECT queries in PLSQL, we often query many columns which we do not use throughout the program. These unused data unnecessarily occupy extra memory, which can be avoided by writing optimal SELECT Queries.
   
   Another mistake we often see while writing select queries is fetching a bigger data set from the query, and then using IF-ELSE-END IF blocks to reduce the data which no longer applies for our logic. These can be avoided by smartly filtering the data from SELECT query itself.
   This will optimize memory usage as well as less work for PLSQL Engine and produce better performance.
   • Avoid SELECT *: Only retrieve the columns you actually need. This reduces memory usage, network traffic, and I/O.
   • Filter Early: Use WHERE clauses to restrict the dataset in the database layer before bringing it into PL/SQL memory.

   Example: Excessive Memory Uses

   DECLARE  CURSOR emp_cur IS SELECT * FROM employees; — Retrieves all columns

     v_emp_rec emp_cur%ROWTYPE;
   BEGIN
     OPEN emp_cur;
     LOOP
       FETCH emp_cur INTO v_emp_rec;
       EXIT WHEN emp_cur%NOTFOUND;
       — Lots of memory used for unused data
     END LOOP;
     CLOSE emp_cur;
   END;

   
   Example: Optimized memory Uses

   DECLARE  — Only select necessary columns, use FOR loop for simpler implicit cursor management

     CURSOR emp_cur IS SELECT employee_id, salary FROM employees WHERE department_id = 10;
   BEGIN
     FOR rec IN emp_cur LOOP
       — Only relevant data in memory
       DBMS_OUTPUT.PUT_LINE(rec.employee_id || ‘ has salary ‘ || rec.salary);
     END LOOP;
   END;

In this part, we discussed key concepts which could help in fixing the performances bottlenecks in our code when kept in mind while writing the PL/SQL codes.

Stay tuned for Part 2 where we explore Datatypes efficiencies, advanced caching and profiling techniques.

About the Author

Vikas is an Oracle apps technical consultant with more than 14 years of experience in IT with expertise in SCM modules. Vikas is very proficient with PLSQL and SQL along with extensive hands-on experience on Oracle Forms, Reports, Shell Scripting, Workflows, Jasper Reports etc.