Turning a 12‑Hour Oracle Query into Minutes: Proven SQL Tuning Techniques

The DBAplus community session presented a step‑by‑step analysis of an extremely slow Oracle SQL statement that originally required more than 12 hours and returned millions of rows.

Understanding the Execution Plan

First, the plan is examined to locate the entry point (ID = 6) and to identify problematic operations such as nested loops driven by inaccurate cardinality estimates and costly Filter nodes with multiple child operations. The analysis highlights how low cardinality values can mislead the optimizer into choosing inefficient execution paths.

Statistics and Cardinality

Accurate statistics are essential for correct cardinality estimation. The article demonstrates collecting extended statistics for expressions like substr(other_class,1,3) using DBMS_STATS.GATHER_TABLE_STATS with appropriate method_opt and estimate_percent parameters. After gathering extended stats, the plan switches from nested loops to a HASH JOIN, reducing runtime from hours to minutes.

Logical Rewrite to Eliminate Filters

Filters often arise when subqueries cannot be unnested. By rewriting OR‑based subqueries into equivalent equality joins (e.g., using substr(...)=...), the optimizer can choose HASH or MERGE joins. Sample rewrites show execution time dropping from 2 hours to 8 minutes, and finally to under 3 minutes after further refinement.

Diagnostic Tools: 10053 and SQLT

The 10053 trace reveals why the optimizer fails to unnest certain subqueries (e.g., due to ROWID references). SQLT’s XPLORE feature automates parameter toggling and plan generation without executing the query, helping pinpoint hidden optimizer parameters such as _optimizer_squ_bottomup that affect plan selection.

SQL Tuning RoadMap and Best Practices

Identify problematic SQL and define optimization goals.

Inspect the execution plan using EXPLAIN, AUTOTRACE, DBMS_XPLAN, 10046, 10053, etc.

Verify and refresh statistics with DBMS_STATS.

Ensure efficient access structures (indexes, partitions).

Review optimizer parameters (optimizer_mode, optimizer_index_cost_adj, etc.).li> Consider Oracle‑specific features and known bugs. Rewrite SQL for clearer semantics and better join choices.

Following this checklist helps prevent performance regressions and enables proactive SQL lifecycle management.

Selected Q&A Highlights

When are nested loops, hash joins, and merge joins used? Nested loops suit small driver rowsets with indexed inner tables; hash joins excel with large equi‑joins; merge joins handle sorted large datasets.

Should statistics be gathered with ANALYZE or DBMS_STATS? DBMS_STATS is the recommended method; ANALYZE lacks many modern capabilities.

Why does a query run fast once then slow later? Causes include cardinality feedback, adaptive cursor sharing, or optimizer bugs; disabling problematic features or re‑gathering stats can help.

Overall, the session demonstrates that thorough plan analysis, accurate statistics, logical query rewriting, and appropriate diagnostic tools can transform a multi‑hour Oracle query into a matter of minutes.