Oracle APEX ORDS Connection Pool Exhaustion: A Real Incident, Two Root Causes, One Permanent Fix

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, and neither one alone would have taken the pool down. Here is how I found both, fixed both, and the one that took three attempts to actually get right.

Stack: Oracle APEX 23.2 · Oracle Database 19c · Oracle REST Data Services (ORDS)

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. That is the defining trait of a connection pool exhaustion issue: it is invisible in development, and it is often invisible in the first weeks of production too.

Investigation traced the exhaustion to two independent root causes, stacking together. Neither one alone would have been catastrophic. Together, under real concurrency, they starved the pool.

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_COUNT

This is the classic N+1 pattern wearing a SQL disguise. It is a correlated subquery: for every single row the report returns, Oracle runs a fresh scan against USER_ACTIVITY_LOG filtered by that row’s LEAD_ID. A report showing 20 rows runs 20 extra scans. A report showing 500 rows, after a user removes a filter, runs 500.

The deeper cost isn’t just CPU. Every one of those per-row scans happens inside the same database session that ORDS checked out of the pool to render the page. A report that should return in 200ms was instead holding its connection open for the full duration of 500 sequential subquery executions. Multiply that by concurrent users pulling up the same report, and connections stop coming back to the pool fast enough to serve new requests.

The Fix: A Maintained Column Instead of a Live Subquery

The report didn’t need a live count. It needed a number that was already correct by the time the report ran. So the fix moved the counting from read time to write time.

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
;

The report column became a plain column read. No subquery, no per-row scan, no dependency on how many rows the report happens to return. The query plan for the whole report went from “N correlated subquery executions” to “one index-friendly read,” and the connection that ORDS checked out came back to the pool as fast as the base query itself, which is exactly how it should behave.

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. Individually, a COUNT(*) is cheap. 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.

That single fact reframes the whole fix. The call itself has to not happen. A server-side gate is the wrong layer entirely, the gate has to sit in JavaScript, before apex.server.process is ever invoked.

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");

The fix for the cache key itself was small once the cause was clear:

// Correct: apex.env exposes session-level values like APP_USER
var cacheKey = "nav_badge_" + 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 actual production-grade fix was to stop counting live at read time altogether and maintain the number instead.

A summary table holds one row per user:

-- One row per user. Updated incrementally, never recalculated from scratch.
CREATE TABLE NAV_BADGE_SUMMARY (
    NABA_USER_FK        NUMBER PRIMARY KEY,
    NABA_TERM_OVERDUE   NUMBER DEFAULT 0,
    NABA_TOTAL          NUMBER DEFAULT 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 exactly the delta involved, not recomputing the whole thing:

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_delta = 0 then
        return;
    end if;

    MERGE INTO NAV_BADGE_SUMMARY t
    USING ( SELECT pi_user_id AS user_id FROM dual ) s
       ON ( NABA_USER_FK = s.user_id )
     WHEN MATCHED THEN
          UPDATE SET
              NABA_TERM_OVERDUE = NABA_TERM_OVERDUE + CASE WHEN pi_bucket = 'TERM_OVERDUE' THEN pi_delta ELSE 0 END
            , NABA_TOTAL        = NABA_TOTAL + pi_delta
            , NABA_UPDATED      = SYSTIMESTAMP
     WHEN NOT MATCHED THEN
          INSERT ( NABA_USER_FK, NABA_TOTAL, NABA_UPDATED )
          VALUES ( pi_user_id, pi_delta, SYSTIMESTAMP )
    ;
exception
    when others then
        null; -- badge count is non-critical; never block the underlying save
end pr_adjust_nav_badge;

That MERGE is a single atomic write against one indexed row, not a table scan. It runs inside the same transaction as the actual lead or customer update, adding negligible overhead to a save the user was already performing.

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. That gap gets closed with a scheduled APEX Automation running once daily, reconciling the summary table against reality for exactly that edge case, not recalculating everything from scratch every time.

The Final Form: Zero JavaScript, Zero Cache, Zero Staleness

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;

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. No staleness window to reason about, because the number is just an indexed row read that happens as part of normal page rendering, the same way any other page item would populate.

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

1. N+1 in reports is easy to miss because it “works fine” in dev. Small tables and low concurrency hide correlated subqueries completely. They only announce themselves at production row counts and production user counts, which is exactly when you can least afford to be debugging them.
2. A server-side condition does not stop an AJAX call from checking out a pool connection. ORDS spends the connection before your WHEN clause is evaluated. If a call shouldn’t happen, the decision has to be made in JavaScript, before apex.server.process fires, not inside the process itself.
3. Caching introduces its own bugs. A shared or incorrectly scoped cache key can leak one user’s data to another in the same browser tab. $v() only reads page items. Session-level substitutions like APP_USER need apex.env.APP_USER instead.
4. The deepest fix isn’t “count faster,” it’s “stop counting live at all.” Moving from live aggregation to a maintained summary table, updated incrementally by triggers with a periodic reconciliation job as a safety net, is the actual production-grade pattern. Not a bigger cache. Not a longer TTL.
5. Non-critical side effects should fail silently and never block the real transaction. A WHEN OTHERS THEN NULL in the badge adjuster, isolated from the actual lead or customer save, is a deliberate design choice. The user’s real work should never fail because a badge count couldn’t update.

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.

If you’ve hit your own version of this, a report that quietly N+1’s itself, a badge or counter that turned into an unexpected AJAX flood, a cache key bug nobody caught until two users compared screens, I’d genuinely like to hear it. Drop your ORDS pool exhaustion war story in the comments below.