How I Slashed an Oracle SQL Query from 11 s to 1.8 s Under a Tight Release Freeze

Background

In early September a major release was scheduled, and two performance‑critical SQL statements (defect IDs D6899590 and D6679058) caused the system to exceed the 3‑second target, threatening the release freeze.

Initial Analysis

Reviewing the execution plan revealed a long plan despite a simple query, caused by OR conditions expanding (CONCATENATION) and a view BPAV_V being accessed twice.

Replacing the view with the underlying table BPAT_T reduced runtime from 11 s to 7 s.

Further Bottlenecks

Key costly operators were MERGE JOIN OUTER and SORT JOIN. The OR condition was rewritten as UNION ALL, but the optimizer still chose a sub‑optimal driver table.

Adding the hint USE_NL(P, PAT) forced the small sub‑query PAT (4 rows) to be the driver, bringing execution time down to ~3 s.

CAST and Type Conversion Issues

Numerous CAST operations (e.g., casting numeric IDs to NVARCHAR2, dates to TIMESTAMP) added overhead. Removing unnecessary casts on fields CD_C, STATE, HANDLER saved several seconds.

Random Function Overhead

The expression CAST('JCS' || DBMS_RANDOM.RANDOM() AS NVARCHAR2(64)) BS_ID_ generated a random string for each of ~1 million rows, severely impacting performance. Replacing it with the existing unique column TID_C eliminated the cost, dropping the runtime to 1.8 s.

Final Result and Lessons

Through view substitution, driver‑table hinting, and eliminating costly casts and random functions, the query met the sub‑3‑second requirement just before the release freeze. The case highlights the importance of driver table selection, minimizing Oracle work, and careful type handling.