Master Oracle SQL Optimization: Proven Roadmap & Real-World Cases

SQL Optimization Essence

SQL tuning aims to shorten response time, increase system throughput, and boost load capacity by reducing the total resources a statement accesses (S) and increasing the resources processed per unit time (V). Effective tuning balances resource consumption and execution time, avoiding blind parallelism that can exhaust system resources.

SQL Optimization Road Map

2.1 Define optimization goals – Identify problematic SQL via AWR, ASH, or user reports and set clear performance targets.

2.2 Examine the execution plan – Use EXPLAIN PLAN, AUTOTRACE, DBMS_XPLAN, or trace events (10046/10053) to view the plan.

2.3 Verify statistics – Ensure accurate DBMS_STATS collection; missing or stale stats lead to wrong cost estimates.

2.4 Check access structures – Review indexes, partitions, and table fragmentation.

2.5 Review optimizer parameters – Parameters such as optimizer_mode, optimizer_index_cost_adj, _optimizer_mjc_enabled, etc., can dramatically affect plan choice.

2.6 Identify SQL coding issues – Simplify statements, replace UNION with UNION ALL when duplicates are not a concern, avoid unnecessary functions, and follow PL/SQL best practices.

2.7 Recognize optimizer limitations – Certain constructs (e.g., OR combined with semi‑joins) may force sub‑optimal plans.

Real‑World Optimization Cases

A high‑CPU query (≈72% of CPU) was traced to a TABLE function that always reported 8,168 rows, causing a costly HASH JOIN. By confirming the actual row count (≈200‑300) and applying hints such as CARDINALITY or USE_NL, the plan switched to NESTED LOOPS with index access, improving execution time by dozens of times and reducing host CPU usage from 47% to 23% even with a 50% increase in execution count.

Before rewrite (image omitted):

After rewrite with cardinality hint (image omitted):

Acquiring Accurate Execution Plans

Key methods include:

EXPLAIN PLAN – estimated plan.

AUTOTRACE – quick statistics with estimated plan.

DBMS_XPLAN.DISPLAY_CURSOR – real plan with actual rows, buffer gets, and elapsed time.

EVENT 10053 – optimizer behavior.

EVENT 10046 – wait events and bind values.

Example to view full statistics:

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats last'));

Comparing estimated rows (E‑ROWS) with actual rows (A‑ROWS) quickly reveals stale statistics or mis‑estimated joins.

Optimizer Parameters, Features, and Bugs

Version‑specific features such as Adaptive Cursor Sharing (ACS) can introduce plan instability. Disabling a problematic feature may be necessary:

ALTER SYSTEM SET "_optimizer_extended_cursor_sharing_rel"='NONE';

Other influential parameters include _optimizer_cost_based_transformation and _optimizer_squ_bottomup. Restoring their default values (e.g., linear for CBQT) often resolves unexpected full scans after an upgrade.

Additional Tuning Techniques

Use hints: USE_NL, CARDINALITY, OPT_ESTIMATE, DYNAMIC_SAMPLING.

Rewrite OR‑combined semi‑joins as UNION/UNION ALL to avoid FILTER operators that force HASH JOINs.

Collect statistics with DBMS_STATS.GATHER_TABLE_STATS using FOR ALL COLUMNS SIZE REPEAT to preserve existing histograms.

Prefer index‑based access for selective predicates; composite indexes should start with the most frequently used equality columns.

Ultimately, successful SQL tuning combines accurate statistics, clean query structure, appropriate optimizer settings, and the right access paths. Simpler statements are not automatically fast, and complex statements are not inherently slow—what matters is how the optimizer interprets them.