By A real production incident on a CRM platform I maintain: the Oracle ORDS connection pool ran dry under normal business-hours load. Two unrelated anti-patterns were stacking on top of each other. Here is how I found both, fixed both, and the one that took three attempts to actually get right.
Revision scope: the incident and troubleshooting sequence below are my original account. The corrected SQL/PLSQL examples and implementation notes are documentation-reviewed, not newly executed against that CRM. No execution plan, benchmark or concurrency-test result is supplied here.
The Incident
The symptom showed up first as slow page loads, then as outright timeouts, then as ORDS returning connection errors to users trying to open the application at all. Nothing had changed in a deployment that week. The only thing that had changed was time: more leads, more customers, more concurrent users than the app had carried a few months earlier.
That detail mattered more than it seemed to at first. Nothing was “broken” in the sense of throwing an exception during testing. The application had been quietly accumulating load-dependent problems that only became visible once real usage caught up to them. Load-dependent problems can be hard to reproduce with small development data sets. Pool exhaustion can also have other causes, so this incident is a case study, not a diagnosis of every ORDS timeout.
jdbc.MaxLimit). Once every connection in the pool is checked out and none are being returned fast enough, new requests queue up and then start failing outright. Pool sizing must be assessed alongside database capacity, request concurrency and connection hold time. Increasing a limit may be appropriate after measurement, but it does not remove an expensive query or unnecessary request pattern.
Investigation traced the exhaustion to two independent root causes, stacking together. Together, under real concurrency, they put pressure on the pool; this article does not provide an isolated load test attributing a precise share to either cause.
Root Cause #1: The Correlated Subquery Hiding in a Report Column
A lead report had a “calls made” column. It looked completely ordinary in the SQL source of the Interactive Report:
, ( SELECT COUNT(*)
FROM USER_ACTIVITY_LOG
WHERE USAL_EVENT_TYPE = 'LEAD_CALL_CUSTOMER'
AND USAL_REF_ID = LEAD_ID
AND USAL_ACCESS_TS BETWEEN TO_DATE(:P500_START_DATE, 'DD.MM.YYYY')
AND TO_DATE(:P500_END_DATE, 'DD.MM.YYYY') + INTERVAL '23:59:59' HOUR TO SECOND
) AS LEAD_CALL_COUNTThis is a correlated scalar subquery and a candidate for repeated work. Its SQL shape alone does not prove one full scan per report row: Oracle may transform, cache or access it through indexes. Inspect the executed plan, starts, actual rows and buffer work for representative binds before describing its physical execution. Oracle documents these possibilities under query transformations.
The deeper cost is connection hold time as well as database work. A slower report request occupies a pooled database session longer. Under concurrent requests, that can reduce capacity for new work. Use DBMS_XPLAN.DISPLAY_CURSOR with appropriate privileges and collected runtime statistics to compare the actual alternatives; this article does not establish a 200 ms baseline or 500 sequential executions.
The Fix: A Maintained Column Instead of a Live Subquery
The maintained-column approach moves counting from read time to write time, but it changes the meaning of the original expression: the query above counts calls inside the selected date range, while a simple incremented column is a lifetime count. Use that column only if lifetime count is the agreed requirement, and label it accordingly. It cannot replace a date-filtered count without changing the result.
A plain column went on the LEAD table:
ALTER TABLE LEAD ADD LEAD_CALL_COUNT NUMBER DEFAULT 0;The application already had a single procedure that fired every time a user clicked “call lead,” used for activity logging. That procedure was the natural place to increment the count atomically, at the moment the event actually happens, instead of recomputing it from scratch every time someone opens a report:
UPDATE LEAD
SET LEAD_CALL_COUNT = NVL(LEAD_CALL_COUNT, 0) + 1
WHERE LEAD_ID = v_lead_id
;For the lifetime-count requirement, the report can read the maintained column without aggregating the log in that expression. This does not guarantee an index-only or constant-cost plan. Backfill existing calls before use, update the counter in the same transaction as the logged event, and handle retries, deleted/corrected events and failed saves so a click is not counted twice or counted when its transaction fails.
When the report must keep its selected-date meaning
This minimal query shows grouped, date-filtered aggregation joined to the lead list. It is an alternative to measure, not a promised faster physical plan. Keep the original report’s access controls and other columns/filters. Require valid non-null date items with format DD.MM.YYYY, validate start ≤ end, and submit both date items when refreshing the report through AJAX. Page-submit validations alone do not run during an AJAX region refresh: enforce date validity and ordering on that refresh path too, returning a clear error before running this query.
WITH calls_in_period AS (
SELECT USAL_REF_ID, COUNT(*) AS call_count
FROM USER_ACTIVITY_LOG
WHERE USAL_EVENT_TYPE = 'LEAD_CALL_CUSTOMER'
AND USAL_ACCESS_TS >= TO_DATE(:P500_START_DATE, 'FXDD.MM.YYYY')
AND USAL_ACCESS_TS < TO_DATE(:P500_END_DATE, 'FXDD.MM.YYYY') + 1
GROUP BY USAL_REF_ID
)
SELECT l.LEAD_ID,
NVL(c.call_count, 0) AS LEAD_CALL_COUNT
FROM LEAD l
LEFT JOIN calls_in_period c ON c.USAL_REF_ID = l.LEAD_ID
-- Retain the original report's authorized-row and business filters.The half-open end boundary includes fractional seconds throughout the selected last day. This example assumes DATE or unzoned TIMESTAMP data in the intended business time basis; timezone-aware data needs explicit business-zone boundaries. For larger date-range workloads, a per-lead/per-day summary is another option, with backfill and reconciliation. A lifetime counter is not an interchangeable substitute.
Root Cause #2: Three “Harmless” AJAX Calls on Every Page Load
The second root cause was less obvious because no single piece of it looked expensive. A navigation badge showed overdue and missing counts next to a “Dashboard” menu item. It was computed by three separate calls firing unconditionally on every page load, one for each user role:
apex.server.process("GET_TERMINATOR_BADGE_COUNT", {}, { success: ... });
apex.server.process("GET_SALES_BADGE_COUNT", {}, { success: ... });
apex.server.process("GET_TECHNICAL_BADGE_COUNT", {}, { success: ... });
Each of those is a live SELECT COUNT(*) against LEAD, CUSTOMER, or DEFECTS. The cost of each COUNT(*) depends on its predicates, data and access path. The problem is the multiplier: three separate ORDS round trips, on every page, for every user, all day. Each round trip checks out a connection from the pool. A user clicking through six pages in a normal workflow just generated eighteen extra connection checkouts that had nothing to do with the page content they actually came to see.
Why a Server-Side Condition Didn’t Save the Pool
The first instinct is usually to add a server-side condition on the AJAX process, something like “only run this if the current page is not the dashboard itself.” That felt like it should cut the unnecessary calls down. It didn’t move the needle at all.
WHEN condition says “skip this,” ORDS has already spent a connection to find that out.
That single fact reframes the whole fix. The call itself has to not happen. To remove this round trip, avoid issuing the request in the first place. Keep server-side authorization regardless: a client-side role or page check is a performance choice, not a security boundary.
First Fix Attempt: Merge and Cache, and the Bug It Introduced
The first pass merged the three separate calls into one role-aware call, cutting three round trips down to one. On top of that, a client-side cache using sessionStorage with a five-minute TTL meant most page loads made zero server calls at all, only refreshing the count once the cache had gone stale.
That cut the connection load dramatically, and it also introduced a bug that took a support ticket to surface. The cache key was built using $v("APP_USER"):
// Wrong: $v() only reads page items, not session-level substitutions
var cacheKey = "nav_badge_" + $v("APP_USER");$v() in Oracle APEX only reads the value of a page item on the current page. APP_USER is a session-level built-in substitution, not a page item, so $v("APP_USER") does not reliably retrieve that identity and can produce an empty or missing value. The exact missing-item result is version/context-dependent; the important bug is that successive users can share an unintended cache key. If User A logged out and User B logged in on the same tab within the five-minute TTL, User B saw User A’s badge counts until the cache expired.
The documented apex.env values provide a better identity basis. Scope a retained cache to application and authenticated session as well as user, clear it on relevant changes/logout, and never treat browser storage as an authorization check:
// Correct: apex.env exposes session-level values like APP_USER
var cacheKey = "nav_badge_" + JSON.stringify([
apex.env.APP_ID, apex.env.APP_SESSION, apex.env.APP_USER
]);
This is worth internalizing on its own, separate from the pool exhaustion story. $v() and apex.env are not interchangeable, and a mistake here doesn’t throw an error. It just quietly leaks one user’s data into another user’s session. That class of bug is exactly the kind that a good testing environment with one logged-in user will never catch, and exactly the kind that a centralized logging habit helps you spot in production instead of guessing. I go deeper on catching exactly this category of silent failure in A Centralized Error Logging Framework for Oracle APEX.
The Real Fix: Killing Live Counting Entirely
Merging calls and caching them bought breathing room, but it was still a live count with a delay bolted on. The approach I adopted was to maintain a summary and read it during page rendering. It trades repeated read-time aggregation for write-time maintenance, contention and reconciliation responsibilities.
A summary table holds one row per user:
-- One row per user. Backfill initially and reconcile against source data.
CREATE TABLE NAV_BADGE_SUMMARY (
NABA_USER_FK NUMBER PRIMARY KEY,
NABA_TERM_OVERDUE NUMBER DEFAULT 0 NOT NULL CHECK (NABA_TERM_OVERDUE >= 0),
NABA_TOTAL NUMBER DEFAULT 0 NOT NULL CHECK (NABA_TOTAL >= 0),
NABA_UPDATED TIMESTAMP
);
Row-level triggers on LEAD, CUSTOMER, and DEFECTS call a single adjuster procedure whenever a status or reminder date actually changes, moving the count by the intended delta. The original trigger definitions and full set of bucket rules are not included here. This corrected package-body excerpt illustrates only TERM_OVERDUE; add every supported bucket explicitly before using it in the wider application:
PROCEDURE pr_adjust_nav_badge(
pi_user_id IN NUMBER,
pi_bucket IN VARCHAR2,
pi_delta IN NUMBER
) IS
BEGIN
IF pi_user_id IS NULL OR pi_bucket IS NULL
OR pi_bucket <> 'TERM_OVERDUE'
OR pi_delta IS NULL OR pi_delta <> TRUNC(pi_delta) THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid badge adjustment');
END IF;
IF pi_delta = 0 THEN RETURN; END IF;
MERGE INTO NAV_BADGE_SUMMARY t
USING (SELECT pi_user_id AS user_id FROM dual) s
ON (t.NABA_USER_FK = s.user_id)
WHEN MATCHED THEN UPDATE SET
t.NABA_TERM_OVERDUE = t.NABA_TERM_OVERDUE + pi_delta,
t.NABA_TOTAL = t.NABA_TOTAL + pi_delta,
t.NABA_UPDATED = SYSTIMESTAMP
WHEN NOT MATCHED THEN INSERT
(NABA_USER_FK, NABA_TERM_OVERDUE, NABA_TOTAL, NABA_UPDATED)
VALUES
(pi_user_id, pi_delta, pi_delta, SYSTIMESTAMP);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- A concurrent first insert may have won the primary-key race.
-- Assumes the table has only the primary-key uniqueness shown above.
UPDATE NAV_BADGE_SUMMARY
SET NABA_TERM_OVERDUE = NABA_TERM_OVERDUE + pi_delta,
NABA_TOTAL = NABA_TOTAL + pi_delta,
NABA_UPDATED = SYSTIMESTAMP
WHERE NABA_USER_FK = pi_user_id;
IF SQL%ROWCOUNT <> 1 THEN
RAISE;
END IF;
-- Other failures propagate; there is no blanket silent handler.
END pr_adjust_nav_badge;The insert branch must initialize the bucket count as well as the total. A MERGE is not a guarantee that two concurrent first inserts cannot conflict; under ordinary READ COMMITTED isolation, the specific duplicate-key handler above retries the delta as an update. Other isolation levels and additional uniqueness rules need their own tested conflict policy. Existing-row updates can wait on row locks, so overhead is workload-dependent. Keep this adjuster in the caller’s transaction: it contains no COMMIT. Backfill before accepting decrements; the non-negative constraints expose drift instead of masking it by clamping counts.
One case doesn’t fit the trigger model at all: a lead going overdue purely because midnight passed, with nobody touching the row. No write happens, so no trigger fires. My design used a daily APEX Automation for that case. Daily refresh creates a freshness window of up to the interval plus job delay, and a failed job can extend it. Reconciliation must also detect and repair all drift from missed updates or earlier failures, not just the midnight edge case.
Failure policy: this sample lets unexpected errors propagate, so a maintenance failure can fail the triggering DML. If badge availability must never block the business save, implement an explicit best-effort path with observable failure logging or a durable repair queue and tested reconciliation. A blanket WHEN OTHERS THEN NULL silently loses evidence and may leave stale counts. Such a recovery path is not supplied by this excerpt.
The Final Form: No Extra Badge Request During Page Render
With the summary table maintaining itself, the AJAX layer became unnecessary entirely. The badge count moved to a native Application Item, populated by a lightweight Application Process running at the “Before Header” point on every page:
-- Application Process, point: On Load: Before Header
BEGIN
SELECT NABA_TOTAL
INTO :NAV_BADGE_COUNT
FROM NAV_BADGE_SUMMARY
WHERE NABA_USER_FK = :APP_USER_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:NAV_BADGE_COUNT := 0;
END;
APP_USER_ID here is an application-defined authenticated numeric user identifier, not an APEX built-in automatically populated for you. Populate it server-side and protect it from client changes. Configure NAV_BADGE_COUNT as an Application Item. That value gets substituted directly into the navigation menu’s List Entry Label using &NAV_BADGE_COUNT.. No apex.server.process call. No client-side cache to invalidate or get the key wrong on. It reads the currently committed summary during page rendering, but the summary may lag time-based changes or failed maintenance. An already-open page also keeps its old displayed badge until refreshed. Choose and document an acceptable freshness target.
Zero extra ORDS round trips per page load, down from three. The Smart Task Tracker piece covers a related idea, surfacing information without forcing extra round trips just to display a summary, if this pattern of “compute once, read cheaply everywhere” is useful to you elsewhere in an app.
Five Lessons for Your Own ORDS Pool
- Measure the actual workload. Correlated syntax is a reason to inspect runtime plans, not proof of repeated full scans. Compare realistic binds, data volumes and concurrency.
- Remove unnecessary requests at their source. A database-evaluated condition cannot undo a request already issued. Keep server-side authorization even when suppressing client calls.
- Scope caches deliberately. Application, session and user identity matter; logout and state changes must invalidate relevant entries.
- Preserve the meaning of the count. Lifetime counters, selected-period counts and time-derived overdue totals have different maintenance requirements.
- Make drift observable. Initialize summaries, cover all state transitions, handle concurrency and monitor reconciliation. Choose explicitly whether maintenance failure blocks a save or enters a tested repair path.
For the mechanics of how ORDS pools are actually sized and tuned, in particular jdbc.MinLimit and jdbc.MaxLimit, Oracle’s own configuration reference is worth reading directly: Miscellaneous Configuration Options of Oracle REST Data Services. Raising those limits can buy time. It will not fix a query or a call pattern that shouldn’t be running in the first place.
The durable lesson from this incident is to shorten unnecessary database work and remove avoidable requests without changing what the displayed number means. Pool configuration, query plans and summary correctness all need to be checked in the application where the pattern is used.
