Why an Oracle SQL Slowed After Upgrade—and How to Fix It

Problem Overview

After migrating a core system from AIX to Linux and upgrading Oracle from 11.2.0.3 to 11.2.0.4, a stop‑restart SQL that previously ran in a few milliseconds began taking several hundred seconds, reducing batch throughput.

During a 15‑minute snapshot at 08:15 the statement executed >10,000 times; later it dropped to ~40 executions per 15 min.

Root Cause Analysis

10053 trace showed that the optimizer failed to unnest the subquery, so the FILTER step was omitted. The subquery contains ROWID and DISTINCT, which violates CBO rules and prevents view merging. Consequently the outer query drives the inner subquery tens of thousands of times.

Oracle’s Cost‑Based Optimizer separates a statement into independent query blocks, estimates costs, and chooses join methods and order. When subquery unnesting fails the optimizer cannot generate the optimal plan.

Solution 1 – Materialize the WITH Subquery

Rewrite the inner subquery as a WITH clause, forcing it to be materialized once and reused.

The resulting plan is similar to the original nested‑loop plan, with an additional materialize step, restoring acceptable performance.

Solution 2 – Rewrite According to Oracle MOS Guidance

Oracle MOS document “Query Referencing ROWID of Subquery With Join Fails With ORA‑01445” states that a subquery may not select ROWID together with DISTINCT or GROUP BY. The fix is to replace the subquery with a straight join, remove DISTINCT, and alias tables.

After the join rewrite the plan returns to a nested‑loop join; one table performs a full‑table scan, but its size (< 100 k rows) keeps overall cost low.

Solution 3 – Simplify to a Single Table Scan

Eliminate the inner subquery entirely and scan the stop‑restart interface table once. The table contains fewer than 100 k rows, so an index or full‑table scan is fast.

Testing under peak load showed low latency; the business logic tolerates minor variations in restart order.

Additional Observations

Index rebuilds, statistics collection, hints, table rebuilds, and SQL Profile binding did not resolve the issue.

The case demonstrates the importance of understanding Oracle optimizer internals, especially subquery unnesting rules, and of rewriting SQL when optimizer hints are ineffective.