Why Fewer Permissions Slow Down Oracle SQL: A Deep Dive into EXISTS Subqueries

Case Overview

In October a performance issue was reported for an Oracle SQL query that checks permissions on 20,000 distribution units (DUs). When all 20,000 DUs have a permission the query finishes in ~2 s; when only 120 DUs have a permission it runs for more than 30 minutes.

SQL Structure

The statement is ~130 lines and contains five EXISTS sub‑queries in the WHERE clause, each joined with OR. All sub‑queries reference LINE.DU_ID and represent different permission sets.

Initial Execution‑Plan Inspection

Running the statement in SQL Developer shows only index scans with low cost values; the plan does not reveal why execution time varies.

Step‑by‑Step EXISTS Testing

Each EXISTS clause was commented out in turn:

Any single EXISTS runs fast.

The first and third together run fast.

The combination of the third, fourth and fifth EXISTS reproduces the slowdown.

SQL Monitor Findings

Oracle SQL Monitor captured runtime metrics. The five sub‑queries have different execution counts and actual row returns:

The first EXISTS executed 20,000 times – once for every DU.

The second executed f(2)=f(1)‑e(1) times, where f(n) is the execution count of the n‑th EXISTS and e(n) is the number of rows it returned.

The sum of rows returned by the first two sub‑queries equals the 120 permission rows mentioned in the ticket.

The third EXISTS returned 19,880 rows; together with the 120 rows from the first two it covers the full 20,000 DUs.

Oracle Execution Logic

For each DU the optimizer evaluates the EXISTS clauses sequentially and stops as soon as one evaluates to true. When few permission rows exist, most DUs must evaluate several EXISTS clauses, causing many more executions. When most DUs match the first clause, evaluation stops early and the total number of sub‑query executions drops dramatically.

Performance‑Impact Model

The relationship between the number of permission types N and the performance impact P can be expressed as:

Attempted Optimizations

Adding optimizer hints – no measurable improvement.

Rewriting the OR EXISTS logic as LEFT JOIN – performance degraded.

Creating specialized indexes on the permission tables – ineffective.

Proposed Solution

Only the first two EXISTS clauses contribute when permissions are sparse. The author suggested generating the SQL dynamically: include only those EXISTS sub‑queries whose permission set contains rows. The permission‑module service that builds the query can omit unnecessary clauses, reducing sub‑query executions from tens of thousands to a few hundred and restoring sub‑second response time.

Takeaways

SQL Monitor is essential for exposing execution counts and actual row returns that static plans hide. Pruning unused EXISTS clauses solves this specific case, but the approach does not automatically scale when many permission types coexist.