Boosting PL/SQL Performance: Data Types, Smart Caching, and Monitoring Strategies
Overview
In Part 1 of the blog “Boosting PL/SQL Performance: Bulk Processing and Memory Optimization Best Practices”, we explored context switching, bulk processing, and cursor optimization techniques. In this second part, we dive deeper into datatype efficiency, caching strategies, and proactive performance monitoring.
Common Bottlenecks and Solutions
The following are a few problems and their solutions that impact the performance of the code if not taken care of properly.
Datatype and Variable Efficiency
PL/SQL offers specialized datatypes that are significantly faster for computational work because they use native machine arithmetic.
- PLS_INTEGER vs. NUMBER: Use PLS_INTEGER or SIMPLE_INTEGER (which is NOT NULL and offers overflow checks) for counting and arithmetic. NUMBER is slower as it is handled by the Oracle library layer.
- BINARY_FLOAT/DOUBLE: Use these for scientific calculations.
Inefficient data Type:
DECLARE
v_counter NUMBER := 0; -- Slower
BEGIN
FOR i IN 1..1000000 LOOP
v_counter := v_counter + 1;
END LOOP;
END;
/
Optimal Data Types:
DECLARE
v_counter PLS_INTEGER := 0; -- Much faster for loop arithmetic
BEGIN
FOR i IN 1..1000000 LOOP
v_counter := v_counter + 1;
END LOOP;
END;
/
Mastering Functions and Logic
Functions called within SQL statements (SELECT lists, WHERE clauses) can destroy performance if not managed carefully. The database may execute the function once every time a single row is returned, potentially running the same logic repeatedly.
Repetitive calling of a function:
When a function runs repetitively for the same parameter, it performs the same database operation every time, which is unnecessary, because we have already called the function, performed the database operation, and received the result. If it is called again, we force the function to complete the same operation over again, wasting resources and memory which can cause significant delays in larger datasets.
Instead, it’s always better to cache the result in the Program memory so that if the function is called gain for same parameter, we can simply read the result from Cached memory and provide the result – producing a faster and more efficient operation.
Function Result Caching:
Oracle allows you to cache the results of deterministic functions based on their input parameters. The first call executes the function; subsequent calls with the same parameters reuse the cached result, avoiding re-computation or I/O.
CREATE OR REPLACE FUNCTION get_department_name (p_dept_id IN NUMBER)
RETURN VARCHAR2
-- Add the RESULT_CACHE clause
RESULT_CACHE RELIES_ON (departments) IS
v_name VARCHAR2(100);
BEGIN
SELECT department_name
INTO v_name
FROM departments
WHERE department_id = p_dept_id;
RETURN v_name;
END;
/
— Now, using this function in a large SELECT will only run the query once per unique p_dept_id.
Caching in the Code
When passing large data structures (like collections or LOBs) to subprograms, Oracle usually makes a copy for safety. The NOCOPY hint tells the compiler to pass by reference instead, eliminating a potentially huge memory and CPU overhead.
Usage of NOCOPY:
PROCEDURE process_huge_collection (
p_data_in IN OUT NOCOPY t_my_collection_type -- Avoids copying the entire collection
) IS
BEGIN
-- Logic to process data
NULL;
END;
/
Repeating the logic or subprogram inside the code:
In some scenarios, there is a piece of code that needs to be repeated inside programs, or a value that we can derive programmatically to be reused later. Instead of repeating the logic or calculation each time, we can cache the value in an associative array to be used repeatedly.
Caching in PL/SQL using collections:
Say you have 10,000 customer transactions to query – you won’t want to run 10,000 SQL queries to get the information you need. Instead, you can fetch all relevant keys into a PL/SQL Associative Array (Index-by table) at the start of your process.
Using the .EXISTS function on a collection is a memory-speed operation, whereas a SELECT statement is a disk/buffer-cache operation requiring a context switch. The .EXISTS call happens entirely within the PL/SQL engine’s private memory (PGA), making it nearly instantaneous compared to a SQL execution.
In this example, we need to process a batch of transactions, but only for “Active” customers. Instead of querying the CUSTOMERS table for every transaction, we cache the active IDs first.
DECLARE
-- 1. Define an Associative Array (Collection) to act as our local cache
TYPE t_active_cache IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
v_cache t_active_cache;
v_cust_id NUMBER;
BEGIN
-- 2. "Fetch Once": Populate the cache with active customer IDs
-- This is a single bulk operation (minimal context switching)
FOR rec IN (SELECT customer_id FROM customers WHERE status = 'ACTIVE') LOOP
v_cache(rec.customer_id) := TRUE;
END LOOP;
-- 3. "Use Many": Process transactions without going back to the SQL Engine
FOR trans IN (SELECT customer_id, amount FROM daily_transactions) LOOP
-- Use the .EXISTS function to check the local memory cache
-- This is significantly faster than querying the database repeatedly
IF v_cache.EXISTS(trans.customer_id) THEN
DBMS_OUTPUT.PUT_LINE('Processing transaction for: ' || trans.customer_id);
-- Procedural logic here...
END IF;
END LOOP;
END;
/
Proactive Performance Monitoring
You cannot fix what you don’t measure, which is why it’s important to build monitoring and reporting into your overall IT strategy. With this blog, and the previous part 1, I hope you have some ideas of bottlenecks and inefficiences to look out for. Luckily, Oracle also provides some tools to pinpoint exact performance bottlenecks.
- DBMS_PROFILER:This package identifies which specific lines of your PL/SQL code take the longest to run.
- EXPLAIN PLAN and SQL Trace (TKPROF):Always review the execution plan of complex SQL statements to ensure indexes are used correctly and efficient access paths are chosen.
- AWR Reports:For system-wide analysis, use Automatic Workload Repository (AWR) reports to identify top resource consumers in the database.
By applying these principles—minimizing context switches, using efficient datatypes, leveraging caching, and profiling your code—you can write robust, high-performance PL/SQL applications.
