In Oracle 19.18, a seemingly well-optimized query involving multiple UNION and UNION ALL operations was causing a puzzling performance issue. Initially, partial data fetches of the first 50, 200, 1000, or even 20,000 records were near-instantaneous when run from tools like SQL Developer and PowerBI. However, when attempting to fetch the entire result set, the query would stall indefinitely, leaving users without a complete set of records.

After weeks of thorough troubleshooting and tracing, the root cause turned out to be a subtle but impactful one: the query struggled with fetching the final batch of records. A key detail was that the customer’s query included ROWNUM=1 without additional filters, adding complexity to the Oracle optimizer’s decision-making process.

Stepping In to Find a Solution

When I took up the challenge, I began testing various configurations and ideas, especially focusing on optimizer hints to understand the root behavior. Finally, using DBeaver, I stumbled upon an invaluable insight: experimenting with OPTIMIZER_MODE=ALL_ROWS appeared to positively influence the fetch operation. This hinted that the optimizer might need additional guidance to process the final records efficiently.

The Breakthrough Solution: Using Hints for Complete Fetching

To resolve the issue, we used an extra hint that made all the difference. We added the ALL_ROWS hint, along with a couple of known hints to guide the optimizer’s behavior explicitly. The hint configuration looked like this:

/*+ ALL_ROWS opt_param('_optimizer_cbqt_or_expansion','ON') cursor_sharing_exact */ DISTINCT

Adding this combination ensured that the query could complete the fetch operation within seconds, achieving the full result set and resolving the stalling issue entirely.

Key Takeaways

This solution was a breakthrough in managing Oracle query performance where complex joins, UNION operations, and filtering specifics (such as ROWNUM filters) are involved. It underscores the power of advanced optimizer hints in Oracle, which can nudge the execution plan to handle complex requirements efficiently.

Sharing this experience, I hope it sheds light on a critical but often overlooked aspect of query tuning: knowing when and how to use optimizer hints for effective data fetching, especially in challenging scenarios with high volumes of data.